Multiple sequences per group, only want to hold the highest sequence

[Franca Meij]

Hi,

I am working on a study to analyze acquisition deal premiums of serial acquirers. I am using Stata 17.

I am analyzing M&A activity between 2004 and 2021 in US market. The acquirers grouped by Acusip can acquire multiple targets (grouped by Tcusip) over the past years.
I define a serial acquirer when an acquirer has acquired 2 or more targets within a timeframe of 5 years. This you can see in the variable serialacquiror__count, I created this variable with rangestat, with the following code: rangestat (count) serialacquiror_, interval(DateAnnounced -1825 0) by(Acusip) // deal order with the 5-year timeframe

However, as you can see in my data a serial acquirer can have multiple deal sequences. I only want to hold the longest deal sequence and drop the other deals for the acquirers.
For instance, for Acusip "000957" I only want to hold the deal sequence with target cusips: "89298L", "19388W", "10538W" and "68305X". Because this deal sequence includes 4 deals and is the highest one for this Acusip.
Could anyone help me out?

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input int DateAnnounced str6(Acusip Tcusip) float(n total_acq serialacquiror_) double serialacquiror__count float max_acquisitions
20072 "000307" "4C8250"  1  4 1 1 4
20114 "000307" "5C7040"  2  4 1 2 4
20185 "000307" "7C1485"  3  4 1 3 4
20221 "000307" "7C7919"  4  4 1 4 4
22602 "000360" "5E8837"  1  1 . 0 0
17588 "000361" "00320W"  1  2 1 1 1
20426 "000361" "9E4796"  2  2 1 1 1
16209 "000400" "17271E"  1  2 1 1 2
16618 "000400" "33555V"  2  2 1 2 2
19457 "000704" "3A8516"  1  1 . 0 0
17472 "00080S" "14195R"  1  1 . 0 0
21349 "00081T" "4H9024"  1  1 . 0 0
16394 "000868" "78249Z"  1  3 1 1 2
20780 "000868" "0F5051"  2  3 1 1 2
21732 "000868" "355685"  3  3 1 2 2
17461 "000886" "50175R"  1  2 1 1 2
17482 "000886" "15618J"  2  2 1 2 2
17027 "00088U" "68286F"  1  1 . 0 0
21914 "00090Q" "9J7349"  1  2 1 1 2
22593 "00090Q" "6K9363"  2  2 1 2 2
16355 "000957" "89298L"  1  7 1 1 4
16427 "000957" "19388W"  2  7 1 2 4
16747 "000957" "10538W"  3  7 1 3 4
17447 "000957" "68305X"  4  7 1 4 4
18597 "000957" "53278Q"  5  7 1 2 4
19274 "000957" "02354K"  6  7 1 2 4
22517 "000957" "5C1770"  7  7 1 1 4
19355 "00101J" "1A9993"  1  1 . 0 0
19282 "00104Q" "93709H"  1  1 . 0 0
18107 "001055" "21019V"  1  2 1 1 1
21742 "001055" "5J1601"  2  2 1 1 1
16951 "001058" "74675K"  1  1 . 0 0
17009 "001069" "93144C"  1  1 . 0 0
16267 "001204" "629431"  1  2 1 1 1
18603 "001204" "654086"  2  2 1 1 1
16447 "00130H" "81215V"  1  2 1 1 1
18737 "00130H" "233293"  2  2 1 1 1
17742 "00141R" "08687Y"  1  1 . 0 0
18904 "001547" "81349A"  1  1 . 0 0
16937 "00163T" "90604F"  1  3 1 1 3
17106 "00163T" "90619V"  2  3 1 2 3
18653 "00163T" "743410"  3  3 1 3 3
19995 "00163U" "3C2161"  1  2 1 1 2
21531 "00163U" "9H2296"  2  2 1 2 2
21241 "00164V" "74965F"  1  1 . 0 0
20283 "00165C" "85534H"  1  2 1 1 2
20516 "00165C" "143436"  2  2 1 2 2
22201 "00165R" "6K8035"  1  1 . 0 0
22557 "00166B" "9L8848"  1  1 . 0 0
17951 "001712" "29076V"  1  1 . 0 0
16138 "001733" "59591N"  1  1 . 0 0
16714 "001744" "55542W"  1 10 1 1 6
17307 "001744" "78298M"  2 10 1 2 6
17577 "001744" "72781K"  3 10 1 3 6
20079 "001744" "5C0390"  4 10 1 1 6
20346 "001744" "0E3126"  5 10 1 2 6
20367 "001744" "0E8167"  6 10 1 3 6
20409 "001744" "1E5615"  7 10 1 4 6
21283 "001744" "3H5250"  8 10 1 6 6
21283 "001744" "3H5442"  9 10 1 6 6
22406 "001744" "5L5426" 10 10 1 3 6
22406 "00175J" "4L7760"  1  1 . 0 0
19236 "001765" "90341W"  1  1 . 0 0
22348 "00182C" "2L7905"  1  1 . 0 0
21468 "00183L" "7H5214"  1  1 . 0 0
18284 "00184X" "86412Q"  1  5 1 1 5
18665 "00184X" "44432Z"  2  5 1 2 5
19577 "00184X" "01240C"  3  5 1 3 5
19746 "00184X" "8A5888"  4  5 1 4 5
19849 "00184X" "0C4707"  5  5 1 5 5
16090 "00186G" "83147K"  1  1 . 0 0
18283 "00187E" "49750T"  1  4 1 1 4
18637 "00187E" "81441V"  2  4 1 2 4
18714 "00187E" "847615"  3  4 1 3 4
19075 "00187E" "12242F"  4  4 1 4 4
17192 "001930" "67206P"  1  4 1 1 3
19222 "001930" "76994Z"  2  4 1 1 3
20205 "001930" "7C4980"  3  4 1 2 3
20282 "001930" "9C1829"  4  4 1 3 3
16271 "001963" "54040Y"  1  2 1 1 2
16349 "001963" "09119L"  2  2 1 2 2
16912 "00203J" "48205X"  1  1 . 0 0
16865 "00206R" "079860"  1 10 1 1 5
17346 "00206R" "256069"  2 10 1 2 5
17440 "00206R" "46151K"  3 10 1 3 5
17842 "00206R" "946757"  4 10 1 4 5
17843 "00206R" "15133V"  5 10 1 5 5
19207 "00206R" "65337Y"  6 10 1 4 5
19551 "00206R" "521863"  7 10 1 4 5
19861 "00206R" "25490A"  8 10 1 3 5
20749 "00206R" "887317"  9 10 1 4 5
20850 "00206R" "31567R" 10 10 1 5 5
17538 "00207R" "52749C"  1  1 . 0 0
16824 "002083" "88577M"  1  1 . 0 0
18414 "00208C" "67313X"  1  1 . 0 0
16741 "00208J" "09125Y"  1  1 . 0 0
17391 "002097" "83042X"  1  1 . 0 0
17405 "00211E" "71599J"  1  2 1 1 2
17454 "00211E" "66930A"  2  2 1 2 2
20661 "002121" "7E6150"  1  1 . 0 0
end
format %td DateAnnounced

Best,
Franca

[Clyde Schechter]

The question is ill-posed. What do you want to do if there are two or more deal sequences tied for the highest number of deals?

[Franca Meij]

What do you want to do if there are two or more deal sequences tied for the highest number of deals? If that would be the case, I would like to keep the most recent deal sequence.

[Clyde Schechter]

//  VERIFY ASSUMPTIONS NECESSARY FOR CODE TO BE CORRECT
by Acusip (n), sort: assert _n == n
by Acusip (n): assert DateAnnounced <= DateAnnounced[_n+1]

//  IDENTIFY END OF MOST RECENT LONGEST ACQUISITION SERIES
by Acusip (serialacquiror__count DateAnnounced), sort: ///
    gen upper = n[_N]

//  IDENTIFY BEGINNING OF MOST RECENT LONGEST ACQUISITION SERIES
by Acusip (serialacquiror__count DateAnnounced): ///
    gen lower = upper - max_acquisitions[_N] + 1

//  KEEP MOST RECENT LONGEST ACQUISITION SERIES
by Acusip (n), sort: keep if inrange(n, lower, upper)

[Franca Meij]

Hi Clyde,

Thank you very much! The code works perfectly.

Best,
Franca