topic Re: horizontal to vertical in New SAS User

horizontal to vertical

Raj00007 — Thu, 13 Aug 2020 17:58:02 GMT

i have this data,

2001 WALKING AEROBICS TENNIS
2002 FOOTBALL BASKETBALL TENNIS
2003 AEROBICS SWIMMING CYCLING

and i need to convert it to like this, how?

Obs Id Pref Sports
1 2001 1 WALKING
2 2001 2 AEROBICS
3 2001 3 TENNIS
4 2002 1 FOOTBALL
5 2002 2 BASKETBA
6 2002 3 TENNIS
7 2003 1 AEROBICS
8 2003 2 SWIMMING
9 2003 3 CYCLING

is it something to do with looping, arrays or proc transpose?.

please show me code. Thanks

Re: horizontal to vertical

novinosrin — Thu, 13 Aug 2020 18:13:29 GMT

data have;
input year sports_list & $50.;
cards;
2001 WALKING AEROBICS TENNIS
2002 FOOTBALL BASKETBALL TENNIS
2003 AEROBICS SWIMMING CYCLING
;

data want;
 set have;
 do Pref=1 to countw(sports_list);
  sports=scan(sports_list,pref);
  output;
 end;
 keep pref sports;
run;

/*OR*/

data have;
input year (sports1-sports3) (:$30.);
cards;
2001 WALKING AEROBICS TENNIS
2002 FOOTBALL BASKETBALL TENNIS
2003 AEROBICS SWIMMING CYCLING
;

data want;
 set have;
 array t sports1-sports3;
 do Pref=1 to dim(t);
  sports=t(pref);
  output;
 end;
 keep pref sports;
run;

Re: horizontal to vertical

Raj00007 — Thu, 13 Aug 2020 18:39:14 GMT

the solution is missing years.

can you please include years also in the code and rewrite the code again

Re: horizontal to vertical

novinosrin — Thu, 13 Aug 2020 18:45:57 GMT

My apologies. Change your keep statement to-

keep year pref sports;