SAS Data Integration Studio 3.3- P36 doc
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (548.03 KB, 5 trang )
170 Configure the Data Validation Transformation Chapter 10
10 Click and drag the table CHECKING_TRANS_VALID
into the target drop zone. The
table appears as the target in the new job, along with a Loader transformation, as
shown in the following display.
Display 10.18 Sources and Targets in the Data Validation Job
The job now contains a complete process flow diagram. The next task is to configure
the Data Validation transformation.
Configure the Data Validation Transformation
The example job has now been populated with a source, a target, and some
transformations. Follow these steps to configure the Data Validation transformation:
1 In the Process Designer window, double-click the Data Validation
transformation to display the transformation’s properties window. To use a lookup
table to validate values, click the
Invalid Values tab.
2 On the Invalid Values tab, click New to display the Invalid Values window.
Click
Column Name, display the columns of the CHECKING_TRANS table, and
select the column CHKING_TRANS_METHOD_CD.
Example Process Flows Configure the Data Validation Transformation 171
3 Click the browse button to the right of the Lookup Table
field and select
CHECKING_TRANS_LOOKUP. Then click
OK to return to the Data Validation
properties window.
Display 10.19 Invalid Values Window
4 To specify the name of the error table, click the
Options
tab. In the Error Table
row, enter the SAS libref and filename (rather than the metadata names) of the
error table, such as
ORGOLD.CHECKING_TRANS_ERROR. Click Apply
to store your
input.
5 To replace missing values, click the Missing Values tab, then click New. In the
Missing Values window, select the column CHKING_TRANS_CHANNEL_CD. In
the
Action if missing field, select Change value to. In the New Value field,
type
"??", with quotation marks. Click OK to return to the properties window.
Display 10.20 Missing Values Window
6 To configure the detection of duplicate values, click the
Duplicate Values tab.
Click
New to display the Duplicate Values window. In the
Available Columns
list,
select CHKING_TRANS_ID, then click the arrow to move that column name into
the
Selected Columns
list.
172 Run the Job and Check the Log Chapter 10
7 To specify that the job is to be aborted if a duplicate transaction ID is found, select
Abort Job
in the
Action if duplicates
field.
Display 10.21 The Duplicate Values Window
8 In the properties window, click OK to store your input and close the window.
The transformation is now fully configured and the job is ready to run. The next step
is to submit the job and check the SAS log.
Run the Job and Check the Log
After the metadata for a job is complete, you submit the job for execution in order to
load data into the target.
1 With the job displayed in the Process Designer window, select Process Submit
from the menu bar. SAS Data Integration Studio generates code for the job and
submits the code to a SAS application server. The server executes the code.
2 If a pop-up error message appears, or if you want to look at the log for the
completed job, click the
Log tab in the Process Designer window.
3 On the Log tab, scroll through the SAS log information that was generated during
the execution of the job. The code that was executed for the job is available in the
Source Code tab.
4 If you find errors in the source code, correct the properties windows of the affected
transformations.
5 Correct the metadata and resubmit the job until there are no more errors.
6 After the job runs without error, save the job. Select File Save from the menu
bar.
The next task is to verify that the job created the correct output.
Example Process Flows Verify Job Outputs 173
Verify Job Outputs
After the job runs without error and has been saved, open the target table
CHECKING_TRANS_VALID and the error table CHECKING_TRANS_ERROR to check
the results.
1
To view the data in CHECKING_TRANS_VALID, right-click the table in the
Project tree or in the Process Designer and select
View Data
.
2
To compare the target to the source, repeat the previous step for the source table
CHECKING_TRANS. As shown in the following display, three missing source
values have received the value ?? in the target in place of the former missing
values.
Display 10.22 Contents of the Target Table CHECKING_TRANS_VALID
3 The source table contains three invalid values. These values were correctly
identified as invalid because they did not appear in the lookup table
CHECKING_TRANS_LOOKUP. The following display confirms that three invalid
values were written into the error table. A careful look at the target table in the
preceding display shows that the source rows were not written into the target.
These errors are also noted in the log.
Display 10.23 Contents of CHECKING_TRANS_ERROR
4 The data validation job was configured to abort if a duplicate value was found in
the CHKING_TRANS_ID column. The source contained no duplicate values in
that column, so the job ran to completion.
174 Example: Using a Generated Transformation in a Job Chapter 10
Example: Using a Generated Transformation in a Job
Preparation
Suppose that you need to perform a special task with a data set, and none of the
standard transformations in the SAS Data Integration Studio Process Library support
this task. An administrator can use the Transformation Generator wizard to create a
custom transformation and make it available in the Process Library. The generated
transformation can then be used in any SAS Data Integration Studio job.
For example, suppose that you needed to create a report that displays hitting
statistics for baseball teams. The following display shows the kind of output that is
desired.
Display 10.24 Tigers Hitting Statistics 2002 Report
An administrator could use the Transformation Generator wizard to create a
transformation that reads an input table with a certain column structure, enables you
to specify certain options, calculates hitting statistics, and displays the result to the
Output tab in the Process Designer window. SAS Data Integration Studio users could
then use this transformation in any job.
Assume that the following preparations have been made:
An administrator has created the generated transformation (PrintHittingStats),
and this transformation is available in the Process Library. For more information
about creating generated transformations, see “Maintaining Generated
Transformations” on page 75.
The input to the PrintHittingStats transformation is a table that contains batting
statistics for a baseball team. The columns in the source table are assumed to be
similar to the columns shown in the following display.
Display 10.25 Contents of TigersHitting2002 Table
