Using an External File in the Process Flow for a Job :: SAS(R) Data Integration Studio 4.5: User's Guide

Problem

You want the process flow for a job to read from an external file or write to an external file.

Solution

In the process flow for a job, you can use the File Reader transformation to read an external file, and you can use the File Writer transformation to write to an external file.

An external file, sometimes called a flat file or a raw data file, is a plain text file that often contains one record per line. Within each record, the fields can have a fixed length or they can be separated by delimiters, such as commas. Most SAS Data Integration Studio transformations cannot use external files as inputs or outputs, so the File Reader and File Writer transformations are used to incorporate external files into the process flow for a job.

Perform the following tasks:

Tasks

Read from an External File in a Job

To read from an external file in a job, add a File Reader transformation to the job. Then, specify the external file as the input to the File Reader transformation, as shown in the next display.

File Reader Process Flow

The File Reader transformation reads information from the external file and writes the output to a temporary work table. By default, the temporary work table is a SAS view. Most SAS Data Integration Studio transformations can read a SAS view, so the output work table could be connected to a second transformation such as the Sort transformation. The second transformation could be connected to a third, and so on. In this way, a chain of transformations can be used to process information from an external file.

Perform the following steps to specify an external file as the input to the File Reader transformation.

If the external file has not been registered, use the appropriate wizard to register the external file. For more information, see Registering a Delimited External File, Registering a Fixed-Width External File, and Registering an External File with User-Written Code.
Create an empty SAS Data Integration Studio job. For more information, see Creating an Empty Job.
Select and drag a File Reader transformation from the Access folder of the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
Select and drag the external file from the tree view. Then, drop it before the File Reader transformation on the Diagram tab.
Drag the cursor from the external file to the input port of the File Reader transformation. This action connects the source to the transformation. At this point, the minimum process flow for your job should look similar to the preceding process flow. You can run the job and verify the results.

Write to an External File in a Job

To write to an external file in a job, add a File Writer transformation to the job. Then, specify a SAS or DBMS table as the input and an external file as the output, as shown in the next display.

File Writer Process Flow

The File Writer transformation reads information from a SAS or DBMS table and writes the output to an external file. The input to a File Writer transformation could be the output of a previous transformation in the current job, or it could be output from another job. In this way, the output of SAS Data Integration Studio jobs can be made available to third-party applications that support external files.

Assume that the SAS or DBMS table input to the File Writer transformation is already registered, and that the external file output is a new file, one that is created when the job that includes the File Writer executes for this first time. Perform the following steps to specify an external file as the output of the File Writer transformation.

If the external file has not been registered, use the appropriate wizard to register the external file. For more information, see Registering a Delimited External File, Registering a Fixed-Width External File, and Registering an External File with User-Written Code.
Create an empty SAS Data Integration Studio job. For more information, see Creating an Empty Job.
Select and drag a File Writer transformation from the Access folder of the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
Select and drag a SAS or DBMS input table from the tree view. Then, drop it before the File Writer transformation on the Diagram tab.
Drag the cursor from the input table to the input port of the File Writer transformation. This action connects the input to the transformation.
Select and drag the external file output from the tree view. Then, drop it after the File Writer transformation on the Diagram tab.
Drag the cursor from the output port of the File Writer transformation to the external file. This action connects the output to the transformation. At this point, the process flow should look similar to the preceding process flow diagram.

The File Writer transformation attempts to automatically map columns between the input table and the output external file. You might want to verify that the mappings are correct.
(Optional) To verify the mappings in the File Writer transformation, right-click the transformation in the job and select Properties from the pop-up menu. The next display shows the Mapping tab for the File Writer transformation.

Mapping Tab for File Writer Transformation

In the preceding display, three columns from the input table (SAS Table) are mapped to three identical columns in the output file (External File 2). If the mappings are what you want, click Cancel to close the properties window. To update the mappings, see Maintaining Column Mappings.
When ready, run the job and verify the results.

Run the Job and Verify the Results

Perform the following steps to run the job and view the output.

Right-click on an empty area of the job, and click Run in the pop-up menu. SAS Data Integration Studio generates code for the job and submits it to the SAS Application Server for execution.
If error messages display, read and respond to the messages as needed.

Right-click the appropriate external file or table and select Open or Open as Table to verify that the correct data was loaded into the table or file.