Unable to run the case statement in proc sql!!

[codyV]

Hi,

i am  using  the  below code to  get a number against the condition. But i only find  the number  getting updated to status = 'new prospect' and not to the status  = 'ACCEPTED-MINE/CHANNEL'.  

I am unsure  what could go wrong with the code. Appreciate your help and suggestions.

proc sql;

create table a11 as

select *,

case when ((m_flag = 1) and (s_flag = 0) and (x_flag = 0)) and

((status = 'NEW PROSPECT') and (outcome = 'COMPETITIVE ACCOUNT') or

(status = 'NEW PROSPECT')  and (outcome = 'CONTACTED') or

(status = 'NEW PROSPECT')  and (outcome = 'IMMEDIATE TIMEFRAME') or

(status = 'NEW PROSPECT')  and (outcome = 'QUALIFIED LEAD')  or

(status = 'NEW PROSPECT')  and (outcome = '') or

(status = 'ACCEPTED-MINE/CHANNEL') and (outcome = 'COMPETITIVE ACCOUNT') or

(status = 'ACCEPTED-MINE/CHANNEL') and (outcome = 'CONTACTED') or

(status = 'ACCEPTED-MINE/CHANNEL') and (outcome = 'IMMEDIATE TIMEFRAME') or

(status = 'ACCEPTED-MINE/CHANNEL') and (outcome = 'QUALIFIED LEAD')  or

(status = 'ACCEPTED-MINE/CHANNEL') and (outcome = ''))

then 100  else  0 end as Lscore

from  have;

quit;

[Tom]

 But i only find  the number  getting updated to status = 'new prospect' and not to the status  = 'ACCEPTED-MINE/CHANNEL'.  

Are you sure you posted the right combination of question and code?

The code you posted is only testing the values of STATUS, not updating them.

[PGStats]

Check the spelling of ACCEPTED-MINE/CHANNEL carefully, especially the hyphen. There are many characters that print like a dash or hyphen.

[Tom]

You have illogical AND/OR combination.  I think you need to add more ().

You have this pattern:

(  (status = 'NEW PROSPECT') and (outcome = 'COMPETITIVE ACCOUNT') 
or (status = 'NEW PROSPECT') and (outcome = 'CONTACTED') 
or  ...
)

and you probably meant this pattern instead.

(  ((status = 'NEW PROSPECT') and (outcome = 'COMPETITIVE ACCOUNT'))
or ((status = 'NEW PROSPECT') and (outcome = 'CONTACTED'))
or  ...
)

You might want to split the different conditions into different WHEN clauses.

Perhaps something like:

case when (m_flag ne 1) then 0
     when (s_flag ne 0) then 0
     when (x_flag ne 0) then 0
...