topic DI Studio Transpose Long to Wide in SAS Data Management

DI Studio Transpose Long to Wide

jwhite — Fri, 09 Feb 2018 14:54:43 GMT

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

Re: DI Studio Transpose Long to Wide

art297 — Fri, 09 Feb 2018 15:18:16 GMT

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-a-dataset/ta-p/433620/jump-to/first-unread-message

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

Re: DI Studio Transpose Long to Wide

jwhite — Fri, 09 Feb 2018 19:41:05 GMT

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!

Re: DI Studio Transpose Long to Wide

jwhite — Fri, 09 Feb 2018 19:53:45 GMT

Nevermind. I understand it all now.

I needed that third table.

Cheers for the solution!!

-Jeff