The example job that
is described in this section uses an Apply Lookup Standardization
transformation. This transformation applies one or more standardization
schemes to one or more columns in a source table. Applying schemes
modifies your source data according to rules that are defined in the
schemes. The specific process of scheme application varies based on
your input. However, in general, when you apply a scheme to a source
column, each value in that column is compared to all data values in
the scheme. If the source value matches a scheme data value, the associated
standardization value in the scheme is written into the target as
a replacement for the source value. If no match is found, the source
value is written into the target without change.
The first task is to
create a job flow that reads a table with nonstandard data (MANUFACTURERS),
uses a standardization scheme to correct the data, and then writes
the corrected output to a target table (MANUFACTURERS_STANDARDIZED).
The flow would look similar to the following figure:
Example Job with an Apply Lookup Standardization Transformation
Perform the following
steps to create and populate the job.
-
Create an empty SAS
Data Integration Studio job.
-
In the
Data folder
of the Transformations tree, drag the Apply Lookup Standardization
transformation into the empty job in the
Diagram tab.
-
Select and drag a source
table from its folder and drop it before the Apply Lookup Standardization
transformation. In this sample job, the name of the source is MANUFACTURERS.
The source provides contact information for suppliers of computer
equipment. In the MANUFACTURERS table, the
Name column
contains inconsistent values for the supplier named
Computer
Furniture
, as depicted in the following display:
Source Table Data with Errors in the Name Column
-
Drag the cursor from
the source table to the input port of the Apply Lookup Standardization
transformation. This action connects the source to the transformation.
-
In the
Access folder
of the Transformations tree, drag the Table Loader transformation
into the empty job in the
Diagram tab.
-
Drag the cursor from
the output of the Apply Lookup Standardization to the input port of
the Table Loader transformation. This action connects the two transformations.
.
-
Drag the target table
from its folder and drop it after the Table Loader transformation
on the
Diagram tab. In this sample job, the
name of the target is MANUFACTURERS_STANDARDIZED. The target has the
same columns as the source.
-