Hi! I need some help with a reverse transpose or bring wide to long.
i have data like this:
NUM | GR3 | GR4 | GR5 |
1 | 0 | 1 | 0 |
2 | 1 | 0 | 0 |
3 | 0 | 0 | 1 |
want to look like this:
NUM | GRADE | RES |
1 | GR3 | 0 |
GR4 | 1 | |
GR5 | 0 | |
2 | GR3 | 1 |
GR4 | 0 | |
GR5 | 0 | |
3 | GR3 | 0 |
GR4 | 0 | |
GR5 | 1 |
Any guidance is appreciated!
data have;
input NUM GR3 GR4 GR5;
datalines;
1 0 1 0
2 1 0 0
3 0 0 1
;
proc transpose data=have out=want;
by num;
var gr:;
run;
data final_Want;
set want;
by num;
if not first.num then call missing(num);
run;
data have;
input NUM GR3 GR4 GR5;
datalines;
1 0 1 0
2 1 0 0
3 0 0 1
;
proc transpose data=have out=want;
by num;
var gr:;
run;
data final_Want;
set want;
by num;
if not first.num then call missing(num);
run;
A group of us just presented a macro, at SGF this past Tuesday, that does almost exactly what you want. You can download the macro from: https://github.com/gerhard1050/Untranspose-a-Wide-File
Download and run the macro, then run:
%untranspose(data=have, out=want, id=GRADE, id_informat=$3., var_first=N/A,var=RES, by=num)
The only thing it does contrary to your stated WANT is that it assigns the NUM values for each record. I suggest that you do keep the dataset in that form or else you won't be able to sort or analyze the file by NUM.
Also, if you actually wanted the variable GRADE to only contain the grade numbers then, instead of running the above macro call, you'd use:
%untranspose(data=have, out=want, id=GRADE, id_informat=1., prefix=GR, var_first=N/A,var=RES, by=num)
Art, CEO, AnalystFinder.com
If you want a data set I strongly recommend to NOT have the variable in your NUM role only appear once in a group. That would mean any BY GROUP processing using that variable would be inaccurate at best.
So I would likely stop at
proc transpose data=have out=want (rename=(col1=res)) name=grade ; by num; var gr:; run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.