BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jenim514
Pyrite | Level 9

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!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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;

View solution in original post

3 REPLIES 3
novinosrin
Tourmaline | Level 20
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;
art297
Opal | Level 21

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

 

 

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 10564 views
  • 2 likes
  • 4 in conversation