Chapter 8 – SQL Case When
Section I – Usage
What is the CASE expression
The CASE expression is essentially the SQL version of “If-Then-Else”. A CASE expression allows you to return a result that is conditionally evaluated for each row in your table. Instead of using If [true] Then [NewValue], use the WHEN-THEN clauses when you want to execute a CASE expression that meet the criteria. The Else statement is optional but highly suggested.
The CASE expression can be executed in two manners. One is be evaluating the operand following the CASE keyword, which will then require you to account for each possible result, the other is to use the operand in each WHEN expression.
Section II – How it works
The above description was most likely incomprehensible, as most programming documentation is, so we’ll go right to examples to get the topic down. First, let’s start with the easiest CASE expression to work with, the Boolean version.
CASE WHEN [boolean]….
This version is called the Boolean-CASE expression. The reason for that is each WHERE expression can be independent of the others. Here are a few examples, from easiest-to-hardest.
Proc SQL;
Create Table Example01 as
Select Distinct
IDYear,
IDMonth,
Case
When IDMonth in (1, 2, 3) Then “Q1″
When IDMonth in (4, 5, 6) Then “Q2″
When IDMonth in (7, 8, 9) Then “Q3″
Else “Q4″
End as IDQuarter
From Chapt08.LKP_LoadPeriods;
Quit;
It’s important to realize that the CASE expression works as an IF-THEN-ELSEIF-THEN-ELSE statement. So as SAS evaluates each row, it will stop once a condition is met. In the below example, I’ve purposefully ordered each evaluation statement to reinforce this.
Proc SQL;
Create Table Example02 as
Select *,
Case
When StatusCodeID < 11 Then “Good Data”
When StatusCodeID <= 7 Then “Estimates”
When StatusCodeID <= 5 Then “Forecasts”
When StatusCodeID = 0 Then “Missing”
Else “Error”
End as DataStatus
From Chapt08.Data_Load;
Quit;
As I’m sure you’ve noticed, every row in the table evaluates on the first WHEN statement, because every status code is less than eleven. In the next two examples, we show the correct way to stack where statements for the previous two statements.
Proc SQL;
Create Table Example03 as
Select *,
Case
When StatusCodeID = 0 Then “Missing”
When StatusCodeID <= 5 Then “Forecasts”
When StatusCodeID <= 7 Then “Estimates”
When StatusCodeID < 11 Then “Good Data”
Else “Error”
End as DataStatus
From Chapt08.Data_Load;
Quit;
Proc SQL;
Create Table Example04 as
Select Distinct
IDYear,
IDMonth,
Case
When IDMonth <= 3 Then “Q1″
When IDMonth <= 6 Then “Q2″
When IDMonth <= 9 Then “Q3″
Else “Q4″
End as IDQuarter
From Chapt08.LKP_LoadPeriods;
Quit;
CASE [operand] WHEN…..
The other way to use a CASE expression is when you are working with Ordinal data and wish to convert to numeric. You can still use numeric data, but Boolean functions will not work in the CASE – Operand – WHEN version. Here’s a rewrite of an earlier example, be sure to note how inefficient this version is.
Proc SQL;
Create Table Example05 as
Select Distinct
IDYear,
IDMonth,
Case IDMonth
When 1 Then “Q1″
When 2 Then “Q1″
When 3 Then “Q1″
When 4 Then “Q2″
When 5 Then “Q2″
When 6 Then “Q2″
When 7 Then “Q3″
When 8 Then “Q3″
When 9 Then “Q3″
Else “Q4″
End as IDQuarter
From Chapt08.LKP_LoadPeriods;
Quit;
In the next example, we can actually take advantage of the ordinal properties.
Proc SQL;
Create Table Example06 as
Select *,
Case OpCo
When “TNC” Then “AEP West”
When “TCC” Then “AEP West”
When “SWP” Then “AEP West”
When “PSO” Then “AEP West”
Else “AEP East”
End as AEPSystem
From Chapt08.Obj_Recorders;
Quit;
This lesson was very short and now is a great time to review everything you’ve done in the first eight chapters. Try to practice as many as you can before we move into the more advanced database properties of SQL.
