I feel like the solution to this problem is very simple, yet, I can't quite figure out how to accomplish this simply either through PROC SQL or PROC TRANSPOSE. I've searched the forums, but I can't seem to find a simple solution. Currently, the data is arranged where each row is a unique observation tied to a unique id with each test measurement as a column. What I need is to have each measurement (column with test_score1, test_score2, test_score3, etc.)
collapsed down onto the unique id as a row... so multiple test observations are showing as rows instead of as columns.
Data is currently arranged like Table A:
Table A:
ID | test_score1 | test_score2 | test_score3 | test_score4 |
00001 | 30.00 | 30.00 | 31.00 | 27.00 |
00002 | 29.00 | 30.00 | 30.00 | 27.00 |
00003 | 25.00 | 33.00 | 20.00 | 25.00 |
00004 | 26.00 | 35.00 | 36.00 | 19.00 |
00005 | 20.00 | 18.00 | 20.00 | 21.00 |
00006 | 21.00 | 18.00 | 22.00 | 26.00 |
I want to convert the table to look like Table B:
Table B:
ID | measure | score |
00001 | test_score1 | 30.00 |
00001 | test_score2 | 30.00 |
00001 | test_score3 | 31.00 |
00001 | test_score4 | 27.00 |
00002 | test_score1 | 29.00 |
00002 | test_score2 | 30.00 |
00002 | test_score3 | 30.00 |
00002 | test_score4 | 27.00 |
00002 | test_score1 | 25.00 |
00003 | test_score2 | 33.00 |
00003 | test_score3 | 20.00 |
00003 | test_score4 | 25.00 |
00004 | test_score1 | 26.00 |
00004 | test_score2 | 35.00 |
00004 | test_score3 | 36.00 |
00004 | test_score4 | 19.00 |
00005 | test_score1 | 20.00 |
00006 | test_score2 | 18.00 |
00006 | test_score3 | 20.00 |
00006 | test_score4 | 21.00 |
00007 | test_score1 | 21.00 |
00007 | test_score2 | 18.00 |
00007 | test_score3 | 22.00 |
00007 | test_score4 | 26.00 |
Thanks!
proc transpose data=a out=b;
by id;
var test_score:;
run;
Thank you.
One question: I see the ":" after the test_score in the "VAR" statement and I assume that acts as a function to call up all of the vars that start with "test_score". I named each test score simply with a number, but, if I need to transpose a differently named test for each ID, do I simply call each one out, like so?:
proc transpose data=scores
out=test;
by id;
var
ACT_COMP_SCORE
ACT_READ_SCORE
ACT_ENG_SCORE
ACT_MATH_SCORE
SATI_COMP_SCORE
SATI_MATH_SCORE
SATI_VERB_SCORE
SATR_MATH_SCORE
SATR_VERB_SCORE
SCA;
run;
Yes
@eer_seer wrote:
Thank you.
One question: I see the ":" after the test_score in the "VAR" statement and I assume that acts as a function to call up all of the vars that start with "test_score". I named each test score simply with a number, but, if I need to transpose a differently named test for each ID, do I simply call each one out, like so?:
proc transpose data=scores
out=test;
by id;
var
ACT_COMP_SCORE
ACT_READ_SCORE
ACT_ENG_SCORE
ACT_MATH_SCORE
SATI_COMP_SCORE
SATI_MATH_SCORE
SATI_VERB_SCORE
SATR_MATH_SCORE
SATR_VERB_SCORE
SCA;
run;
The list might also be made as
Var Act: SAT: SCA ;
but the order would tend to be alphabetic by variable name and use all variables whose names start with the specific characters. So if you had other variables like ACTBB they would be included. But you could use ACT_: which would skip any variables that didn't have the _.
Or if the variables are contiguous, adjacent column order numbers, you could use:
VAR ACT_COMP_SCORE -- SCA;
Note that is 2 dash characters.
Plus you can mix the lists with both the : for some variables and a -- for others. Plus if you have variable names that have a common name with a numerical suffix you can use Var1 - Var10 as a list which would exclude Var11, Var12, Var13 (if such variables exist).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.