topic Re: Unpivot a recird to multiple records in SAS Programming

Unpivot a recird to multiple records

Suminder — Fri, 29 Nov 2019 00:00:52 GMT

Hi Guys,

I have following datasets
dataset1
nameid name    sportsPlayed
1 sam cricket|football|badminton
2 ravi    cricket|chess

The output sas table has to be
outputDATASET
nameid name sportsPlayed
1 sam cricket
1 sam    football
1 sam    badminton
2 ravi cricket
2 ravi chess

I tried using sort and 'by' but ...

Thanks in advance

Re: Unpivot a recird to multiple records

r_behata — Fri, 29 Nov 2019 00:49:58 GMT

data have;
input nameid  name $ sportsPlayed :$50.;
cards;
1 sam cricket|football|badminton
2 ravi cricket|chess
run;


data want;

set have(rename=(sportsPlayed=sportsPlayed_));
length sportsPlayed $15.;
do _n_=1 to countw(sportsPlayed_,'|');
	sportsPlayed=scan(sportsPlayed_,_n_,'|');
	output;
end;

drop sportsPlayed_;
run;

Re: Unpivot a recird to multiple records

ballardw — Fri, 29 Nov 2019 17:20:52 GMT

@Suminder wrote:

Hi Guys,

I have following datasets
dataset1
nameid name    sportsPlayed
1 sam cricket|football|badminton
2 ravi    cricket|chess

The output sas table has to be
outputDATASET
nameid name sportsPlayed
1 sam cricket
1 sam    football
1 sam    badminton
2 ravi cricket
2 ravi chess

I tried using sort and 'by' but ...

Thanks in advance

So why did you ask how to put them in that hard to use format in the first place in this topic https://communities.sas.com/t5/SAS-Programming/Pivot-multiple-values-of-same-column-into-one-column-based-on/m-p/607488#M176625