SAS: How to Use IF Statement in PROC SQL


While it’s not possible to use an IF statement in PROC SQL in SAS, you can use the CASE operator to define the values that a variable should take on based on certain conditions.

The following examples show how to use the CASE operator in practice with the following dataset in SAS that contains information about various basketball players:

/*create dataset*/
data my_data;
    input team $ points;
    datalines;
Cavs 12
Cavs 14
Warriors 15
Hawks 18
Mavs 31
Mavs 32
Mavs 35
Celtics 36
Celtics 40
;
run;

/*view dataset*/
proc print data=my_data;

Example 1: Use CASE Operator with Only Two Outcomes

We can use the CASE operator in PROC SQL to generate a new column in the dataset called points_flag that takes a value of 0 if the value in the points column is less than 20 or a value of 1 otherwise:

/*create new column called points_flag using case operator*/ 
proc sql;
  select *,
      case 
      when points < 20 then 0 else 1
      end as points_flag
      from my_data;
quit;

Notice that the points_flag column takes on a value of 0 if the value in the points column is less than 20 or a value of 1 otherwise.

Example 2: Use CASE Operator with More Than Two Outcomes

We can also use the CASE operator in PROC SQL to generate a new column in the dataset called points_flag that takes a value of 0 if the value in the points column is less than 20, a value of 1 if points is less than 35, or a value of 2 otherwise:

/*create new column called points_flag using case operator*/ 
proc sql;
  select *,
      case 
      when points < 20 then 0
      when points < 35 then 1 else 2
      end as points_flag
      from my_data;
quit;

Notice that the points_flag column takes on a value of 0, 1, or 2 depending on the corresponding value in the points column.

Note: Feel free to use as many when statements as you’d like to generate as many different values as you’d like in a new column.

Additional Resources

The following tutorials explain how to perform other common tasks in SAS:

SAS: How to Use the WHERE Operator in PROC SQL
SAS: How to Use the IN Operator in PROC SQL
SAS: How to Use UPDATE Within PROC SQL
SAS: How to Use CONTAINS in PROC SQL

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *