topic Pivot multiple values of same column into one column based on another column in SAS Programming

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

Suminder — Tue, 26 Nov 2019 19:09:19 GMT

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

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

Tom — Tue, 26 Nov 2019 19:13:21 GMT

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;

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

PeterClemmensen — Tue, 26 Nov 2019 19:13:30 GMT

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;

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

Suminder — Tue, 26 Nov 2019 19:56:21 GMT

hi Tom,

Thanks for the quick reply!

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

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

Tom — Tue, 26 Nov 2019 20:27:15 GMT

You forgot to only keep the last observation per group.

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

ballardw — Tue, 26 Nov 2019 22:36:46 GMT

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".