topic Restructure the table in SAS Data Management

Restructure the table

Ujjawal — Mon, 05 May 2014 09:58:43 GMT

Hi SAS Experts,

I have a dataset consisting three variables - Questions, Dummy, Score. The variable "Dummy" consists of two values - either 0 or 1. I want to restructure the dataset.

The current data set is shown below -

Questions	Dummy	Score
q7	0	-0.17
q7	1	0.085
q8	0	-0.24
q8	1	0.085
q9	0	-0.19
q9	1	0.07
q10	0	-0.22
q10	1	0.077
q11	0	-0.21
q11	1	0.043
q12	0	-0.28
q12	1	0.08

The desired formatted data is shown below -

Questions	0	1
q7	-0.166	0.085
q8	-0.242	0.085
q9	-0.187	0.07
q10	-0.222	0.077
q11	-0.206	0.043
q12	-0.278	0.08

Please let me know if you have any query(s). Any help would be greatly appreciated!

Thanks in advance

Re: Restructure the table

SKK — Mon, 05 May 2014 10:50:42 GMT

Use proc transpose..

proc transpose data=have out=want (drop=_name_);

by Questions notsorted;

id Dummy;

var Score;

run;

Re: Restructure the table

pradeepalankar — Mon, 05 May 2014 11:17:06 GMT

try this,

data have;

input Questions :$10. Dummy : Score ;

cards;

q7 0 -0.17

q7 1 0.085

q8 0 -0.24

q8 1 0.085

q9 0 -0.19

q9 1 0.07

q10 0 -0.22

q10 1 0.077

q11 0 -0.21

q11 1 0.043

q12 0 -0.28

q12 1 0.08

;

run;

proc transpose data =have out=want(drop=_name_);

by questions notsorted;

id dummy;

var score;

run;

Re: Restructure the table

Kurt_Bremser — Mon, 05 May 2014 11:44:39 GMT

Depending on the setting of system option VALIDVARNAME, the new variables will be named _0 and _1 (VALIDVARNAME=V7) or 0 and 1 (VALIDVARNAME=ANY), respectively. A variable named 0 needs to be adressed as '0'n in the following code, which may be a hassle.

Re: Restructure the table

LinusH — Tue, 06 May 2014 10:19:30 GMT

May I ask why you want to do this transformation?

Most data manipulation, reporting and analysis is easier to do with your original lay-out.