9.5. Troubleshooting and Debugging Techniques
PROC SQL provides numerous troubleshooting and debugging capabilities for the practitioner to choose from. From statements to options to macro variables, you can use the various techniques to control the process of finding and gathering information about SQL procedure coding problems quickly and easily.
9.5.1. Validating Queries with the VALIDATE Statement
The SQL procedure syntax checker identifies syntax errors before any data is processed. Syntax checking is automatically turned on in the SQL procedure without any statements or options being specified. But to enable syntax checking without automatically executing a step, you can specify a VALIDATE statement at the beginning of a SELECT statement or, as will be presented later, specify the NOEXEC option.
The VALIDATE statement is available for SAS users to control what SELECT statement is checked. Because you specify it before a SELECT statement, you are better able to control the process of debugging code. A message indicating syntax correctness is automatically displayed in the SAS log when code syntax is valid. Otherwise, an error message is displayed identifying the coding violation. In the next example, a VALIDATE statement is specified at the beginning of a SELECT statement to enable syntax checking without code execution. The SAS log shows that the code contains valid syntax and automatically displays a message to that effect.
SQL Code
PROC SQL;
VALIDATE
SELECT *
FROM PRODUCTS
WHERE PRODTYPE = 'Software';
QUIT;SAS Log Results
PROC SQL;
VALIDATE
SELECT *
FROM PRODUCTS
WHERE PRODTYPE = 'Software';
NOTE: PROC SQL statement has valid syntax.
QUIT; |
9.5.2. Documented PROC SQL Options and Statement
This section shows several examples of widely used, documented SQL options and the RESET statement. A description along with a working example of each option and statement is presented below.
9.5.2.1. FEEDBACK Option
The FEEDBACK option displays additional documentation with SELECT queries. When specified, this option expands a SELECT * (wildcard) statement into a list of columns that it represents by displaying the names of each column in the underlying table(s) as well as any resolved macro values and macro variables. The column display order of a SELECT * statement is determined by the order defined in the table’s record descriptor.
The following example illustrates the FEEDBACK option. Because a SELECT * statement does not automatically display the columns it represents, it may be important to expand the individual column names by specifying the FEEDBACK option. This becomes particularly useful for determining whether a desired column is present in the output and available for documentation purposes. The results of the expanded list of columns are displayed in the SAS log.
SQL Code
PROC SQL FEEDBACK;
SELECT *
FROM PRODUCTS;
QUIT;SAS Log Results
PROC SQL FEEDBACK;
SELECT *
FROM PRODUCTS;
NOTE: Statement transforms to:
select PRODUCTS.prodnum, PRODUCTS.prodname,
|
PRODUCTS.manunum,The FEEDBACK option can be particularly helpful in determining the column order when joining two or more tables. The next example illustrates the expansion of the columns in the SELECT * statement in a two-way equijoin. The FEEDBACK option displays all the columns in both tables.
SQL Code
PROC SQL FEEDBACK;
SELECT *
FROM PRODUCTS, MANUFACTURERS
WHERE PRODUCTS.MANUNUM = MANUFACTURERS.MANUNUM AND
MANUFACTURERS.MANUNAME = 'KPL Enterprises';
QUIT;PROC SQL FEEDBACK;
SELECT *
FROM PRODUCTS, MANUFACTURERS
WHERE PRODUCTS.MANUNUM = MANUFACTURERS
|
The FEEDBACK option can also be used to display macro value and macro variable resolution. The next example shows the macro resolution of the macro variables &LIB, &TABLE, and &GROUPBY for debugging purposes.
SQL Code
%MACRO DUPS(LIB, TABLE, GROUPBY); PROC SQL FEEDBACK; SELECT &GROUPBY, COUNT(*) AS Duplicate_Rows FROM &LIB..&TABLE GROUP BY &GROUPBY HAVING COUNT(*) > 1; QUIT; %MEND DUPS; %DUPS(WORK,PRODUCTS,PRODTYPE);
%MACRO DUPS(LIB, TABLE, GROUPBY);
PROC SQL FEEDBACK;
SELECT &GROUPBY, COUNT(*) AS Duplicate_Rows
FROM &LIB..&TABLE
GROUP BY &GROUPBY
HAVING COUNT(*) > 1;
QUIT;
%MEND DUPS;
%DUPS(WORK,PRODUCTS,PRODTYPE);
NOTE: Statement transforms to:
select PRODUCTS.prodtype, COUNT(*) as
|
You can also specify a %PUT statement instead of the FEEDBACK option to display the values of macro variables after macro resolution. The next example illustrates inserting the macro statement %PUT LIB = &LIB TABLE = &TABLE GROUPBY = &GROUPBY between the QUIT and %MEND statements to produce the results illustrated below.
SQL Code
%MACRO DUPS(LIB, TABLE, GROUPBY);
PROC SQL;
SELECT &GROUPBY, COUNT(*) AS Duplicate_Rows
FROM &LIB..&TABLE
GROUP BY &GROUPBY
HAVING COUNT(*) > 1;
QUIT;
%PUT LIB = &LIB TABLE = &TABLE GROUPBY = &GROUPBY;
%MEND DUPS;
%DUPS(WORK,PRODUCTS,PRODTYPE);%MACRO DUPS(LIB, TABLE, GROUPBY); . . . code not shown . . . LIB = WORK TABLE = PRODUCTS GROUPBY = PRODTYPE |
9.5.2.2. INOBS= Option
The INOBS= option reduces the amount of query execution time by restricting the number of rows that PROC SQL processes. This option is most often used for troubleshooting or debugging purposes where a small number of rows are needed as opposed to all the rows in the table source. Controlling the number of rows processed on input with the INOBS= option is similar to specifying the SAS System option to OBS=. The following example illustrates the INOBS= option being limited to the first ten rows in the PRODUCTS table. A warning message is also displayed in the SAS log indicating that the number of records read was restricted to ten.
SQL Code
PROC SQL INOBS=10;
SELECT *
FROM PRODUCTS;
QUIT;SAS Log Results
PROC SQL INOBS=10;
SELECT *
FROM PRODUCTS;
WARNING: Only 10 records were read from WORK.
|
In the next example, the INOBS= option is set to five in a Cartesian product join (the absence of a WHERE clause). A two-way join with the INOBS=5 specified without a WHERE clause limits the number of rows from each table to five producing a maximum of 25 rows.
PROC SQL INOBS=5;
SELECT prodname, prodcost,
manufacturers.manunum, manuname
FROM PRODUCTS, MANUFACTURERS;
QUIT;SAS Log Results
PROC SQL INOBS=5;
SELECT prodname, prodcost,
manufacturers.manunum, manuname
FROM PRODUCTS, MANUFACTURERS;
NOTE: The execution of this query involves
|
9.5.2.3. LOOPS= Option
The LOOPS= option reduces the amount of query execution time by restricting how many times processing occurs through a query’s inner loop. As with the INOBS= and OUTOBS= options, the LOOPS= option is used for troubleshooting or debugging to prevent the consumption of excess computer resources or the creation of large internal tables as with the processing of multi-table joins. The following example shows the LOOPS= option being restricted to eight inner loops through the rows in the PRODUCTS table.
SQL Code
PROC SQL LOOPS=8;
SELECT *
FROM PRODUCTS;
QUIT;PROC SQL LOOPS=8;
SELECT *
FROM PRODUCTS;
WARNING: PROC SQL statement interrupted by LOOPS=8
|
Results
The SAS System
Product Manufacturer
|
The next example shows what happens when the LOOPS= option is applied in a three-way join by restricting the number of processed inner loops to 50 to prevent the creation of a large and inefficient internal table. To determine an adequate value to assign to the LOOPS= option, you can specify an &SQLOOPS macro variable in a %PUT statement. To learn more about this macro variable, see the section, “Macro Variables,” later in this chapter.
SQL Code
PROC SQL LOOPS=50;
SELECT P.prodname, P.prodcost,
M.manuname,
I.invqty
FROM PRODUCTS P,
MANUFACTURERS M,
INVOICE I
WHERE P.manunum = M.manunum AND
P.prodnum = I.prodnum AND
M.manunum = 500;
QUIT; The SAS System
|
9.5.2.4. NOEXEC Option
The NOEXEC option checks all nonquery statements such as CREATE TABLE or ALTER TABLE within the SQL procedure for syntax-related errors and displays any identified errors in the SAS log. The NOEXEC option is similar to the VALIDATE statement because both of them check for syntax correctness without the execution of any input data. The only difference between the NOEXEC option and the VALIDATE statement is in the way each is specified. As was presented earlier, the VALIDATE statement is specified before each SELECT statement; the NOEXEC option is specified only once as an option in the PROC SQL statement. The NOEXEC option checks any nonquery statements in the step for syntax correctness. The following example illustrates the NOEXEC option and what happens when an error is found in any nonquery statement.
SQL Code
PROC SQL NOEXEC;
CREATE TABLE NOEXEC_CHECK
SELECT *
FROM PRODUCTS
WHERE PRODTYPE = 'Software';
QUIT; PROC SQL NOEXEC;
CREATE TABLE NOEXEC_CHECK
SELECT *
------
73
ERROR 73-322: Expecting an AS.
FROM PRODUCTS
WHERE PRODTYPE = 'Software';
QUIT; |
9.5.2.5. OUTOBS= Option
The OUTOBS= option reduces the amount of query execution time by restricting the number of rows that PROC SQL sends as output to a designated output source. As with the INOBS= option, the OUTOBS= option is most often used for troubleshooting or debugging purposes where a small number of rows are needed in an output table. Controlling the number of rows sent as output with the OUTOBS= option is similar to setting the SAS System option OBS= (or the data set option OBS=). The following example creates an output table called PRODUCTS_SAMPLE by specifying five rows for the OUTOBS= option. A warning message displayed in the SAS log indicates that the new table contains five rows.
SQL Code
PROC SQL OUTOBS=5;
CREATE TABLE PRODUCTS_SAMPLE AS
SELECT *
FROM PRODUCTS;
QUIT;PROC SQL OUTOBS=5;
CREATE TABLE PRODUCTS_SAMPLE AS
SELECT *
FROM PRODUCTS;
WARNING: Statement terminated early due to
|
9.5.2.6. PROMPT Option
The PROMPT option is issued during interactive sessions to prompt users to continue or stop processing when the limits of an INOBS=, LOOPS=, and/or OUTOBS= option are reached. If the PROMPT option is specified along with one or more of these options, a dialog box appears indicating that the limits of the specified option have been reached and asking whether to stop or continue processing. This prompting feature is a useful process for stepping through a running application.
The following example shows the PROMPT option being issued to initiate a dialogue between the SQL procedure session and the user. The PROMPT option specifies that the INOBS= option limit input processing to the first five rows in the PRODUCTS table. A dialog box automatically appears after five rows are read asking whether processing is to stop or continue. If processing is continued, another five rows are processed and, if additional rows are available for processing, another prompt dialog box appears. This dialogue process continues until all rows are processed or until the user halts processing.
SQL Code
PROC SQL PROMPT INOBS=5; SELECT * FROM PRODUCTS; QUIT;
PROC SQL PROMPT INOBS=5;
SELECT *
FROM PRODUCTS;
WARNING: Only 5 records were read from WORK
|
9.5.2.7. RESET Statement
The RESET statement is used to add, drop, or change one or more PROC SQL options without the need of restarting the procedure. Once an option is specified, it stays in effect until it is changed or reset. Being able to change options with the RESET statement is a handy debugging technique. The following example illustrates turning off the FEEDBACK option by resetting it to NOFEEDBACK. By turning off this option, you prevent the expansion of a SELECT * (wildcard) statement into a list of columns that it represents.
SQL Code
PROC SQL FEEDBACK; SELECT * FROM PRODUCTS; RESET NOFEEDBACK; SELECT * FROM PRODUCTS WHERE PRODTYPE='Software'; QUIT;
SELECT * FROM PRODUCTS; NOTE: Statement transforms to: select PRODUCTS.prodnum, PRODUCTS.prodname, |
Multiple options can be reset in a single RESET statement. Options in the PROC SQL and RESET statements can be specified in any order. The next example shows how, in a single RESET statement, double-spaced output is changed to single-spaced output with the NODOUBLE option, row numbers are suppressed with the NONUMBER option, and output rows are changed to the maximum number of rows with the OUTOBS= option.
SQL Code
PROC SQL DOUBLE NUMBER OUTOBS=1;
SELECT *
FROM PRODUCTS
WHERE PRODTYPE='Software';
RESET NODOUBLE NONUMBER OUTOBS=MAX;
SELECT *
FROM PRODUCTS
WHERE PRODTYPE='Software';
QUIT;PROC SQL DOUBLE NUMBER OUTOBS=1; SELECT * FROM PRODUCTS WHERE PRODTYPE='Software'; WARNING: Statement terminated early due to |
Output Results
The SAS System
Product
|
The next example shows a RESET statement being issued to change the way the SQL procedure handles updating data in a table. The first UPDATE query is set to reverse any updates that have been performed up to the point of an error using the UNDO_POLICY=REQUIRED (default value) option. (Note: Because this is the default value for this option, it could have been omitted.) A RESET statement of UNDO_POLICY=NONE is issued before the second update query to change the way updates are handled in PROC SQL. The NONE option keeps any updates that have been made regardless of whether an error is detected. A warning message is displayed on the SAS log alerting you to the change in the way updates are handled.
PROC SQL UNDO_POLICY=REQUIRED; UPDATE PRODUCTS SET PRODCOST = PRODCOST - (PRODCOST * 0.2) WHERE UPCASE(PRODTYPE) = 'LAPTOP'; RESET UNDO_POLICY=NONE; UPDATE PRODUCTS SET PRODCOST = PRODCOST - (PRODCOST * 0.2) WHERE UPCASE(PRODTYPE) = 'LAPTOP'; QUIT;
SAS Log Results
PROC SQL UNDO_POLICY=REQUIRED; UPDATE PRODUCTS SET PRODCOST = PRODCOST - (PRODCOST * 0.2) WHERE UPCASE(PRODTYPE) = 'LAPTOP'; NOTE: 1 row was updated in WORK.PRODUCTS. RESET UNDO_POLICY=NONE; UPDATE PRODUCTS SET PRODCOST = PRODCOST - (PRODCOST * 0.2) WHERE UPCASE(PRODTYPE) = 'LAPTOP'; WARNING: The SQL option UNDO_POLICY=REQUIRED is |