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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.