topic Re: create multiple rows from single row in SAS Programming

create multiple rows from single row

Feyng819 — Tue, 27 Feb 2024 16:00:56 GMT

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

Re: create multiple rows from single row

PaigeMiller — Tue, 27 Feb 2024 16:13:34 GMT

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

Re: create multiple rows from single row

FreelanceReinh — Tue, 27 Feb 2024 16:15:46 GMT

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;

Re: create multiple rows from single row

Tom — Tue, 27 Feb 2024 16:17:28 GMT

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;

Re: create multiple rows from single row

Tom — Tue, 27 Feb 2024 16:20:09 GMT

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.

Re: create multiple rows from single row

Feyng819 — Tue, 27 Feb 2024 16:20:54 GMT

Thanks for your help! It works!

Re: create multiple rows from single row

Feyng819 — Tue, 27 Feb 2024 16:21:06 GMT

Thanks a lot this also works for me

Re: create multiple rows from single row

Feyng819 — Tue, 27 Feb 2024 16:21:29 GMT

Thanks a lot!