topic Re: Names matching in SAS Programming

Names matching

EJAA — Wed, 26 Jul 2017 05:02:24 GMT

Hi All

I have to two datasets with one dateset having about 4000 company names and the other with about 3200 companies. I want to merge the two data using similarity in company names. I used the codes below and using the gedscore am only getting few firms with similarilty in names which shouldnt be the case.The gedscore is extremely high for most names matched. Approximately i should get a little over 1000 firms with similarity in names following prior studies after the merge. Please what am i not doing right from my codes hence not getting the right names merge?Is there anything am missing out with my codes.NB:new user. Thanks in advance. Rgds EJAA.

%let maxscore=999;

data box.namesmerge;
set box.cus(rename=(comp=bdx)) end=eof1 nobs=nobs1;
do i = 1 to nobs1;
set box.com(rename=(conm=comp)) point=i;
gedscore=compged(bdx,comp,&maxscore,'i' );
if _n_ < i then do;
if gedscore < &maxscore then output box.namesmerge;
end;
end;
keep bdx comp gedscore tic;
run;

Re: Names matching

ChrisNZ — Wed, 26 Jul 2017 06:24:30 GMT

proc sql is much more efficient for fuzzy matches.

Try this to study the results:

  
proc sql;
  select cus.COMP 
        ,com.CONM 
        ,comped(cus.COMP,com.CONM) as DISTANCE
  from BOX.CUS
      ,BOX.COM
  where compged(cus.COMP,com.CONM,&maxscore.,'il') < &maxscore.;
run;

Without access to your data, it is difficult to know why your matches are not what you expect.

Re: Names matching

ballardw — Wed, 26 Jul 2017 14:37:55 GMT

@EJAA wrote:

Hi All

I have to two datasets with one dateset having about 4000 company names and the other with about 3200 companies. I want to merge the two data using similarity in company names. I used the codes below and using the gedscore am only getting few firms with similarilty in names which shouldnt be the case.The gedscore is extremely high for most names matched. Approximately i should get a little over 1000 firms with similarity in names following prior studies after the merge. Please what am i not doing right from my codes hence not getting the right names merge?Is there anything am missing out with my codes.NB:new user. Thanks in advance. Rgds EJAA.

Having done similar projects the problem is likely to be at least 50% data driven. People cam't spell (intentional example).

So you end needing a process that can match ABC Company to Abc co.

My generic process in abscence of more dedicated text processing software is to:

1) create new variables with some things standardized, only single spaces, all upper case, remove punctuation (ABC COMPANY not ABC, CO.), standardize frequent special characters such as & in Simon & Sons with 'and', expand common abbreviates such as Co. , Inc. Ltd. (list varies with project type).

Then start match on those somewhat standardized variables.

One project I worked with after providing the data entry staff with a list of expected companies and the name to entered had 18 'spellings' for what should have been IBM (3 simple upper case letters). Most entertaining: I>B>M>, most puzzling were those that had to spell out International Business Machines.