## DI Studio Transpose Long to Wide

Solved
Frequent Contributor
Posts: 105

# 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;
var p25 p50 p75;
output out=need (drop=_:) sum=;
run;

%transpose(data=need, out=want, by=inst_code deg_code cip_4dig grad_cohort,
```

Art, CEO, AnalystFinder.com

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;
var p25 p50 p75;
output out=need (drop=_:) sum=;
run;

%transpose(data=need, out=want, by=inst_code deg_code cip_4dig grad_cohort,
```

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.