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
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
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
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!
Nevermind. I understand it all now.
I needed that third table.
Cheers for the solution!!
-Jeff
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.