1.3. Column Names and Reserved Words
The ANSI Standard reserves a number of SQL keywords from being used as column names. The SAS SQL implementation is not as rigid, but users should be aware of what reserved words exist to prevent unexpected and unintended results during SQL processing. Column names should conform to proper SAS naming conventions (as described in the SAS Language Reference), and they should not conflict with certain reserved words found in the SQL language. The following list identifies the reserved words found in the ANSI SQL standard.
ANSI SQL Reserved Words
| AS | INNER | OUTER |
| CASE | INTERSECT | RIGHT |
| EXCEPT | JOIN | UNION |
| FROM | LEFT | UPPER |
| FULL | LOWER | USER |
| GROUP | ON | WHEN |
| HAVING | ORDER | WHERE |
You probably will not encounter too many conflicts between a column name and an SQL reserved word, but when you do you will need to follow a few simple rules to prevent processing errors from occurring. As was stated earlier, although PROC SQL’s naming conventions are not as rigid as other vendors’ implementations, care should still be exercised, in particular when PROC SQL code is transferred to other database environments expecting it to run error-free. If a column name in an existing table conflicts with a reserved word, you have three options at your disposal:
1. | Physically rename the column in the table, as well as any references to the column. |
2. | Use the RENAME= data set option to rename the desired column in the current query. |
3. | Specify the PROC SQL option DQUOTE=ANSI, and surround the column name (reserved word) in double quotes, as illustrated below. |
SQL Code
PROC SQL DQUOTE=ANSI; SELECT * FROM RESERVED_WORDS WHERE "WHERE"="EXAMPLE"; QUIT;