topic Re: How to get Subgroups with Null Frequency with PROC SQL COUNT(*) GROUP BY in SAS Programming

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

Raitag — Thu, 19 Oct 2017 20:46:41 GMT

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 !

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

PGStats — Thu, 19 Oct 2017 21:26:39 GMT

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;

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

ballardw — Thu, 19 Oct 2017 21:27:40 GMT

Any particular reason the solution has to be SQL?

proc freq data=mytable;

tables var_a*var_b/ list missing;

run;

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

Reeza — Fri, 20 Oct 2017 15:13:12 GMT

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.

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

Raitag — Mon, 30 Oct 2017 22:19:36 GMT

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 😄