how to use firm new names to replace old names in two different dataset?

Fred Lee

Join Date: Nov 2017
Posts: 454

how to use firm new names to replace old names in two different dataset?

22 Dec 2020, 09:18

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str65 firmNameNow str60 firmNameUsedAdjusted
"中创政通科技（北京）有限公司"       "中创政通（北京）科技服务有限公司"
"中声科技（北京）有限公司"             "北京中声能源科技有限公司"            
"中安锐达（北京）电子科技有限公司" "北京中宏远达科技发展有限公司"      
"中环曼普科技（南京）有限公司"       "中环曼普科技（北京）有限公司"      
"中环曼普科技（南京）有限公司"       "睿诚恒基（北京）科技有限公司"      
"中电通途（北京）科技有限公司"       "北京通途永久科技有限公司"            
"中科三清科技有限公司"                   "中科三清环保科技（北京）有限公司"
"中科正奇（北京）科技有限公司"       "中科正奇科技（北京）有限公司"      
"中经智业（北京）信息科技有限公司" "中经智业（北京）文化传媒有限公司"
"中能东道集团有限公司"                   "中能东道（北京）科技有限公司"      
end
save data1,replace

* Example generated by -dataex-. To install: ssc install dataex
clear
input str96 firmName
"一拍优聘（北京）科技有限公司"                  
"万维云网（北京）数据科技有限公司"            
"三个爸爸家庭智能环境科技（北京）有限公司"
"三角兽（北京）科技有限公司"                     
"世优（北京）科技有限公司"                        
"中以环能（北京）科技有限公司"                  
"中佳智通（北京）科技有限公司"                  
"中元众诚（北京）科技有限公司"                  
"中关村国际医药检验认证科技有限公司"         
"中创三优（北京）科技有限公司"                  
"中创政通科技（北京）有限公司"                  
"中卫祥光（北京）科技有限公司"                  
"中声科技（北京）有限公司"                        
"中天朗洁(北京)环保科技有限公司"                
"中广高科（北京）信息技术有限公司"            
"中悦民安（北京）科技发展有限公司"            
"中新城镇化（北京）科技有限责任公司"         
"中新网桥科技（北京）有限公司"                  
"中智诚征信有限公司"                                 
"中泰德信（北京）档案管理有限公司"            
"中物动产信息服务股份有限公司"                  
"中玉金标记（北京）生物技术股份有限公司"   
"中环曼普科技(北京)有限公司"                      
"中电新元（北京）电力科技有限公司"            
"中科三清科技有限公司"                              
"中科创想（北京）软件有限公司"                  
"中科正奇（北京）科技有限公司"                  
"中科清能燃气技术（北京）有限公司"            
"中科直线（北京）科技传播有限责任公司"      
"中科芯电半导体科技（北京）有限公司"         
"中科视拓（北京）科技有限公司"                  
"中科诺信（北京）高新技术有限公司"            
"中科金审（北京）科技有限公司"                  
"中经智业（北京）信息科技有限公司"            
"中联达通广（北京）新能源科技有限公司"      
"中能东道集团有限公司"                              
"中能瑞通（北京）科技有限公司"                  
"中航迈特粉冶科技（北京）有限公司"            
"中航鹰航空技术（北京）有限公司"               
"中证技术股份有限公司"                              
"中资国际新能源储能动力科技有限公司"         
"中金鼎晟（北京）电子商务有限公司"            
"中青高科（北京）科技发展有限公司"            
"中驭（北京）信息工程有限公司"                  
"乐天时代（北京）科技有限公司"                  
"乐居乐筑（北京）建筑材料有限公司"            
"乐码（北京）科技有限公司"                        
"九章今创（北京）咨询有限公司"                  
"二十七度（北京）网络科技有限公司"            
"云上天使（北京）信息科技有限公司"            
end
save data2,replace

how to update the old firm names in data2 uisng the new firm names in data1? you can match the old names based on firmName in data2 and firmNameUsedAdjusted in data1.
I was wodering can Mata make this purpose come true? or Frame?

Last edited by Fred Lee; 22 Dec 2020, 09:20.

Tags: None

William Lisowski

Join Date: Dec 2014
Posts: 10150

22 Dec 2020, 10:34

This seems to do what you want. Mata or Frames are not needed - just a basic knowledge of the merge command.

Code:

. use data2

. rename firmName firmNameNow

. merge 1:m firmNameNow using data1, keep(master matched)

    Result                           # of obs.
    -----------------------------------------
    not matched                            44
        from master                        44  (_merge==1)
        from using                          0  (_merge==2)

    matched                                 6  (_merge==3)
    -----------------------------------------

. generate firmNameUpdated = firmNameNow

. replace firmNameUpdated = firmNameUsedAdjusted if _merge==3
(6 real changes made)

. drop _merge firmNameUsedAdjusted

.

Comment

Fred Lee

Join Date: Nov 2017
Posts: 454

22 Dec 2020, 10:40

Originally posted by William Lisowski View Post

This seems to do what you want. Mata or Frames are not needed - just a basic knowledge of the merge command.

Code:

. use data2

. rename firmName firmNameNow

. merge 1:m firmNameNow using data1, keep(master matched)

Result # of obs.
-----------------------------------------
not matched 44
from master 44 (_merge==1)
from using 0 (_merge==2)

matched 6 (_merge==3)
-----------------------------------------

. generate firmNameUpdated = firmNameNow

. replace firmNameUpdated = firmNameUsedAdjusted if _merge==3
(6 real changes made)

. drop _merge firmNameUsedAdjusted

.

Thanks William! Actually, I am also wondering how to use mata or frame to fulfill this. I want to learn more about mata or frame.

Comment

William Lisowski

Join Date: Dec 2014
Posts: 10150

22 Dec 2020, 12:56

Before I address the question in post #3 I am going to correct the answer in post #2.

Where I had

Code:

merge 1:m ...

I should have had

Code:

merge m:1 ...

Making that correction exposes the fact that your example data has the same firmNameNow in both observations 4 and 5 of data1, with different values of firmNameUsedAdjusted. The following example starts by removing the ambiguous observations from data1 and then runs the code from post #2 with the merge command corrected.

Code:

. use data1, clear

. duplicates tag firmNameNow, generate(bad)

Duplicates in terms of firmNameNow

. list if bad

     +-------------------------------------------------------------------+
     |                  firmNameNow           firmNameUsedAdjusted   bad |
     |-------------------------------------------------------------------|
  4. | 中环曼普科技（南京）有限公司   中环曼普科技（北京）有限公司     1 |
  5. | 中环曼普科技（南京）有限公司   睿诚恒基（北京）科技有限公司     1 |
     +-------------------------------------------------------------------+

. drop if bad
(2 observations deleted)

. save data1, replace
file data1.dta saved

. use data2, clear

. rename firmName firmNameNow

. merge m:1 firmNameNow using data1, keep(master matched)

    Result                           # of obs.
    -----------------------------------------
    not matched                            44
        from master                        44  (_merge==1)
        from using                          0  (_merge==2)

    matched                                 6  (_merge==3)
    -----------------------------------------

. generate firmNameUpdated = firmNameNow

. replace firmNameUpdated = firmNameUsedAdjusted if _merge==3
(6 real changes made)

. drop _merge firmNameUsedAdjusted

.

Comment

William Lisowski

Join Date: Dec 2014
Posts: 10150

22 Dec 2020, 13:02

Here is a solution using the frames command. It also deletes the duplicated observations in data1.

Code:

. use data2, clear

. frame create newnames

. frame change newnames

. use data1, clear

. duplicates tag firmNameNow, generate(bad)

Duplicates in terms of firmNameNow

. list if bad

     +-------------------------------------------------------------------+
     |                  firmNameNow           firmNameUsedAdjusted   bad |
     |-------------------------------------------------------------------|
  4. | 中环曼普科技（南京）有限公司   中环曼普科技（北京）有限公司     1 |
  5. | 中环曼普科技（南京）有限公司   睿诚恒基（北京）科技有限公司     1 |
     +-------------------------------------------------------------------+

. drop if bad
(2 observations deleted)

. frame change default

. frlink m:1 firmName, frame(newnames firmNameNow)
  (44 observations in frame default unmatched)

. frget firmNameUsedAdjusted, from(newnames)
(44 missing values generated)
  (1 variable copied from linked frame)

. replace firmName = firmNameUsedAdjusted if !missing(firmNameUsedAdjusted)
(6 real changes made)

. drop firmNameUsedAdjusted

. frame drop newnames

.

Comment

Fred Lee

Join Date: Nov 2017

Posts: 454
#6

22 Dec 2020, 19:16

Thanks William!
Comment

Announcement

how to use firm new names to replace old names in two different dataset?

Comment

Comment

Comment

Comment

Comment