Example: Creating a SAS Code Transformation Template | SAS 9.1.3 ETL Studio: Users Guide

< Day Day Up >

This example demonstrates how to create a user-written SAS code transformation template.

Overview

As described in "Transformation Generator Wizard" on page 112, one of the easiest ways to customize SAS ETL Studio is to write your own SAS code transformation templates. The Transformation Generator wizard guides you through the steps of specifying SAS code for a transformation template and saving the template in the current metadata repository. After a template is saved, it is displayed in the Process Library tree, where it is available for use in any job.

The Transformation Generator wizard is used to create custom SAS code transformation templates. The wizard enables you to enter the SAS code that runs when the template is executed as part of a job. This code typically includes macro variables. When you use a macro variable, the person who configures the job in which the template appears must specify the value of the variable, and SAS ETL Studio generates the %let statement that creates the variable and assigns a value to it.

The rules for writing SAS code transformations templates are as follows:

A template can have 0 or 1 input tables or transformation objects and 0 or 1 output tables or transformation objects.
You cannot use hard-coded names for the input or output.
The code that is entered must have valid SAS syntax.

Preparation

For this example, assume that a SAS data set called TigersHitting2002 contains batting statistics for a baseball team. The following display shows the content and structure of this data set.

click to expand
Display 9.13: Contents of Data Set TigersHitting2002

The goal is to create a transformation template that takes a data set such as TigersHitting2002 as input and produces a report. The report will display a user-defined title, a user-defined set of columns, and it will calculate the sum of the values in one column of the table. The following display shows the kind of output that is desired.

click to expand
Display 9.14: Example Hitting Statistics Report

Assume the following about the current example:

The main metadata repository is under change-management control. In this example, however, assume that an administrator is creating the new template, so the template would be added to directly to the Process Library tree, without having to be checked in. For details about change management, see "Working with Change Management" on page 64.
You have selected a default SAS application server for SAS ETL Studio, as described in "Select a Default SAS Application Server" on page 59.

Start SAS ETL Studio and Open the Appropriate Metadata Profile

Perform the following steps to begin work in SAS ETL Studio:

Start SAS ETL Studio.
Open the appropriate metadata profile. For the current example, the metadata profile would be for an administrator who has the appropriate level of privilege to directly update metadata in the main metadata repository, without having to work through a project repository.

The next task is to display the Transformation Generator wizard.

Display the Transformation Generator Wizard

Perform the following steps to display the wizard that will guide you through the process of creating a user-defined SAS code transformation template.

From the SAS ETL Studio desktop, select Tools Transformation Generator from the menu bar. The first window of the wizard is displayed, as shown in the following display.

Display 9.15: First Window in the Transformation Generator Wizard
Enter a name and a description for the new transformation template, as shown in the previous display.
Specify the folder in the Process Library tree in which you want to store the new transformation. You do this by specifying a relative path from the Process Library folder to the directory that will hold the transformation. If the path contains two or more directory levels, separate directory level names with a period. For example, UserDefined.Reports
When you are finished with this window, click .

The next task is to specify SAS code for this transformation.

Specify SAS Code for the Transformation Template

In the SAS Code window of the wizard, enter SAS code for the transformation template. The following display shows the code that could be entered for the current example.

click to expand
Display 9.16: SAS Code Window

A number of macro variables appear in the code. The variable &SYSLAST is a system variable that refers to the last data set created. The &SYSLAST variable enables a transformation in a process flow diagram to use the output from the previous transformation.

Another system variable, &_OUTPUT, is also available, but we have not used it in this example. &_OUTPUT enables a transformation in a process flow diagram to send its output to a temporary work table.

The other variables that are shown in the previous display, such as &ColumnsToPrint, are user-defined variables. Any user-defined variables must be defined in the SAS Code Options window.

After you have finished writing your SAS code, click .

Define Any User-Defined Variables

In the SAS Code Options window of the wizard, define any user-defined variables that you used in the SAS Code window. The following table shows the values that would be entered for the user-defined variables that are shown Display 9.16 on page 123.

Table 9.2: User-Defined Variables from the SAS Code Window
Option Name	Macro Variable	Description	Type
Home runs	HomeRuns	Home runs hit	OPTION
Columns to print	ColumnsToPrint	Name of the columns to print	INPUTS SAS
Report title	ReportTitle	Title of the report	OPTION

The following display shows the SAS Code Options window after the values in the previous table have been entered.

click to expand
Display 9.17: SAS Code Options Window

The SAS Code Options window enables you to specify five types of variables:

OPTION
INPUTS SAS
INPUTS SQL
OUTPUTS SAS
OUTPUTS SQL

The variables that you define in the SAS Code Options window will be used in the transformation template that you are creating. For example, variables of type OPTION will appear on the Options tab of the properties window for the PrintHittingStatistics template. Users will display the Options tab of the window and enter values for each option.

The INPUTS and OUTPUTS variables will appear on the Column Options tab of the properties window for the transformation template that you are creating. For example, users will display the Column Options tab of the properties window for the PrintHittingStatistics template and select columns that correspond to the ColumnsToPrint variable of type INPUTS SAS.

To define any user-defined variables that you used in the SAS Code Options window, perform the following steps for each variable:

Click the button. A new row displays in the options table.
In the Option Name field, enter a descriptive name. Replace the initial value (Untitled). Double-click the value to highlight it, then type over the highlighted value.
In the Macro Variable field, enter the name of the macro variable as it appears in the SAS Code Options window.
In the Description field, enter a description of the variable.
In the Type field, double-click the current value. A down arrow displays. Click the down arrow to reveal a list of types, and select one of them.

When you are finished defining the user-defined variables in your transformation, click .

Specify the Remaining Options for the Transformation Template

Use the Transform Options window to specify the remaining options for your transformation template. The Transform Options window for the example transformation resembles the following display.

click to expand
Display 9.18: Transform Options Window

Use the controls that are described as follows:

Register transform to metadata server—Select this check box if you want to save your transformation as a metadata object in the current metadata repository. Do this if you want the transformation template to be available in the Process Library tree. For this example, assume that this option is selected.

Save transform to a local file—Select this check box if you want to save your transformation as an XML file on the local file system. Other SAS ETL Studio users can imported transformations that are saved this way.

File—The name of, and path to, the XML file that was previously described.

Generate SYSLAST for this step—Determines whether the &SYSLAST macro variable is available to your code. Leave this check box selected unless your transformation does not require any input.

This transform displays an output template—If you select this check box, when a user drags your transformation to a process flow diagram, the template displayed includes a drop zone for an output table or transformation.

Prompt—Specifies the text that you want displayed in the output drop zone.

This transform displays an input template—If you select this check box, when a user drags your transformation to a process flow diagram, the template displayed includes a drop zone for an input table or transformation.

Prompt—Specifies the text that you want displayed in the input drop zone. When you are finished defining options for your transformation, click .

Save the Transformation Template

Use the Wizard Finish window to review the metadata that you have entered. When you are satisfied, click . The transformation is created and saved in your metadata repository or an XML file (or both), as specified in Display 9.18 on page 126.

For this example, assume that the SAS code transformation was saved to the current metadata repository. The template will now be visible in the Process Library tree, as specified in Display 9.12 on page 113. The following display illustrates the updated Process Library tree.

click to expand
Display 9.19: Process Library Tree With User-Defined Transformation Template

The new template, PrintHittingStatistics, can now be used to create a job, as described in "Example: Using a SAS Code Transformation Template in a Job" on page 155.

Document Any Usage Details for the Template

The person who creates a user-written transformation template should document how it can be used to get the desired result. SAS ETL Studio users would need to know the following:

Any requirements for inputs and outputs.

For example, the columns in the source table for the PrintHittingStatistics template are assumed to be similar to the columns that are shown in Display 9.13 on page 121.
Where the template sends its output: to a table, to the Output tab in the Process Designer window, or elsewhere.
How to specify any values that are required by the template.

For example, to produce the report that is shown in Display 9.14 on page 121, a title must be specified, a set of columns must be selected from the source, and the sum of the values in the HR column must be calculated.

< Day Day Up >