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
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;
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;
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
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.