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

Dictionary

BETWEEN Condition

Selects rows where column values are within a range of values.

Syntax

sql-expression <NOT> BETWEEN sql-expression

AND sql-expression

Required Argument

sql-expression

See “sql-expression” on page 351.

Details

• The SQL expressions must be of compatible data types. They must be either all

numeric or all character types.

• Because a BETWEEN condition evaluates the boundary values as a range, it is not

necessary to specify the smaller quantity first.

• You can use the NOT logical operator to exclude a range of numbers. For example,

you can eliminate customer numbers between 1 and 15 (inclusive) so that you can

retrieve data on more recently acquired customers.

• PROC SQL supports the same comparison operators that the DATA step supports.

For example:

x between 1 and 3

x between 3 and 1

1<=x<=3

x>=1 and x<=3

BTRIM Function

Removes blanks or specified characters from the beginning, the end, or both the beginning and end of a

character string.

Syntax

BTRIM(<<btrim-specification> <'btrim-character' FROM>> sql-expression)

Required Argument

sql-expression

must resolve to a character string or character variable.

316 Chapter 8 • SQL Procedure Components

See

“sql-expression” on page 351.

Optional Arguments

btrim-specification

is one of the following:

LEADING

removes the blanks or specified characters from the beginning of the character

string.

TRAILING

removes the blanks or specified characters from the end of the character string.

BOTH

removes the blanks or specified characters from both the beginning and the end

of the character string.

Default

BOTH

btrim-character

is a single character that is to be removed from the character string. The default

character is a blank.

Details

The BTRIM function operates on character strings. BTRIM removes one or more

instances of a single character (the value of btrim-character) from the beginning, the end,

or both the beginning and end of a string, depending whether LEADING, TRAILING, or

BOTH is specified. If btrim-specification is not specified, then BOTH is used. If btrim-

character is omitted, then blanks are removed.

Note: SAS adds trailing blanks to character values that are shorter than the length of the

variable. Suppose you have a character variable Z, with length 10, and a value

xxabcxx. SAS stores the value with three blanks after the last x (for a total length of

10). If you attempt to remove all the x characters with

btrim(both 'x' from z)

, then the result is abcxx because PROC SQL sees the trailing characters as blanks,

not the x character. In order to remove all the x characters, use this statement.

btrim(both 'x' from btrim(z))

The inner BTRIM function removes the trailing blanks before passing the value to

the outer BTRIM function.

CALCULATED

Refers to columns already calculated in the SELECT clause.

Syntax

CALCULATED column-alias

CALCULATED 317

Required Argument

column-alias

is the name that is assigned to the column in the SELECT clause.

Details

CALCULATED enables you to use the results of an expression in the same SELECT

clause or in the WHERE clause. It is valid only when used to refer to columns that are

calculated in the immediate query expression.

CASE Expression

Selects result values that satisfy specified conditions.

Examples: “Example 3: Updating Data in a PROC SQL Table” on page 273

“Example 13: Producing All the Possible Combinations of the Values in a Column” on

page 303

Syntax

CASE <case-operand>

WHEN when-condition THEN result-expression

<WHEN when-condition THEN result-expression …>

<ELSE result-expression>

END

Required Arguments

when-condition

• When case-operand is specified, when-condition is a shortened SQL expression

that assumes case-operand as one of its operands and that resolves to true or

false.

• When case-operand is not specified, when-condition is an SQL expression that

resolves to true or false.

result-expression

is an SQL expression that resolves to a value.

See

“sql-expression” on page 351.

Optional Argument

case-operand

is a valid SQL expression that resolves to a table column whose values are compared

to all the when-conditions.

See

“sql-expression” on page 351.

318 Chapter 8 • SQL Procedure Components