proc summary to roll up data

[leahcho]

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

[PaigeMiller]

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.

[Astounding]

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. 

[leahcho]

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

[Astounding]

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.

[Kurt_Bremser]

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;