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 !
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.
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;
Any particular reason the solution has to be SQL?
proc freq data=mytable;
tables var_a*var_b/ list missing;
run;
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.
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 😄
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.