topic reverse transpose -wide to long in SAS Programming

reverse transpose -wide to long

jenim514 — Fri, 13 Apr 2018 19:12:08 GMT

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!

Re: reverse transpose -wide to long

novinosrin — Fri, 13 Apr 2018 19:19:56 GMT

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;

Re: reverse transpose -wide to long

art297 — Fri, 13 Apr 2018 20:18:58 GMT

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

Re: reverse transpose -wide to long

ballardw — Fri, 13 Apr 2018 20:12:37 GMT

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;