BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EinarRoed
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
GG44
Calcite | Level 5

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

4 REPLIES 4
DaveR_SAS
SAS Employee

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

LinusH
Tourmaline | Level 20

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
GG44
Calcite | Level 5

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.

EinarRoed
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 6960 views
  • 8 likes
  • 4 in conversation