9.6. Undocumented PROC SQL Options

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.

Undocumented PROC SQL Options

OptionDescription
_AGGRdisplays a tree structure in the SAS log with a before-and-after summary.
_ASGNdisplays a tree structure in the SAS log consisting of resolved before-and-after names.
_DFRdisplays a before-and-after dataflow and subcall resolution in a tagged tree structure in the SAS log.
_METHODdisplays 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.”
_PJDdisplays 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.
_RSLVdisplays a tree structure in the SAS log consisting of before-and-after early semantic checks.
_SUBQdisplays subquery transformations as a tree structure in the SAS log.
_TREEdisplays a query as a tree structure in the SAS log. The tree structure consists of the transformed code that the SQL processor will execute.
_UTILdisplays 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.

SQL Code

PROC SQL _TREE;
  SELECT *
    FROM PRODUCTS
      WHERE PRODTYPE = 'Software';
QUIT;

SAS Log Results

[View full size image]

9.6.1. Macro Variables

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.

9.6.1.2. SQLOBS Macro Variable

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.

SQL Code

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;


9.6.1.3. SQLOOPS Macro Variable

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.

SQL Code

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;


9.6.1.4. SQLRC Macro Variable

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;

SAS Log Results

PROC SQL;
 SELECT *
  FROM PRODUCTS
   WHERE PRODTYPE='Software';

 %PUT SQLRC = &SQLRC;
													SQLRC = 0

QUIT;


9.6.2. Troubleshooting and Debugging Examples

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.

ERROR 78-322: Expecting a ','
Problem Description

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.

Code and Error
PROC SQL;
  SELECT CUSTNUM, ITEM, UNITS * UNITCOST AS Total-Cost
                                                 -
								78
								ERROR 78-322: Expecting a ','.

    FROM PURCHASES
      ORDER BY TOTAL;
QUIT;

Corrective Action

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.

ERROR 202-322: The option or parameter is not recognized and will be ignored
Problem Description

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.

Code and Error
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;

Corrective Action

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.

ERROR Ambiguous reference, column
Problem Description

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.

Code and Error
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;

Corrective Action

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.

ERROR 200-322: The symbol is not recognized and will be ignored
Problem Description

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.

Code and Error
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;

Corrective Action

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.

ERROR 22-322: expecting one of the following: a name, (, '.', AS, ON
Problem Description

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.

Code and Error
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;

Corrective Action

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.

ERROR 180-322: Statement is not valid or it is used out of proper order
Problem Description

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.

Code and Error
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;

Corrective Action

To correct this problem and conform to valid rules of syntax for a UNION operation, remove the semicolon after the first SELECT query.

ERROR 73-322: Expecting an AS
Problem Description

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.

Code and Error
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;

Corrective Action

To correct the problem, add the AS keyword in the CREATE VIEW statement to follow valid syntax rules and define the view’s query.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.54.245