BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Raitag
Fluorite | Level 6

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

4 REPLIES 4
PGStats
Opal | Level 21

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
ballardw
Super User

Any particular reason the solution has to be SQL?

 

proc freq data=mytable;

   tables var_a*var_b/ list missing;

run;

Reeza
Super User

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
Fluorite | Level 6

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 😄

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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