SAS Data Integration Studio 4.9

Creating Transposed Data from Table Data

Overview

Use a Transpose transformation to create a target table that contains transposed data.

You can control many aspects of how the target table is created, including the following:

• the type of data to be transposed

• which columns are analyzed

The TRANSPOSE procedure creates an output data set by restructuring the values in a

SAS data set, transposing selected variables into observations. The TRANSPOSE

procedure can often eliminate the need to write a lengthy DATA step to achieve the

same result. Further, the output data set can be used in subsequent DATA or PROC steps

for analysis, reporting, or further data manipulation. A transposed variable is a variable

that the procedure creates by transposing the values of an observation in the input data

set into values of a variable in the output data set.

PROC TRANSPOSE does not produce printed output. To print the output data set from

the PROC TRANSPOSE step, use the List Data transformation or another SAS

publishing, summary, or reporting tool.

The target table that is created by this transformation contains only the columns selected

to be analyzed. In addition, there is an informational column, _NAME_, which contains

the name of the column that is to be used as the transposed column. There are also

additional columns needed, depending on the type of transposition you are performing.

Note: You should set Update the table metadata for the target tables to Yes. This

action ensures that the proper columns are included in the target table. Alternatively,

you can create the columns in the output table on the Mapping tab of the Transpose

transformation property window. You must create the _NAME_ column and any

other columns in the target table for that data to be included in the final table.

Problem

You want to create a target table that contains transposed data.

Solution

You can use the Transpose transformation in a job that transposes the data in a table and

creates an ODS document that displays the transposed table. The transformation uses the

TRANSPOSE procedure to load transposed data into a target table. Transposing the data

in a table turns the rows in a table into columns and the columns into the rows.

For example, you can create a job similar to the sample job featured in this topic. This

sample job generates a target table that contains information about baking. The values in

the Age column are transposed into columns in the SAS column generated in the job.

This data is then output to the target table and to a report that is generated with ODS.

The sample job includes the following tasks:

• “Create and Populate the Job” on page 737

• “Configure Analytical Options” on page 737

736 Appendix 3 • Miscellaneous Transformations