How to get Subgroups with Null Frequency with PROC SQL COUNT(*) GROUP BY

[Raitag]

Hello !

Hope you're doing well guys .

I'm working on a  SAS table which contains two categorical variables : Var_a  Var_b  and i m trying to execute the query below via PROC SQL

PROC SQL ; 
SELECT var_a , var_b , COUNT(*) AS Frequency 
FROM MyTable 
GROUP BY var_a , var_b ; 
QUIT ; 

The problem is i do only get subgroups for which Frequency >0

Is there any option for PROC SQL to get all the subgroups even those with FREQUENCY = 0 .

Thanks for Help !

[PGStats]

Use a cross join (cartesian product) to get all class combinations:

proc sql;
select a.sex,
       b.age,
       count(c.name) as n
from 
    (select unique sex from sashelp.class) as a cross join
    (select unique age from sashelp.class) as b left join
    sashelp.class as c on a.sex=c.sex and b.age=c.age
group by a.sex, b.age;
quit;

 

[ballardw]

Any particular reason the solution has to be SQL?

proc freq data=mytable;

   tables var_a*var_b/ list missing;

run;

[Reeza · Accepted solution]

If all values are listed in your data set somewhere you can use PROC FREQ with the SPARSE option. 

If not, then you need to use another solution. Basically if it doesn't exist in the data, how does SAS know it even exists? In this case you can use either CLASSDATA or PRELOADFMT or a more manual solution via DATA STEP or PROC SQL.

[Raitag]

Thank you so much for your help.

Here what i did  : I used proc summary with  completetypes option with adding preloadfmt option to the class statement and it worked perfectly 😄