How to append variables?

[pmesh]

Hello,

The data I have is as follows:

ID	Score1	Score2	Score3
A	5	2	8
B	4	3	7
C	9	6	1

I want to convert it as follows:

ID	Score
A	5
B	4
C	9
A	2
B	3
C	6
A	8
B	7
C	1

The way I'm doing it pretty crude:

data want;

     set have (keep = id score1 rename=(score1=score)

          have (keep = id score2 rename=(score2=score)

          have (keep = id score3 rename=(score3=score);

run;

This could be tedious with say 50 variables. Is there any sophisticated way? Can proc transpose handle this?

Thanks,

P

[Kurt_Bremser]

One option would be to create the list of input datasets automatically with a macro loop. proc transpose would (IMHO) not be able to create your A,B,C,A,B,C order. It would rather give you A,A,A,B,B,B,C,C,C instead.

If the order of output records doe not matter, usage of an array and looping through that would also come to mind.

[pmesh]

Thanks for your reply. No, order is not important here.

[Kurt_Bremser · Accepted solution]

Then do the following:

data want (keep=id score);

set have;

array scores {} score1-score3; * or use _numeric_ if all numeric variables are to be taken for output;

do i = 1 to dim(scores);

  score = scores{i};

  output;

end;

run;

[pmesh]

Superb. Worked perfectly!

[RW9]

Hi,

data have;

  infile datalines;

  input ID $ Score1 Score2 Score3;

datalines;

A 5 2 8

B 4 3 7

C 9 6 1

;

run;

data _null_;

  set sashelp.vcolumn (where=(libname="WORK" and memname="HAVE" and substr(name,1,5)="Score")) end=last;

  if _n_=1 then call execute('data want; set ');

  call execute(' work.have (keep=id '||strip(name)||' rename=('||strip(name)||'=Score))');

  if last then call execute(';run;');

run;

[Community_Help]

Hi - thanks for being part of Communities on SAS - I noticed this question was asked in the "about communities on SAS" community .. just for greater visibility I'm moving it to the SAS procedures community. Best regards!