Differences between SAS SQL and SPD Server SQL

SPD Server uses keywords to initiate statements or to refer to syntax elements. For example, you can use the words where and group only in certain ways because SPD Server uses WHERE and GROUP BY clauses. Keywords are treated as reserved words. You cannot use keywords in the name of a libref, a table, a column, or an index.

In contrast, SAS SQL allows keywords in some, but not all, syntax locations. For more information about keywords that are restricted by SPD Server, see Reserved Keywords.

SPD Server SQL uses brackets to delimit table options. SAS SQL uses parentheses as delimiters. You can place table options in a CREATE TABLE statement. You must put table options inside parentheses to delimit column definitions in a table.

SPD Server SQL does not allow mixing scalar expressions with Boolean predicates. SAS SQL does allow mixing scalar expressions with Boolean predicates in most places. For more information about what content is permissible in expressions, see Scalar Expressions and Boolean Predicates.

SPD Server SQL does not support the INTO clause. For example, SPD Server SQL does not support the following statement:

select a, b into :var1, :var2 from t where a > 7;

In contrast, SAS SQL supports the INTO clause.

SPD Server SQL supports the use of the tilde character (~) only to negate the equals operator (=), as in ~= (not equals). SAS SQL supports the use of the tilde character where the tilde is synonymous with not and can be combined with various operators. For example, SAS SQL can use the tilde with the BETWEEN operator, as in ~BETWEEN (not between). SPD Server does not recognize this expression.

SAS SQL permits subqueries without delimiting parentheses in more places than does SPD Server SQL. SPD Server SQL uses parentheses to explicitly group subqueries or expressions that are nested in a query statement whenever possible. Queries with nested expressions execute more reliably and are also easier to read.

SPD Server SQL does not support the USER keyword in the INSERT statement. For example, the following query fails in SPD Server SQL:

insert into t1(myname) values(USER);

SPD Server SQL does not support all of the SQL functions that SAS supports. For a complete list of the SQL functions that SPD Server SQL supports, see SQL Functions Supported by SPD Server.

For a complete list of the SQL WHERE processing functions that SPD Server SQL supports, see SQL WHERE-Processing Functions Supported by SPD Server.