4.2. Conditional Logic

As experienced PROC SQL programmers know, it is often necessary to test and evaluate conditions as true or false. From a programming perspective, the evaluation of a condition determines which of the alternate paths a program will follow. Conditional logic in PROC SQL is frequently implemented in a WHERE clause to reference constants and relationships among columns and values. The SQL procedure also allows the identification and assignment of data values in a SELECT statement using CASE expressions (described in the next section). To show how constants and relationships are referenced in a WHERE clause, a simple example is given.

The WHERE clause expression evaluates whether the cost of a product (PRODCOST) is less than $400.00. During execution the expression evaluates to true only when the value of PRODCOST is less than 400.00. Otherwise, when the value is greater than or equal to 400.00, the expression evaluates to false. This is an important concept because any data rows satisfying the condition are only selected if the specified condition is true.

SQL Code

PROC SQL;
  SELECT *
    FROM PRODUCTS
      WHERE PRODCOST < 400.00;
QUIT;

The following relation tests whether the cost of a product (PRODCOST) is greater than $400.00. During execution the expression evaluates to true when the value of PRODCOST is greater than 400.00 and the rows of data satisfying the expression are selected. Otherwise, if the value is less than or equal to 400.00, the expression evaluates to false.

SQL Code

PROC SQL;
  SELECT *
    FROM PRODUCTS
      WHERE PRODCOST > 400.00;
QUIT;

A relation can also be used on nonnumeric literals and nonnumeric columns. In the next example, a case-sensitive expression is constructed to represent the type of product (PRODTYPE) made by a manufacturer. When evaluated, a condition of “true” or “false” is produced depending on whether the current value of PRODTYPE is identical (character-by-character) to the literal value “Software”. When a condition of “true” occurs then the rows of data satisfying the expression are selected; otherwise they are not.

SQL Code

PROC SQL;
  SELECT *
    FROM PRODUCTS
      WHERE PRODTYPE = "Software";
QUIT;

To ensure a character-by-character match of a character value, the previous expression could be specified with the UPCASE function as follows.

SQL Code

PROC SQL;
  SELECT *
    FROM PRODUCTS
      WHERE UPCASE(PRODTYPE) = "SOFTWARE";
QUIT;

When the relations < and > are defined for nonnumeric values, the issue of implementation-dependent collating sequence for characters comes into play. For example, “A” < “B” is true, “Y” < “Z” is true, “B” < “A” is false, and so forth. For more information about character collating sequences, refer to the specific operating system documentation for the platform you are using.