SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Identifying patterns in a character string

Reply
New Contributor
Posts: 3

Identifying patterns in a character string

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

Super User
Posts: 17,865

Re: Identifying patterns in a character string

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

Super User
Posts: 10,516

Re: Identifying patterns in a character string

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 ?

New Contributor
Posts: 3

Re: Identifying patterns in a character string

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.

Respected Advisor
Posts: 3,895

Re: Identifying patterns in a character string

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?

New Contributor
Posts: 3

Re: Identifying patterns in a character string

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?

Respected Advisor
Posts: 3,895

Re: Identifying patterns in a character string

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=_Smiley Happy;

  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;

Super User
Posts: 9,682

Re: Identifying patterns in a character string

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

Ask a Question
Discussion stats
  • 7 replies
  • 419 views
  • 1 like
  • 5 in conversation