Pivot multiple values of same column into one column based on another column

[Suminder]

Hi Guys,

I have following datasets

dataset1

nameid      name         sportsPlayed       howGood

 1               sam           cricket                   vg

 1               sam           football                    g

 1               sam           badminton               a

2                ravi            cricket                     vg

2                ravi            chess                      vg

The output sas table has to be

outputDATASET

nameid       name      sportsPlayed

1                  sam       cricket|football|badminton

2                  ravi        cricket|chess

I tried using sort and 'by' but seems like I am not getting the idea correctly...

Thanks in advance

[Tom · Accepted solution]

Assuming that NAME is just a decode of NAMEID you could do something like this:

data want ;
   set have ;
   by nameid ;
   length list_of_SportsPlayed $200 ;
   if first.nameid then list_of_SportsPlayed = SportsPlayed ;
   else list_of_SportsPlayed =catx('|',list_of_SportsPlayed ,SportsPlayed);
   retain list_of_SportsPlayed ;
   if last.nameid;
   drop SportsPlayed HowGood;
run;

[Suminder]

hi Tom,

Thanks for the quick reply!

It is returning 5 observations whereas required is just two observations....pls have another look...

[Tom]

You forgot to only keep the last observation per group.

[PeterClemmensen]

data have;
input nameid name $ sportsPlayed :$10. howGood $;
datalines;
1 sam cricket vg
1 sam football g
1 sam badminton a
2 ravi cricket vg
2 ravi chess vg
;

data want(drop=_: howgood);
   do until (last.nameid);
      set have(rename=sportsPlayed=_sportsPlayed);
      by nameid;
      length sportsPlayed $ 200;
      sportsPlayed=catx('|', sportsPlayed, _sportsPlayed);
   end;
run;

[ballardw]

If you like your final Sportsplayed list to possibly have the values appear in a semi-consistent order:

data have;
input nameid name $ sportsPlayed :$10. howGood $;
datalines;
1 sam cricket vg
1 sam football g
1 sam badminton a
2 ravi cricket vg
2 ravi chess vg
;

proc transpose data= have out=trans (drop=_name_);
by nameid name;
var sportsplayed;
run;

data want;
   set trans;
   array c col: ;
   call sortc (of c(*));
   sportsplayed = catx('|',of c(*));
   keep nameid name sportsplayed;
run;

This would have two (or more people) with chess and cricket all have the same "chess|cricket" where processing in order might have some with "chess|cricket" and others with "cricket|chess".