Unpivot a recird to multiple records

[Suminder]

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

[r_behata]

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;

[ballardw]

---

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