BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
QLi
Fluorite | Level 6 QLi
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

2 REPLIES 2
art297
Opal | Level 21

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
Super User

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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