topic Unable to run the case statement in proc sql!! in New SAS User

Unable to run the case statement in proc sql!!

codyV — Mon, 02 Sep 2019 01:25:28 GMT

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;

Re: Unable to run the case statement in proc sql!!

Tom — Mon, 02 Sep 2019 02:27:12 GMT

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.

Re: Unable to run the case statement in proc sql!!

PGStats — Mon, 02 Sep 2019 03:48:15 GMT

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

Re: Unable to run the case statement in proc sql!!

Tom — Mon, 02 Sep 2019 04:03:38 GMT

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