Using CASE and LIKE to evaluate - logic issues

Question

Unfortunately I am not a coder…I have used CASE statements before but not in combination with "LIKE". I'm not sure if my issues have to do with CASE or something else.

I am using proc sql (SAS) to identify cases that have purchased specific products as defined by the COMPANY and by words in the PROD_LONG_NM. This will be a series of statements necessary for each COMPANY and PROD-LONG_NM combination.

Any cases that meet a definition will be flagged and can then be excluded from further consideration for the remaining statements – if they don’t meet the definition then they need to be considered by subsequent statements until they do meet one…

For example, this statement runs without (syntax) errors but logically does not find any cases until fire_smbiz_conv and then not all of them. The final six statements pick up ALL the cases without omitting what should have been picked up by the previous CASE/LIKE statements:

    case when company='MUTUAL FUNDS' and (PROD_LONG_NM LIKE '401') or (PROD_LONG_NM LIKE 'SEP') or (PROD_LONG_NM LIKE 'SIMPLE')
            then 1 else 0 end as mf_smbiz_conv,

case when company='BANK' and (PROD_LONG_NM LIKE 'BUSINESS') 
            then 1 else 0 end as bk_smbiz_conv,

case when company='AUTO' and (PROD_LONG_NM LIKE 'COMMERCIAL') or (PROD_LONG_NM LIKE 'ENOL') or (PROD_LONG_NM LIKE 'BUSINESS')
            then 1 else 0 end as auto_smbiz_conv,

case when company='FIRE' and (PROD_LONG_NM LIKE 'BUSINESS') or (PROD_LONG_NM LIKE 'COMMERCIAL') or (PROD_LONG_NM LIKE 'CONTRACTORS')
            or (PROD_LONG_NM LIKE 'RANCH') or (PROD_LONG_NM LIKE 'FIDELITY') or (PROD_LONG_NM LIKE 'RENTAL') 
            or (PROD_LONG_NM LIKE 'SURETY') or (PROD_LONG_NM LIKE 'WORKERS') 
            then 1 else 0 end as fire_smbiz_conv,

     case when company='AUTO'           then 1 else 0 end as oth_auto_conv,
     case when company='FIRE'           then 1 else 0 end as oth_fire_conv,
     case when company='LIFE'           then 1 else 0 end as oth_life_conv,
     case when company='HEALTH'         then 1 else 0 end as oth_health_conv,
     case when company='MUTUAL FUNDS'   then 1 else 0 end as oth_mf_conv,
     case when company='BANK'           then 1 else 0 end as oth_bank_conv

You are not properly using LIKE anyway. Your usage is no different than if you had used `=`. — StilesCrisis, Jun 01 '15 at 16:03
Do you mean in combination with the CASE or that I could be using a shorter format such as (PROD_LONG_NM LIKE 'xxxx', 'xxxxxx'). The SAS manual has an example: job_code like 'xxx'; — user3791254, Jun 01 '15 at 17:01
The `LIKE` keyword allows you to use wildcard characters like `%` and `_`. If you don't use wildcards, it's no better than `=`. The manual likely assumes you already know how SQL wildcards work. — StilesCrisis, Jun 01 '15 at 17:03
That's good to know because perhaps I should be using CONTAINS instead - I don't want to be limited literally to what's in quotes. — user3791254, Jun 01 '15 at 17:10
Okay - so changed out all the LIKEs for CONTAINS and that looks much better. The main issue I see now is that I need to exclude those found in e.g. the auto_smbiz_conv from the later oth_auto_conv and this code seems to be including them. — user3791254, Jun 01 '15 at 17:59
Cool, I've made an answer if you feel like marking the question as answered. — StilesCrisis, Jun 01 '15 at 18:00
I'd say it is half answered - still need to have this progress through a "funnel" . Instead of continued CASE statements for the last 6 statements, is there another way to be sure they each include those from the parallel statement at the top (those with the same company name)? — user3791254, Jun 01 '15 at 18:13
You should include sample data and desired results in the question. That would be helpful. — StilesCrisis, Jun 01 '15 at 19:41
It's probably inelegant but I just had to make the last parallel set of CASE statements using "NOT CONTAINS". Thanks for the CONTAINS suggestion - that got me going. — user3791254, Jun 01 '15 at 21:14

score 0 · Accepted Answer · answered Jun 01 '15 at 17:59

0

It looks like you're using the LIKE keyword when you really meant to say CONTAINS.

answered Jun 01 '15 at 17:59

StilesCrisis

15,972
4
39
62

score 0 · Answer 2 · answered Jun 01 '15 at 18:08

0

Put % before and after the value condition, also it is usually good to pass lower() on the field and write the conditions in lowercase

answered Jun 01 '15 at 18:08

JohnandLyn Henry

277
1
3
11

Using CASE and LIKE to evaluate - logic issues

2 Answers2