SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Restructure the table

Reply
Regular Contributor
Posts: 184

Restructure the table

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 -

QuestionsDummyScore
q70-0.17
q710.085
q80-0.24
q810.085
q90-0.19
q910.07
q100-0.22
q1010.077
q110-0.21
q1110.043
q120-0.28
q1210.08

The desired formatted data  is shown below -

Questions01
q7-0.1660.085
q8-0.2420.085
q9-0.1870.07
q10-0.2220.077
q11-0.2060.043
q12-0.2780.08

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

Thanks in advance

Contributor SKK
Contributor
Posts: 35

Re: Restructure the table

Use proc transpose..

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

by Questions notsorted;

id Dummy;

var Score;

run;

Frequent Contributor
Posts: 106

Re: Restructure the table

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;

Super User
Posts: 7,863

Re: Restructure the table

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,438

Re: Restructure the table

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.

Data never sleeps
Ask a Question
Discussion stats
  • 4 replies
  • 335 views
  • 0 likes
  • 5 in conversation