Creating a Parameterized Job

Problem

You want to create a job that will enable you to perform an identical set of tasks on a series of tables. For example, you might need to extract specific demographic information for each of the 50 states in the United States when the data for each state is contained in a separate table.

Solution

You need to create a job that enables you to run each table through the loop in an iterative job. This job then writes data to an output table with each iteration. You set parameters on the job, the input table, and the output table. Then, you connect the parameters to the control table in the iterative job.
Perform the following tasks:

Tasks

Create and Populate the Job

Perform the following steps to create and populate the job:
  1. Create and register the input and output tables. The input and output tables must contain exactly the same columns as the tables that are listed in the control table for the loop processing in the iterative job to work properly.
  2. Create an empty job.
  3. Select and drag the SAS transformation that is used to process the data from the appropriate folder in the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window. The sample job uses an Extract transformation to extract a subset of the data with a specified marital status from the source tables that are run through the loop.
  4. Select and drag the source table from its folder. Then, drop it before the SAS transformation on the Diagram tab. You set the input parameter on this table.
  5. Drag the cursor from the source table to the input port of the SAS transformation. This action connects the source to the transformation.
  6. Because you must have a permanent target table to contain the output parameter that is needed for the loop job to work, 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. (For more information about temporary work tables, see Working with Default Temporary Output Tables.) You set the output parameter on this table.
A sample completed parameterized job is shown in the following example.
Completed Parameterized Job
Completed Parameterized Job
The input table for the sample job is named PARAMTABLE_IN. The output table is named PARAMTABLE_OUT.

Set Input and Output Parameters

Perform the following steps to set the input and output table parameters for the parameterized job:
  1. Open the Parameters tab in the properties window for the input table. Click New Prompt to display the New Prompt window. Enter appropriate values in the following fields on the General tab:
    • Name: a valid macro variable name, such as mstatus
    • Displayed Text: a display name for the macro variable, such as Marital Status.
    If you want to enter a default value for the input table, click the Prompt Type and Values tab. Then, enter the value in the Default value field. The default value in the sample job is CHECKING_ACCOUNT_DIVORCED. Because the default prompt type of Text is appropriate, you keep the defaulted values in the other fields on the Prompt Type and Values tab.
  2. Click OK to save the parameter and close the New Prompt window.
  3. Open the Physical Storage tab. Enter an appropriate value in the Name field. Create this value by combining an ampersand sign (&) with the value that was entered in the Macro Variable Name field in the New Prompt window (for example, &mstatus).
  4. Click OK to save the settings and close the properties window for the input table.
  5. Open the Parameters tab in the properties window for the output table. Click New Prompt to display the New Prompt window. Enter appropriate values in the following fields on the General tab:
    • Name: a valid macro variable name, such as mstatus.
    • Displayed Text: a display name for the macro variable, such as Marital Status Out.
    If you want to enter a default value for the output table, click the Prompt Type and Values tab. Then, enter the value in the Default value field. The default value in the sample job is CHECKING_ACCOUNT_DIVORCED. Because the default prompt type of Text is appropriate, you keep the defaulted values in the other fields on the Prompt Type and Values tab.
  6. Click OK to save the parameter and close the New Prompt window.
  7. Open the Physical Storage tab. Enter an appropriate value in the Name field. Create this value by combining an ampersand sign with the value that was entered in the Macro Variable Name field in the New Prompt window and appending .OUT to the combination (for example, &mstatus.OUT).
  8. Click OK to save the settings and close the properties window for the input table.

Set Parameters for the Job

Perform the following steps to set the parameters for the parameterized job and to complete job configuration:
  1. Open the Parameters tab in the properties window for the parameterized job.
  2. Click Import Parameters to display the Import Parameters window. Click an appropriate value such as PARAMTABLE_IN in the Available Parameters field. Select the parameter that is assigned to the input table and move it to the Selected Parameters field. Then, click OK to save the setting and close the properties window.

Complete Parameterized Job Configuration

Perform the following steps to complete the configuration of the parameterized job:
  1. Configure any settings needed to process the data in the parameterized job. For example, you can set a WHERE condition in an Extract transformation if one is included in the job. These settings vary depending on the structure of the individual job. For the sample job, the WHERE condition is 
    CHECKING_APP_MARITAL_STATUS_CD = 'D'
  2. Open the Mapping tab in the properties window for the transformation that is included in the parameterized job. Verify that all of the columns in the source table are mapped to an appropriate column in the target table and close the properties window.
  3. Do not run the job. It will be submitted as a part of the iterative job.
Copyright © SAS Institute Inc. All rights reserved.