Chapter Contents

Previous

Next

CASE expression

Featured in:

Updating Data in a PROC SQL Table and Producing All the Possible Combinations of the Values in a Column

• case-operand, when-condition, and result-expression must be valid sql-expressions. See sql-expression .

Details

The CASE expression selects values if certain conditions are met. A CASE expression returns a single value that is conditionally evaluated for each row of a table (or view). Use the WHEN-THEN clauses when you want to execute a CASE expression for some but not all of the rows in the table that is being queried or created. An optional ELSE expression gives an alternative action if no THEN expression is executed.

When you omit case-operand, when-condition is evaluated as a Boolean (true or false) value. If when-condition returns a nonzero, nonmissing result, the WHEN clause is true. If case-operand is specified, it is compared with when-condition for equality. If case-operand equals when-condition, the WHEN clause is true.

If the when-condition is true for the row being executed, the result-expression following THEN is executed. If when-condition is false, PROC SQL evaluates the next when-condition until they are all evaluated. If every when-condition is false, PROC SQL executes the ELSE expression, and its result becomes the CASE expression's result. If no ELSE expression is present and every when-condition is false, the result of the CASE expression is a missing value.

You can use CASE expressions in the SELECT, UPDATE, and INSERT statements.

Example

The following two PROC SQL steps show two equivalent CASE expressions that create a character column with the strings in the THEN clause. The CASE expression in the second PROC SQL step is a shorthand method that is useful when all the comparisons are with the same column.

proc sql;
   select *, case
                when degrees > 80 then 'Hot'
                when degrees < 40 then 'Cold'
                else 'Mild'
                end
     from temperatures;

proc sql;
   select *, case Degrees
                when > 80 then 'Hot'
                when < 40 then 'Cold'
                else 'Mild'
                end
     from temperatures;

Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.