Creating a Table That Appends Two or More Source Tables :: SAS(R) Data Integration Studio 4.5: User's Guide

Overview

Use the Append transformation to create a single target by appending (concatenating) two or more sources.

Note: You cannot append a table to itself.

Problem

You want to combine data from several source tables into a single target table.

Solution

You can use the Append transformation in a SAS Data Integration Studio job to combine the source tables into the target table. For example, you can create a job similar to the sample job featured in this topic. This sample job combines several months of sales data into a table that contains quarterly sales data. The sample job includes the following tasks:

Tasks

Create and Populate the Job

Perform the following steps to create and populate the job:

Create an empty SAS Data Integration Studio job.
Select and drag an Append transformation from the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
Select and drag the source tables from the Inventory tree. Then, drop them before the Append transformation on the Diagram tab.
Drag the cursor from the source tables to the input port of the Append transformation. This action connects the sources 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 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 following display shows a sample process flow diagram for a job that contains the Append transformation.

Sample Process Flow

The source tables for the sample job are named Jan, Feb, and Mar. The target table for the sample job is named QUARTERLY_SALES. It contains sales information for specific customers for the months of January, February, and March.

Run the Job and View the Output

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. The following display shows a successful run of a sample job.

Sample Completed Job
If error messages display, read and respond to the messages as needed.
Right-click the target table in the Diagram tab. Then, click Open in the pop-up menu. The following display shows the target table data for the sample job.

Sample Target Table in the View Data Window

Note that the first ten rows in the sample target table display data from the Jan table. The next five rows display data from the Feb table. The remaining rows display the rest of the data from the source tables.