topic IF THEN ELSE using wildcards in an open text variable in SAS Programming

IF THEN ELSE using wildcards in an open text variable

hnb_matt_d — Wed, 16 Sep 2020 17:01:28 GMT

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.

Re: IF THEN ELSE using wildcards in an open text variable

Kurt_Bremser — Wed, 16 Sep 2020 17:03:43 GMT

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

Re: IF THEN ELSE using wildcards in an open text variable

PaigeMiller — Wed, 16 Sep 2020 17:13:02 GMT

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

Re: IF THEN ELSE using wildcards in an open text variable

pink_poodle — Wed, 16 Sep 2020 17:14:30 GMT

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.

Re: IF THEN ELSE using wildcards in an open text variable

hnb_matt_d — Thu, 17 Sep 2020 15:37:39 GMT

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

Re: IF THEN ELSE using wildcards in an open text variable

hnb_matt_d — Thu, 17 Sep 2020 20:01:17 GMT

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

order by table_notes.create_date;

quit;