Chapter 11: Troubleshooting and Debugging

Introduction. 365

The World of Bugs. 365

The Debugging Process. 366

Types of Problems. 367

Troubleshooting and Debugging Techniques. 368

Validating Queries with the VALIDATE Statement 368

Documented PROC SQL Options and Statement 369

Undocumented PROC SQL Options. 382

Macro Variables. 383

Troubleshooting and Debugging Examples. 385

Summary. 389

When it comes to tracking down the source of SQL coding problems, users can be very resourceful. To find all those pesky errors, warnings, notes, and unexpected results, you need to carefully inspect the SAS log and telltale clues from code reviews and sample runs. Using reliable troubleshooting and debugging techniques will help get to the root of any syntax, data, system‑related, and logic errors that are or could be problematic to the successful execution of a PROC SQL program.

This chapter introduces a number of strategies and techniques for troubleshooting and debugging SQL procedure coding problems. The guidelines presented in this chapter will help you learn about the different types of bugs, the steps involved in troubleshooting problems, the types of errors that can occur, and the options and statements that are available in the SQL procedure to troubleshoot and debug problems.

Since the birth of the software industry, software problems (often referred to as bugs) have been created by programmers (and users) of all skill levels in virtually every conceivable form. A bug is something the software or a program does that it is not supposed to do. When the software encounters a bug, it can cause the software to cease to operate or to misbehave.

Bugs come in all forms, shapes, and sizes. A problem that occurs while you’re using the SQL procedure incorrectly and directly violating the rules of the language is referred to as a usage error. The severity of these types of errors depends on the nature of the violation as well as the effect on the user. Generally speaking though, a usage error results in a syntax error and the stoppage of the program or step.

Other types of bugs can be problematic, too. Bugs such as resource problems, data dependencies, and implementation errors can cause a program step to stop or produce unreliable results, which will then display errors or warnings in the SAS log.

The debugging process consists of a number of recommended steps to correct an identified problem and verify that it does not reappear. The objective is to identify, classify, fix, and verify a problem in a PROC SQL step, program, or application as quickly and easily as possible. Not every problem requires the application of each step recommended in this chapter. Rather, you can pick and choose from the suggestions to effectively correct problems and make sure they do not reappear. Adhering to a methodical and effective debugging process increases the likelihood that problems are identified and fixed correctly, thereby expediting and improving the way you handle problems.

To further explore this topic, see Effective Methods for Software Testing by William E. Perry (Wiley Publishing, Inc., 2006) and The Science of Debugging by Matthew A. Telles and Yuan Hsieh (The Coriolis Group, 2001).

The debugging process consists of five steps. See Table 11.1.

Table 11.1: Debugging Process Steps and Tasks

Debugging Step	Task Description
Problem Identification	1.       Determine if a bug exists in the code. 2.       Determine what the problem is by playing detective. 3.       Describe why the problem is a bug. 4.       Determine what the code should do. 5.       Determine what the code is doing.
Information Collection	1.       Collect user comments and feedback. 2.       Collect personal observations and symptoms. 3.       Review SAS log information. 4.       Collect test case(s) that highlight the problem. 5.       Capture environmental information (for example, system settings, operating environment, external elements, etc.).
Problem Assessment & Classification	1.       Develop a theory about what caused the problem. 2.       Review the code. 3.       Classify the problem into one of the following categories: ◦         Requirements problem ◦         Syntax error ◦         CPU problem ◦         Memory problem ◦         Storage problem ◦         I/O problem ◦         Logic problem
Problem Resolution	1.       Propose a solution. 2.       Describe why the solution will fix the problem. 3.       Verify that the solution will not cause additional problems. 4.       Fix the problem by implementing the solution.
Validate Solution	1.       Verify whether the problem still exists. 2.       Verify whether the problem can be recreated or reproduced. 3.       Determine whether other methods can cause the same problem to occur. 4.       Verify that the solution does not cause other problems.

The leading causes of SQL programming problems include misusing the language syntax; referencing data, especially column data, incorrectly; ignoring or incorrectly specifying system parameters; and constructing syntactically correct but illogical code that does not produce results as expected.

Usage errors can cause SAS to stop processing, produce warnings, or produce unexpected results. Table 11.2 illustrates the four types of usage errors and briefly describes each.

Table 11.2: Types of Usage Errors

Problem	Description
Syntax	Syntax problems are a result of one or more violations of the SQL procedure language constructs. These problems can prevent a program from processing until you make the required changes. For example, a variable that is referenced in a SELECT statement but is not found in the referenced table causes the program to stop.
Data	Data problems are a result of an inconsistency between the data and the program specification. These problems can prevent a program from processing, but might allow processing to continue and result in the assignment of missing, incomplete, or unreliable data. For example, missing values might be generated in a column when character data is incorrectly referenced as numeric data.
System-related	System-related problems frequently result from specifying incompatible system options or choosing the wrong system option values. These problems can prevent a program from processing, but most frequently permit processing to occur with unsatisfactory results. For example, forgetting to specify a title statement has little effect on the production of output, but it might force the program to be rerun after the addition of one or more titles.
Logic	Logic problems are frequently the result of not specifying a coding condition correctly. For example, specifying an OR condition when an AND condition is needed might produce wrong results without any warnings, errors, or notes.

Other sources of problems can occur in a program for a variety of reasons. Table 11.3 illustrates these other problem sources and briefly describes each.

Table 11.3: Other Types of Errors

Problem	Description
Feature Creep	Additional features can creep into a program or application during the design, implementation, or testing phases. This can create a greater likelihood of problems.
Solution Complexity	More complex or esoteric solutions can also translate into problems. Difficult-to-maintain programs can result.
Requirements	Requirements might be inadequately stated, misunderstood, or omitted. In these situations, programs might not meet the needs of the user community and be classified as bugs.
Testing Environment	Inadequate testing environments, poor test plans, or insufficient test time often lead to bug-filled programs. In these situations, bugs can slip through the testing phase and into the production environment.

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.

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 a VALIDATE statement before a SELECT statement, you are better able to control the process of debugging code. A message that indicates syntax correctness is automatically displayed in the SAS log when code syntax is valid. Otherwise, an error message is displayed that identifies 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

This section illustrates examples of widely used and documented troubleshooting and debugging SQL options along with the RESET statement. A description and a working example of each option and statement are presented below.

FEEDBACK/NOFEEDBACK 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 might 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

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;

SAS Log Results

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);

SAS Log Results

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);

SAS Log Results

_METHOD and _TREE Options with MSGLEVEL=I

As a troubleshooting and debugging tools, PROC SQL supports the _METHOD and _TREE options. These options are extremely useful for understanding the hierarchy of processing methods selected by the SQL optimizer and the internal form of the query plan, respectively. To help users understand the specific codes that are generated and displayed in the SAS log from the _METHOD option, a table of codes along with a brief description appears in Table 11.4.

When combined with the _METHOD and _TREE options, the MSGLEVEL=I System option displays additional messages in the SAS log that pertain to index usage, merge processing, and sort utility used.

Table 11.4: Codes That Are Generated and Displayed in the SAS Log from the _METHOD Option

Code	Description
SQXCRTA	Create table as Select.
SQXSLCT	Select statement or clause.
SQXJSL	Step loop join (Cartesian).
SQXJM	Merge join operation.
SQXJNDX	Index join operation.
SQXJHSH	Hash join operation.
SQXSORT	Sort operation.
SQXSRC	Source rows from table.
SQXFIL	Rows filtration.
SQXSUMG	Summary stats (aggregates) with GROUP BY clause.
SQXSUMN	Summary stats with no GROUP BY clause.

SQL Code

OPTIONS MSGLEVEL=I;

PROC SQL _METHOD _TREE;

  SELECT sum(inventory.invenqty)

           AS Products_Ordered_Before_09012000

    FROM PRODUCTS   P,

         INVOICE    I,

         CUSTOMERS  C,

         INVENTORY  I2

      WHERE I2.orddate < mdy(09,01,00) AND

            P.prodnum  = I.prodnum AND

            I.custnum  = C.custnum AND

            I.prodnum  = I2.prodnum;

QUIT;

 

SAS Log Results

The _TREE option, when specified, provides a graphical representation of the way the query optimizer handled the execution of a query. Essentially, the query execution plan shows how a query was interpreted and executed by the optimizer based on the statements, clauses, options, keywords, and other coding constructs used.

When a query is submitted for execution, the optimizer determines the most optimal way to execute the query, and consequently constructs an execution plan. Typically, the decision about what execution plan a cost-based optimizer takes is influenced by the estimated CPU and IO processing costs, and the speed in which the query is expected to execute. The result is a query execution plan depicting a tree structure of labels, nodes, input sources, join parameters, where-clause conditions, and other descriptive information, as shown below.

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 10 rows in the PRODUCTS table. A warning message is also displayed in the SAS log that indicates that the number of records read was restricted to 10.

SQL Code

PROC SQL INOBS=10;

 SELECT *

  FROM PRODUCTS;

QUIT;

SAS Log Results

In the next example, the INOBS= option is set to 5 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 5, which produces a maximum of 25 rows.

SQL Code

PROC SQL INOBS=5;

 SELECT prodname, prodcost, manufacturers.manunum, manuname

    FROM PRODUCTS, MANUFACTURERS;

QUIT;

SAS Log Results

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;

SAS Log Results

Results

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 “Macro Variables” section 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;

Results

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. 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;

SAS Log Results

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;

SAS Log Results

Output Results

The next example shows an UPDATE query that reverses 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. The NONE option keeps any updates that have been made regardless of whether an error is detected. A warning message is displayed alerting you to the change in the way updates are handled.

SQL Code

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

EXEC/NOEXEC Option

The NOEXEC option checks all non-query 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 in that it checks 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 they are specified. As was presented earlier, the VALIDATE statement is specified before each SELECT statement; the NOEXEC / EXEC option can be specified as a PROC SQL statement option or in a RESET statement.

To illustrate the troubleshooting and debugging process, the following non-query SQL code will be shown using the NOEXEC and EXEC options. The next example illustrates that the non-query CREATE TABLE statement is not to be executed after its syntax is checked for correctness. As a result of the NOEXEC option being specified, any syntax-related errors are automatically displayed in the SAS log.

SQL Code

PROC SQL NOEXEC;

  CREATE TABLE SOFTWARE_PRODUCTS

    SELECT *

      FROM PRODUCTS

        WHERE PRODTYPE = ‘Software’;

As illustrated on the SAS log, a syntax error is found and the resulting error is displayed.

SAS Log Results

In the next example, with the NOEXEC option still in effect, the AS keyword is specified with the CREATE TABLE statement, which corrects the syntax error that is displayed in the previous example.

SQL Code

  CREATE TABLE SOFTWARE_PRODUCTS AS

    SELECT *

      FROM PRODUCTS

        WHERE PRODTYPE = ‘Software’;

As illustrated in the SAS log, no syntax errors were found, but because the NOEXEC option is still active the CREATE TABLE statement was not executed.

SAS Log Results

The next example illustrates changing the NOEXEC option to EXEC with a RESET statement, which results in error-free non-query code to execute.

SQL Code

  RESET EXEC;

  CREATE TABLE SOFTWARE_PRODUCTS  AS

    SELECT *

      FROM PRODUCTS

        WHERE PRODTYPE = ‘Software’;

QUIT;

As illustrated in the SAS log, no syntax errors were found. And because the RESET statement was specified to change the NOEXEC option to EXEC, the CREATE TABLE statement executed.

SAS Log Results

ERRORSTOP/NOERRORSTOP Option

The ERRORSTOP option instructs SQL to stop executing statements and to continue to check syntax in noninteractive or batch sessions when an error is encountered. Conversely, the NOERRORSTOP option tells SQL to execute statements when an error is encountered and to continue checking syntax. The ERRORSTOP / NOERRORSTOP option can be specified as a PROC SQL statement option or in a RESET statement. The next example illustrates a CREATE TABLE statement being used incorrectly, and shows the SAS log results that are generated when the ERRORSTOP option is specified.

SQL Code

PROC SQL ERRORSTOP;

  CREATE TABLE SOFTWARE_PRODUCTS

    SELECT *

      FROM PRODUCTS

        WHERE PRODTYPE = ‘Software’;

QUIT;

As illustrated in the SAS log, the ERRORSTOP option automatically stops executing the SQL statements and displays a SAS log error message when a syntax error is encountered.

SAS Log Results

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 that are 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 with five rows as designated by the OUTOBS= option. A SAS log warning message shows that the new table contains five rows.

SQL Code

PROC SQL OUTOBS=5;

  CREATE TABLE PRODUCTS_SAMPLE AS

    SELECT *

      FROM PRODUCTS;

QUIT;

SAS Log Results

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 that indicates that the limits of the specified option have been reached, and asks 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;

SAS Log Results

This section lists several undocumented PROC SQL options. Although undocumented options can 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. Table 11.5 presents several of these undocumented SQL procedure options for troubleshooting and debugging purposes.

Table 11.5: Undocumented PROC SQL Options

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 that consists 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.
_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 that consists of before-and-after early semantic checks.
_SUBQ	Displays subquery transformations as a tree structure in the SAS log.
_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).

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.

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

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

SQLOOPS Macro Variable

The SQLOOPS macro variable displays the number of times that the inner loop is processed by the SQL procedure. To display the contents of the SQLOOPS 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 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

 

SQLRC Macro Variable

The SQLRC macro variable displays a status value that indicates 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, which indicates that the SELECT statement was successful.

SQL Code

PROC SQL;

 SELECT *

  FROM PRODUCTS

   WHERE PRODTYPE='Software';

 %PUT SQLRC = &SQLRC;

QUIT;

SAS Log Results

This section shows a number of errors that I have personally experienced while working on SQL procedure problems. Although not representative of all of the possible errors that might 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 that was 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 that is 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 below, an unrecognized option or parameter is encountered, which results 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 that is 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.

1.       The objective of the debugging process is to identify, classify, fix, and verify a problem in a PROC SQL step, program, or application as quickly and easily as possible (see “The Debugging Process” section).

2.       Usage errors can cause the SAS System to stop processing, produce warnings, or produce unexpected results (see the “Types of Problems” section).

3.       PROC SQL provides numerous troubleshooting and debugging capabilities for the practitioner (see the “Troubleshooting and Debugging Techniques” section).

4.       PROC SQL options provide effective troubleshooting and debugging techniques for resolving coding problems (see the “Documented PROC SQL Options and Statements” section).

5.       Several useful, but nonproduction, PROC SQL options are available for troubleshooting and debugging (see the “Undocumented PROC SQL Options” section).