proc summary to roll up data

Reply
Contributor
Posts: 29

proc summary to roll up data

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

Respected Advisor
Posts: 2,661

Re: proc summary to roll up data

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.

--
Paige Miller
Super User
Posts: 6,543

Re: proc summary to roll up data

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. 

Contributor
Posts: 29

Re: proc summary to roll up data

Posted in reply to Astounding

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

Super User
Posts: 6,543

Re: proc summary to roll up data

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.

 

 

Super User
Posts: 9,611

Re: proc summary to roll up data

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 5 replies
  • 932 views
  • 0 likes
  • 4 in conversation