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
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;
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;
hi Tom,
Thanks for the quick reply!
It is returning 5 observations whereas required is just two observations....pls have another look...
You forgot to only keep the last observation per group.
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;
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".
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.