If we want to do conditional processing in a PROC SQL procedure, we can use the SAS case expression. For the conditions, we use when. 

proc sql;
    select name, age, 
        case age 
        when < 18 then "Can't Drink or Smoke"
        when > 21  then "Can Drink and Smoke"
        else "Can't Smoke, but Can Smoke"
        end as legal_description
        from data;
quit;

When working with data, conditional processing is very useful for defining new variables and performing queries on our datasets.

When using the PROC SQL procedure, conditional processing is very useful for creating new columns.

The correct conditional logic in PROC SQL is using a case expression with when clauses. Below is a template for the correct conditional logic in PROC SQL.

proc sql;
...
  case 
    when when-condition then result-expression
    
    ...
   
  end
...
quit;

Let’s go through a few examples.

Using case when in PROC SQL for Conditional Processing

In our SAS code, we can use case when expressions in PROC SQL for simple conditional processing, or complex conditional processing.

Let’s say we have a dataset of information for a group of people.

We can use PROC SQL to perform conditional processing to create a new column “Height Category” in the following way.

proc sql;
    select name, height, 
    case height
        when < 60 then "Short"
        when > 72 then "Tall"
        else "Average"
    end as height_category
    from people_dataset;
quit;

In the third line of the above PROC SQL statement, you don’t need to put “height” after “case”. You can also put “height” in the when condition.

proc sql;
    select name, height, 
    case 
        when height < 60 then "Short"
        when height > 72 then "Tall"
        else "Average"
    end as height_category
    from people_dataset;
quit;

Using Operators for Complex when Conditions in PROC SQL

Like with any conditional statement, we can use the logical operators “and” and “or” to define more complex conditional when expressions.

Let’s say we have the same dataset as the last example. We can define a new column called “size” with a conditional expression using the variables “height” and “weight”.

proc sql;
    select name, height, 
    case 
        when height < 60 and weight < 100 then "Small"
        when height > 72 and weight > 200 then "Big"
        else "Average"
    end as size
    from people_dataset;
quit;

case can take any number of when conditions. One final example has some more complex logical statements.

proc sql;
    select name, height, 
    case 
        when height < 60 and weight < 100 then "Smallest"
        when height between 60 and 66 and weight between 100 and 125 then "Small"
        when height between 66 and 72 and weight between 175 and 200 then "Big"
        when height > 72 and weight > 200 then "Biggest"
        else "Average" /* weight between 125 and 175 */
    end as size
    from people_dataset;
quit;

If you are looking for how to do conditional processing in a SAS data step or the SAS Macro Language, read this article on using if then else statements in SAS.

Hopefully this article has been useful for you to understand how to use case when conditional expressions in PROC SQL.

Categorized in:

SAS,

Last Update: February 26, 2024