topic Re: DI Studio: Transpose transformation, is this possible? in SAS Data Management

DI Studio: Transpose transformation, is this possible?

EinarRoed — Thu, 03 Oct 2013 14:04:06 GMT

Hello,

I'm experimenting with the Transpose transformation in Data Integration Studio, and wonder if it's possible to accomplish what I'm trying to do.

Here's a very simplified example of my input data:

Here's what I want to rearrange it into:

As you can see, the year-columns need to become values, under a new column named "Year".

Is it just a matter of configuring the Transpose transformation the correct way, or is this only possible with user written code?

Thanks for your time.

Re: DI Studio: Transpose transformation, is this possible?

DaveR_SAS — Thu, 03 Oct 2013 14:08:24 GMT

While we are waiting for someone with field experience to respond, here's an example of what the Transpose transformation can do:

SAS(R) Data Integration Studio 4.7: User's Guide

Re: DI Studio: Transpose transformation, is this possible?

LinusH — Thu, 03 Oct 2013 14:23:55 GMT

What proc transpose can do, generally the Transpose transformation can do.

So yes.

But I'm no big fan of this transformation, cause it doesn't give you any help - you need to understand PROC TRANSPOSE to make it work, AND you need to define all metadata manually as well, such as creating new output column names, and mappings. One could easily see that this logic could be done by the transformation itself.

Re: DI Studio: Transpose transformation, is this possible?

GG44 — Thu, 03 Oct 2013 15:38:07 GMT

a.) You will need to sort the incoming dataset by state, sex, status as these are "by statement" variables.

b.) On Transpose Properties: Under Options --> "Assign Columns" --> "Select Columns whose values define groups of records to transpose (BY statement): Provide state, sex & status as by variables.

c.) On Transpose Properties: Under Options --> "Other Options" --> "Specify Proc Transpose options": Provide "name=year prefix=count" as input.

d.) On Transpose Properties: Under Options --> "Additional Options" --> Select "Yes" for "Update the Metadata for the Target tables".

d.) On Transpose Properties --> "Mappings" tab: Bring state, sex, status on the Target table.

e.) On Transpose Properties --> "Mappings" tab: On the target table: Add "Year" as Character variable and "Count1" as numeric variable.

f.) Run the Transpose Transformation.

Re: DI Studio: Transpose transformation, is this possible?

EinarRoed — Fri, 04 Oct 2013 07:46:01 GMT

Thanks so much for the great assistance, that worked perfectly.