10-25-2017 02:57 PM
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'
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'
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.
10-25-2017 03:10 PM - edited 10-25-2017 05:31 PM
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.
10-25-2017 04:49 PM
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.
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;
10-30-2017 11:04 AM
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.
10-30-2017 03:48 PM
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
10-30-2017 11:06 AM
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.