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

Hey Community,

 

I'm struggling a bit with the Transpose transformation.

I have data that looks like so:

inst_code deg_code year_postgrad cip_4dig grad_cohort p25 p50 p75
3656 4 1 300 2011  5  10  15
3656 4 1 300 2011  5  10  15
3656 4 1 300 2011  5  10  15
3656 4 5 300 2011  2  4  6
3656 4 5 300 2011  2  4

 6

 

I need it to end up like this:

inst_code deg_code cip_4dig grad_cohort p25_1 p50_1 p75_1 p25_5 p50_5 p75_5
3656 4 300 2011 15  30  45  4  8  12

 

So, I'm grouping by inst_code, deg_code, cip_4dig, and grad_cohort, and using that 'year_postgrad' column to help transpose the 'pxx' numbers.

 

I can do this in a series of various other transformations, but I was hoping to just do this in one for simplicity and assumed the Transpose transformation would be best.

 

Thanks for any suggestions or advice!

-Jeff

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

You can reduce it to two steps if you first download and run the macro you can find at: https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-...

 

Using that macro, the following would accomplish the task:

 

proc summary data=have nway;
  class inst_code deg_code cip_4dig grad_cohort year_postgrad;
  var p25 p50 p75;
  output out=need (drop=_:) sum=;
run;

%transpose(data=need, out=want, by=inst_code deg_code cip_4dig grad_cohort,
 id=year_postgrad, delimiter=_, var=p25	p50	p75)

Art, CEO, AnalystFinder.com

 

View solution in original post

3 REPLIES 3
art297
Opal | Level 21

You can reduce it to two steps if you first download and run the macro you can find at: https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-...

 

Using that macro, the following would accomplish the task:

 

proc summary data=have nway;
  class inst_code deg_code cip_4dig grad_cohort year_postgrad;
  var p25 p50 p75;
  output out=need (drop=_:) sum=;
run;

%transpose(data=need, out=want, by=inst_code deg_code cip_4dig grad_cohort,
 id=year_postgrad, delimiter=_, var=p25	p50	p75)

Art, CEO, AnalystFinder.com

 

jwhite
Quartz | Level 8

Ok, so I've saved that macro.

Would I use a 'User Written' node in DI Studio and not have any mappings, so the macro can generate the new (transposed) columns? OR would I need to create and assign the transposed columns in some way?

 

Also, it looks like that code snipped requires three datasets (HAVE, NEED, and WANT). I currently have two. An IN and an OUT, or a SORTED and TRANSPOSED.

 

Finally, I _think_ I'm on the way. I have wide columns now, but they values aren't summed based on groupings. Either I need to fill something in the 'sum=' option or maybe the three vs two datasets is an issue?

 

Thanks!

jwhite
Quartz | Level 8

Nevermind. I understand it all now.

I needed that third table.

 

Cheers for the solution!!

 

-Jeff

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 connect to databases in SAS Viya

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.

Discussion stats
  • 3 replies
  • 945 views
  • 0 likes
  • 2 in conversation