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.
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: January 16, 2018