topic Re: Case when multiple condition in expression in SAS Data Science

Case when multiple condition in expression

David_Billa — Sun, 07 Jun 2020 12:50:00 GMT

I need help writing the case when with multiple conditions to use in 'expression' in DI Job. I'm getting an syntax error if I use below. How to keep adding the conditions in case when? Shouldn't we use 'else' to seperate the condition?

case when ENTITY_ID_SF in ('1000','1111')  and SEGMENT='NL' then (sum(GR_INPUT)/(sum(PREM_RES_GR)/3)) 
end
else
case when ENTITY_ID_SF in ('1000','1111') and SEGMENT='PL' then (sum(GR_INPUT)/(sum(PREM_RES_GR)/3)) 
end

Re: Case when multiple condition in expression

Jagadishkatam — Sun, 07 Jun 2020 14:52:36 GMT

try the below code

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END as variable_name 



case 
when ENTITY_ID_SF in ('1000','1111') and SEGMENT='NL' then (sum(GR_INPUT)/(sum(PREM_RES_GR)/3)) when ENTITY_ID_SF in ('1000','1111') and SEGMENT='PL' then (sum(GR_INPUT)/(sum(PREM_RES_GR)/3)) else . 
end as variable_name

Re: Case when multiple condition in expression

ballardw — Sun, 07 Jun 2020 17:33:04 GMT

Please. Anytime you get an ERROR, copy the entire procedure with the messages from the LOG and show the code plus the errors. Please.

Include the entire procedure because the cause could be something like a missed or extra quote or parentheses in a previous statement than the place that SAS indicates the error.

Re: Case when multiple condition in expression

Tom — Sun, 07 Jun 2020 18:21:53 GMT

Why would you but an ELSE between them? ELSE is for catching all of the observations that do not meet any of the earlier WHEN conditions. If you have multiple WHEN conditions just list then one after the other.

case when ENTITY_ID_SF in ('1000','1111') and SEGMENT='NL' then sum(GR_INPUT)/(sum(PREM_RES_GR)/3)
     when ENTITY_ID_SF in ('1000','1111') and SEGMENT='PL' then sum(GR_INPUT)/(sum(PREM_RES_GR)/3) 
end

Right now both of your two different conditions will return the same result when they are true. So you could collapse them into one WHEN condition. Like this:

case when (ENTITY_ID_SF in ('1000','1111') and SEGMENT='NL')
       or (ENTITY_ID_SF in ('1000','1111') and SEGMENT='PL') then sum(GR_INPUT)/(sum(PREM_RES_GR)/3)
end

or like this:

case when (ENTITY_ID_SF in ('1000','1111') and SEGMENT in ('NL','PL')) then sum(GR_INPUT)/(sum(PREM_RES_GR)/3)
end

But what are you actually trying to do? Did you expect to get different results based on the value of SEGMENT? If so then show some simple input data and what output you want.