topic Re: Columns to Rows - Specific Example in SAS Programming

Columns to Rows - Specific Example

eer_seer — Fri, 04 Sep 2020 14:32:08 GMT

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!

Re: Columns to Rows - Specific Example

PaigeMiller — Fri, 04 Sep 2020 14:36:36 GMT

proc transpose data=a out=b;
by id;
var test_score:;
run;

Re: Columns to Rows - Specific Example

eer_seer — Fri, 04 Sep 2020 14:52:56 GMT

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;

Re: Columns to Rows - Specific Example

PaigeMiller — Fri, 04 Sep 2020 14:54:45 GMT

Yes

Re: Columns to Rows - Specific Example

ballardw — Fri, 04 Sep 2020 15:12:53 GMT

@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).