Identifying patterns in a character string

[harvey7415]

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

[Reeza]

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

[ballardw]

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 ?

[harvey7415]

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.

[Patrick]

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?

[harvey7415]

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?

[Patrick]

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;

[Ksharp]

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