Working with Default Temporary Output Tables

Problem

You added a transformation to the Diagram tab of the Job Editor window. The transformation sends its output to a temporary output table, and you need to decide what you should do with the temporary output table. Of course, the temporary output table is populated with data only when the job that contains it has been run.

Solution

You can use default temporary output tables in the following ways:

Tasks

Use the Default Temporary Output Table As the Final Output

When the default temporary output table is placed at the end of a job, you can keep the table and use it to view the output of the transformation. Then, you can review the results of the transformation without writing the data to a permanent target table. Perform the following steps to create a process flow diagram that uses the default temporary output table as the final output:

Create an empty job.
Select and drag a transformation from the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
Select and drag a source table from the Inventory tree. Then, drop it before the transformation on the Diagram tab.
Drag the cursor from the source table to the input port of the transformation. This action connects the source to the transformation.

The following display shows a sample job that works this way.

Sample Job with Default Temporary Output Table

By default, the temporary output table for single-output transformations has the same name as the transformation that provides its input. However, when a transformation has multiple outputs, a numerical suffix is added to each output table (for example, Splitter 0 and Splitter 1). In addition, users can change these default names in the property window for the table. The new name must be a valid SAS table name, just like the name for any other table.

Use the Default Temporary Output Table As an Input to Another Transformation

You cannot use one transformation as the direct data input to another transformation. The data must first flow from a transformation to a permanent or temporary output table. Then, it can proceed to the next transformation.

Of course, if you need to save the output into a physical table that you can access after the current SAS session is terminated, you must use a permanent output table. You need to consider performance when you decide whether to use permanent or temporary output storage.

Temporary output storage can be created either as a table in the WORK library or as a view. If the data from the first transformation in the job is referenced multiple times in a process flow, then putting the data into a table generally improves overall performance. When you use a view as a temporary output table, SAS must execute the underlying code repeatedly each time the view is accessed.

However, if the data is referenced only once in a process flow, then the use of a view that is created from a temporary output table usually offers better performance.

You can tell whether a temporary output table takes the form of a view or a physical table by looking for the View modifier on the temporary output table. You can also right-click a temporary output table and look at the pop-up menu. If the Create as View item is checked, a view is generated. If not, the output is stored in a temporary physical table.

You can also click Create as View to switch between a physical table and a view. Note, however, that some transformations, such as Sort, do not support the creation of views. You can click Create as View, but the transformation ignores it and produces a temporary physical table.

Perform the following steps to create a process flow diagram that uses a temporary output table as an input to a transformation:

Create an empty job.
Select and drag a transformation from the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
Select and drag a source table from the Inventory tree. Then, drop it before the transformation on the Diagram tab.
Drag the cursor from the source table to the input port of the transformation. This action connects the source to the transformation.
Select and drag a second transformation from the Transformations tree on the Diagram tab.
Drag the cursor from the output port of the temporary output table that is attached to the first transformation to the input port of the second transformation. This action connects the temporary output table to the second transformation.

The following display shows a sample job that works this way.

Sample Job with Default Temporary Output Table between Transformations

Note: Some transformations, such as Return Code Check, produce no data output. Because they are not data transformations, they are linked to other transformations only by control flow lines. The User Written transformation also has an optional data target. When it is used without a data target, it also connects only with control flow lines.

Replace the Default Temporary Output Table with a Permanent Target Table

You can replace the default temporary output table with a permanent target table. Then, you can write the data directly to the target table without first passing it through a temporary view. You might use this approach with the last transformation in a process flow, which is when you want to store the output in a permanent table. These permanent target tables perform better than temporary output tables under the following conditions:

The data is referenced multiple times in a process flow. In a temporary output table, SAS must execute the underlying code repeatedly each time the view is accessed.
The data is referenced once in a process flow, but the reference is a resource-intensive procedure that performs multiple passes of the input.
The data is generated with SQL and is referenced once, but the reference is from another SQL view. SAS SQL optimization can be less effective when views are nested. This is especially true if the steps involve joins or RDBMS sources.

Note that these performance issues occur when the temporary output table takes the form of a view.

Perform the following steps to create a process flow diagram that replaces the default temporary output table with a permanent table:

Create an empty job.
Select and drag a transformation from the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
Select and drag a source table from the Inventory tree. Then, drop it before the transformation on the Diagram tab.
Drag the cursor from the source table to the input port of the transformation. This action connects the source to the transformation.
Right-click the temporary output table that is attached to the transformation. Then, click either Register Table or Replace in the pop-up menu.
- Click Register Table to display a Register Table window that enables you to change the temporary output table into a permanent physical table. This permanent table is displayed on the Diagram tab of the Job Editor window and added to the Inventory tree.
  
  The table is added to the library that was used when the register table function was last run in the current SAS session. If register table has not been used in the current session, then you must add a library for the table on the Physical Storage tab of the Register Table window. This step prevents a design-time warning in the Job Editor.
- Click Replace to display a Table Selector window that enables you to replace the selected temporary output table with a specified physical table. If you want to retain the mappings, then choose a physical table that matches the temporary table.
Both the register table and replace functions attempt to keep mappings and expressions intact. When you simply delete the temporary table and connect the transformation directly to a target table that you drop on the Diagram tab, these mappings are lost.

The following display shows a sample job that includes a permanent target table.

Sample Job with a Permanent Target Table

Use the Temporary Output Table As an Input to a Table Loader

You can always let a SAS Data Integration Studio transformation perform a simple load of its output table that drops and replaces the table. However, you can also add a Table Loader transformation to a permanent output table. Then, you can use the options in the Table Load transformation to control how data is loaded into the target table. In fact, a separate Table Loader transformation might be desirable under the following conditions:

loading a DBMS table with any technique other than drop and replace
loading tables that contain rows that must be updated upon load (instead of dropping and recreating the table each time the job is executed)
creating primary keys, foreign keys, or column constraints
performing operations on constraints before or after the loading of the output table
performing operations on indexes other than after the loading of the output table

Note that some of these actions are also possible with the SCD Type 2 Loader transformation.

Perform the following steps to create a sample process flow diagram that includes a source table, an initial transformation, a temporary output table, a Table Loader transformation, and a permanent target table:

Create an empty job.
Select and drag a transformation from the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
Select and drag a source table from the Inventory tree. Then, drop it before the transformation on the Diagram tab.
Drag the cursor from the source table to the input port of the transformation. This action connects the source to the transformation.
Select and drag a Table Loader transformation from the Transformations tree on the Diagram tab.
Drag the cursor from the output port of the temporary output table that is attached to the first transformation to the input port of the Table Loader transformation. This action connects the temporary output table to the Table Loader transformation.
Select and drag the target table out of the Inventory tree. Then, drop it after the Table Loader transformation on the Diagram tab.
Drag the cursor from the output port of the Table Loader transformation to the input port of the target table. This action connects the Table Loader transformation to the target table.

The following display shows a sample job that works this way.

Sample Job with a Default Temporary Output Table and a Table Loader

You can feed any table, temporary output table, or physical table into a Table Loader transformation. For example, you can omit the initial Sort transformation and its input and output tables. Then, the job consists of a table that feeds into the Table Loader transformation. The Table Loader then feeds into the target table. In fact, you can use the same table as both the input and the output for the Table Loader, as shown in the following display.

Sample Job Table Loader and a Single Table

This approach enables you to use the Table Loader transformation to reload the table with a different load technique.