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
You could load the words into a temporary array and then loop through them.
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 ?
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.
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?
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?
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;
You can use SQL's cartesian product + findw()
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.