Collapsing data

[QLi]

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

[art297 · Accepted solution]

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;

[Ksharp]

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