7 Proc SQL Automatic Macro Variables for debugging

During the execution of each PROC SQL step, macro variables are set up with certain values after the statement is executed. These Proc SQL Automatic Macro Variables can then be tested within a macro to determine whether the PROC SQL step should continue to be executed.

When each PROC SQL statement is executed, the following macro variables are updated with the values from the PROC SQL statement:

1. SQLEXITCODE

It contains the highest return code from some types of SQL insert failures. This return code is written to the SYSERR macro variable when PROC SQL terminates.

Return Codes Definition
0 PROC SQL statement completed successfully with no errors.
4 PROC SQL statement encountered a situation for which it issued a warning. The statement continued to execute
8 PROC SQL statement encountered an error. The statement stopped execution at this point
12 PROC SQL statement encountered an internal error, indicating a bug in PROC SQL that should be reported to SAS Technical Support. These errors can occur only during compile time
16 PROC SQL statement encountered a user error. For example, this error code is used when a subquery (that can return only a single value) evaluates to more than one row. These errors can be detected only during run time.
24 PROC SQL statement encountered a system error. For example, this error is used if the system cannot write to a PROC SQL table because the disk is full. These errors can occur only during run time
28 PROC SQL statement encountered an internal error, indicating a bug in PROC SQL that should be reported to SAS Technical Support. These errors can occur only during run time.
PROC SQL Return Codes – SQLRC

2. SQLOBS

It contains the number of rows processed by an SQL procedure statement. For example, the SQLOBS macro variable contains the number of rows formatted and displayed in SAS output by a SELECT statement. It also includes the number of rows deleted by a DELETE statement.

proc sql;
	create table class as select * from sashelp.class where age ge 15;
quit;

%put sqlobs= &sqlobs

SQLOBS is assigned after the SELECT statement executes.
As you can see, in this case, 19 records were read to obtain the count total. However, SQLOBS is 4 since only 5 records have been generated.

The documentation provides some helpful considerations about how SQLOBS is affected by the NOPRINT option.

With NOPRINT, the value of the SQLOBS macro variable depends on whether an output table, single macro variable, macro variable list, or macro variable range is created:

  • If an output table is created, SQLOBS contains the number of rows in the output table.
  • If no data set, table, macro variable list, or macro variable range is created, then SQLOBS contains the Value 1.
  • If a single macro variable is defined, SQLOBS contains the value 1.
  • If a macro variable list is created, SQLOBS contains the number of rows processed to create the macro variable list or range.

If an SQL view is created, then SQLOBS contains the value 0.

3. SQLOOPS

Contains the number of iterations that PROC SQL processes during the inner loop. The number of iterations increases proportionally with the complexity of the query.

Please read our article about Limiting Iterations with the LOOPS= Option for more information.

Automatic Macro Variables for Relational Databases

There are four SAS macro variables: SYSDBMSG, SYSDBRC, SQLXMSG, and SQLXRC. SYSDBMSG and SQLXMSG are blank at first, and SYSDBRC and SQLXRC are both zero. The SAS/ACCESS engine and your DBMS determine these values.

You can use SQLXMSG and SQLXRC only through explicit pass-through with the SQL pass-through facility. 

4. SQLRC 

In SQLXRC, the return code is database-specific. So, an Oracle of 43568 is probably not the same as a Teradata of 43568.

5. SQLXMSG

SYSDBMSG contains the text of the error message you would see in the log. It will indicate what the problem was.

Note: The value of the SQLXMSG macro variable can contain special characters such as contain special characters ( &, %, /, *, and 😉

So it is recommended to use the %SUPERQ macro function when working with SQLXMSG content.

$let dbErrorMsg=%superq (sqlxmsg) ;

6. SYSDBMSG

It contains DBMS-specific text of the error message you see in the log. It will indicate what the problem was.

7. SYSDBRC

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

These SQL automatic variables allow for more robust and database-independent error-trapping routines.

Every week we'll send you SAS tips and in-depth tutorials

JOIN OUR COMMUNITY OF SAS Programmers!

Subhro Kar is an Analyst with over five years of experience. As a programmer specializing in SAS (Statistical Analysis System), Subhro also offers tutorials and guides on how to approach the coding language. His website, 9to5sas, offers students and new programmers useful easy-to-grasp resources to help them understand the fundamentals of SAS. Through this website, he shares his passion for programming while giving back to up-and-coming programmers in the field. Subhro’s mission is to offer quality tips, tricks, and lessons that give SAS beginners the skills they need to succeed.