topic How to append variables? in SAS Procedures

How to append variables?

pmesh — Wed, 11 Mar 2015 15:08:12 GMT

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,

Re: How to append variables?

Kurt_Bremser — Wed, 11 Mar 2015 15:17:59 GMT

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.

Re: How to append variables?

pmesh — Wed, 11 Mar 2015 15:23:19 GMT

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

Re: How to append variables?

RW9 — Wed, 11 Mar 2015 15:28:01 GMT

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;

Re: How to append variables?

Community_Help — Wed, 11 Mar 2015 15:49:05 GMT

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!

Re: How to append variables?

Kurt_Bremser — Wed, 11 Mar 2015 15:50:30 GMT

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;

Re: How to append variables?

pmesh — Wed, 11 Mar 2015 16:35:35 GMT

Superb. Worked perfectly!