Displaying 'NA' when it's an empty row in SAS

Question

This is the given data

Company_NO Hierarchy_1 Hierarchy_2
1234       Insurance   A
1234       Insurance   A
1234       Auto        B
5678       Claims      B
5678       Claims      B
5678       New         C

In the above table, the column hierarchy_2 has three distinct values A,B,C. In the above example, for company_no=1234, since there is no row for hierarchy_2='C', that row should still appear. That is company_no=1234, hierarchy_1='NA', hierarchy_2='C'

Expected Output:

Company_NO Hierarchy_1 Hierarchy_2
1234       Insurance   A
1234       Insurance   A
1234       Auto        B
1234       NA          C
5678       Claims      B
5678       Claims      B
5678       New         C
5678       NA          A

As you can see above, an extra row is added with hierarchy_1='NA' whenever there is an empty row. Please help! Thank you!

What did you try? Did you look at this question/answer? https://stackoverflow.com/questions/25646976/creating-all-possible-combinations-in-a-table-using-sas — Tom, Jul 15 '20 at 20:21
Do you want a data set like this or is this for reporting purposes? — PeterClemmensen, Jul 15 '20 at 20:28
PRELOADFMT or CLASSDATA are what you're looking for typically. — Reeza, Jul 15 '20 at 21:29
What if a given data set does not contain all possible `Hierarchy_2` values? For example, suppose, in a greater context of allowed values, there may be `Hierarchy_2='D'` not present in either companies `1234` or `5678`. — Richard, Jul 16 '20 at 11:00

Jeppe Rømer Juul · Accepted Answer · 2020-07-15T21:25:39.603

One option is to first create all combinations of company_no and hierarchy_2 and then left join your dataset on this table:

data have;
length company_no 8. hierarchy_1 hierarchy_2 $20;
input company_no hierarchy_1 $ hierarchy_2 $;
datalines;
1234     Insurance   A
1234     Insurance   A
1234     Auto        B
5678     Claims      B
5678     Claims      B
5678     New         C
;
run;

proc sql;
   create table want as 
      select a.company_no
            ,case when missing(c.hierarchy_1) then "NA"
                  else c.hierarchy_1
            end as hierarchy_1
            ,b.hierarchy_2
      from (select distinct company_no from have) as a
      cross join (select distinct hierarchy_2 from have) as b
      left join have as c
         on a.company_no = c.company_no and
            b.hierarchy_2 = c.hierarchy_2
   ;
quit;

You are awesome! Thank you so much! – Chug Jul 16 '20 at 14:17 — Chug, Jul 16 '20 at 14:17

Displaying 'NA' when it's an empty row in SAS

1 Answers1