Creating a dummy variable

[Preetham Venkat]

Hello Forum,

I am trying to create a dummy variable ( multiple ) that takes the value of 1 if the firm has completed five or more acquisitions within three years of the first acquisition. I have tried the below code but it seems like I didn't do it correctly. I am sharing my data as well can you help me how to create this dummy variable? In the data, SDCdealno is unique for each deal and Acquiror Datastream is unique for each firm.

Thank You.

egen Firmid= group(AcquirorDatastream)

gen Year= year(DateAnnounced)
egen Yearid= group(Year)
sort Firmid DateAnnounced

gen long obs_no = _n
local three_years = 365.25*3
rangestat (count) obs_no, by(AcquirorDatastream) interval(DateAnnounced, 0, `three_years')
by AcquirorDatastream, sort: egen keeper = max(obs_no_count >= 5)

gen Multiple=( AcquirorDatastream[_n-3]== AcquirorDatastream ) & Year[_n-3]>Year-5

* Example generated by -dataex-. For more info, type help dataex
clear
input int DateAnnounced str6 AcquirorDatastream str10 SDCDealNo
17636 "130062" "1924997020"
18063 "130062" "2082259020"
19751 "130062" "2593089020"
20996 "130062" "3119302020"
17603 "130086" "1960259020"
18886 "130086" "2345790020"
18886 "130086" "2345788020"
21929 "130086" "3502739020"
20726 "130088" "3012582020"
17633 "130298" "1968943020"
19919 "130298" "2655953020"
20689 "130298" "3010025040"
21532 "130298" "3240426040"
18017 "130375" "2069880020"
20674 "130485" "2993116020"
20999 "130485" "3121259020"
20120 "130502" "2728784020"
20485 "130502" "2938996020"
21063 "130502" "3146004020"
21181 "130502" "3200683020"
21277 "130556" "3230754020"
18434 "130720" "2194164020"
18904 "130775" "2352405020"
20606 "130841" "2966732020"
21684 "130841" "3397826020"
19262 "130859" "2454300020"
20013 "130859" "2686750020"
20709 "130859" "3006379020"
21027 "130859" "3152961020"
21879 "130859" "3482995020"
18656 "131745" "2285871020"
19120 "131745" "2412064020"
20207 "131745" "2747970020"
20669 "131745" "2990661020"
21153 "131745" "3184217020"
21354 "131745" "3257258020"
21502 "131745" "3309280020"
22264 "131745" "3676141020"
20587 "131771" "2964624040"
21451 "131771" "3291789020"
18326 "131844" "2162135020"
18455 "131844" "2202088020"
19289 "131844" "2462221020"
19701 "131844" "2590117020"
19950 "131844" "2665213020"
20030 "131844" "2691896020"
21063 "131844" "3149391020"
20361 "131880" "2807508020"
21783 "131880" "3480757020"
17371 "132050" "1891110020"
18819 "132050" "2327919020"
19570 "132050" "2551509020"
19886 "132050" "2646129020"
21459 "132050" "3317334040"
21076 "132125" "3157036020"
21837 "132125" "3468244020"
21657 "132736" "3383166020"
20248 "132800" "2761411020"
20879 "132800" "3075445040"
21216 "132800" "3208848020"
19275 "133234" "2458094020"
19683 "133234" "2586586020"
19827 "133234" "2628559020"
20894 "133234" "3082008020"
21649 "133234" "3381936020"
20395 "133324" "2817066020"
20495 "133324" "2920211020"
20667 "133324" "2989654020"
20747 "133324" "3020251020"
20909 "133324" "3087786020"
21467 "133324" "3297366020"
17700 "133891" "1987730020"
18822 "133891" "2328909020"
19395 "133891" "2493821020"
18437 "134057" "2195660020"
19242 "134057" "2447925020"
19709 "134057" "2594208020"
21181 "134057" "3206932020"
21661 "134057" "3385269040"
21684 "134057" "3393935020"
17540 "134058" "1942387040"
17576 "134058" "1952903040"
17584 "134058" "1954744020"
17639 "134058" "1970197020"
17650 "134058" "1973089020"
17973 "134058" "2057835020"
21370 "134499" "3264203020"
21040 "134507" "3136606020"
21978 "13466Q" "3522125040"
19220 "13471D" "2442379020"
19747 "13471D" "2604312020"
19981 "13471D" "2674984040"
20172 "13471D" "2737295040"
20437 "13471D" "2847189020"
21034 "13471D" "3134516020"
21398 "13471D" "3273424020"
21697 "13471D" "3397174020"
21032 "13473U" "3645927020"
21759 "13473U" "3434769020"
22215 "13473U" "3652914020"
end
format %td DateAnnounced

[Clyde Schechter]

What your code does is set keeper == 1 for any firm that, any point, completes five or more acquisitions within a 3 year period. However you say you want this only if they complete five or more acquisitions within 3 years of the first one. That's a different calculation:

egen Firmid = group(AcquirorDatastream)
assert !missing(SDCDealNo)

by Firmid (DateAnnounced), sort: gen date_rank = sum(DateAnnounced != DateAnnounced[_n-1])
by Firmid (DateAnnounced): egen n_deals_in_3_years = ///
    count(cond(datediff_frac(DateAnnounced[1], DateAnnounced, "y") <= 3, _n, .))
by Firmid: gen byte keeper = n_deals_in_3_years >= 5

The above code assumes, and verifies, that SDCDealNo is never missing. It also assumes that the count of deals within three years includes the first deal itself. If you meant five deals in addition to the first deal, change >= 5 to >= 6 in the final line of the code.

By using datediff_frac(), first introduced in Stata 17, instead of subtracting dates and comparing to 365.25*3, we finesse the edge cases created by leap years.

[Preetham Venkat]

Thank You Clyde it worked perfectly. Continuing the above one i want to do pooled regression model in general i am using the code

 reg CAR_3day Multiple

The multiple is the dummy variable created using the code you helped with, now i have to do analysis comparing multiple 1st deal compared to their 5th or more deals with the CAR_3day( dependent variable). I have no clue how to do that i am attaching my dataset along. Kindly help me with this. I will also be needing the mean difference pvalue between Multiple: 1st deal comparing with the Multiple: 5 or more deals.

Thank You

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 SDCDealNo int DateAnnounced str6 AcquirorDatastream float CAR_3day byte multiple
"1976325020" 17660 "14769R"    -.0315837 0
"1941060020" 17538 "357295"    -.0395705 0
"3317761020" 21521 "141405"     .0299039 0
"2117325020" 18183 "921883"  .0044030994 0
"2082499040" 18063 "153014" -.0044768997 0
"3373112040" 21632 "921218"     .0568158 0
"2248553020" 18665 "912941"      .032804 0
"1892421020" 17373 "901892"     .0074759 0
"2553004020" 19577 "921725"    -.0370614 1
"1983931020" 17687 "15283Q"    -.0094126 0
"3305629040" 21476 "325475"    -.0291507 0
"3252851020" 21341 "7552EK"    -.0429876 0
"1734732020" 16840 "362665"    -.0283563 0
"1731970040" 16832 "905115"     .0051895 1
"2683664020" 20004 "36174H"   .006335799 0
"2470992040" 19347 "325475"     .0816481 0
"3666176040" 21971 "31610N"     -.021326 0
"2081618020" 18059 "329097"     -.167867 1
"1739364040" 16832 "905118"    -.0053034 1
"2760864020" 20247 "912588"    -.0164385 1
"1786593020" 17035 "152849"     -.008573 0
"1968955040" 17630 "329097"    -.0364541 1
"2124848040" 18199 "680902"   .003505301 0
"1837236020" 17202 "277355"  -.005914497 0
"1914352020" 17454 "912941"    -.0091995 0
"2017667040" 17811 "687609"    -.0324181 1
"2003122020" 17764 "921290"    -.0152041 1
"3664053040" 22243 "32593H"     .0276691 1
"3411629020" 21710 "906220"    -.0069552 0
"3423034040" 21728 "905966"  .0034593004 0
"1801680020" 17086 "132966"     .0559394 0
"3057489020" 20824 "324784"     -.043221 0
"2482781040" 19355 "329097"     .0324173 1
"2462666040" 19291 "329097"    -.0194387 1
"1949403020" 17566 "921268"     .0178035 0
"1990906020" 17709 "923650"    -.0303852 0
"2057835020" 17973 "134058"    -.0244759 1
"1851481020" 17247 "921161"   .007150301 0
"3282458020" 21426 "683118"     .0073977 0
"2463794040" 19295 "325475"      -.00679 0
"1770272020" 17112 "906807"      .091206 0
"2137569040" 18245 "916328"    -.0089546 0
"2293178020" 18710 "912157"    -.0098249 0
"2053533020" 17099 "921645"    -.0989406 1
"1998667020" 17735 "328723"    -.1629119 1
"3493731020" 21969 "154423"     .0150797 0
"3334705020" 21565 "741123"  -.011819702 0
"3140929040" 21025 "543755"     .0143336 0
"2417090040" 19134 "921503"     .0384517 1
"2104735020" 18141 "322287"     .0394686 0
"2466896020" 19306 "13951F"     .0141193 0
"2040405040" 17902 "153014"     .0341748 0
"1877812020" 17331 "921725" -.0001912997 1
"3014080040" 20730 "329097"  -.018030599 1
"2133213040" 18231 "902224"      .013324 0
"1879412020" 17336 "884529"  -.008438899 1
"2132922020" 18232 "324784"  .0013928006 0
"2120124020" 18191 "906194"    -.0049285 0
"1933016040" 17510 "946387"    -.0327929 0
"1763571020" 16947 "951849"     -.023208 0
"1726378020" 16807 "916445"     .0475001 0
"3450277020" 21796 "921725"      .012225 1
"2152074020" 18294 "544833"    -.0891569 0
"1748988020" 16895 "500344"    -.0320848 0
"1895434020" 17384 "699628"    -.2909848 0
"1764378020" 16952 "546781"     .0112843 0
"3012582020" 20726 "130088"    -.0285692 0
"2144116020" 18261 "324784"    -.0032485 0
"1925716040" 17482 "982384"     .0196747 0
"2770654040" 20268 "905115"  -.002373899 1
"2928583040" 20515 "134784"   .011080801 0
"1780264020" 17009 "27251C"    -.0164854 0
"3013976020" 20731 "277355"     .0567969 0
"2426827020" 19165 "902172"   .004811801 0
"1752047020" 16903 "912941"   .018044401 0
"2220494020" 18582 "699943"    -.0612219 1
"1937019020" 17520 "132834"    -.0363363 0
"1897228040" 17391 "921725"     .0013105 1
"2835557020" 20436 "906933"    -.0568346 0
"2305557040" 18729 "912588"    -.5150018 1
"2132521020" 18231 "981722"     .0093399 0
"1812498020" 17120 "312024"     .0245295 0
"2553733040" 19579 "680902"    -.0370614 0
"4065915040" 19536 "997350"     .0495915 0
"2786781040" 20299 "905898"   .005221901 0
"2499440020" 19581 "13997V"  .0014973993 0
"1781512020" 17014 "326739"    -.0147392 1
"2087547020" 18079 "997918"  -.003854999 0
"1925398020" 17489 "516419"     .0381652 1
"1783000020" 17020 "756210"    -.0272093 0
"2503464040" 19428 "906511"     .0241893 0
"2140535020" 18252 "132800"   .008206599 0
"3483706020" 21866 "68470T"    -.0527029 0
"2097013040" 18113 "292703"    -.0651084 1
"3172713020" 21129 "134784"  -.002526799 0
"2063395020" 17990 "292703"  -.007646501 1
"1780515020" 17009 "912157"     -.142865 0
"3674960020" 22265 "741643"     .0294387 0
"2044120020" 17917 "998173"     -.024015 0
"1894837040" 17379 "921964"      .011871 1
end
format %td DateAnnounced

[Clyde Schechter]

now i have to do analysis comparing multiple 1st deal compared to their 5th or more deals with the CAR_3day( dependent variable).

Sorry, but I don't understand this. Please explain more clearly.

[Preetham Venkat]

I want to do something similar to the picture above attached. Based on the image above CAAR(-1,+1) variable in my data is CAR_3day and Multiple acquirer variable is Multiple which is a dummy variable taking a value of 1 if any company has made 5 or more than 5 deals within a time frame of 3 years. In general if use code

 reg CAR_3day multiple

than it will be doing analysis on companies which went to make more than 5 or more deals within 3 years. Now i want to analysis how my CAR_3day has an impact on multiple (within multiple its 1st deal while comparing with the multiple 5 or more deals). Like reg CAR_3day Multiple(1stdeal) & reg CAR_3day Multiple(5 or more deals).

[Clyde Schechter]

In general if use code Code:

reg CAR_3day multiple
than it will be doing analysis on companies which went to make more than 5 or more deals within 3 years.

No, that's not correct. That regression will contrast CAR_3day among companies which went on to make more than 5 or more deals within 3 years to the CAR_3day among companies that did not make 5 or more deals within 3 years.

And I don't understand

Like reg CAR_3day Multiple(1stdeal) & reg CAR_3day Multiple(5 or more deals).

By definition, Multiple means 5 or more deals. So I don't get what Multiple(1stdeal) means.

And I'm afraid the table you show at the top of #5 is equally mysterious to me. Perhaps somebody who works in finance will see this and immediately grasp what it means. Hopefully such a person is following along and will chime in.

[Preetham Venkat]

 egen Firmid = group(AcquirorDatastream)
assert !missing(SDCDealNo)
 by Firmid (DateAnnounced), sort: gen date_rank = sum(DateAnnounced != DateAnnounced[_n-1])
by Firmid (DateAnnounced): egen n_deals_in_3_years =   count(cond(datediff_frac(DateAnnounced[1], DateAnnounced, "y") <= 3, _n, .))
by Firmid: gen byte multiple = n_deals_in_3_years >= 5

* Example generated by -dataex-. For more info, type help dataex
clear
input int DateAnnounced str6 AcquirorDatastream str10 SDCDealNo float(Firmid n_deals_in_3_years) byte multiple
17636 "130062" "1924997020"  1 2 0
18063 "130062" "2082259020"  1 2 0
19751 "130062" "2593089020"  1 2 0
20996 "130062" "3119302020"  1 2 0
17603 "130086" "1960259020"  2 1 0
18886 "130086" "2345790020"  2 1 0
18886 "130086" "2345788020"  2 1 0
21929 "130086" "3502739020"  2 1 0
20726 "130088" "3012582020"  3 1 0
17633 "130298" "1968943020"  4 1 0
19919 "130298" "2655953020"  4 1 0
20689 "130298" "3010025040"  4 1 0
21532 "130298" "3240426040"  4 1 0
18017 "130375" "2069880020"  5 1 0
20674 "130485" "2993116020"  6 2 0
20999 "130485" "3121259020"  6 2 0
20120 "130502" "2728784020"  7 4 0
20485 "130502" "2938996020"  7 4 0
21063 "130502" "3146004020"  7 4 0
21181 "130502" "3200683020"  7 4 0
21277 "130556" "3230754020"  8 1 0
18434 "130720" "2194164020"  9 1 0
18904 "130775" "2352405020" 10 1 0
20606 "130841" "2966732020" 11 2 0
21684 "130841" "3397826020" 11 2 0
19262 "130859" "2454300020" 12 2 0
20013 "130859" "2686750020" 12 2 0
20709 "130859" "3006379020" 12 2 0
21027 "130859" "3152961020" 12 2 0
21879 "130859" "3482995020" 12 2 0
18656 "131745" "2285871020" 13 2 0
19120 "131745" "2412064020" 13 2 0
20207 "131745" "2747970020" 13 2 0
20669 "131745" "2990661020" 13 2 0
21153 "131745" "3184217020" 13 2 0
21354 "131745" "3257258020" 13 2 0
21502 "131745" "3309280020" 13 2 0
22264 "131745" "3676141020" 13 2 0
20587 "131771" "2964624040" 14 2 0
21451 "131771" "3291789020" 14 2 0
18326 "131844" "2162135020" 15 3 0
18455 "131844" "2202088020" 15 3 0
19289 "131844" "2462221020" 15 3 0
19701 "131844" "2590117020" 15 3 0
19950 "131844" "2665213020" 15 3 0
20030 "131844" "2691896020" 15 3 0
21063 "131844" "3149391020" 15 3 0
20361 "131880" "2807508020" 16 1 0
21783 "131880" "3480757020" 16 1 0
17371 "132050" "1891110020" 17 1 0
18819 "132050" "2327919020" 17 1 0
19570 "132050" "2551509020" 17 1 0
19886 "132050" "2646129020" 17 1 0
21459 "132050" "3317334040" 17 1 0
21076 "132125" "3157036020" 18 2 0
21837 "132125" "3468244020" 18 2 0
21657 "132736" "3383166020" 19 1 0
20248 "132800" "2761411020" 20 3 0
20879 "132800" "3075445040" 20 3 0
21216 "132800" "3208848020" 20 3 0
19275 "133234" "2458094020" 21 3 0
19683 "133234" "2586586020" 21 3 0
19827 "133234" "2628559020" 21 3 0
20894 "133234" "3082008020" 21 3 0
21649 "133234" "3381936020" 21 3 0
20395 "133324" "2817066020" 22 6 1
20495 "133324" "2920211020" 22 6 1
20667 "133324" "2989654020" 22 6 1
20747 "133324" "3020251020" 22 6 1
20909 "133324" "3087786020" 22 6 1
21467 "133324" "3297366020" 22 6 1
17700 "133891" "1987730020" 23 1 0
18822 "133891" "2328909020" 23 1 0
19395 "133891" "2493821020" 23 1 0
18437 "134057" "2195660020" 24 2 0
19242 "134057" "2447925020" 24 2 0
19709 "134057" "2594208020" 24 2 0
21181 "134057" "3206932020" 24 2 0
21661 "134057" "3385269040" 24 2 0
21684 "134057" "3393935020" 24 2 0
17540 "134058" "1942387040" 25 6 1
17576 "134058" "1952903040" 25 6 1
17584 "134058" "1954744020" 25 6 1
17639 "134058" "1970197020" 25 6 1
17650 "134058" "1973089020" 25 6 1
17973 "134058" "2057835020" 25 6 1
21370 "134499" "3264203020" 26 1 0
21040 "134507" "3136606020" 27 1 0
21978 "13466Q" "3522125040" 28 1 0
19220 "13471D" "2442379020" 29 4 0
19747 "13471D" "2604312020" 29 4 0
19981 "13471D" "2674984040" 29 4 0
20172 "13471D" "2737295040" 29 4 0
20437 "13471D" "2847189020" 29 4 0
21034 "13471D" "3134516020" 29 4 0
21398 "13471D" "3273424020" 29 4 0
21697 "13471D" "3397174020" 29 4 0
21032 "13473U" "3645927020" 30 2 0
21759 "13473U" "3434769020" 30 2 0
22215 "13473U" "3652914020" 30 2 0
end
format %td DateAnnounced

We have used this code for creating multiple dummy variable. Here n_deals_in_3_years takes into account total number of deals made in 3 years.If we look at that dataset for example for (AcquirorDatastream -133324) it says n_deals_in_3_years takes value of 7 for every entry. Now i want to have one more variable which takes value like 1,2,3,4,5,6 taking cumulative values after every lastest entry within the 3 years time frame.

[Clyde Schechter]

Something like this:

sort Firmid DateAnnounced, stable
by Firmid (DateAnnounced): gen wanted = _n if _n <= n_deals_in_3_years & multiple

Note: There can be more than one deal with the same DateAnnounced for the same Firmid. In that case the order among those is indeterminate. In this code, I have used a -stable- -sort-, so that they are ordered in the order that they already appear in the dataset as you show it. This makes the process reproducible, but perhaps arbitrary. If you have some other rule for how you would like to break these ties, that can be built into the sort order of the data with code that reflects it.

[Preetham Venkat]

Thank you Clyde it worked perfectly. Can you have a look at this post I didn't get any reply from anyone yet. Thank You. https://www.statalist.org/forums/for...el#post1723905