Solved
Contributor
Posts: 59

# Collapsing data

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

Accepted Solutions
Solution
‎10-25-2011 04:14 PM
PROC Star
Posts: 8,169

## Collapsing data

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 bs

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;

All Replies
Solution
‎10-25-2011 04:14 PM
PROC Star
Posts: 8,169

## Collapsing data

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 bs

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;

Super User
Posts: 10,787

## Re: Collapsing data

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

🔒 This topic is solved and locked.