Desktop productivity for business analysts and programmers

Case Statement Multiple Ends or One?

Reply
Contributor
Posts: 42

Case Statement Multiple Ends or One?

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!

Super User
Super User
Posts: 7,162

Re: Case Statement Multiple Ends or One?

[ Edited ]
Posted in reply to mmagnuson

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.

 

 

PROC Star
Posts: 1,180

Re: Case Statement Multiple Ends or One?

Posted in reply to mmagnuson

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.
Contributor
Posts: 42

Re: Case Statement Multiple Ends or One?

I used this case statement to create a computed column.

PROC Star
Posts: 1,180

Re: Case Statement Multiple Ends or One?

Posted in reply to mmagnuson

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

Contributor
Posts: 42

Re: Case Statement Multiple Ends or One?

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

Super User
Super User
Posts: 7,162

Re: Case Statement Multiple Ends or One?

Posted in reply to mmagnuson

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.

Ask a Question
Discussion stats
  • 6 replies
  • 205 views
  • 0 likes
  • 3 in conversation