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

DI Studio: Transpose transformation, is this possible?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 90
Accepted Solution

DI Studio: Transpose transformation, is this possible?

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.


Accepted Solutions
Solution
‎10-03-2013 11:38 AM
New Contributor
Posts: 2

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

Posted in reply to EinarRoed

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.

View solution in original post


All Replies
SAS Employee
Posts: 75

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

Posted in reply to EinarRoed

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

Super User
Posts: 5,431

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

Posted in reply to EinarRoed

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.

Data never sleeps
Solution
‎10-03-2013 11:38 AM
New Contributor
Posts: 2

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

Posted in reply to EinarRoed

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.

Frequent Contributor
Posts: 90

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

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 2776 views
  • 7 likes
  • 4 in conversation