topic Re: Creating columns out of rows in SAS Programming

Creating columns out of rows

Amanda_Lemon — Wed, 20 Jun 2018 20:19:50 GMT

Hello,

I have a data set that looks like this: each person (ID) was tested twice: at Time 1 and at Time 2 (so there are two rows for each ID). Each person also has its gender and race (these values will not differ for the same person).

ID Time TestScore Gender Race

1 1 5 1 2

1 2 7 1 2

2 1 3 2 4

2 2 8 2 4

3 1 1 1 3

3 2 4 1 3

Now, I want my data set to look like that:

ID TestScore1 TestScore2 Gender Race

1 5 7 1 2

2 3 8 2 4

3 1 4 1 3

I ran out of ideas to try... Is there an easy way to create a data set in this form?

Thank you in advance.

Re: Creating columns out of rows

Reeza — Wed, 20 Jun 2018 20:26:17 GMT

PROC TRANSPOSE? Use ID, Gender, Race as your BY variables.

@Amanda_Lemon wrote:

Hello,

I have a data set that looks like this: each person (ID) was tested twice: at Time 1 and at Time 2 (so there are two rows for each ID). Each person also has its gender and race (these values will not differ for the same person).

ID Time TestScore Gender Race

1 1 5 1 2

1 2 7 1 2

2 1 3 2 4

2 2 8 2 4

3 1 1 1 3

3 2 4 1 3

Now, I want my data set to look like that:

ID TestScore1 TestScore2 Gender Race

1 5 7 1 2

2 3 8 2 4

3 1 4 1 3

I ran out of ideas to try... Is there an easy way to create a data set in this form?

Thank you in advance.

Re: Creating columns out of rows

Reeza — Wed, 20 Jun 2018 20:26:18 GMT

PROC TRANSPOSE? Use ID, Gender, Race as your BY variables.

@Amanda_Lemon wrote:

Hello,

I have a data set that looks like this: each person (ID) was tested twice: at Time 1 and at Time 2 (so there are two rows for each ID). Each person also has its gender and race (these values will not differ for the same person).

ID Time TestScore Gender Race

1 1 5 1 2

1 2 7 1 2

2 1 3 2 4

2 2 8 2 4

3 1 1 1 3

3 2 4 1 3

Now, I want my data set to look like that:

ID TestScore1 TestScore2 Gender Race

1 5 7 1 2

2 3 8 2 4

3 1 4 1 3

I ran out of ideas to try... Is there an easy way to create a data set in this form?

Thank you in advance.

Re: Creating columns out of rows

novinosrin — Wed, 20 Jun 2018 20:31:53 GMT

data have;
input ID  Time  TestScore Gender Race;
cards;
1       1            5              1        2
1       2            7              1        2
2       1            3              2        4
2       2            8              2        4
3       1            1              1        3
3       2            4              1        3
;
run;

proc transpose data=have out=want(drop=_:) prefix=testscore;
by id gender race;
var TestScore;
run;

Re: Creating columns out of rows

Amanda_Lemon — Wed, 20 Jun 2018 20:55:24 GMT

It works! Thank you!!

A follow-up question: I am not quite sure which of my variables are attributes of a person (i.e., don't vary within a person between Time 1 and Time 2) and which are attributes of a test (different values for Time 1 and Time 2 for the same person). Is there an easy way to find out which variables are which?

The best option I found so far is to split the file into two: one file with Time 1 values and another file with Time 2 values, and then use proc compare. It gives me a table with unequal values... but I thought maybe there is a way that I can get a LIST of variables with unequal values and a LIST of variables with equal values so that it's easier to copy-paste in proc transpose? (I have about 600 variables, so I am really looking to automatize this process...).

Any ideas would be highly appreciated!

Re: Creating columns out of rows

Reeza — Wed, 20 Jun 2018 20:57:01 GMT

Working on a very similar problem at the moment. I test it out by checking if the max=min for the variables in question across the person ID.

This is easier done in SQL than a data step because PROC SQL will allow the max/min functions to be used on character variables.

Re: Creating columns out of rows

novinosrin — Wed, 20 Jun 2018 21:21:09 GMT

You could play with this approach

proc means data=have noprint;
by id;
var _all_;
output out=want(drop= _:) std=;
run;
proc transpose data=want(obs=1) out=w;
run;
proc sql;
select _name_ into :by_group separated by ' '
from w
where col1 = 0;

select _name_ into :tranpose_var separated by ' '
from w
where col1 ne  0;

quit;
/*by group and transpose lists*/
%put &by_group;
%put &tranpose_var;

Re: Creating columns out of rows

Amanda_Lemon — Tue, 03 Jul 2018 13:45:04 GMT

So, this code works only if I have 1 test. But now I have many tests... So, I thought maybe I can do this code for all of my test variables separately and then just merge the data sets.

Here is what I came up with so far. The problem is that I don't know how to assign names to the "want" data set so that each newly created want has its unique name, e.g., want_Test1, want_Test2, etc. (So that I can merge them at the end by the ID and demographic variables). The same problem with COL1 and COL2 that are in each want data set -- I need to rename COL1 into Test1_1 and COL2 into Test1_2 in the first data set, COL1 into Test2_1 and COL2 into Test2_2 in the second data set, etc. Could someone please help me with that?

data varset_test;
length test_name $40;
do test_name = 'Test1', 'Test2', 'Test3';
     output;
end;
run;
%macro trans(test=);
proc transpose data=have out=want(drop=_:);
by ID Race Gender Age;
var &test;
run;
%mend trans;
data new;
set varset_test;
call execute('%trans(test='||strip(test_name)||');');
run;

Thank you in advance.

P.S. Maybe there is an easier way to do transposing for multiple test variables than what I am trying to do?

Re: Creating columns out of rows

novinosrin — Tue, 03 Jul 2018 14:03:29 GMT

Can you please provide us a full comprehensive sample of your dataset and an output sample to that input sample that is great representative plz? This would help in focusing on getting to develop a solution in one go and in full.

Re: Creating columns out of rows

Amanda_Lemon — Tue, 03 Jul 2018 14:19:04 GMT

Sure! So, the first table below is what I have: People were tested twice, and for each testing time there is a separate row for each person. Test scores are different between rows within a person but demographic variables are the same.

ID	Time	Gender	Race	Age	Test1	Test2	Test3
1	1	0	1	22	50	70	50
1	2	0	1	22	60	75	45
2	1	1	3	31	35	60	30
2	2	1	3	31	80	65	60
3	1	1	5	18	25	85	50
3	2	1	5	18	45	90	40
4	1	1	1	42	10	20	80
4	2	1	1	42	60	70	50
5	1	0	2	21	50	60	60
5	2	0	2	21	55	40	80
6	1	1	2	36	70	40	15
6	2	1	2	36	80	45	30

The second table is what I want: to have one row per person and to have two variables for each test -- one variable for the test at Time 1 and another variable for the test at Time 2. And like that for each test.

ID	Gender	Race	Age	Test1_1	Test1_2	Test2_1	Test2_2	Test3_1	Test3_2
1	0	1	22	50	60	70	75	50	45
2	1	3	31	35	80	60	65	30	60
3	1	5	18	25	45	85	90	50	40
4	1	1	42	10	60	20	70	80	50
5	0	2	21	50	55	60	40	60	80
6	1	2	36	70	80	40	45	15	30

Re: Creating columns out of rows

novinosrin — Tue, 03 Jul 2018 14:55:41 GMT

@Amanda_Lemon Sorry for the little delay as i was away for my morning coffee. Hmm Thank you for better sample and clear requirement. Very straight forward indeed with a double transpose-->

data have;
input ID	Time	Gender	Race	Age	Test1	Test2	Test3;
datalines;
1	1	0	1	22	50	70	50
1	2	0	1	22	60	75	45
2	1	1	3	31	35	60	30
2	2	1	3	31	80	65	60
3	1	1	5	18	25	85	50
3	2	1	5	18	45	90	40
4	1	1	1	42	10	20	80
4	2	1	1	42	60	70	50
5	1	0	2	21	50	60	60
5	2	0	2	21	55	40	80
6	1	1	2	36	70	40	15
6	2	1	2	36	80	45	30
;
proc transpose data=have out=temp;
by id  gender race age time;
var test:;
run;
proc sort data=temp;
by id gender race age _name_ time;
run;
proc transpose data=temp out=want delim=_;
by id gender race age  ;
var col1;
id _name_ time;
run;

proc transpose data=temp out=want(drop=_name_) delim=_;
by id gender race age  ;
var col1;
id _name_ time;
run;

Re: Creating columns out of rows

Amanda_Lemon — Tue, 03 Jul 2018 15:00:09 GMT

Oh WOW! So easy! Thank you so much!!!

Re: Creating columns out of rows

novinosrin — Tue, 03 Jul 2018 15:03:44 GMT

@Amanda_Lemon There you go, the more clearer the INPUT sample and the WANTED output sample, it makes it easy for all of us. If you are satisfied with answer, may i request to mark the answer that you accept and close the thread plz. Thank you!

Re: Creating columns out of rows

Amanda_Lemon — Thu, 05 Jul 2018 03:23:28 GMT

Ran into a problem... It appears that if at least one of my test variables is a character variable, then the output of proc transpose for ALL test variables would be character. Is there any way to prevent that from happening? I need my numeric test variables to continue being numeric in the output. Below is the kind of input data set that I am talking about:

data have;
input ID	Time	Gender	Race	Age	Test1	Test2	Test3 Test4 $;
datalines;
1	1	0	1	22	50	70	50  A
1	2	0	1	22	60	75	45  B
2	1	1	3	31	35	60	30  A
2	2	1	3	31	80	65	60  C
3	1	1	5	18	25	85	50  C
3	2	1	5	18	45	90	40  A
4	1	1	1	42	10	20	80  C
4	2	1	1	42	60	70	50  D
5	1	0	2	21	50	60	60  F
5	2	0	2	21	55	40	80  B
6	1	1	2	36	70	40	15  F
6	2	1	2	36	80	45	30  C
;
proc transpose data=have out=temp;
by id  gender race age time;
var test:;
run;
proc sort data=temp;
by id gender race age _name_ time;
run;
proc transpose data=temp out=want delim=_;
by id gender race age  ;
var col1;
id _name_ time;
run;

proc transpose data=temp out=want(drop=_name_) delim=_;
by id gender race age  ;
var col1;
id _name_ time;
run;

Re: Creating columns out of rows

novinosrin — Thu, 05 Jul 2018 03:26:57 GMT

Hi @Amanda_Lemon Can i respond to you soon as get to my college lab tomorrow morning chicago time. I'm afraid i dont have SAS software at home and it is 10:25pm here. Meanwhile, Please try and make sure you provide us the best sample you can.

Good night for now and will look into it first thing in the morning.

Re: Creating columns out of rows

Amanda_Lemon — Thu, 05 Jul 2018 12:31:43 GMT

Absolutely! Thank you for your help!

The sample above is quite representative, I think. The problem is in adding character variables -- without them, numeric variables transpose fine, but with them, numeric variables become character...

Re: Creating columns out of rows

novinosrin — Thu, 05 Jul 2018 14:12:39 GMT

@Amanda_Lemon Good morning, Please find the below fix

data have;
input ID	Time	Gender	Race	Age	Test1	Test2	Test3 Test4 $;
datalines;
1	1	0	1	22	50	70	50  A
1	2	0	1	22	60	75	45  B
2	1	1	3	31	35	60	30  A
2	2	1	3	31	80	65	60  C
3	1	1	5	18	25	85	50  C
3	2	1	5	18	45	90	40  A
4	1	1	1	42	10	20	80  C
4	2	1	1	42	60	70	50  D
5	1	0	2	21	50	60	60  F
5	2	0	2	21	55	40	80  B
6	1	1	2	36	70	40	15  F
6	2	1	2	36	80	45	30  C
;
/*separate the needed the vars by read and splitting into 2 sets chars and nums with by group vars*/
data chars(keep=id  gender race age time _char_) nums(keep=id  gender race age time _numeric_);
set have(keep=id  gender race age time test:);
run;
/*parse the nums */
proc transpose data=nums out=temp;
by id  gender race age time;
var test:;
run;
proc sort data=temp;
by id gender race age _name_ time;
run;
proc transpose data=temp out=want1(drop=_name_) delim=_;
by id gender race age  ;
var col1;
id _name_ time;
run;
/*parse the chars */
proc transpose data=chars out=temp;
by id  gender race age time;
var test:;
run;
proc sort data=temp;
by id gender race age _name_ time;
run;
proc transpose data=temp out=want2(drop=_name_) delim=_;
by id gender race age  ;
var col1;
id _name_ time;
run;
/*combine the tranposed chars and nums into one*/
data final_want;
merge want1 want2;
run;

Re: Creating columns out of rows

Reeza — Thu, 05 Jul 2018 14:52:42 GMT

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

The macro on this page will do what you want, and it's pre-packaged. Worth learning how to use.

Re: Creating columns out of rows

Amanda_Lemon — Fri, 06 Jul 2018 02:40:48 GMT

@novinosrin and @Reeza, thank you both! I understood both approaches. Thanks again for your help!

Re: Creating columns out of rows

Amanda_Lemon — Fri, 06 Jul 2018 02:43:32 GMT

One more question -- is there a similarly easy way to do the reverse procedure, i.e., transform a wide data set into a tall one?