26142 - Validating SQL Query Syntax with the VALIDATE Statement

Sample 26142: Validating SQL Query Syntax with the VALIDATE Statement

Wouldn’t it be handy to check your SQL queries for syntax errors before the execution of any code or data being processed? Here’s a tip that will show you how to make your job of troubleshooting and debugging your SQL queries easier.

As a normal processing sequence the SQL procedure automatically turns on syntax checking each time a query is submitted. But to enable syntax checking without the automatic execution of a query, a VALIDATE statement may be specified prior to a SELECT statement to control what is checked. Since the VALIDATE statement is designed to be specified in conjunction with a SELECT statement, users are better able to control what code is checked for troubleshooting and debugging purposes. The appropriate message is displayed on the SAS log to indicate whether coding problems exist. As illustrated in the example below, a VALIDATE statement is specified at the beginning of a SELECT statement to enable syntax checking. The SAS log displays the validity of the query syntax as is the case in the example below.

SQL Code

PROC SQL;
 VALIDATE
   SELECT *
    FROM MOVIES
     WHERE RATING = ‘G’;
QUIT;

SAS Log Results

PROC SQL;
 VALIDATE
  SELECT *
   FROM MOVIES
    WHERE RATING = 'G';
NOTE: PROC SQL statement has valid syntax.
QUIT;

The next VALIDATE statement example illustrates what happens when the syntax checker discovers an error in an SQL query. The resulting error message is the direct result of the missing FROM clause. Since the FROM clause is the only required parameter in a SELECT statement, the step was automatically stopped with a significant error. Also notice that the incorrect spelling of the WHERE clause was ignored because of the missing FROM clause.

SQL Code

PROC SQL;
 VALIDATE
   SELECT *
    MOVIES
     WERE RATING = ‘G’;
QUIT;

SAS Log Results

PROC SQL;
 VALIDATE
   SELECT *
    MOVIES
    ------
    22
    76
ERROR 22-322: Syntax error, expecting one of the following: ',', FROM, INTO.

ERROR 76-322: Syntax error, statement will be ignored.

     WERE RATING = 'G';
QUIT;
NOTE: The SAS System stopped processing this step because of errors.

About the Author
Kirk Paul Lafler is the author of PROC SQL: Beyond the Basics Using SAS, published by SAS Press. He also writes the popular SAS tips column "Kirk's Korner," which appears regularly in several SAS users group newsletters, and is a frequent speaker at SAS users group meetings.

His book is available from the online bookstore.

PROC SQL: Beyond the Basics Using SAS

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Type:	Sample
Topic:	Internal Administration ==> BBU Non SAS Authors ==> Kirk Paul Lafler SAS Reference ==> Procedures ==> SQL

Date Modified:	2007-03-13 03:03:48
Date Created:	2007-03-13 03:03:48

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	Base SAS	All	n/a	n/a

Support

Sample 26142: Validating SQL Query Syntax with the VALIDATE Statement

Operating System and Release Information