Macro Variables for Relational Databases

Automatic Macro Variables for SAS/ACCESS Processing

SYSDBMSG, SYSDBRC, SQLXMSG, and SQLXRC are automatic SAS macro variables. The SAS/ACCESS engine and your DBMS determine their values. Initially, SYSDBMSG and SQLXMSG are blank, and SYSDBRC and SQLXRC are set to 0.

SAS/ACCESS generates several return codes and error messages while it processes your programs. This information is available to you through these SAS macro variables.

SYSDBMSG

contains DBMS-specific error messages that are generated when you use SAS/ACCESS software to access your DBMS data.

SYSDBRC

contains DBMS-specific error codes that are generated when you use SAS/ACCESS software to access your DBMS data. Error codes that are returned are text, not numbers.

You can use these variables anywhere while you are accessing DBMS data. Only one set of macro variables is provided, however. So it is possible that, if tables from two different DBMSs are accessed, it might not be clear from which DBMS the error message originated. To address this problem, the name of the DBMS is inserted at the beginning of the SYSDBMSG macro variable message or value. The contents of the SYSDBMSG and SYSDBRC macro variables can be printed in the SAS log by using the %PUT macro. They are reset after each SAS/ACCESS LIBNAME statement, DATA step, or procedure is executed. In the statement below, %SUPERQ masks special characters such as &, %, and any unbalanced parentheses or quotation marks that might exist in the text stored in the SYSDBMSG macro.

%put %superq(SYSDBMSG)

These special characters can cause unpredictable results if you use this statement:

%put &SYSDBMSG

It is more advantageous to use %SUPERQ.

If you try to connect to Oracle and use the incorrect password, you receive the messages shown in this output.

2? libname mydblib oracle user=pierre pass=paris path="mypath";

ERROR: Oracle error trying to establish connection.  Oracle error is
       ORA-01017: invalid username/password; logon denied
ERROR: Error in the LIBNAME or FILENAME statement.
 3? %put %superq(sysdbmsg);

Oracle: ORA-01017: invalid username/passsword; logon denied
 4? %put &sysdbrc;

-1017
 5?

You can also use SYMGET to retrieve error messages:

msg=symget("SYSDBMSG");  

Here is an example.

data_null_;
msg=symget("SYSDBMSG");
put msg;
run;

The SQL pass-through facility generates return codes and error messages that are available to you through these SAS macro variables:

SQLXMSG

contains DBMS-specific error messages.

SQLXRC

contains DBMS-specific error codes.

You can use SQLXMSG and SQLXRC through implicit or explicit pass-through with the SQL pass-through facility. See Return Codes.

You can print the contents of SQLXMSG and SQLXRC in the SAS log by using the %PUT macro. SQLXMSG is reset to a blank string, and SQLXRC is reset to 0 when any SQL pass-through facility statement is executed.

Macro Variables for Passing Content to DBMS Queries

With some data sources, you can pass extra content to the data source when you pass an explicit SQL query with PROC SQL. To do this, you enable the PRESERVE_COMMENTS= LIBNAME option and pass the content in a comment. The comment can contain keywords or markup that is used by the query processor for your data source.

For the same data sources that use the PRESERVE_COMMENTS= LIBNAME option, you can pass similar content at the beginning of SQL statements that are generated by SAS for other operations, such as a call to PROC PRINT. To do this, you define a macro variable that corresponds to your data source. The text that you assign to the marcro variable is added at the front of each generated SQL command that is passed to the data source. This text can contain keywords or markup that is used by the query processor for your data source. To see the query that is passed to the data source, including any text that you have added, specify SASTRACE=',,,d' in the OPTIONS statement.

For example, if you want to add content to the front of a query before a call to PROC PRINT, you might enter the following code. In this case, the Db2lib libref connects to a DB2 data source.

%let db2_sql_comment=<OPTGUIDELINES> <REGISTRY> <OPTION NAME='DB2_UNION_OPTIMIZATION' 
VALUE='DISABLE_OJPPD_FOR_NP'/> </REGISTRY> </OPTGUIDELINES>; ;
PROC PRINT data=db2lib.cars; run;

The text string that you assign to the db2_sql_comment variable is added to the beginning of the SELECT statement that SAS generates from the call to PROC PRINT. The resulting query that is passed to the DB2 data source would be:

/* <OPTGUIDELINES> <REGISTRY> <OPTION NAME='DB2_UNION_OPTIMIZATION' 
VALUE='DISABLE_OJPPD_FOR_NP'/> </REGISTRY> </OPTGUIDELINES>; */ SELECT * from CARS