DATA Step, Macro, Functions and more

Like Operator

Accepted Solution Solved
Reply
Super Contributor
Posts: 673
Accepted Solution

Like Operator

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;


Accepted Solutions
Solution
‎09-06-2013 01:34 PM
Super User
Posts: 19,875

Re: Like Operator

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;

View solution in original post


All Replies
Super User
Posts: 19,875

Re: Like Operator

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?

Trusted Advisor
Posts: 1,137

Re: Like Operator

you can try sounds like operator

proc sql;

   create table want as

  select a.* from have where

    upcase(notes)=*"WORD";

quit;

thanks,

jagadish

Thanks,
Jag
Super Contributor
Posts: 673

Re: Like Operator

Posted in reply to Jagadishkatam

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.

Super Contributor
Posts: 673

Re: Like Operator

Posted in reply to Jagadishkatam

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.

Super User
Posts: 19,875

Re: Like Operator

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;

Super Contributor
Posts: 673

Re: Like Operator

I'm gettin this error after declaring array:

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

ERROR 391-185: Expecting a variable length specification

Solution
‎09-06-2013 01:34 PM
Super User
Posts: 19,875

Re: Like Operator

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;

Super Contributor
Posts: 339

Re: Like Operator

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.

Valued Guide
Posts: 2,177

Re: Like Operator


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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 701 views
  • 4 likes
  • 5 in conversation