Reviewing Temporary Output Tables

Problem

Most transformations in a SAS Data Integration Studio job create at least one output table. Then, they store these tables in the Work library on the SAS Workspace Server that executes the job. The output table for each transformation becomes the input to the next transformation in the process flow. All output tables are deleted when the job is finished or the current server session ends.

Sometimes a job does not produce the expected output. Other times, something can be wrong with a particular transformation. In either case, you can view the output tables for the transformations in the job to verify that each transformation is creating the expected output. Output tables can also be preserved to determine how much disk space they require. You can even use them to restart a process flow after it has failed at a particular step (or in a specific transformation).

Note: You can also redirect temporary output tables to an alternative location. For details, see Redirecting Temporary Output Tables.

Solution

You can view a transformation's temporary output table from the Process Designer window and preserve temporary output tables so that you can view their contents by other means. You can perform the following tasks to accomplish these objectives:

Tasks

Preserve Temporary Output Tables

When SAS Data Integration Studio jobs are executed in batch mode, a number of SAS options can be used to preserve intermediate files in the Work library. These system options can be set as described in Set SAS Invocation Options on Jobs.

Use the NOWORKINIT system option to prevent SAS from erasing existing Work files on invocation. Use the NOWORKTERM system option to prevent SAS from erasing existing Work files on termination.

For example, to create a permanent SAS Work library in UNIX and PC environments, you can start the SAS Workspace Server with the WORK option to redirect the Work files to a permanent Work library. The NOWORKINIT and NOWORKTERM options must be included, as follows:

C:\>"C:\Program Files\SASHome\SASFoundation\
9.3\sas.exe" 
-work "C:\Users\sasapb\My Documents\My SAS Files\9.3\
My SAS WorkFolder"
-noworkinit  
-noworkterm

This redirects the generated Work files in the folder My SAS Work Folder.

To create a permanent SAS Work library in the z/OS environment, edit your JCL statements and change the WORK DD statement to a permanent MVS data set. For example:

 //STEP1 EXEC SDSSAS9,REGION=50M 
//* changing work lib definition to a permanent data set 
//SDSSAS9.WORK DD DSN=userid.somethin.sasdata,DISP=OLD 
//* other file defs 
//INFILE DD ... .

CAUTION:

If you redirect Work files to a permanent library, you must manually delete these files to avoid running out of disk space.

View Temporary Output Tables

Perform the following steps to view the output file:

Open the job in the Job Editor window.
Submit the job for execution. The transformations must execute successfully. (Otherwise, a current output table is not available for viewing.)
Right-click the transformation of the output table that you want to view, and click Open. The transformation's output table is displayed in the View Data window.

This approach works if you do not close the Job Editor window. When you close the Job Editor window, the current server session ends, and the output tables are deleted. For information, see Browsing Table Data.

Redirect Temporary Output Tables

The default name for a transformation's output table is a two-level name that specifies the Work libref and a generated member name, such as work.W54KFYQY. You can specify the name and location of the output table for that transformation on the Physical Storage tab on the properties window of the temporary output table. Note that this location can be a SAS library or RDBMS library. This has the added benefit of providing users the ability to specify which output tables they want to retain and to allow the rest to be deleted by default. Users can use this scheme as a methodology for checkpoints by writing specific output tables to disk when needed.

Note: If you want to save a transformation output table to a library other than the SAS User library, replace the default name for the output table with a two-level name.

If you refer to an output table with a single-level name (for example, employee), instead of a two-level name (for example, work.employee), SAS automatically sends the output table into the User library, which defaults to the Work library. However, this default behavior can be changed by any SAS user. Through the USER= system option, a SAS user can redirect the User library to a different library. If the USER= system option is set, single-level tables are stored in the User library, which has been redirected to a different library, instead of to the Work library.

Add the List Data Transformation to a Process Flow

In SAS Data Integration Studio, you can use the List Data transformation to print the contents of an output table from the previous transformation in a process flow. Add the List Data transformation after any transformation whose output table is of interest to you.

The List Data transformation uses the PRINT procedure to produce output. Any options that are associated with that procedure can be added from the Options tab in the transformation's property window. By default, output goes to the Output tab in the Job Editor window. Output can also be directed to an HTML file. For large data, customize this transformation to print just a subset of the data. For details, see the “Example: Create Reports from Table Data” topic in SAS Data Integration Studio Help.

Add a User-Written Code Transformation to the Process Flow

You can add a User Written Code transformation to the end of a process flow that moves or copies some of the data sets in the Work library to a permanent library. For example, assume that there are three tables in the Work library (test1, test2, and test3). The following code moves all three tables from the Work library to a permanent library named PERMLIB and then deletes them from the Work library:

libname permlib base 
"C:\Users\ramich\My Documents\My SAS Files\9.3"; 
proc copy move  
in = work  
out = permlib;
select test1 test2 test3; 
run;

For information about User Written Code transformations, see Adding a User Written Code Transformation to a Job.