horizontal to vertical

[Raj00007]

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

[novinosrin · Accepted solution]

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;

[Raj00007]

the solution is missing years.

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

[novinosrin]

My apologies. Change your keep statement to-

keep year pref sports;