PROC SQL Troubleshooting and Debugging Techniques
PROC SQL provides several options which can be used to control and gain information about queries. This article will highlight several of the more common methods for troubleshooting an SQL query.
The VALIDATE statement
Use this statement to validate the syntax of an SQL query before any data is processed. The following example uses the validate statement to check the query.
When executed a message is sent to the log indicating the validity of the query -
NOTE: PROC SQL statement has valid syntax.
FEEDBACK/NOFEEDBACK Option
The FEEDBACK option expands a SELECT * statement into all the selected variables including any macro variables. This can be very useful to show the variable order when joining multiple tables. The results of the expanded list of columns are displayed in the SAS log.
The FEEDBACK option can also be used to display macro value resolution.
_METHOD WITH MSGLEVEL=I
These options are extremely useful for understanding the choices made by the SQL optimizer - index use, which join strategy and any sorting performed. The _METHOD option returns a number of codes to the log, a few important ones are -
SQXJM – A merge join was performed
SQXJNDX - An index join was performed.
SQXJHSJ – A Hash join was performed.
SQXSORT – A sort operation was performed.
These options can be used to debug any issues, for example detect if the correct join strategy is being used, are my indexes being used in the join?
Note: You need to specify the MSGLEVEL=I options to activate this setting.
INOBS= OPTION
The INOBS= option limits the number of rows that PROC SQL processes. This option can be very useful when developing a new query and testing on a limited number of records. There is also an equivalent OUTOBS= option which reduces the number of records being written by a query.
METHOD AND _TREE OPTIONS WITH MSGLEVEL=I
The _TREE option, provides a graphical representation of the way the query optimizer handled the execution of a query. This options shows the chosen query plan, this decision is based on multiple factors (estimated CPU and I/O). Sometime this plan may not be the most efficient. Using the TREE option can help debug.
RESET STATEMENT
The RESET statement is used to add, drop, or change one or more PROC SQL options without the need to restart the procedure. This example shows the FEEDBACK option being turned on and then off.
Macro Variables
Macro variables are useful debugging tools. Two useful macros are SQLOBS and SQLRC.
&SQLOBS
The SQLOBS macro variable displays the number of rows that are processed by an SQL procedure statement.
&SQLRC
The SQLRC macro variable displays a status value that indicates whether the PROC SQL statement was successful or not.
The %PUT statement displays a return code of zero, which indicates that the SELECT statement was successful.
