BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mmagnuson
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

 

 

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

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
Onyx | Level 15

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
Quartz | Level 8

I used this case statement to create a computed column.

TomKari
Onyx | Level 15

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
Quartz | Level 8

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
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 53679 views
  • 6 likes
  • 3 in conversation