topic Collapsing data in SAS Programming

Collapsing data

QLi — Tue, 25 Oct 2011 19:51:10 GMT

I have a data set need to collaspse.

Then current data looks the following:

ID Type Code Amt

234 RC1 A 102

234 RC1 C 101

234 RC1 B 104

238 RC1 A 125

238 RC1 B 126

238 RC2 C 127

Want data looks the following:

ID Type Code1(amt Max) Amt1 Code2 Amt2 Code3(amt min) Amt3(amt min)

234 RC1 B 104 A 102 C 101

238 RC1 B 126 A 125 .

238 RC2 C 127 . .

Do you have any idea to do it?

Thanks

Collapsing data

art297 — Tue, 25 Oct 2011 20:14:15 GMT

There are probably many ways to do it. I, personally, would sort the data by amt, get one needed value via proc sql, and then transpose the file using proc summary. E.g.:

data have;

input ID Type $ Code $ Amt;

cards;

234 RC1 A 102

234 RC1 C 101

234 RC1 B 104

238 RC1 A 125

238 RC1 B 126

238 RC2 C 127

;

proc sort data=have;

by decending amt;

run;

proc sql noprint;

select max(obs) into :obs

from ( select count(*) as obs

from have

group by id,type )

;

quit;

proc summary nway data=have missing;

class ID type;

output out = want(drop=_type_ _freq_)

idgroup(out[&obs](code amt)=);

run;

Re: Collapsing data

Ksharp — Wed, 26 Oct 2011 04:50:48 GMT

Art has already given a excellent solution.

It is another way.

data have;
  input ID $ Type $ Code $ Amt;
  cards;
234      RC1       A        102
234     RC1        C        101
234      RC1      B         104
238    RC1       A         125
238    RC1       B         126
238    RC2       C         127
;
run;
proc sort data=have; by id type descending amt;run;
data _null_;
 set have end=last;
 by id type;
 if _n_ eq 1 then call execute('data want;');
 if first.type then do;call execute('id="'||id||'";'||'type="'||type||'";'); count=0;end;
 count+1;
 call execute('code'||strip(count)||'="'||code||'";'||'amt'||strip(count)||'='||amt||';');
 if last.type then call execute('output;call missing(of _all_);');
 if last then call execute('run;');
run;

Ksharp