Creating Standardized Statistics from Table Data

Overview

The Standardize transformation is an interface to the STANDARD procedure. The STANDARD procedure standardizes variables in a SAS data set to a given mean and standard deviation, and it creates a new SAS data set containing the standardized values. You can use it to create a target table that contains standardized data. You can control many aspects of how the target table is created, which can include the following:
  • the type of standardization
  • which columns are analyzed

Problem

You want to generate a target table that is standardized to a given mean and standard deviation.

Solution

You can use the Standardize transformation in a SAS Data Integration Studio job. This transformation uses the STANDARD procedure to standardize variables in a SAS data set to a given mean and standard deviation. Then, it creates a new SAS data set that contains the standardized values.
For example, you can create a job similar to the sample job featured in this topic. This sample job generates a standardized analysis based on a table that contains information about test scores. Note that the output for this job is sent to a target table. The sample job includes the following tasks:

Tasks

Create and Populate the Job

Perform the following steps to create and populate the job:
  1. Create an empty SAS Data Integration Studio job.
  2. Select and drag a Standardize transformation from the Data folder in the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
  3. Select and drag the source table out of the Inventory tree. Then, drop it before the Standardize transformation on the Diagram tab.
  4. Drag the cursor from the source table to the input port of the Standardize transformation. This action connects the source to the transformation.
  5. Right-click the Standardize transformation, and click Add Output Port from the Ports option in the drop-down menu. This step enables you to add an output port to the transformation.
  6. Because you want to have a permanent target table to contain the output for the transformation, right-click the temporary work table attached to the transformation and click Replace in the pop-up menu. Then, use the Table Selector window to select the target table for the job. The target table must be registered in SAS Data Integration Studio.
The following display shows a sample process flow diagram for a job that contains the Standardize transformation:
Sample Process Flow
Sample Process Flow
Note that the source table for the sample job is named SCORE, and the target table is named SCORE_OUT.

Configure Analytical Options

Use the Options tab in the properties window for the Standardize transformation to configure the output for your analysis. Note that the Options tab is divided into two parts, with a list of categories on the left-hand side and the options for the selected category on the right-hand side. Perform the following steps to set the options that you need for your job:
  1. Open the properties window for the Correlations transformation in the Diagram tab in the Job Editor window. Then, click the Options tab.
  2. Click Standardize to access the Standardize page. In the sample job, the mean value and the standard deviation are set in the Standardize page. You can also specify that missing values are replaced with the column mean value on the same page. These settings are shown in the following display:
    Sample Standardize Options
    Sample Standardize Options

Run the Job and View the Output

Perform the following steps to run the job and view the output:
  1. Right-click on an empty area of the job, and click Run in the pop-up menu. SAS Data Integration Studio generates code for the job and submits it to the SAS Application Server for execution. The following display shows a successful run of a sample job:
    Successfully Completed Sample Job
    Successfully Completed Sample Job
  2. If error messages display, read and respond to the messages as needed.
  3. To view the target table, right-click the target and select Open. The following display shows the target table data for the sample job.
    Output for Standardize Transformation
    Output for Standardize Transformation
Last updated: January 16, 2018