DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

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

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 !


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 19,855

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

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.

View solution in original post


All Replies
Respected Advisor
Posts: 4,930

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

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;

 
PG
Super User
Posts: 11,343

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

Any particular reason the solution has to be SQL?

 

proc freq data=mytable;

   tables var_a*var_b/ list missing;

run;

Solution
3 weeks ago
Super User
Posts: 19,855

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

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.

Occasional Contributor
Posts: 19

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

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 Smiley Very Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 100 views
  • 4 likes
  • 4 in conversation