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!
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;
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;
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
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.