O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required