Trying to merge data with wrong dates

[Juan Gonzalex]

Hi, I'm trying to merge two datasets: one with M&A deals and one with returns. When merging 1:1, most of the observations are merged correctly, but some are not merged due to wrong dates given by the data provider:

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 acquirerticker str8 targetticker float(ann_date car3)
"UTX" "COL" 21066          .
"UTX" "RTN" 21709          .
"UTX" ""    21067 -.08387527
"UTX" ""    21710  -.0820588
end
format %td ann_date

These dates are often 1 or 2 days off (sometimes more). Is there a way to paste the car3 (returns) to the closest observation with the same acquirer ticker for these wrong observations (_merge == 2)?

Thanks.

[Clyde Schechter]

Without example data from the other data set, I can only give you a general outline of the code. Go back to before you did the -merge-, as this code will cover that as well. It will look something like this:

joinby acquirerticker using returns_data_set, unmatched(master) update
gen delta = abs(returns_date - ann_date)
by acquirerticker ann_date (delta), sort: keep if _n == 1

Absent example data from the returns data, this code is untested. You will need to at least change some variable names to match what is actually there.

In addition, in the event that there are multiple observations in the returns data set that are tied for closest to ann_date (e.g. one of them is 2 days before and the other is 2 days after), this tie is broken at random and irreproducibly.

[Juan Gonzalex]

It worked perfectly, thank you very much!