Extracting Data from a Source Table :: SAS(R) Data Integration Studio 4.5: User's Guide

Overview

A SAS Extract transformation is a transformation that you can typically use to create one subset from a source. You can also use it to create columns in a target that are derived from columns in a source. For example, you can add a column to the target that concatenates two columns from the source or that calculates a value that is based on a column in the source.

Problem

You want to select a set of rows from a source table and write those rows to a target table.

Solution

You can use the Extract transformation in a SAS Data Integration Studio job to create jobs that require the data to be filtered or columns to be created from expressions. For example, you can create a job similar to the sample job featured in this topic. This sample job extracts only the rows that contain information about female employees from a table that contains information about both male and female employees. 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 Extract transformation from the Data folder in the Transformations tree. Then, drop it in the empty job on the Diagram tab in the Job Editor window.
Select and drag the source table from the Inventory tree. Then, drop it before the Extract transformation on the Diagram tab.
Drag the cursor from the source table to the input port of the Extract transformation. This action connects the source 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 target table must be registered in SAS Data Integration Studio.

Sample Process Flow

The source table for the sample job is named ALL_EMP. The target table is named ALL_FEMALE_EMP. The preceding display shows the sample process flow.

Specify Selection Conditions for the Target

Use the tabs in the properties window for the Extract transformation to configure the output in the target table. Perform the following steps to configure the output:

Open the properties window for the Extract transformation on the Diagram tab in the Job Editor window. Then, click the Where tab.
Enter an appropriate WHERE condition in the Expression Text field. The following WHERE condition is entered in the sample job:
```
Sex='F'
```
For more information about using the Where tab, see Creating or Configuring a WHERE Clause.
Set the other criteria for your data extraction. The sample job uses the Order by tab to sort on the Age column in ascending order.
Click OK to save the selection criteria for the target table included in the job.

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.
To view the target table, right-click the target and select Open. The following display shows the target table data for the sample job.

Sample Target Table in the View Data Window

Note that all of the employee dependents listed in the output are female. They are sorted by age in ascending order.