DATA Step, Macro, Functions and more

Collapsing data

Accepted Solution Solved
Reply
Contributor QLi
Contributor
Posts: 59
Accepted Solution

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: 7,471

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 Smiley Surprisedbs

    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;

View solution in original post


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

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 Smiley Surprisedbs

    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,028

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 134 views
  • 3 likes
  • 3 in conversation