Search the site...

SASCRUNCH TRAINING
  • Home
  • Member's Area
  • How to Start
  • SAS Interface
  • Creating a Data Set
  • Practical SAS Training Course
  • SAS Certified Specialist Training Program
  • Proc SQL Course
  • Introduction to Time Series Analysis
  • SAS Project Training Course
  • Full Training / Membership
  • Sign up
  • About us
  • Contact us
  • Home
  • Member's Area
  • How to Start
  • SAS Interface
  • Creating a Data Set
  • Practical SAS Training Course
  • SAS Certified Specialist Training Program
  • Proc SQL Course
  • Introduction to Time Series Analysis
  • SAS Project Training Course
  • Full Training / Membership
  • Sign up
  • About us
  • Contact us
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.
Picture

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.
Picture


The breakdown of the code appears below:

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

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

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

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.
Picture

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. 
Next

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.
Already a member? Go to member's area.