create multiple rows from single row

[Feyng819]

Hi I would like to ask that how can I create multiple rows from single row data:
Let’s say I have the following data:
Data have;
Input name$ score1 score2 score3 score4 score5;
Cards;
Leo 1 1 3 3 5
Sean 3 4 . . .
Nathan 4 5 7 . .
; run;

This is what I want;
Data want;
Input name$ score;
Leo 1
Leo 1
Leo 3
Leo 3
Leo 5
Sean 3
Sean 4
Nathan 4
Nathan 5
Nathan 7
;run;
I was trying to use loop function but couldn’t figure it out.. thanks for your help in advance

[PaigeMiller · Accepted solution]

data want;
    set have;
    array s score1-score5;
    do i=1 to dim(s);
        score=s(i);
        output;
    end;
    drop i score1-score5;
run;

[Feyng819]

Thanks for your help! It works!

[FreelanceReinh]

Hi @Feyng819,

You can also use PROC TRANSPOSE:

proc transpose data=have out=want(drop=_: rename=(col1=score) where=(score>.));
by name notsorted;
var score:;
run;

[Feyng819]

Thanks a lot this also works for me

[Tom]

That is what PROC TRANSPOSE was made for.

You can use RENAME= dataset option to change the name it assigns to the new variable to something more meaningful.

proc transpose data=have out=want(rename=(col1=SCORE) );
  by name;
  var score1-score5;
run;

[Feyng819]

Thanks a lot!

[Tom]

No need to LOOP.  You can just write some wallpaper code.

data want;
  set have;
  score=score1;
  output;
  score=score2;
  output;
  .....
run;

You can complete the pattern.