Adding rows for missing values when grouping by multiple variables in proc sql

Question

Like the title suggests, I'm trying to add rows for observations whose value is 0 in proc sql.

When I do a frequency check, I get this:

I do a proc sql to group by my respective categories using this code which fails to account for 0 observations for some combinations show in the proc freq:

proc sql; 
create table count7 as
select distinct status as d_cat, program, count(status) as count_16, 'm_cit' as m_type
from cy16
group by status, program; 
quit;

which produces: Have

I want to create rows from the Proc Freq that produces rows for those categories that have 0 observations. Can this be achieved in a proc sql or would I need an additional data step too?

Do you just want to mimic PROC FREQ? (If so why not just use PROC FREQ? ) Or do you need to include values of STATUS or PROGRAM that do not appear in CY16? If the later then what is the source of the list of all values of STATUS and all values of PROGRAM? — Tom, Nov 01 '19 at 17:56
I'm basically creating a list of variables (status and others) that is categorized by program. All these values come from the original dataset but I'm stacking all these variables (status and others) in one dataset. Even if I do proc freq I still do not get these 0 variables to show up in the new dataset. — a_swoosh, Nov 01 '19 at 18:06

Tom · Accepted Answer · 2019-11-01T18:22:39.897

You can use the SPARSE option on the TABLES statement to get PROC FREQ to include the empty cells. In PROC SQL you will need to make a shell dataset of all possible combinations.

Let's get some sample data with at least one empty cell:

data class;
 set sashelp.class;
 where age in (15 16);
run;

We can use PROC FREQ to generate counts.

proc freq data=class;
 tables age*sex/noprint sparse out=count1(drop=percent);
run;

Or PROC SQL:

proc sql ;
create table count2 as
select b.age,c.sex,coalesce(a.count,0) as COUNT 
  from (select age,sex,count(*) as COUNT 
        from class group by age,sex ) a
  full join 
       ((select distinct age from class) b
       ,(select distinct sex from class) c
       )
  on a.age=b.age and a.sex=c.sex
  order by age,sex
;
quit;

You can also use the CLASS statement and the COMPLETETYPES and NWAY options in PROC SUMMARY.

proc summary data=class complettypes nway;
 class age sex ;
 output out=count3(drop=_type_ rename=(_freq_=COUNT));
run;

Adding rows for missing values when grouping by multiple variables in proc sql

1 Answers1