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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.