Transpose Data :: SAS(R) Data Loader 2.4 for Hadoop: User's Guide

Introduction

Transpose Data icon in the SAS Data Loader window

Use the Transpose Data directive to transpose one or more columns in a source table into rows in a target table. The columns in the target are the values of a specified column in the source. For example, you could specify that the columns of the target be taken from the values of a source table column that contains customer ID numbers. Each unique customer ID value in the source becomes a separate column in the target.

You do not have to transpose all of the columns in the source. You can select source columns that will be copied directly to the target.

This directive contains embedded help that includes examples of transposed data.

CAUTION:

Selecting columns with a high degree of cardinality (number of unique values) can decrease performance in Transpose jobs.

To maximize performance, profile your source columns and filter your source rows. You can filter source rows in the directives Cleanse Data or Query or Join Tables.

Example

Follow these steps to use the Transpose Data directive.

On the SAS Data Loader directives page, click Transpose Data. The Source Table task is displayed. For more information about data sources and tables, see Viewing Data Sources and Tables.
In the Source Data task, click the data source that contains your source table, click the source table, and then click the Table Viewer .

Examine the source table to determine the roles for the columns.

Note: Valid source table selections must have names that contain no more than 32 characters. Longer table names cause transpose jobs to fail. For information about other requirements, see Usage Notes.
In the Transpose Data task, click the required Transpose data, click the columns that you want to see as rows, and click the right arrow. If you transpose multiple columns, then you can arrange them in Roles using the up and down arrows.
Click the required Columns to group by, click an available column, and then click the right arrow. The group-by column becomes the leftmost column. Each row in that column receives a set of values from the transposed columns.
As needed, click ID column, click an available column, and then click the right arrow. The values of the ID column become column names in the target.
To copy a column from the source to the target, select Copy column, select an available column, and click the right arrow. The copied column will be positioned as the last, or rightmost, column.

Usage Notes

Changing the Maximum Length of Character Columns

If necessary, you can change the maximum length of character columns for source tables to this directive. For more information, see Change the Maximum Length for SAS Character Columns.

Avoid Using DS2 Reserved Keywords as Column Names

Do not use a DS2 reserved keyword for the name of a column that is the target of the Transpose directive. For example, assume that a source table contains a column named OTHER. If the column that is named OTHER is specified as a column to transpose, a runtime error is generated because OTHER is a DS2 reserved keyword.

For more information about DS2 keywords, see SAS 9.4 DS2 Language Reference.