Using the Additional Generated Code

When the SAS code for the User Written Staging transformation is generated, additional code is generated and precedes the user-written code. This additional code provides information that can be useful in the user-written code. The generation of this additional code is controlled by the options on the User Written section of the Staging Parameters tab.

Macro Variables

Macro variables for any extended attributes can be entered on the Extended Attributes tab of the User-Written Staging Properties dialog box. The name of each extension is the macro variable name, and the value of the extension is the value of the macro variable. If the name has spaces or other invalid characters, they are removed.

&input

If there is a source table to the transformation, then this macro variable contains the libref.tablename of the source table.

&target1–&targetn

These macro variables contain the names of the n target tables in the libref.tablename format.

Macros

These macros are available for use with user-written staging transformations.

The following macro generates table options for specified target tables.
```
%macro _ITMS_tableOptions (targetTableNum=, 
	                        generateTableName=YES,
                          generateIndexes=YES);
```
- If the TARGETTABLENUM= parameter is left blank, then the table options for all the target tables are generated.
- If you specify generateTableName=NO, the macro generates a list of the table options.
- If you specify generateTableName=YES, the macro generates the table name with the table options in parentheses after it.
The following macro generates an ATTRIB statement for each column of the requested table. If the TARGETTABLENUM= parameter is left blank, the macro generates an ATTRIB statement for each column for all tables. The ATTRIB statement includes the column name, type, length, format, informat, and label.
```
%macro _ITMS_attrib (targetTableNum=);
```
The following macro generates assignment statements for all the columns of the requested table that will set the value to " " or . (missing), depending on the type of column. If the TARGETTABLENUM= parameter is left blank, the macro generates assignment statements for all columns in all tables.
```
%macro _ITMS_assignToMissing (targetTableNum=);
```
The following macro generates a statement for each computed column of the requested table. If the TARGETTABLENUM= parameter is left blank, the macro generates a statement for each computed column for all tables.
```
%macro _ITMS_computedColumns (targetTableNum=);
```
The following macro generates a list of all the columns of the requested table. If the TARGETTABLENUM= parameter is left blank, the macro generates a list of all the columns for all tables.
```
%macro _ITMS_columnList (targetTableNum=, 
                         delimiter=,  
                         sqlAlias=); 
```
The additional parameters allow the list to be generated in different ways:

DELIMITER=

This parameter is set to a character string that will be put between each column in the list. The most common value of this parameter is a comma, so that the list can be used in SQL code. If a value is not specified, then the column names will be separated by blanks.

SQLALIAS=

This parameter is an alias that can be used as a prefix for each column name. There will be a period between this prefix and the column name. The most common use of this parameter is in SQL when a table alias is needed for each column.

Tabinfo and Varinfo

You can choose to create the Tabinfo and Varinfo tables in the Work library. These tables can be used in the user-written code to generate SAS code that is based on the target table and column information.

The Tabinfo table contains one record for each target table. The following columns are included in the Tabinfo table:

TABLENM

specifies the name of the table.

LIBREF

specifies the libref for this table.

ISCOMPRESSED

specifies the COMPRESS data set option. The values can be YES (that is, COMPRESS=YES) or NO (that is COMPRESS=NO).

INDEXOPTION

specifies the INDEX data set option (if there is an index on the table).

The Varinfo table contains one record for each column in all the target tables. The following columns are included in the Varinfo table:

TABLENM

specifies the name of the table.

VARNM

specifies the name of the column.

VARLAB40

specifies the column label.

VARFMT

specifies the column format.

VARINFMT

specifies the column informat.

VARLEN

specifies the length of the column.

VARTYPE

specifies the type of the column. The type can be C (that is, character) or N (that is, numeric).

FORMULA

specifies the expression that is used to create this column, if it is a computed column.