IF THEN ELSE using wildcards in an open text variable

[hnb_matt_d]

Good afternoon,

I am working through trying to create a new variable based on possible text in an open text field from a previously created table with little success.  

This first piece of code is working as desired.  It is giving results of an item_identifier and then note(s) associated with it that contain one of the 4 specific pieces of text as indicated.  The table_notes.note variable is open text from an Oracle database, as an FYI.  

proc sql;
create table NOTES_KEYWORD as
select table_cases.item_identifier
          ,table_notes.note

from libone.table_cases
        ,libone.table_case_audits
        ,libone.table_audits
        ,libone.table_notes

where table_cases.item_internal_id = table_case_audits.item_internal_id
and table_case_audits.item_audit_internal_id = table_audits.item_audit_internal_id
and table_audits.item_audit_internal_id = table_notes.item_audit_internal_id
and table_cases.item_identifier like '20__C%'
and (table_cases.case_date >= '01jun2020'd
and table_cases.case_date < '01aug2020'd)
and (upcase(table_notes.note) like '%ZELLE%'
or upcase(table_notes.note) like '%CASHEDGE%'
or upcase(table_notes.note) like '%PAYPOINT%'
or upcase(table_notes.note) like '%FDR%')

order by table_notes.create_date;

quit;

What I'm wanting to do next is create a new variable (note2) from the existing note variable using one of the 4 text snippets, as appropriate:

data notes_keyword_final;
set notes_keyword;

if upcase(note) =: 'ZELLE' then NOTE2='ZELLE';
else if upcase(note) =: 'CASHEDGE' then NOTE2='CASHEDGE';
else if upcase(note) =: 'PAYPOINT' then NOTE2='PAYPOINT';
else if upcase(note) =: 'FDR' then NOTE2='FDR';

drop note;

run;

This produces the list of item_identifiers, but nothing populated in the note2 variable.  

I cannot substr the initial note as it is open text and the text string may appear anywhere therein.  Likewise, delimiting is not possible as it is not preceded by any exclusive formatting.  

Thanks in advance for any and all assistance rendered.  Always a lot of help on this site.  It is GREATLY appreciated.  

[Kurt_Bremser]

Use the INDEX function to check if a string is present in another.

[PaigeMiller]

The FIND() function also works in this situation. And optionally can operate in case-insensitive mode.

[pink_poodle]

Symbol "=:" is a begins-with operator, but your inputs have something else at start  (e.g., "%Zelle%"), which is probably why its not seeing them.

[hnb_matt_d]

Currently working through suggestions provided.  Will update with final (working) code when it's developed.  THANKS!  

[hnb_matt_d]

This code is giving me what I'm wanting:

proc sql;
create table NOTES_KEYWORD as
select table_cases.item_identifier
,case when upcase(table_notes.note) like '%ZELLE%' then 'ZELLE'
when upcase(table_notes.note) like '%CASHEDGE%' then 'CASHEDGE'
when upcase(table_notes.note) like '%MOBILE%' then 'MOBILE'
else 'OTHER'
end as NOTE2

from libone.table_cases
,libone.table_case_audits
,libone.table_audits
,libone.table_notes

where table_cases.item_internal_id = table_case_audits.item_internal_id
and table_case_audits.item_audit_internal_id = table_audits.item_audit_internal_id
and table_audits.item_audit_internal_id = table_notes.item_audit_internal_id
and table_cases.item_identifier like '20__C%'
and (table_cases.case_date >= '01jun2020'd
and table_cases.case_date < '01aug2020'd)
and (upcase(table_notes.note) like '%ZELLE%'
or upcase(table_notes.note) like '%CASHEDGE%'
or upcase(table_notes.note) like '%MOBILE%')

order by table_notes.create_date;

quit;