<sql-expression>

Produces a single value from a combination of symbols and operators or predicates.

Syntax

<sql-expression>::=
| (<sql-expression>)
| <sql-expression> {operator | predicate} <sql-expression>

Arguments

constant

is a number, a quoted character string, or a datetime value that represents a single, specific data value.

alias

is the alias that is assigned to a table by using the AS keyword in the FROM clause of a SELECT statement.

column

is the name of a column.

function

is a SAS or aggregate function.

See FedSQL Functions

scalar-subquery

is a subquery that returns a single value.

operator

is a symbol that specifies an action that is performed on one or more expressions. The following table shows valid operators. An expression can also contain the CASE or COALESCE expressions. For more information, see CASE Expression or COALESCE Expression.

Valid Operators
Operator
Description
+
adds
subtracts
*
multiplies
/
divides
=
equals
<>
does not equal
>
is greater than
<
is less than
>=
is greater than or equal to
<=
is less than or equal to
**
raises to a power
unary –
indicates a negative number
||
concatenates

Details

Overview of <sql-expression>

Simple expressions can be a single constant, column name, or function. Complex expressions are two or more simple expressions that are joined by an operator or predicate.

Functions in Expressions

An expression can contain a SAS function or an aggregate function. SAS functions perform a computation or system manipulation on one or more arguments and return a value. Aggregate functions produce a statistical summary of data in the entire table that is listed in the FROM clause or for each group that is specified in a GROUP BY clause. If GROUP BY is omitted, then all the rows in the table are considered to be a single group. Aggregate functions reduce all the values in each row or column in a table to one summarizing or aggregate value. For example, the sum (one value) of a column results from the addition of all the values in the column.

Subqueries in Expressions

FedSQL allows a scalar subquery (contained in parentheses) at any point in an expression where a simple column value or constant can be used. In this case, a subquery must return a single value (that is, one row with only one column).

Order of Evaluation

The operators and predicates that are shown in the following table are listed in the order in which they are evaluated.
Expressions, Operators, and Predicates and Order of Evaluation
Group
Expressions, Operators, and Predicates
Description
0
( )
forces the expression enclosed to be evaluated first
1
CASE expression
2
**
raises to a power
unary +, unary −
indicates a positive or negative number
3
*
multiplies
/
divides
4
+
adds
subtracts
5
||
concatenates
6
[NOT] BETWEEN predicate
DISTINCT predicate
[NOT] EXISTS predicate
[NOT] IN predicate
IS [NOT] TRUE predicate
IS [NOT] FALSE predicate
IS [NOT] MISSING predicate
IS [NOT] NULL predicate
IS [NOT] UNKNOWN predicate
LIKE predicate
7
=
equals
^=, <>
does not equal
>
is greater than
<
is less than
>=
is greater than or equal to
<=
is less than or equal to
8
AND
indicates logical AND
9
OR
indicates logical OR
10
NOT
indicates logical NOT
SAS missing values and null values always appear as the smallest value in the collating sequence.
You can use parentheses to group values or to nest mathematical expressions. Parentheses make expressions easier to read and can also be used to change the order of evaluation of the operators. Evaluating expressions with parentheses begins at the deepest level of parentheses and moves outward. For example, SAS evaluates A+B*C as A+(B*C), although you can add parentheses to make it evaluate as (A+B)*C for a different result.

See Also

Statements:
Last updated: February 23, 2017