This section lists several undocumented PROC SQL options. Although undocumented options may be freely explored and used, you should carefully consider the ramifications before using them. Because of unannounced changes, possible removal, or nonsupport in future releases, you should exercise care when using them throughout SQL procedure applications. However, undocumented options provide a wealth of opportunities for identifying and resolving coding problems. The table below presents several of these undocumented SQL procedure options for troubleshooting and debugging purposes.
Option | Description |
---|---|
_AGGR | displays a tree structure in the SAS log with a before-and-after summary. |
_ASGN | displays a tree structure in the SAS log consisting of resolved before-and-after names. |
_DFR | displays a before-and-after dataflow and subcall resolution in a tagged tree structure in the SAS log. |
_METHOD | displays the various PROC SQL execution options in the SAS log. Note: This option is explained in greater detail in Chapter 10, “Tuning for Performance and Efficiency.” |
_PJD | displays various table attributes including the number of observations (rows), the logical record length (lrecl), the number of restricted rows, and the size of the table in bytes. |
_RSLV | displays a tree structure in the SAS log consisting of before-and-after early semantic checks. |
_SUBQ | displays subquery transformations as a tree structure in the SAS log. |
_TREE | displays a query as a tree structure in the SAS log. The tree structure consists of the transformed code that the SQL processor will execute. |
_UTIL | displays a breakdown in the SAS log of each step defined in the procedure including each row’s buffer length, each column by name, and the column position with each row (or offset). |
One of my favorite undocumented PROC SQL options is _TREE. As its name implies, it displays a tree structure or hierarchy illustrating an expanded view of each statement, clause, and column name within the procedure step. This provides a handy and unique way of seeing SQL procedure code to aid in troubleshooting logic and column lists. The next example shows the _TREE option being used in a simple query that specifies the wildcard character “*” that represents all columns with a WHERE clause to subset only “Software” products.
PROC SQL _TREE; SELECT * FROM PRODUCTS WHERE PRODTYPE = 'Software'; QUIT;
SAS Log Results
To assist with the process of troubleshooting and debugging problematic coding constructs, PROC SQL assigns values to three automatic macro variables after the execution of each statement. The contents of these three macro variables can be used to test the validity of SQL procedure code as well as to evaluate whether processing should continue.
The SQLOBS macro variable displays the number of rows that are processed by an SQL procedure statement. To display the contents of the SQLOBS macro variable in the SAS log, specify a %PUT macro statement. The following example retrieves the software products from the PRODUCTS table and displays SAS output with a SELECT statement. The %PUT statement displays the number of rows processed and sent to SAS output as four rows.
SQL Code
PROC SQL;
SELECT *
FROM PRODUCTS
WHERE PRODTYPE='Software';
%PUT SQLOBS = &SQLOBS;
QUIT;
SAS Log Results
PROC SQL;
SELECT *
FROM PRODUCTS
WHERE PRODTYPE='Software';
%PUT SQLOBS = &SQLOBS;
SQLOBS = 4
QUIT; |
The next example shows two new products inserted in the PRODUCTS table with an INSERT INTO statement. The %PUT statement displays the number of rows added to the PRODUCTS table as two rows.
PROC SQL;
INSERT INTO PRODUCTS
(PRODNUM, PRODNAME, PRODTYPE, PRODCOST)
VALUES(6002,'Security Software','Software',375.00)
VALUES(1701,'Travel Laptop SE', 'Laptop', 4200.00);
%PUT SQLOBS = &SQLOBS;
QUIT;
SAS Log Results
PROC SQL; INSERT INTO PRODUCTS (PRODNUM, PRODNAME, PRODTYPE, PRODCOST) VALUES(6002,'Security Software','Software',375.00) VALUES(1701,'Travel Laptop SE', 'Laptop', 4200.00); NOTE: 2 rows were inserted into WORK.PRODUCTS. %PUT SQLOBS = &SQLOBS; SQLOBS = 2 QUIT; |
The SQLOOPS macro variable displays the number of times the inner loop is processed by the SQL procedure. To display the contents of the SQLOOPS macro variable on the SAS log, specify a %PUT macro statement. The following example retrieves the software products from the PRODUCTS table and displays SAS output with a SELECT statement. The %PUT statement displays the number of times the inner loop is processed as 15 times even though there are only 10 product rows. As a query becomes more complex, the number of times the inner loop of the SQL procedure processes also increases proportionally.
PROC SQL;
SELECT *
FROM PRODUCTS
WHERE PRODTYPE='Software';
%PUT SQLOOPS = &SQLOOPS;
QUIT;
SAS Log Results
PROC SQL; SELECT * FROM PRODUCTS WHERE PRODTYPE='Software'; %PUT SQLOOPS = &SQLOOPS; SQLOOPS = 15 QUIT; |
The SQLRC macro variable displays a status value indicating whether the PROC SQL statement was successful or not. A %PUT macro statement is specified to display the contents of the SQLRC macro variable. The following example retrieves the software products from the PRODUCTS table and displays SAS output with a SELECT statement. The %PUT statement displays a return code of zero indicating that the SELECT statement was successful.
SQL Code
PROC SQL;
SELECT *
FROM PRODUCTS
WHERE PRODTYPE='Software';
%PUT SQLRC = &SQLRC;
QUIT;
PROC SQL; SELECT * FROM PRODUCTS WHERE PRODTYPE='Software'; %PUT SQLRC = &SQLRC; SQLRC = 0 QUIT; |
This section shows a number of errors that I have personally experienced while working on SQL procedure problems. Although not representative of all the possible errors that may occur, it does illustrate a set of common problems along with a technical approach for correcting each problem.
Syntax errors messages can, at times, provide confusing information about the specific problem at hand. A case in point is the error, 78-322: Expecting a ','. In the example below, it initially appears that a comma is missing between two column names in the SELECT statement. On closer review, the actual problem points to a violation of the column’s naming conventions caused by specifying an invalid character in the assigned column alias in the AS keyword.
PROC SQL; SELECT CUSTNUM, ITEM, UNITS * UNITCOST AS Total-Cost - 78 ERROR 78-322: Expecting a ','. FROM PURCHASES ORDER BY TOTAL; QUIT;
Correct the problem associated with the assigned column-alias name by adhering to valid SAS naming conventions. For example, replace the hyphen “-” in Total-Cost with an underscore, as in Total_Cost.
Sometimes problems occur because of unfamiliarity with the SQL procedure language syntax. In the syntax error illustrated below an unrecognized option or parameter is encountered, resulting in the procedure stopping before any processing occurs.
PROC SQL; SELECT prodtype, MIN(prodcost) AS Cheapest Format=dollar9.2 Label='Least Expensive' FROM PRODUCTS ORDER BY cheapest GROUP BY prodtype; ----- -- 22 202 ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, ASC, ASCENDING, BETWEEN, CONTAINS, DESC, DESCENDING, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=. ERROR 202-322: The option or parameter is not recognized and will be ignored. QUIT;
This problem is the result of the SELECT statement clauses not being specified in the correct order. It can be corrected by specifying the SELECT statement’s GROUP BY clause before the ORDER BY clause.
In the next example, the syntax error points to a problem where a column name that is specified in a SELECT statement appears in more than one table resulting in a column ambiguity. This problem not only creates confusion for the SQL processor, but also prevents the query from executing.
PROC SQL; SELECT prodname, prodcost, manunum, manuname FROM PRODUCTS AS P, MANUFACTURERS AS M WHERE P.manunum = M.manunum; ERROR: Ambiguous reference, column manunum is in more than one table. QUIT;
To remove any and all column ambiguities, you should reference each column that appears in two or more tables with its respective table name in a SELECT statement and its clauses. For example, to reference the MANUNUM column in the MANUFACTURERS table and remove all ambiguities, you would specify the column in the SELECT statement as MANUFACTURERS.MANUNUM.
In the next example, the syntax error points to the left parenthesis at the end of the second SELECT statement’s WHERE clause as being invalid. The key to finding the actual problem is to work backward, line-by-line, from the point where the error is marked. Using this approach, you will notice that the second SELECT statement is a subquery (or inner query) and does not conform to valid syntax rules. As with other syntax errors, the query as well as the subquery does not execute.
PROC SQL; SELECT * FROM INVOICE WHERE manunum IN SELECT manunum FROM MANUFACTURERS WHERE UPCASE(manucity) LIKE 'SAN DIEGO%'), - 22 - 200 ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, AND, ESCAPE, EXCEPT, GROUP, HAVING, INTERSECT, OR, ORDER, OUTER, UNION, |, ||. ERROR 200-322: The symbol is not recognized and will be ignored. QUIT;
A subquery must conform to valid syntax rules. To correct this problem, a right parenthesis must be added at the beginning of the second SELECT statement immediately after the IN clause.
In the next example, the syntax error identifies a WHERE clause being used in a left outer join. Although the SQL procedure permits an optional WHERE clause to be specified in the outer join syntax, an ON clause must be specified as well.
PROC SQL; SELECT prodname, prodtype, products.manunum, invenqty FROM PRODUCTS LEFT JOIN INVENTORY WHERE products.manunum = ----- 22 76 ERROR 22-322: Syntax error, expecting one of the following: a name, (, '.', AS, ON. ERROR 76-322: Syntax error, statement will be ignored. inventory.manunum; QUIT;
To correct this problem and to conform to valid outer join syntax requirements, specify an ON clause before an optional WHERE clause that is used to subset joined results.
In the next example, the syntax error identifies the UNION statement as being invalid or used out of proper order. On further inspection, it is clear that one of two problems exists. The SQL procedure code consists of two separate queries with an invalid UNION operator specified, or a misplaced semicolon appears at the end of the first SELECT query.
PROC SQL; SELECT * FROM products WHERE prodcost < 300.00; UNION ----- 180 ERROR 180-322: Statement is not valid or it is used out of proper order. SELECT * FROM products WHERE prodtype = 'Workstation'; QUIT;
To correct this problem and conform to valid rules of syntax for a UNION operation, remove the semicolon after the first SELECT query.
In the next example, the syntax error identifies a missing AS keyword in the CREATE VIEW statement and highlights the view’s SELECT statement. If the AS keyword is not specified, the CREATE VIEW step is not executed and the view is not created.
PROC SQL; CREATE VIEW WORKSTATION_PRODUCTS_VIEW SELECT PRODNUM, PRODNAME, PRODTYPE, PRODCOST ------ 73 ERROR 73-322: Expecting an AS. FROM PRODUCTS WHERE UPCASE(PRODTYPE)="WORKSTATION"; QUIT;
To correct the problem, add the AS keyword in the CREATE VIEW statement to follow valid syntax rules and define the view’s query.