Can we still Merge the unmatched observations from a Merge using different identifiers ?

[Mike Kraft]

Dear All
I have datasets that look like:

Call this:
ibes.dta

CUSIP	TICKER	yr	eps
.	ASTT	1980	0.2
.	ASTT	1981	0.3
00000011	AGO	1980	0.4
00000011	AGO	1981	0.2
30405567	PPT	2000	0.4
30406678	FFB	1980	1.4
73636392	KNN	1980	1.1
....	KNN	1981	2.1
45999332	.	1980	0.81

Another data set, call it:
finance

CUSIP	TICKER	yr	cashflow
	ASTT	1980	1000000
	ASTT	1981	8000000
00000011	AGO	1980	9000000
45999332			8333387

The trusted link between the data can be obtained by CUSIP and yr . However, after I merge the data, I will end up with some observations that are not merged because they do not have available cusip in both data sets. For these observations, I want to do another trial using TICKER yr.

I did the following:
use finance.dta,clear
duplicates drop cusip yr
sort cusip yr
save new_finance,replace

use ibes.dta
duplicates drop cusip yr,force
sort cusip yr
compress
save new_ibes,replace

merge 1:1 cusip yr using new_finance.dta

Output:
Result # of obs.
-----------------------------------------
not matched 104,145
from master 83,118 (_merge==1)
from using 21,027 (_merge==2)

matched 51,420 (_merge==3)


***********Now how can I try with a second merge using TICKER yr ??

Thanks

[Devra Golbe]

Mike,

You have to be careful merging with ticker symbols, because they are not unique. For instance, ADP has represented at different times Allied Products Corp and Automatic Data Processing. (And Automatic Data Processing has had more than one ticker over its life.) That said, you can save the observations which did not match by cusip and try a second merge by ticker. Nick Cox's -savesome- is helpful here. The code below is untested.

ssc install savesome
savesome if _merge==1 using masternomatch
savesome if _merge==2 using usingnomatch

clear
use masternomatch
merge 1:1 ticker yr using usingnomatch

Devra

[Mike Kraft]

Hi
Thanks for your suggestion. I used:

***after first merge:

savesome if _merge==1 using masternomatch
savesome if _merge==2 using usingnomatch

use usingnomatch,clear
duplicates drop TICKER yr,force
sort TICKER yr
drop _merge
save new_usingnomatch,replace


use masternomatch,clear
duplicates drop TICKER yr,force
sort TICKER yr
drop _merge
save new_master,replace


merge 1:1 TICKER yr using new_usingnomatch




************************************************** *************Unfortunately, though it shows a _merge==3 of about 600 more obs, but browsing the data , it does not seem to produce any match !
Any suggestion ?
If you have worked with IBES before, I really do not know why the merge with IBES unadjusted results in a big loss of data (when merged with Compustat/CRSP CCM data base) !

[Jimmy Yang]

You can simply use merge with the option of

update

[Jimmy Yang]

the save way to deal with duplicate is:
do not drop it
use

reshape wide

to index duplicate
and use

merge 1:m or merge m:1

[Jimmy Yang]

And when you

merge, update

be careful with

update replace

I would only recommend use

missing update

If you still have any doubt, maybe you can send me sample dataset.
Just make sure beforehand, how to deal with

update replace