180
181
CHAPTER
11
Optimizing Process Flows
Building Efficient Process Flows
182
Introduction to Building Efficient Process Flows
182
Choosing Between Views or Physical Tables
182
Cleansing and Validating Data
183
Managing Columns
183
Drop Columns That Are Not Needed 183
Do Not Add Unneeded Columns
183
Aggregate Columns for Efficiency
184
Match the Size of Column Variables to Data Length
184
Managing Disk Space Use for Intermediate Files
184
Deleting Intermediate Files at the End of Processing
184
Deleting Intermediate Files at the End of Processing
185
Minimizing Remote Data Access 185
Setting Options for Table Loads 186
Using Transformations for Star Schemas and Lookups 186

Using Surrogate Keys 187
Working from Simple to Complex 187
Analyzing Process Flow Performance 187
Introduction to Analyzing Process Flow Performance 187
Simple Debugging Techniques 188
Monitoring Job Status 188
Verifying a Transformation’s Output 188
Limiting a Transformation’s Input 188
Redirecting Large SAS Logs to a File 189
Setting SAS Options for Jobs and Transformations 189
Using SAS Logs to Analyze Process Flows 189
Introduction to Using SAS Logs to Analyze Process Flows 189
Evaluating SAS Logs 190
Capturing Additional SAS Options in the SAS Log 190
Redirecting SAS Data Integration Studio’s Log to a File 191
Viewing or Hiding the Log in SAS Data Integration Studio 191
Using Status Codes to Analyze Process Flows 191
Adding Debugging Code to a Process Flow 191
Analyzing Transformation Output Tables 192
Viewing the Output Table for a Transformation 192
Setting SAS Options to Preserve Intermediate Files for Batch Jobs 192
Using a Transformation’s Property Window to Redirect Output Files 193
Adding a List Data Transformation to the Process Flow 193
Adding a User Written Code Transformation to the Process Flow 194
182 Building Efficient Process Flows Chapter 11
Building Efficient Process Flows
Introduction to Building Efficient Process Flows
Building efficient processes to extract data from operational systems, transform it,
and load it into the star schema data model is critical to the success of your process
flows. Efficiency takes on greater importance as data volumes and complexity increase.

This section describes some simple techniques that can be applied to your processes to
improve their performance.
Choosing Between Views or Physical Tables
In general, each step in a process flow creates an output table that becomes the input
for the next step in the flow. Consider what format would be best for transferring data
between steps in the flow. There are two choices:
write the output for a step to disk (in the form of SAS data files or RDBMS tables)
create views that process input and pass the output directly to the next step, with
the intent of bypassing some writes to disk
SAS supports two kinds of views, SQL views and DATA Step views, and the two types
of views can behave differently. Switching from views to physical tables or tables to
views sometimes makes little difference in a process flow. At other times, improvements
can be significant. The following tips are useful:
If the data that is defined by a view is only referenced once in a process flow, then
a view is usually appropriate.
If the data that is defined by a view is referenced multiple times in a process flow,
then putting the data into a physical table will likely improve overall performance.
As a view, SAS must execute the underlying code repeatedly, each time the view is
accessed.
If the view is referenced once in an process flow, but the reference is a
resource-intensive procedure that performs multiple passes of the input, then
consider using a physical table.
If the view is SQL and is referenced once, but the reference is another SQL view,
then consider using a physical table. SAS SQL optimization can be less effective
when views are nested. This is especially true if the steps involve joins or RDBMS
sources.
If the view is SQL and involves a multi-way join, it is subject to performance
limitations and disk space considerations.
Assess the overall impact to your process flow if you make changes based on these tips.
In some circumstances, you might find that you have to sacrifice performance in order

to conserve disk space.
Some of the standard transformations provided with SAS Data Integration Studio
have a
Create View option on their Options tabs, or a check box that serves the same
purpose. Some of the transformations that enable you to specify a view format or a
physical table format for their temporary output tables include the following:
Append
Data Validation
Extract
Optimizing Process Flows Managing Columns 183
Library Contents
Lookup
SQL Join
Use the appropriate control in the interface to make the switch, and test the process.
Cleansing and Validating Data
Clean and deduplicate the incoming data early in the process flow so that extra data
that might cause downstream errors in the flow is caught and eliminated quickly. This
process can reduce the volume of data that is being sent through the process flow.
To clean the data, consider using the Sort transformation with the NODUPKEY
option and/or the Data Validation transformation. The Data Validation transformation
can perform missing-value detection and invalid-value validation in a single pass of the
data. It is important to eliminate extra passes over the data, so try to code all of these
validations into a single transformation. The Data Validation transformation also
provides deduplication capabilities and error-condition handling. See “Example:
Creating a Data Validation Job” on page 167. See also “Create Match Code and Apply
Lookup Standardization Transformations” on page 105.
Managing Columns
Drop Columns That Are Not Needed
As soon as the data comes in from a source, consider dropping any columns that are
not required for subsequent transformations in the flow. Drop columns and make

aggregations early in the process flow instead of late so that extraneous detail data is
not being carried along between all transformations in the flow. The goal is to create a
structure that matches the ultimate target table structure as closely as possible, early
in an process flow, so that extra data is not being carried along.
To drop columns in the output table for a SAS Data Integration Studio
transformation, click the
Mapping tab and remove the extra columns from the
Target
table
area on the tab. Use derived mappings to create expressions to map several
columns together. You can also turn off automatic mapping for a transformation by
right-clicking the transformation in the process flow, then deselecting the
Automap
option in the popup menu. You can then build your own transformation output table
columns to match your ultimate target table and map.
Do Not Add Unneeded Columns
As data is passed from step to step in an process flow, columns could be added or
modified. For example, column names, lengths, or formats might be added or changed.
In SAS Data Integration Studio, these modifications to a table, which are done on a
transformation’s
Mapping tab, often result in the generation of an intermediate SQL
view step. In many situations, that intermediate step adds processing time. Try to
avoid generating more of these steps than is necessary.
Accordingly, instead of doing column modifications or additions throughout many
transformations in an process flow, rework your flow so that these activities are
consolidated within fewer transformations. Avoid using unnecessary aliases; if the
mapping between columns is one-to-one, then keep the same column names. Avoid
multiple mappings on the same column, such as converting a column from a numeric to
184 Managing Disk Space Use for Intermediate Files Chapter 11
a character value in one transformation and then converting it back from a character to

a numeric value in another transformation. For aggregation steps, do any column
renaming within those transformations, rather than in subsequent transformations.
Aggregate Columns for Efficiency
When you add column mappings, also consider the level of detail that is being
retained. Ask these questions:
Is the data being processed at the right level of detail?
Can the data be aggregated in some way?
Aggregations and summarizations eliminate redundant information and reduce the
number of records that have to be retained, processed, and loaded into a data collection.
Match the Size of Column Variables to Data Length
Verify that the size of the column variables in the data collection is appropriate to
the data length. Consider both the current and future uses of the data:
Are the keys the right length for the current data?
Will the keys accommodate future growth?
Are the data sizes on other variables correct?
Do the data sizes need to be increased or decreased?
Data volumes multiply quickly, so ensure that the variables that are being stored in the
data warehouse are the right size for the data.
Managing Disk Space Use for Intermediate Files
Deleting Intermediate Files at the End of Processing
As described in “How Are Intermediate Files Deleted?” on page 8, intermediate files
are usually deleted after they have served their purpose. However, it is possible that
some intermediate files might be retained longer than desired in a particular process
flow. For example, some user-written transformations might not delete the temporary
files that they create.
The following is a post-processing macro that can be incorporated into an process
flow. It uses the DATASETS procedure to delete all data sets in the Work library,
including any intermediate files that have been saved to the Work library.
%macro clear_work;
%local work_members;

proc sql noprint;
select memname
into :work_members separated by ","
from dictionary.tables
where
libname = "WORK" and
memtype = "DATA";
quit;
data _null_;
work_members = symget("work_members");
num_members = input(symget("sqlobs"), best.);