Case Statement Multiple Ends or One?

[mmagnuson]

I have started to get the hang of CASE statements and found that I write them differently than other people.

Usually I do something similar to this,

Case when Name=Joe 'Yes' else 'NO'

         when Name=Jane 'Yes' else 'No'

         end

Other CASE statements I've seen use CASE ELSE and then multiple ENDs:

Case when Name=Joe 'Yes' else

Case when Name=Jane 'Yes' else 'No'

         end end

Is one way more efficient?  I find that using only one END makes it feel neat but I don't want to end up slowing the program down by accident.

Thanks!

[Tom · Accepted solution]

In general the advice for speeding up processing time for CASE statement (or DO SELECT statement or any other way of coding a series of tests) is to make sure that the most common cases are listed first, since once it finds a match it stops having to perform the other tests.

You code is messed, but nesting the CASE statements should not slow your program down.  It really should be doing the same number of comparisons.

proc sql ;
select
  name
, case when (name='Alfred') then 'Yes' 
       when (name='Jane') then 'Yes' 
       else 'No'
  end as FLAG
from sashelp.class
;

select
  name
, case when (name='Alfred') then 'Yes' 
       else case when (name='Jane') then 'Yes' 
                 else 'No'
            end
  end as FLAG
from sashelp.class
;
quit;

But you could just go ahead and run some timing tests using your real data or example data and see if there is any impact.

[TomKari]

From my memory, neither of your options looked right. I reviewed the PROC SQL documentation. Here's an extract from it, that describes two ways to use the CASE statement. I suggest you stick to them, simply so that other programmers won't get confused.

If I had run across either of the options that you've shown, I'd have to run some tests to satisfy myself that they worked correctly. Even then, I'd probably recode them.

Tom

CASE Expression

Example

The following two PROC SQL steps show two equivalent CASE expressions that create a character column with the strings in the THEN clause. The CASE expression in the second PROC SQL step is a shorthand method that is useful when all the comparisons are with the same column.

proc sql;
   select Name, case
                when Continent = 'North America' then 'Continental U.S.'
                when Continent = 'Oceania' then 'Pacific Islands'
                else 'None'
                end as Region
      from states;

proc sql;
   select Name, case Continent
                when 'North America' then 'Continental U.S.'
                when 'Oceania' then 'Pacific Islands'
                else 'None'
                end as Region
      from states;

Note: When you use the shorthand method, the conditions must all be equality tests. That is, they cannot use comparison operators or other types of operators.

[mmagnuson]

I used this case statement to create a computed column.

[TomKari]

When I use the advanced expression builder, I get the following scaffold code:

CASE  
   WHEN <condition>
   THEN <value>
   ELSE <value>
END

With this code, just copy the "when...then" lines as many times as you need, and leave one "else" after them, followed by "end". That should give you nice, clean code.

Tom

[mmagnuson]

So it doesn't slow it down if there is only one END vs multiple ends?  My main concern was slowing down the process but if it doesn't seem to make a difference then that's great news

[Tom]

---

@mmagnuson wrote:

I used this case statement to create a computed column.

---

So after the END you put the name you want the computed column to have.