topic Re: Identifying patterns in a character string in SAS Data Management

Identifying patterns in a character string

harvey7415 — Fri, 21 Aug 2015 14:09:02 GMT

I have a variable with company names in it. I would like to identify all the observations which have some specific words in the company name. This list of words are in a separate file and are almost 30,000. Is there an efficient way to do this? Would appreciate any assistance. Thanks. Harvey

Re: Identifying patterns in a character string

Reeza — Fri, 21 Aug 2015 14:35:47 GMT

You could load the words into a temporary array and then loop through them.

Re: Identifying patterns in a character string

ballardw — Fri, 21 Aug 2015 16:13:44 GMT

Is specific case an issue: Example would word Ltd match in a name like Company LTD ?

What about substrings: word Engineer and company ABC Engineering

or component: word Engineering and company Engineer Contracting

Do your words contain punctuation such as Ltd. would the period have to match for Some Name, Ltd ?

Re: Identifying patterns in a character string

harvey7415 — Fri, 21 Aug 2015 17:46:58 GMT

I don't care about the specific case. If it is LtD or ltd, I would like these observations identified.

Some words in my list may have a punctuation mark like O'Reilly.

I am looking for exact word matches. So if the company name is Engineering and the word name is Engineer, I would like that observation to be flagged off.

But if word name is Engineering and company name has Engineer in it, then I don't want them to be lagged off.

Re: Identifying patterns in a character string

Patrick — Sat, 22 Aug 2015 01:54:48 GMT

Your question is something which comes up regularly in this forum in one variation or another. Did you already try a search using something like "find word in string" or something similar?

Re: Identifying patterns in a character string

harvey7415 — Sat, 22 Aug 2015 02:17:25 GMT

I have. I know there are plenty of solutions available if you need to identify one or two words in a string. That's easy. But how does one search 30,000 possible words that might appear in a character string? I am just looking for an efficient way of doing it?

Re: Identifying patterns in a character string

Ksharp — Sat, 22 Aug 2015 05:57:46 GMT

You can use SQL's cartesian product + findw()

Re: Identifying patterns in a character string

Patrick — Sat, 22 Aug 2015 23:04:28 GMT

You will have to loop over your string, pull out word for word and then compare it with a reference table. As for punctuation in strings and whether to treat them as word delimiters or as part of the string will depend on your reference table to compare with. Will something like "Ltd." also have a dot at the end? Or will you have all possible versions in this list?

Below a code sample which creates and uses a SAS format for the comparison.

data wordlist;

input word:$20.;

datalines;

Company

Ltd

;

run;

data cntlin_source;

retain fmtname 'MatchWords' type 'c';

set wordlist(rename=(word=start)) end=last;

start=upcase(start);

label='1';

output;

if last then

do;

hlo='O';

label='0';

output;

end;

run;

proc format cntlin=cntlin_source;

run;

data have;

infile datalines truncover;

input string $60.;

datalines;

this is an a sample string with no match

string with company ltd. in text

;

run;

data matching_words(drop=_:);

set have;

source_obs=_n_;

length word $40.;

_stop=countw(string,' .');

do _i=1 to _stop;

word=scan(string,_i,' .');

if put(upcase(word),$MatchWords.)='1' then output;

end;

run;