Proc SQL [4-14]

Case Expression
CASE
 WHEN height>=60 
   THEN ">=60 inches"
 ELSE "<60 inches"  
END AS hflag
​
The CASE expression is similar to the if-then statement in data step.

​It can be used to conditionally assign values to columns.

Copy and run the SCHOOL table from the yellow box below if you haven't done so.

The HEIGHT column contains the height of students in inches.

Now, we are going to identify whether the student is taller or shorter than 60 inches.

Example
Proc sql;
select name, height,
  case
    when height>60
      then ">60 inches"
    else "<=60 inches"
  end as hflag
from school;
quit;
The CASE expression creates a new column called HFLAG.

The new column flags whether the student is taller or shorter than 60 inches.

The breakdown of the code appears below:

The keywords CASE and END signal the start and end of the CASE expression:

The WHEN clause assigns values to the column, based on the conditions specified.

The column name is assigned at the end of the CASE expression:

You can also specify more than two conditions in the CASE expression.

Example
Proc sql;
select name, height,
  case
    when height>70
      then ">70 inches"
    when height between
      60 and 70
    then "60-70 inches"
    else "<60 inches"
  end as hflag
from school;
quit;

The CASE expression above flags the height of students as "< 60 inches", "60-70 inches" or ">70 inches" based on the HEIGHT column.
Exercise

Copy and run the SALARY table from the yellow box below:

​The SALARY table contains two columns: employee ID and their salary. Employee's year-end bonus is calculated based on their yearly salary.
  • Salary Below $60,000:
    5% Bonus
  • Salary Between $60,000 to $80,000:
    6% Bonus
  • Salary Above $80,000:
    ​5.5% Bonus

Example: an employee making $55,000 will receive 5% of their salary as the year-end bonus. The bonus amount is $55,000 x 5% = $2750.

Write a Proc SQL step that computes the year-end bonus for each employee. 
Need some help? 


HINT:

Create the BONUS column based on the SALARY column. Use the CASE expression to calculate the bonus based on the yearly salary.


SOLUTION:

proc sql;
select *,
case
when salary < 60000 then salary*0.05
when salary between 60000 and 80000 then salary*0.06
when salary > 80000 then salary*0.055
end as bonus
from salary;
quit;

Fill out my online form.