Creating Custom DI Transformations Using Macro Variables
SAS Data Integration (DI) Studio provides a comprehensive set of transformations for performing the ETL processes necessary to build and maintain a data warehouse , however with the plethora of tasks required there are occasions when a custom transformation will be required.
SAS DI Studio automatically generates a set of macro variables for each input and output table within a transformation. These variables can be leveraged within a custom transformation to make it dynamic and reusable. The table below lists some of the commonly used macro variables and describes their purpose.
In each case below, n can be replaced by a number to represent the nth input / output table (starting from 0 and incrementing by 1 for each table). If the transformation only has one input/output then the n can be dropped from the macro variable name.
|
Macro Variable Name |
Purpose |
|---|---|
|
&_INPUT_count / &_OUTPUT_count |
Provides a count of the input / output tables for the transformation, useful for looping through tables |
|
&_INPUTn / &_OUTPUTn |
Provides the SAS two-level name of the table |
|
&_INPUTn_connect / &_OUTPUTn_connect |
If the table is a DBMS table (e.g. SQL Server) then this macro variable provides the connection string that can be used in a CONNECT statement for explicit pass-through SQL processing. |
| &_INPUTn_engine / &_OUTPUTn_engine | Provides the library engine for the table |
| &_OUTPUTn_keep | List of variables to keep for the output table, dynamically generated based on mappings within the transform. |
As such the following code can be used to create a custom transformation which:
- Sorts the (single) input data set (by a set of variables specified using the &BYVAR parameter)
- Writes the sorted data set to the transformations first output data set keeping only the required variables
- Writes the duplicate observations to the transformation second output data set keeping only the required variables
