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

Get SAS Data Integration Studio 4.9 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.