O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.
For information about how SQL differs from ANSI SQL, see “Differences in PROC
SQL and ANSI SQL” on page 390.
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
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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required