Hi,
I need to roll up my dataset to individual level. I tried to use proc summary for this but I am getting the result I want
I have
ID flag
1 2
1 2
1 2
1 3
So I used
proc summary data=have nway;
by id;
var flag;
output out=want max=;
run;
I was hoping to make it from long to wide
ID flag_1 flag_2
1 2 3
But using the above proc summary I get
ID flag
1 3
Should I use a different proc? like Proc transpose?
Thanks
I was hoping to make it from long to wide
ID flag_1 flag_2
1 2 3
The simple answer is that PROC SUMMARY does not do this.
If you want the distinct values of FLAG for each ID, I am not really sure of an easy way to get this result. Is that what you want? It's hard to generalize your needs from this simple example, and the words "roll up" are meaningless to me.
For this particular ID value, you could use:
output out=want min=flag_1 max=flag_2;
However, we don't really have enough information about the inputs and desired outputs to propose a better solution. You would need to provide more of a description, with a few examples.
I need to provide more information here as you suggested.
I need a frequency of all flagged variables
So my current dataset have flags for treatment and diagnosis. So patients either have the treatment (yes=1 and no=0) and diagnosis of various illnesses ranging from 0-3 (0=other illnesses or no illness , 1=diabetes 2=hypertension 3=asthma) for example.
Each line represents one claim and I want to get a frequency of each illness category.
So ultimately I want to run proc freq so I can see how many patients have illnesses based on TX categories. I hope this makes sense.
ID TX DX
1 0 1
1 0 0
1 0 2
2 1 1
2 1 3
2 1 0
Based on the added description, it looks like you are heading in this direction:
proc sort data=have out=temp nodupkey;
by id tx dx;
run;
proc freq data=temp;
tables dx * tx / missing list;
run;
The FREQ column in the report gives you the number of patients.
Looks like you want to condense your flag values first into single rows, and then transpose:
data have;
input id flag;
cards;
1 2
1 2
1 2
1 3
;
run;
proc sort data=have;
by id flag;
run;
data int;
set have;
by id flag;
if first.id
then count = 1;
else if first.flag then count + 1;
if last.flag then output;
run;
proc transpose data=int out=want (drop=_name_) prefix=flag_;
by id;
id count;
var flag;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.