Hi all,
I have a large SAS dataset with multiple columns where two in particular are of interest: ID (unique) and subject.
I am trying to count the number of times each ID has an associated subject (see example below).
This is what I have:
ID | Subject |
1 | High |
1 | Med |
1 | Med |
2 | Low |
2 | Low |
3 | High |
3 | Low |
3 | Med |
3 | Med |
This is what I need:
ID | High | Med | Low |
1 | 1 | 2 | 0 |
2 | 0 | 0 | 2 |
3 | 1 | 2 | 1 |
Any suggestions on how I can achieve this in a single data step?
I have tried to do something like this but can't get it to work efficiently:
data need;
set have;
by ID notsorted;
if first.ID and subject='High' then High=0;
High+1;
run;
Any help would be greatly appreciated.
Cheers,
Pete
The suggestion you already received will get you the result you want. However, since you specifically asked for a DATA step solution in a single step, here is how you would go about that:
data want;
set have;
by id;
if first.id then do;
high = 0;
med = 0;
low = 0;
end;
if subject='High' then high + 1;
else if subject='Med' then med + 1;
else if subject='Low' then low + 1;
drop subject;
if last.id;
run;
data have;
input ID Subject$;
cards;
1 High
1 Med
1 Med
2 Low
2 Low
3 High
3 Low
3 Med
3 Med
;
proc freq data= have;
by id;
tables subject/out=_have(drop=percent);
run;
proc transpose data=_have out=want(drop=_label_);
by id;
var count;
id subject;
run;
The suggestion you already received will get you the result you want. However, since you specifically asked for a DATA step solution in a single step, here is how you would go about that:
data want;
set have;
by id;
if first.id then do;
high = 0;
med = 0;
low = 0;
end;
if subject='High' then high + 1;
else if subject='Med' then med + 1;
else if subject='Low' then low + 1;
drop subject;
if last.id;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.