Differences between SAS SQL and SPD Server SQL

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

In contrast, SAS allows keywords in some, but not all, syntax locations. The documentation chapter SPD Server SQL Syntax Reference Guide contains a list of (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 within parentheses to delimit column definitions within 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. The Help section on Scalar Expressions Contrasted with Boolean Predicates contains more information about permissible expression content.

SPD Server SQL does not support the INTO clause, as in

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 only to negate the 'equals' operator, ~= (not equals). SAS SQL allows broader 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 'between' ~ between (not between). SPD Server does not recognize that expression.

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

SAS SQL permits sub-queries without parentheses delimiters in more places than SPD Server SQL. SPD Server SQL uses parentheses whenever possible to explicitly group sub-queries or expressions that are nested within a query statement. Queries with nested expressions execute more reliably and are 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 supports the following functions:

abs, addr, arcos, arsin, atan, band, betainv, blshift, bnot, bor, brshift, bxor,
byte, ceil, cinv, collate, compbl, compound, compress, cos, cosh, css, cv,
daccdb, daccdbsl, daccsl, daccsyd, dacctab, date, datejul, datepart, datetime,
day, dcss, depdb, depdbsl, depsl, depsyd, deptab, dequote, dhms, digamma, dmax,
dmean, dmin, drange, dstd, dstderr, dsum, duss, dvar, erf, erfc, exp, finv,
fipname, fipnamel, fipstate, floor, fnonmiss, fuzz, gaminv, gamma, hms, hour,
int, intck, intnx, intrr, irr, ispexec, isplink, kurtosis, left, length, lgamma,
log, log10, log2, lowcase, max, mdy, mean, min, minute, mod, month, mort, n,
netpv, nmiss, npv, ordinal, poisson, probbeta, probbnml, probchi, probf, probgam,
probhypr, probit, probnegb, probnorm, probt, qtr, quote, range, ranuni, rank,
recip, repeat, reverse, right, round, saving, second, sign, signum, sin, sinh,
skewness, sqrt, std, stderr, stfips, stname, stnamel, substr, sum, tan, tanh,
time, timepart, tinv, today, tranwrd, trigamma, trim, upcase, uss, var, weekday,
year, zipfips, zipname, zipnamel, and zipstate.

Ranuni functions can show slight variation from run to run due to the impact of parallel processing.

Note that date, int, left, right and trim are reserved keywords. Therefore, they must be preceded by a backslash in SPD Server SQL queries:

select \date() from t ;