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

Introduction

Transpose Data icon in the SAS Data Loader window

Use the Transpose Data in Hadoop 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 Cleanse Data in Hadoop or Query or Join Tables in Hadoop directives.

Example

Follow these steps to use the Transpose Data in Hadoop directive.

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.
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

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