Creating a Control Table :: SAS(R) Data Integration Studio 4.7: User's Guide

Problem

You want to create a control table that lists the tables that you plan to include in an iterative job. Iterative jobs are used to run a series of similarly structured tables through the same task or series of tasks. The control table supplies the name of the table that is run through each iteration of the job.

Solution

You can reuse an existing control table or create one manually. You can also create a job that uses the Library Contents transformation. This transformation generates a listing of the tables contained in the library that holds the tables that you plan to run through the iterative job. This control table is based on the dictionary table of that library.

Perform the following tasks:

Create and Register the Control Table
Create and Populate the Job
Run the Job and Examine the Output

Tasks

Create and Register the Control Table

If you have an existing control table, you can use it. If you do not use an existing control table, you can use the Code Editor window in SAS Data Integration Studio to execute an SQL statement. The statement creates an empty instance of the table that has same column structure as the dictionary table for the library. Then use New Table wizard to register the empty table. Perform the following steps to create the empty control table:

Determine the identity and location of the library that contains the tables that you need to process in an iterative job.
From the SAS Data Integration Studio desktop, select ToolsCode Editor.
The Source Editor window appears. Submit code similar to the following code:
```
libname tgt 'C:\targets\sas1_tgt';
  proc sql;
       create table tgt.CHECKLIB
       as select *
 	    from dictionary.tables
 	    where libname='checklib';
  quit;
```
Be sure to check the Log tab to verify that the code ran without errors.
Register the table that you just created using the Register Tables wizard. This action creates a metadata object for the table.
(Optional) You can confirm that the empty control table was created in physical storage. Right-click the metadata object for the table and select Open. A sample empty control table is shown in the following example.

View of Empty Control Table Output

Create and Populate the Job

Perform the following steps to create and populate the job:

Create an empty job.
Select and drag a Library Contents transformation from the Access folder in the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
Select and drag the library that you plan to use to generate the control table from its folder. Then, drop it before the Library Contents transformation on the Diagram tab.
Drag the cursor from the library to the input port of the Library Contents transformation. This action connects the library to the transformation.
Because you want to have a permanent target table to contain the output for the transformation, right-click the temporary work table that is 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.)
Drag the cursor from the output port of the Library Contents transformation to the target table. This action connects the transformation to the target.
Open the Mapping tab in the properties window for the Library Contents transformation. Verify that all of the rows in the source table are mapped to the corresponding row in the target table. You can click Map all columns to correct any errors.

A sample completed control table job is shown in the following example.

Completed Control Table Job

The library for the sample job is named CHECKLIB. The target table is also named CHECKLIB.

Run the Job and Examine the Output

Perform the following steps to run the control table job and examine its output:

Run the job. The following display shows a successfully completed sample job.

Successful Sample Control Job
If the job completes without error, right-click the control table icon and click Open. The View Data window appears, as shown in the following example.

View of Control Table Output

Note that the all of the rows in the table are populated with the name of the control table in the libname column. This name confirms that all of the rows are drawn from the appropriate library. You can now use the table as the control table for the iterative job.