topic Re: Like Operator in SAS Programming

Like Operator

SASPhile — Fri, 06 Sep 2013 16:14:11 GMT

I'm trying to find some words in a variable called notes. There are more than 100 words that I need to look for.Is there a better way to subsitue the following, instead of write 100 like statements?

proc sql;

create table want as

select a.* from have where

upcase(notes) like '%WORD1%' or upcase(notes) like '%WORD2%' ......

quit;

Re: Like Operator

Reeza — Fri, 06 Sep 2013 16:18:44 GMT

1. Can you put the words in a table and then merge with a like merge?

2. Use a datastep and then put the words in an array and use the whichc function?

Re: Like Operator

Jagadishkatam — Fri, 06 Sep 2013 16:19:44 GMT

you can try sounds like operator

proc sql;

create table want as

select a.* from have where

upcase(notes)=*"WORD";

quit;

thanks,

jagadish

Re: Like Operator

SASPhile — Fri, 06 Sep 2013 16:29:33 GMT

When I say WORD in my example I'm not looking exactly for WORD. Word1 can be a Lion ,Word2 can be water word3 can be Mars etc etc.

Re: Like Operator

Vince28_Statcan — Fri, 06 Sep 2013 16:30:14 GMT

Lookup this fairly recent thread with similar topic:

https://communities.sas.com/thread/46818

You would really need to provide additionnal details on why exactly you need to use the like operator and what the 100 words you are searching for resemble.

Many like-operator jobs can be solved fairly easily with PERL (or PRX functions/routines in SAS) but the problem described reads as though you shouldn't use the like operator at all can could use a simple syntax.

you could also use an array _temporary_ to act as a 100 word dictionary that you loop through for syntax.

There are also other solutions than like-operator and PERL such as the use of string distance functions such as complev and compged (levenstein and generalized edit distance)

Worst case scenario, you can always use macro to write your statement with like-operator. I doubt the like operator is very efficient but if your job is small and you are satisfied with the like operator results, that can be a fast and easy solution to writing the programming statement.

Re: Like Operator

SASPhile — Fri, 06 Sep 2013 16:31:17 GMT

Notes is a varible that contains complaints from customers. I identified a 100 different words in the complaints column and need to look for them and filter those records.

Re: Like Operator

Reeza — Fri, 06 Sep 2013 16:41:26 GMT

Do you want to flag which word matches? what about multiple matches?

I think the array solution above is the simplest one to implement in a datastep from a programming standpoint.

data want;

set have;

array words_to_find(100) $ {"Word1", "word2", "word3"....};

array flag_word(100) flag1-flag100;

do i=1 to 100;

if find(string, words_to_find(i))>0 then flag_word(i)=1;

else flag_word(i)=0;

end;

if sum(of flag_word(*))>0 then output;

run;

Re: Like Operator

SASPhile — Fri, 06 Sep 2013 17:13:26 GMT

I'm gettin this error after declaring array:

array words_to_find(100) $ {"Word1", "word2", "word3"....};

ERROR 391-185: Expecting a variable length specification

Re: Like Operator

Reeza — Fri, 06 Sep 2013 17:34:21 GMT

Wrong Brackets apparently ({ to ()

data want;

set sashelp.class;

array names_to_find(2) $ ("Alfred", "Jane");

array flag(2) flag1-flag2;

do i = 1 to dim(names_to_find);

if name=names_to_find(i) then flag(i)=1;

else flag(i)=0;

end;

run;

Re: Like Operator

Peter_C — Sat, 07 Sep 2013 10:38:06 GMT

SASPhile wrote:

I'm trying to find some words in a variable called notes. There are more than 100 words that I need to look for.Is there a better way to subsitue the following, instead of write 100 like statements?

proc sql;

create table want as

select a.* from have where

upcase(notes) like '%WORD1%' or upcase(notes) like '%WORD2%' ......

quit;

rather than approach a SQL question with a data step solution, consider the benefits of placing those 100+ words to be searched for, into a table of their own. Then the information handling problem moves from the syntax layer into the data layer, and PROC SQL can provide its solution with a simple join using the FIND() function, like

select * from have

inner join word_list_table

on FIND( have.notes, word_list_table.word, 'IT' ) >0

In that IT parameter the 'I' treats upper and lower case the same. and the 'T' removes trailing blanks from the WORD variable before making that search.

Read the documentation on the FIND() function at http://support.sas.com/documentation/cdl/en/lefunctionsref/64814/HTML/default/viewer.htm#p00ab6ey29t2i8n1ihel88tqtga9.htm