Amadeus cookies policy - you'll see this message only once.

Amadeus use cookies on this website. They help us to know a little bit about you and how you use our website, which improves the browsing experience and marketing - both for you and for others. They are stored locally on your computer or mobile device. To accept cookies, continue browsing as normal. Or, go to the privacy policy for more information.

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

2012 07 MJ Creating custom DI transformations using DI Studio macro variables