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
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;
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.
Thanks for your reply. No, order is not important here.
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;
Superb. Worked perfectly!
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;
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!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.