SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

Appendix 2

PROC SQL and the ANSI

Standard

Compliance

PROC SQL follows most of the guidelines set by the American National Standards

Institute (ANSI) in its implementation of SQL. However, it is not fully compliant with

the current ANSI standard for SQL.

2

The SQL research project at SAS has focused primarily on the expressive power of SQL

as a query language. Consequently, some of the database features of SQL have not yet

been implemented in PROC SQL.

SQL Procedure Enhancements

Reserved Words

PROC SQL reserves very few keywords. And then, only in certain contexts. The ANSI

standard reserves all SQL keywords in all contexts. For example, according to the

standard, you cannot name a column GROUP because of the keywords GROUP BY.

The following words are reserved in PROC SQL:

• The keyword CASE is always reserved. Its use in the CASE expression (an SQL2

feature) precludes its use as a column name.

If you have a column named CASE in a table and you want to specify it in a PROC

SQL step, then you can use the SAS data set option RENAME= to rename that

column for the duration of the query. You can enclose CASE in double quotation

marks (“CASE”) and set the PROC SQL option DQUOTE=ANSI.

• The keywords AS, ON, FULL, JOIN, LEFT, FROM, WHEN, WHERE, ORDER,

GROUP, RIGHT, INNER, OUTER, UNION, EXCEPT, HAVING, and INTERSECT

cannot be used for table aliases. These keywords introduce clauses that appear after a

table name. Because the table alias is optional, PROC SQL handles this ambiguity by

assuming that any one of these words introduces the corresponding clause and is not

the table alias. If you want to use one of these keywords as a table alias, then enclose

the keyword in double quotation marks and set the PROC SQL option

DQUOTE=ANSI.

• The keyword USER is reserved for the current user ID. If you specify USER in a

SELECT statement in conjunction with a CREATE TABLE statement, then the

column is created in the table with a temporary column name that is similar to

_TEMA001. If you specify USER in a SELECT statement without a CREATE

TABLE statement, then the column is written to the output without a column

heading. In either case, the value for the column varies by operating environment,

2

International Organization for Standardization (ISO): Database SQL. Document ISO/IEC 9075:1992. Also, as American National

Standards Institute (ANSI) Document ANSI X3.135-1992.

387