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.(footnote 1)
The SQL research project at SAS Institute 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 the SAS System.
This section describes
Most of the enhancements described here are required by the current
- enhancements to SQL that SAS Institute has made through PROC
- the ways in which PROC SQL differs from the current ANSI Standard
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
The following words are reserved in PROC SQL:
PROC SQL supports the SAS System's INFORMAT=, FORMAT=, and LABEL= modifiers
for expressions within the SELECT clause. These modifiers control the format
in which output data are displayed and labeled.
PROC SQL allows you to specify an alternate collating (sorting) sequence
to be used when you specify the ORDER BY clause. See the description of the
SORTSEQ= option in PROC SQL Statement for more information.
PROC SQL permits you to specify an ORDER BY clause in a CREATE VIEW
statement. When the view is queried, its data are always sorted according
to the specified order unless a query against that view includes a different
ORDER BY clause. See CREATE VIEW Statement for more information.
The ability to code nested query-expressions in the FROM clause is a
requirement of the ANSI Standard. PROC SQL supports such nested coding.
The ability to include columns that both match and do not match in a
join-expression is a requirement of the ANSI Standard. PROC SQL supports
PROC SQL supports the SAS System exponentiation (**) operator. PROC
SQL uses the notation < > to mean not equal.
PROC SQL permits the combination of comparison, Boolean, and algebraic
expressions. For example, (X=3)*7 yields a value of 7 if X=3 is true because
true is defined to be 1. If X=3 is false, it resolves to 0 and the entire
expression yields a value of 0.
PROC SQL permits a subquery in any expression. This feature is required
by the ANSI Standard. Therefore, you can have a subquery on the left side
of a comparison operator in the WHERE expression.
PROC SQL permits you to order and group data by any kind of mathematical
expression (except those including summary functions) using ORDER BY and GROUP
BY clauses. You can also group by an expression that appears on the SELECT
clause by using the integer that represents the expression's ordinal position
in the SELECT clause. You are not required to select the expression by which
you are grouping or ordering. See ORDER BY Clause and
GROUP BY Clause for more information.
The set operators UNION, INTERSECT, and EXCEPT are required by the ANSI
Standard. PROC SQL provides these operators plus the OUTER UNION operator.
The ANSI Standard also requires that the tables being operated upon
all have the same number of columns with matching data types. The SQL procedure
works on tables that have the same number of columns, as well as on those
that do not, by creating virtual columns so that a query can evaluate correctly.
See query-expression for more information.
PROC SQL supports many more summary functions than required by the ANSI
Standard for SQL.
PROC SQL supports the remerging of summary function results into the
table's original data. For example, computing the percentage of total is
achieved with 100*x/SUM(x) in PROC SQL. See summary-function for more information on the available summary
functions and remerging data.
PROC SQL supports all the functions available to the SAS DATA step,
except for LAG, DIF, and SOUND. Other SQL databases support their own set
PROC SQL differs from the ANSI Standard for SQL in the following ways.
The COMMIT statement is not supported.
The ROLLBACK statement is not supported. The UNDO_POLICY= option in
the PROC SQL statement addresses rollback. See the description of the UNDO_POLICY=
option in PROC SQL Statement for more information.
In the SAS System, table names, column names, and aliases are limited
to 32 characters and can contain mixed case. For more information on SAS naming
conventions, see SAS Language Reference: Dictionary. The ANSI Standard for SQL allows longer names.
The GRANT statement, PRIVILEGES keyword, and authorization-identifier
features of SQL are not supported. You may want to use operating environment-specific
means of security instead.
ANSI-compatible SQL has three-valued logic, that is, special cases for
handling comparisons involving NULL values. Any value compared with a NULL
value evaluates to NULL.
PROC SQL follows the SAS System convention for handling missing values:
when numeric NULL values are compared to non-NULL numbers, the NULL values
are less than or smaller than all the non-NULL values; when character NULL
values are compared to non-NULL characters, the character NULL values are
treated as a string of blanks.
Currently there is no provision for embedding PROC SQL statements in
other SAS programming environments, such as the DATA step or SAS/IML
FOOTNOTE 1: International Organization for Standardization (ISO): Database SQL. Document ISO/IEC 9075:1992. Also available
as American National Standards
Institute (ANSI) Document ANSI X3.135-1992.
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.