Doing More with Data Management

Transposing a Table

You can use Transpose table under Utilities on the Data Management menu to invert a table. Transposing a table turns the rows of a table into columns and vice versa.

In this section, you first sort the ORANGES table in order of the store number because you group the transposed rows by store. Then you transpose the sorted table so that the price for the first variety of oranges becomes a row with a separate column for each day's price.

The following display shows a listing report of the ORANGES table before it is transposed.

Listing of ORANGES Table Before Transposing

[Listing of ORANGES Table Before Transposing]

Additional Information

For additional information, refer to "The TRANSPOSE Procedure" in the Base SAS Procedures Guide.

Instructions

To display the Sort a Table window, follow this selection path:Tasks Data Management Sort

Sort a Table Window
Use the Table button to select the SASUSER.ORANGES table.
Select Output table. The Specify Output Table window appears. The Specify Output Table is identical to the window shown in Output Table or View Window.
In the Table field, type ORGSORT as the name of the output table.

By leaving the default of Temporary, a temporary table is created in the WORK library. The WORK.ORGSORT table is deleted when you exit SAS. An indicator next to Temporary shows that this item is selected.
Select OK to return to the Sort a Table window.
Use the Sort by columns button to select the STORE column.
Select OK to return to the Sort a Table window.
To sort the table, follow this selection path:Run SubmitA message appears to indicate that the table was successfully sorted.
Select Goback.
Follow this selection path to open the Transpose a Table window:Tasks Data Management Utilities Transpose TableThe Transpose a table window appears.

Transpose a Table Window
The WORK.ORGSORT table should already be listed for Table. If not, use the Table button to select the WORK.ORGSORT table.
Select Output table. The Specify Output Table window appears.
In the Table field, type ORGTRANS as the name of the output table, then select OK to return to the Transpose a Table window.

By leaving the default of Temporary, a temporary table is created in the WORK library. The WORK.ORGTRANS table is deleted when you exit SAS. An indicator next to Temporary shows that this item is selected.
Use the Columns to be transposed button to select the PRICE1 column as the column to be transposed (that is, changed to a row).
Select Additional options. The Additional Transpose Options window appears.

Additional Transpose Options Window
Use the Group the transposed rows button to select the STORE column as the column you want to use as a grouping column.
Use the column to be used to label transposed columns button to select the DAY column as the label you want to use to label the columns of the transposed data in the ORGTRANS output table.

SAS generates a row for each column that is being transposed for each group.
In the Additional Transpose Options window, position the cursor in the Prefix field and type Day as the prefix to each number that represents a day of the week.

Make sure each formatted value of the DAY column occurs only once in each group of the active table. Duplicate values produce a warning message in the LOG window and stop the procedure.
Select OK to return to the Transpose a Table window.
To transpose the table, follow this selection path:Run SubmitA message appears asking if you want to view the transposed table. Select OK, then Goback. The transposed table appears in an FSVIEW window.

Transposed Table

Use the scroll bars or the RIGHT command or function key to display the rest of the transposed data.
To exit the FSVIEW window, follow this selection path:File Close

Top of Page