How to fuzzy match one dataset with IDs and Names to another dataset with only Names

[Jae Li]

Dear Statalists,

I have an inquiry about using -matchit- to fuzzy match. I have two datasets: dataset1 has complete company IDs and names, while dataset2 only has company names.


----------------------- dataset1 -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 ID str50 cnms
"008972" "RAYTHEON"              
"008972" "RAYTHEON"              
"008972" "RAYTHEON"              
"010983" "UTD TECHS"             
"010983" "UTD TECHS"             
"010983" "UTD TECHS"             
"010983" "UTD TECHS"             
"002322" "BORG-WARNER"           
"002322" "BORG-WARNER"           
"003022" "CHRYSLER"              
"003022" "CHRYSLER"              
"003022" "CHRYSLER"              
"003022" "CHRYSLER"              
"003022" "CHRYSLER"              
"003022" "CHRYSLER"              
"003022" "CHRYSLER"              
"003734" "DANA CORP"             
"003734" "DANA CORP"             
"004321" "EMERSON EL"            
"004321" "EMERSON EL"            
"004321" "EMERSON EL"            
"004839" "FORD MTR"              
"004839" "FORD MTR"              
"004839" "FORD MTR"              
"004839" "FORD MTR"              
"004839" "FORD MTR"              
"004839" "FORD MTR"              
"004839" "FORD MTR"              
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"006674" "LEROY INDS"            
"004839" "FORD MOTOR"            
"004839" "FORD MOTOR"            
"004839" "FORD MOTOR"            
"004839" "FORD MOTOR"            
"004839" "FORD MOTOR"            
"004839" "FORD MOTOR"            
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"005073" "GEN MTR"               
"001581" "AM TEL & TEL"          
"001581" "AM TEL & TEL"          
"001581" "AM TEL & TEL"          
"001581" "AM TEL &TEL"           
"001581" "AM TEL &TEL"           
"001581" "AM TEL &TEL"           
"009899" "AT&T"                  
"009899" "AT&T"                  
"009899" "AT&T"                  
"009899" "AT&T"                  
"001581" "AT&T CORP"             
"004961" "GTE CORP"              
"006066" "INTL BUS MA"           
"006066" "INTL BUS MA"           
"006066" "INTL BUS MA"           
"006066" "INTL BUS MA"           
"006066" "INTL BUS MA"           
"006066" "INTL BUS MA"           
"006066" "INTL BUS MA"           
"006066" "INTL BUS MA"           
"006066" "INTL BUS MA"           
"006864" "MCI COMMUN"            
"006864" "MCI COMMUN"            
"002136" "VERIZON COMMUNICATIONS"
"002136" "VERIZON COMMUNICATIONS"
"002136" "VERIZON COMMUNICATIONS"
"002136" "VERIZON COMMUNICATIONS"
"002136" "VERIZON COMMUNICATIONS"
"002136" "VERIZON COMMUNICATIONS"
"002136" "VERIZON COMMUNICATIONS"
"002136" "VERIZON COMMUNICATIONS"
"025880" "Patterson Dental Co"   
"061494" "Schein Henry Inc"      
"061494" "Schein Henry Inc"      
"061494" "Schein Henry Inc"      
"061494" "Schein Henry Inc"      
"011638" "XONICS INC"            
"011638" "XONICS INC"            
"001581" "AM TEL & TEL"          
"001581" "AM TEL & TEL"          
"001581" "AM TEL & TEL"          
"001581" "AM TEL & TEL"          
"002138" "BELL LABS"             
"002138" "BELL LABS"             
"002138" "BELL LABS"             
"001553" "AM SVG-NY"             
"010199" "SUPERMRKT"             
"005055" "GEN FOODS"             
"005055" "GEN FOODS"             
end

------------------ copy up to and including the previous line ------------------

Listed 100 out of 122563 observations
Use the count() option to list more



----------------------- dataset2 -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str50 cnms
"RAYTHEON"              
"DAGE CORP"             
"UTD TECHS"             
"UTD TECHS"             
"DAGE CORP"             
"RAYTHEON"              
"DECCA ELECTR"          
"DECCA ELECTR"          
"DECCA ELECTR"          
"BRIDGE ENGR"           
"DINAS ENGR"            
"EMERSON EL"            
"GEN MTR"               
"NEW PROC GEAR"         
"GEN MTR"               
"EMERSON EL"            
"DANA CORP"             
"DANA CORP"             
"GEN MTR"               
"GKN"                   
"NEW PROC GEAR"         
"CHRYSLER"              
"GEN MTR"               
"LEROY INDS"            
"GKN"                   
"CHRYSLER"              
"GKN"                   
"GEN MTR"               
"GKN"                   
"CHRYSLER"              
"BORG-WARNER"           
"GEN MTR"               
"BORG-WARNER"           
"FORD MTR"              
"CHRYSLER"              
"FORD MTR"              
"CHRYSLER"              
"FORD MTR"              
"CHRYSLER"              
"GEN MTR"               
"FORD MTR"              
"CHRYSLER"              
"GEN MTR"               
"GKN AUTO INC"          
"NEW VENTURE"           
"FORD MTR"              
"GEN MTR"               
"FORD MTR"              
"GEN MTR"               
"NEW VENTURE"           
"GKN AUTO INC"          
"GKN AUTO INC"          
"NEW VENTURE"           
"GEN MTR"               
"FORD MTR"              
"FORD MOTOR"            
"GEN MTR"               
"2 CUSTOMERS"           
"GEN MTR"               
"FORD MOTOR"            
"FORD MOTOR"            
"GEN MTR"               
"FORD MOTOR"            
"GEN MTR"               
"FORD MOTOR"            
"FORD MOTOR"            
"AM TEL & TEL"          
"INTL BUS MA"           
"MCI COMMUN"            
"INTL BUS MA"           
"AM TEL &TEL"           
"AM TEL &TEL"           
"INTL BUS MA"           
"MCI COMMUN"            
"AM TEL &TEL"           
"INTL BUS MA"           
"GTE CORP"              
"WESTN UN TE"           
"INTL BUS MA"           
"AT&T CORP"             
"INTL BUS MA"           
"INTL BUS MA"           
"VERIZON COMMUNICATIONS"
"6 Customers"           
"4 Customers"           
"3 Customers"           
"VERIZON COMMUNICATIONS"
"6 Customers"           
"VERIZON COMMUNICATIONS"
"3 Customers"           
"6 Customers"           
"6 Customers"           
"VERIZON COMMUNICATIONS"
"3 Customers"           
"AT&T"                  
"8 Customers"           
"VERIZON COMMUNICATIONS"
"AT&T"                  
"8 Customers"           
"VERIZON COMMUNICATIONS"
end

------------------ copy up to and including the previous line ------------------

Listed 100 out of 158468 observations
Use the count() option to list more

Their company names can be slightly different below. How can I fuzzy match them together so I can obtain complete company IDs for two datasets? Thank you in advance!

RAYTHEON CO    RAYTHEON

UNITED TECHNOLOGIES CORP    UTD TECHS

BURNS INTL SERVICES CORP    BORG-WARNER

CHRYSLER CORP    CHRYSLER

DANA INC    DANA CORP

EMERSON ELECTRIC CO    EMERSON EL

[Jorge Eduardo Perez Perez]

Take a look at the -matchit- command:

PowerPoint Presentation (stata.com)

[Jae Li]

@Jorge Eduardo Perez Perez Hi Jorge, thank you for your reply! I've been using -matchit- command and find @Andrew Musau's following post is very useful: https://www.statalist.org/forums/for...08#post1664108. My case is a little different as I want to assign each company ID to all possible company names after -matchit- like below. However, after trying that post's code, I'm getting more confused. Can anyone share some thoughts about how to do this?

cnms ID
3C COMM INTL 1800
3COM 1800
3COM CORP 1800
3COM Corp 1800
3Com Corp 1800
BAXTER HEALTH 2900
BAXTER HEALTHCARE 2900
BAXTER HLTH 2900
BAXTER HLTHCARE 2900
BAXTER HLTHCR 2900
BAXTER INTERNATIONAL INC 2951
BAXTER INTL 2951
BAXTER LTD 2951

Also, there are many company names like below. Do you know how to remove them?

cnms2
38 Customers
4 companies
4 customers
39 Customers
43 CUSTOMERS
3900 Customers
392 Customers

Many thanks to you!