Create a Table That Ranks the Contents of a Source :: SAS(R) Data Integration Studio 4.9: User's Guide

Overview

A Rank transformation uses the RANK procedure so you can rank one or more numeric variables in the source and store the ranks in the target.

Problem

You want to rank a set of numeric data according to some criteria. For example, you might want to rank a set of regional offices according to actual net profitability.

Solution

Create a job in which a Rank transformation reads numeric data, ranks that data according to some criteria, and then writes the ranked data to a target table. This sample job reads a table that contains profitability figures for a set of regional offices. Then it ranks them according to actual net profitability and writes the ranked data to a target table. The sample job includes the following tasks:

Create and Populate the Job
Select Rank Variables
Run the Job and View the Output

Tasks

Create and Populate the Job

Perform the following steps to create and populate a new job:

Create an empty SAS Data Integration Studio job.
Select and drag the Rank transformation from the Data folder in the Transformations tree. Drop it in the empty job on the Diagram tab in the Job Editor window.
From the Inventory tree, select and drag the source table. Then, drop it before the Rank transformation on the Diagram tab.
Drag the cursor from the source table to the input port of the Rank transformation. This action connects the transformation to the source.
A temporary work table appears after the Rank transformation. You can use this temporary table or delete it and add a target table. In this example, the temporary table is deleted.
From the Inventory tree, select and drag the target table. Then, drop it after the Rank transformation on the Diagram tab.
Drag the cursor from an output port of the Rank transformation to the input port of the target table. This action connects the transformation to the target. Note that if you have not deleted the temporary work table, you cannot make this connection. The following example shows the sample process flow.

Sample Process Flow

Select Rank Variables

Use the Rank Variable Columns tab in the properties window for the Rank transformation to specify how the information in the target table is to be ranked. The left pane on the Rank Variable Columns tab displays the column variables in the source. (For the rank to succeed, you must select the same number of columns for the target table as you did from the source table.) Perform the following steps to specify how the information in the target table is to be ranked:

Click the column that you want to select in the Available source columns field. Then move it to the Selected source columns field. The source column selected in the sample job is named ACTUAL.
Click the column that you want to select in the Available target columns field. Then move it to the Selected target columns field. The source column selected in the sample job is named ActualProfit.
Click OK to save the selection criteria for the target table and close the properties window for the Rank transformation. The following display shows the Rank Variables Columns tab for the sample job.

Rank Source and Target Information

Run the Job and View the Output

Perform the following steps to run the job and view the output:

Right-click on an empty area of the job, and click Submit in the pop-up menu. SAS Data Integration Studio generates code for the job and submits it to the SAS Application Server for execution.
If error messages display, read and respond to the messages as needed.
To view the target table, right-click the target and select Open. The following display shows the target table data for the sample job.

Data in Offices Ranked Table