Complex merging (an alternative m:m merge with criteria)

[Ben James]

A private equity fund makes investments in companies.

I want to know which of these companies have had an acquisition, and in this way gauge the performance of a private equity fund. (the idea being that the private equity fund invests in a company and sells the company in an acquisition somewhere later in time for more money). As some investments can fail, not all companies invested have an acquisition.

The investments are in one file, the acquisitions in another. The problem however, is that companies can be acquired multiple times over their lifetime. In case of multiple acquisitions, the correct acquisition is the one after the investment date and closest in time to that investment date (“idate”). For example if the private equity fund invest in a company in 2000 and this company has acquisition dates (“edate”) of 1998, 2004 and 2005, the correct edate is 2004. If there are no edates after the idate (see example 2 and 3) I want to have the reverse, namely the closest edate in the past.

Other examples:

Example 1: the fund makes an investment in company in 2000, this company has one acquisition in 2001. The exit date is 2001
Example 2: the fund makes an investment in company in 2000, this company has an acquisition in 1999. Even though negative, I still want to report it in my output. The exit date is 1999.
Example 3: the fund makes an investment in company in 2000, the company has exit dates of 1976 and 1988. Even though negative, I still want to report it in my output. The exit date is 1988.
Example 4: the fund makes an investment in company in 2000, the company has exit dates of 1976, 1999 and 2010. I prefer any positive exit date to any negative exit date, so the exit date is 2010.

I thus have three files (see below):
1) Investment data inputs
2) Acquisition data inputs
3) My desired output from merging 1) with the closest future or past acquisition date in 2)

How would I go about this? Thank you in advance!



1) Investment INPUT

clear*
input str3(fund company) idate
f1 c1 1999
f1 c2 2011
f1 c3 2011
f1 c4 2003
f2 c1 2011
f2 c2 2003
f2 c2 2006
end

2) Acquisition INPUT

clear*
input str3(company) edate str3(extrainfo)
c1 2000 101
c2 2005 102
c3 2010 103
c1 2001 104
c1 2002 105
c2 2006 106
c4 2006 107
end

3) Desired OUTPUT

clear*
input str3(fund company) idate edate str3(extrainfo)
f1 c1 1999 2000 101
f1 c2 2011 2006 106
f1 c3 2011 2010 103
f1 c4 2003 2006 107
f2 c1 2011 2002 105
f2 c2 2003 2005 102
f2 c2 2006 2006 106
f2 c9 2000 . .
end

[Clyde Schechter]

I don't understand where the last observation in your desired OUTPUT is supposed to come from There is no mention of company c9 in either input file. Apart from that, I think the following does what you want:

// READ IN THE DATA AND SAVE IN SEPARATE FILES
clear*
input str3(fund company) idate
f1 c1 1999
f1 c2 2011
f1 c3 2011
f1 c4 2003
f2 c1 2011
f2 c2 2003
f2 c2 2006
end
tempfile investments
save `investments'

clear
input str3(company) edate str3(extrainfo)
c1 2000 101
c2 2005 102
c3 2010 103
c1 2001 104
c1 2002 105
c2 2006 106
c4 2006 107
end
tempfile acquisitions
save `acquisitions'

// READ THE INVESTMENT FILE
use `investments', clear

// COMBINE WITH ACQUISITIONS USING -joinby-, NOT -merge-
joinby company using `acquisitions'

// CALCULATE TIME DISTANCE BETWEEN edate AND idate
gen delta = abs(edate - idate)

// DISTINGUISH WHEN edate IS BEFORE FROM WHEN IT IS AFTER idate
gen byte pre = (edate < idate)

// FOR EACH FUND-COMPANY PAPER RETAIN THE OBSERVATION
// THAT HAS edate CLOSEST IN TIME TO idate AND FOLLOWS
// IT, OR, IF NO SUCH, CLOSEST IN TIME AND PRECEDES IT
by fund company (pre delta), sort: keep if _n == 1

list, noobs clean

I object strenuously, vociferously to the title of your post. This is NOT an m:m merge. It is -joinby-! It is quite pointedly -joinby-. Attempting to do this with m:m merge would yield nothing but garbage. In fact, it is only the rarest of situations in which an m:m merge ever yields anything but garbage.

By the way, thank you for using -dataex- to post the sample data.

[Ben James]

Sorting on pre and delta is a very elegant and simple solution!
I'm always surprised at what Stata is capable of which I do not know of. I did not know of ‘joinby’ before, thank you for learning me what is possible!


Ps. Company C9 was meant to be in the investment input, and also in the final output. After your help I took a look at joinby and saw that by default it drops all observations that do not appear in both datasets. I altered your code so it keeps investments that do not have an exit:
joinby company using `acquisitions' becomes
joinby company using `acquisitions', unmatched(master)

Thanks Clyde!