SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DI Studio Transpose Long to Wide

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 105
Accepted Solution

DI Studio Transpose Long to Wide

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


Accepted Solutions
Solution
‎02-09-2018 02:53 PM
PROC Star
Posts: 8,149

Re: DI Studio Transpose Long to Wide

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


All Replies
Solution
‎02-09-2018 02:53 PM
PROC Star
Posts: 8,149

Re: DI Studio Transpose Long to Wide

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

 

Frequent Contributor
Posts: 105

Re: DI Studio Transpose Long to Wide

[ Edited ]

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!

Frequent Contributor
Posts: 105

Re: DI Studio Transpose Long to Wide

Nevermind. I understand it all now.

I needed that third table.

 

Cheers for the solution!!

 

-Jeff

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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