topic Proc sql count for multiple variables in SAS Procedures

Proc sql count for multiple variables

luch25 — Fri, 18 Sep 2020 13:37:58 GMT

Hi,

I need to be able to count the number of individuals within a state that have a certain option. I know how to do this in proc sql for a single option, but not sure how to construct this if I want counts by states for multiple (i.e., 5) options. I could merge 5 separate proc sql, but I'm wondering if there's a way to do this in a single step.

Here's sample SAS code for the dataset:

data have;
input id state option1 option2 option3 option4 option5;
cards;
1 1 1 0 1 1 1
2 2 0 0 1 1 1
3 3 0 1 0 1 0
4 1 1 1 0 1 1
5 2 0 1 1 0 1
6 3 1 1 1 1 1
7 1 1 0 0 0 1
8 2 0 1 0 1 0
9 3 1 1 1 1 1
;

And this is the dataset that I'm trying to obtain. Note that the counts of individuals are by each state.

state option1 option2 option3 option4 option5
1 3 1 1 2 3
2 0 2 2 2 2
3 2 3 2 3 2

Thanks for any advice that you have!

Re: Proc sql count for multiple variables

novinosrin — Fri, 18 Sep 2020 13:53:03 GMT

data have;
input id state option1 option2 option3 option4 option5;
cards;
1 1 1 0 1 1 1
2 2 0 0 1 1 1
3 3 0 1 0 1 0
4 1 1 1 0 1 1
5 2 0 1 1 0 1
6 3 1 1 1 1 1
7 1 1 0 0 0 1
8 2 0 1 0 1 0
9 3 1 1 1 1 1
;

proc summary data=have nway;
 class state;
 var option1-option5;
 output out=want(drop=_:) sum=;
run;

proc print noobs data=want;
run;

Re: Proc sql count for multiple variables

ballardw — Fri, 18 Sep 2020 14:05:19 GMT

Since 0 is a value you apparently do not actually want a Count. If all of the values are 0/1 then use SUM and group by state

Re: Proc sql count for multiple variables

luch25 — Fri, 18 Sep 2020 14:26:24 GMT

Thank you! This works perfectly. Should have thought of proc summary