About Code Generated for Jobs

Overview

When SAS Data Integration Studio generates code for a job, it typically generates the following items:
  • specific code to perform the transformations used in the job
  • a LIBNAME statement for each table in the job
  • a SYSLAST macro statement at the end of each transformation in the job
  • remote connection statements for any remote execution machine that is specified in the metadata for a transformation within a job
  • macro variables for status handling
You can set options for the code that SAS Data Integration Studio generates for jobs and transformations. For details, see Specifying Options for Jobs and Specifying Options for a Transformation.

LIBNAME Statements

When SAS Data Integration Studio generates code for a job, a library is considered local or remote in relation to the SAS Application Server that executes the job. If the library is stored on one of the machines that is specified in the metadata for the SAS Application Server that executes the job, it is local. Otherwise, it is remote.
SAS Data Integration Studio generates the appropriate LIBNAME statements for local and remote libraries.
Here is the syntax that is generated for a local library:
libname libref <enginer> <"lib-specification"> <connectionOptions> 
<libraryOptions>
<schema=databaseSchema>
<user=userID>
<password=password>;
Here is the syntax that is generated for a remote library:
options
comamid=connection_type; 
%let remote_session_id=host_name <host_port>;
signon
remote_session_id<user=userID>
<password=password>; 
rsubmit remote_session_id; 
   libname <library details>;
endrsubmit;

rsubmit remote_session_id;
proc download
data=table_on_remote_machine
out=table_on_local_machine; 
run; 
endrsubmit; 

SYSLAST Macro Statements

The Options tab in the property window for most transformations includes a field that is named Create SYSLAST Macro Variable. This field specifies whether SAS Data Integration Studio generates a SYSLAST macro variable to hold the name of the transformation's output table. In general, accept the default value of YES when the current transformation creates an output table that should be the input of the next transformation in the process flow. Otherwise, select NO.

Remote Connection Statements

Most transformations within a job can specify their own execution host. When SAS Data Integration Studio generates code for a job, a host is considered local or remote in relation to the SAS Application Server that executes the job. If the host is one of the machines that is specified in the metadata for the SAS Application Server that executes the job, it is local. Otherwise, it is remote.
A remote connection statement is generated if a remote machine has been specified as the execution host for a transformation within a job:
options
comamid=connection_type; 
%let remote_session_id=host_name <host_port>;
signon remote_session_id
<user=userID
password=password>;
rsubmit remote_session_id; 
... SAS code ... 
endrsubmit;
Note: This is done implicitly for users if the machine is remote. Users can also use the Data Transfer transformation to explicitly handle moving data between machine environments when needed. The Data Transfer transformation provides more control over the transfer when needed, such as support for locale-specific settings.

Macro Variables

When SAS Data Integration Studio generates the code for a job, the code includes the macro variables that are listed in the following table:
Macro Variable
Description
etls_jobName
Specifies the name as supplied on the job properties panel.
etls_userID
Specifies the user ID that is used to generate the code for the job.
_INPUT
Specifies the libref.tablename of the first input table.
_INPUT_count
Specifies the count of input tables.
_INPUT_connect
Specifies the connect statement for the table. This macro variable is used for explicit pass-through statements.
_INPUT_engine
Specifies the library engine. This macro variable can be used for explicit pass-through statement construction.
_INPUT_memtype
Specifies the member type of the table, either DATA or VIEW. Users can use this variable to write transformation code to enable creation of views on output tables or to know whether the input is a VIEW.
_INPUT_options
Specifies the table option string, such as COMPRESS=YES ENCRYPT=YES. This macro option is found on the table options dialog box from physical storage tab on the table’s properties window.
_INPUT_alter
Specifies an alter or password option text so that the table can be deleted or altered. This macro variable is a subset of the _options string.
_INPUT_path
Specifies the location of table on metadata server.
_INPUT_type
Specifies a macro given by the prompting framework. This macro variable should always be 1 for usage with SAS Data Integration Studio.
jobID
Specifies the unique metadata ID code that is given to the job when the job is first created.
JOB_RC
Specifies a status handling macro variable that is set and reset (as the job runs) to be the maximum return code value (&trans_rc) of the completed transformations.
_OUTPUT_count
Specifies the count of output tables.
SYSLAST
Specifies the name of the transformation's output table. In general, accept the default value of YES when the current transformation creates an output table that should be the input of the next transformation in the process flow. Otherwise, select NO.
trans_rc
Specifies a status handling macro variable that is set based on the return code of individual steps within a transformation.
Note: Any variable that begins with _INPUT or _OUTPUT deals with the macros that are always generated with transformations that have inputs, outputs, or both. The _INPUT and _OUTPUT variables are present on the first table by default because SAS Data Integration Studio uses a legacy macro set. If identical _INPUT and _INPUT1 variables are present, _INPUT1 is the name that the user chose when setting up the INPUT macro variable, or it is the default if a name was not specified for the macro.
Users can add references to any of these in user-written code. See About User-Written Code. SAS Data Integration Studio uses these macro variables in header comments and in code that is associated with the status handling features of the Return Code Checker, SQL Join, and loader transformations.

User Credentials in Generated Code

By default, SAS Data Integration Studio looks up user credentials rather than explicitly including them in the code that it generates when it accesses tables in a library. This behavior can be changed by selecting Toolsthen selectOptions from the main menu, clicking the General tab, and then selecting or deselecting the Use runtime lookup for credentials for statements requiring credentials check box. When this option is selected, the code generated by SAS Data Integration Studio does not contain user names and passwords for SAS Connect sign-on statements. Instead, the user name and password are looked up at run time, using the Authentication Domain that has been specified for the user is his or her metadata identify on the SAS Metadata Server.
If this option is not selected, the code that is generated is based on the credentials and permission settings of the user who generated the code. When required, such as in LIBNAME statements to a relational DBMS, for pass-through, or for remote machine data movement, the generated code might also contain embedded credentials, with encoded passwords.
If the credentials of the person who created the job are changed and a deployed job contains outdated user credentials, then the deployed job fails to execute. The solution is to redeploy the job with the appropriate credentials.