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
Use proc transpose..
proc transpose data=have out=want (drop=_name_);
by Questions notsorted;
id Dummy;
var Score;
run;
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;
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.
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.
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.