Top 7 Tips to Troubleshoot Your Proc SQL Code Like a Pro

Debugging Proc SQL code can be challenging, especially when dealing with complex queries or multiple tables. It can be difficult to identify the source of errors or understand what is happening behind the scenes.

The frustration of debugging Proc SQL code can lead to wasted time and lost productivity. It can also be frustrating for those new to SAS and Proc SQL, as they may not have the necessary skills or experience to troubleshoot effectively.

Fortunately, SAS provides several automatic macro variables to help debug Proc SQL code. These variables can provide valuable information about the query, such as the number of rows processed, the execution time, and any errors that occurred. You can more easily identify and fix problems in your Proc SQL code using these macro variables.

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.

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.

Conclusion

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

In conclusion, using the automatic macro variables provided by SAS can greatly simplify the process of debugging Proc SQL code. By understanding the various variables and how to use them, you can save time and improve the efficiency of your SAS programming.