Keeping duplicates

[Stayros Ieronymos]

Hello,
I am struggling with Stata syntax a bit and hope someone, with better knowledge, can help out.

I have a list of dates (price history of firms) and merged it with a list of Merge&Acquisitions dates.
The problem is that now for some dates I have multiple events. For example MSFT announced 4 M&A in the same day.
Thus, when I try to create a panel data set I get en error.
I know how to drop the duplicates but I hope there is a way I can "correct" them so I do not loose data.

For example looking at the example below, we have company ID 42 and on 12/8/09 announced a 'Minority Stake' buy and 'Acquisition/Merger'. How can I keep these two events and run

egen dateid = group(Date)
tsset Date ID //gives error

so later I can run event windows like

gen CAR_m1=l1.CumulativeReturn
gen CAR_0=CumulativeReturn
gen CAR_p1=f1.CumulativeReturn

* Example generated by -dataex-. For more info, type help dataex
clear
input float ID int Date double(Price Change Return CumulativeReturn CumulativeChange) str20 DealType str6 Role str33 TargetIndustry double(Acq_Size Acg_Lvrg) byte(_merge isdup)
42 12809     4.625                   .                  .  783.582089552238  783.582089552238 "Minority Stake"       "Buyer"  "Information Technology Services"    2.605815553877567  .2508792388109271 3 1
42 12809     4.625                   .                  .  783.582089552238  783.582089552238 "Acquisition / Merger" "Buyer"  "Packaged Software"                  2.605815553877567  .2508792388109271 3 1
50 13819         .                   .                  .                 .                 . "Acquisition / Merger" "Seller" "Miscellaneous Commercial Services" 4.4605971888976015  .3318905817174515 2 1
50 13819         .                   .                  .                 .                 . "Acquisition / Merger" "Seller" "Miscellaneous Commercial Services" 4.4605971888976015  .3318905817174515 2 1
37 13849   1.34375   .0104166000000001   .781244960937765 -99.5520654159499 -99.5520654159499 "Acquisition / Merger" "Buyer"  "Electronics Distributors"          2.2610748321237004  .4769146644300821 3 1
37 13849   1.34375   .0104166000000001   .781244960937765 -99.5520654159499 -99.5520654159499 "Acquisition / Merger" "Buyer"  "Information Technology Services"   2.2610748321237004  .4769146644300821 3 1
 1 13879  16.15625               -.125  -.767754318618041  16517.8575226938  16517.8575226938 "Minority Stake"       "Buyer"  "Information Technology Services"    4.226342087163631  .2669833729216152 3 1
 1 13879  16.15625               -.125  -.767754318618041  16517.8575226938  16517.8575226938 "Acquisition / Merger" "Buyer"  "Specialty Telecommunications"       4.226342087163631  .2669833729216152 3 1
50 14335  30.21875                  .5   1.68243569474104  211551.051763738  206790.845570887 "Acquisition / Merger" "Buyer"  "Packaged Software"                  4.641959693496038   .255057127870647 3 1
50 14335  30.21875                  .5   1.68243569474104  211551.051763738  206790.845570887 "Acquisition / Merger" "Seller" "Specialty Telecommunications"       4.641959693496038   .255057127870647 3 1
50 14396  25.34375             -1.6875  -6.24277208018244  177489.932776179   173414.45269699 "Minority Stake"       "Buyer"  "Specialty Telecommunications"       4.641959693496038   .255057127870647 3 1
50 14396  25.34375             -1.6875  -6.24277208018244  177489.932776179   173414.45269699 "Acquisition / Merger" "Buyer"  "Computer Peripherals"               4.641959693496038   .255057127870647 3 1
50 14424    29.625               1.125   3.94736909768089  207489.709582249  202725.772079836 "Acquisition / Merger" "Buyer"  "Information Technology Services"    4.641959693496038   .255057127870647 3 1
50 14424    29.625               1.125   3.94736909768089  207489.709582249  202725.772079836 "Acquisition / Merger" "Buyer"  "Information Technology Services"    4.641959693496038   .255057127870647 3 1
50 14488  41.71875                .625   1.52090905938626  292354.052859442  285524.900555465 "Acquisition / Merger" "Buyer"  "Computer Peripherals"               4.641959693496038   .255057127870647 3 1
50 14488  41.71875                .625   1.52090905938626  292354.052859442  285524.900555465 "Acquisition / Merger" "Buyer"  "Specialty Telecommunications"       4.641959693496038   .255057127870647 3 1
50 14641  50.71875                1.25   2.52684901285318  355579.018921642  347142.856630352 "Acquisition / Merger" "Buyer"  "Computer Communications"            4.680743322963766  .2215663781416206 3 1
50 14641  50.71875                1.25   2.52684901285318  355579.018921642  347142.856630352 "Acquisition / Merger" "Seller" "Building Products"                  4.680743322963766  .2215663781416206 3 1
50 14643  52.09375              2.0625   4.15239632971036  365326.753640183  356556.711030682 "Acquisition / Merger" "Buyer"  "Semiconductors"                     4.680743322963766  .2215663781416206 3 1
50 14643  52.09375              2.0625   4.15239632971036  365326.753640183  356556.711030682 "Acquisition / Merger" "Buyer"  "Electrical Products"                4.680743322963766  .2215663781416206 3 1
50 14670   57.9375              1.4375   2.54424681115148  406319.440821156  396565.592232084 "Acquisition / Merger" "Buyer"  "Packaged Software"                  4.680743322963766  .2215663781416206 3 1
50 14670   57.9375              1.4375   2.54424681115148  406319.440821156  396565.592232084 "Acquisition / Merger" "Buyer"  "Electronic Production Equipment"    4.680743322963766  .2215663781416206 3 1
68 14726     12750                  50   .393699642867706  798.850893689446  690.140729331498 "Minority Stake"       "Buyer"  "Electronic Equipment/Instruments"   4.829889494274453   .703449311377964 3 1
68 14726     12750                  50   .393699642867706  798.850893689446  690.140729331498 "Minority Stake"       "Buyer"  "Electronic Equipment/Instruments"   4.829889494274453   .703449311377964 3 1
50 14731   63.5625              .15625   .246415841297653   445777.59442404  435076.814778888 "Acquisition / Merger" "Buyer"  "Publishing: Books/Magazines"        4.680743322963766  .2215663781416206 3 1
50 14731   63.5625              .15625   .246415841297653   445777.59442404  435076.814778888 "Acquisition / Merger" "Buyer"  "Information Technology Services"    4.680743322963766  .2215663781416206 3 1
 1 14792         .                   .                  .                 .                 . "Minority Stake"       "Buyer"  "Specialty Telecommunications"        4.76110806696504 .19533376089858037 2 1
 1 14792         .                   .                  .                 .                 . "Minority Stake"       "Buyer"  "Major Telecommunications"            4.76110806696504 .19533376089858037 2 1
end
format %td Date

Appreciate any inputs.
Cheers!
Stayros

[Nick Cox]

You can't have it both ways if you want to keep Minority Stake and Acquisition/Merger as values. in separate observations. What you could do is create count variables side by side.

[Stayros Ieronymos]

Thank you Nick for your answer.
I see your point. First thought was to go with dummy variables to separate them but it could be that same type of events happen on the same day. Fixes the issue for some but not all.

What I tried next was to create a volume count:

gen vol = sum ( isdup != 0) if isdup != 0
gen newID = ( ID+100) if vol != .

This kind of works, so I don't get an error when trying to do panel data and can create my event windows BUT Stata now sees them as 'new' firms with no other inputs so I still can't get what I aim for.

Could you elaborate what you mean with count variables side by side?

[Stayros Ieronymos]

Maybe it is a crude (politely saying stupid) way but this is what I came up with...

//check for duplicates
duplicates report Date ID
duplicates tag Date ID, gen(isdup)

//create volume count and replace ID
gen vol = sum(isdup!=0) if isdup!=0
gen newID = (ID+vol+100) if vol!=.
replace ID=newID if vol!=.

//run the panel date command
egen dateid = group(Date)
tsset Date ID  

//switch back to old ID version
gen nnID = (ID -vol-100) if vol !=.
replace ID =nnID if vol !=.

//Clean up
drop newID nnID isdup vol

Now if I run the duplicate check I still get them but now my dataset is in panel mode and can continue with slicing event windows.

Maybe someone could point out if I am missing something?

[Nick Cox]

This is what I had in mind:

* Example generated by -dataex-. For more info, type help dataex
clear
input float ID int Date double(Price Change Return CumulativeReturn CumulativeChange) str20 DealType str6 Role str33 TargetIndustry double(Acq_Size Acg_Lvrg) byte(_merge isdup)
42 12809     4.625                   .                  .  783.582089552238  783.582089552238 "Minority Stake"       "Buyer"  "Information Technology Services"    2.605815553877567  .2508792388109271 3 1
42 12809     4.625                   .                  .  783.582089552238  783.582089552238 "Acquisition / Merger" "Buyer"  "Packaged Software"                  2.605815553877567  .2508792388109271 3 1
50 13819         .                   .                  .                 .                 . "Acquisition / Merger" "Seller" "Miscellaneous Commercial Services" 4.4605971888976015  .3318905817174515 2 1
50 13819         .                   .                  .                 .                 . "Acquisition / Merger" "Seller" "Miscellaneous Commercial Services" 4.4605971888976015  .3318905817174515 2 1
37 13849   1.34375   .0104166000000001   .781244960937765 -99.5520654159499 -99.5520654159499 "Acquisition / Merger" "Buyer"  "Electronics Distributors"          2.2610748321237004  .4769146644300821 3 1
37 13849   1.34375   .0104166000000001   .781244960937765 -99.5520654159499 -99.5520654159499 "Acquisition / Merger" "Buyer"  "Information Technology Services"   2.2610748321237004  .4769146644300821 3 1
 1 13879  16.15625               -.125  -.767754318618041  16517.8575226938  16517.8575226938 "Minority Stake"       "Buyer"  "Information Technology Services"    4.226342087163631  .2669833729216152 3 1
 1 13879  16.15625               -.125  -.767754318618041  16517.8575226938  16517.8575226938 "Acquisition / Merger" "Buyer"  "Specialty Telecommunications"       4.226342087163631  .2669833729216152 3 1
50 14335  30.21875                  .5   1.68243569474104  211551.051763738  206790.845570887 "Acquisition / Merger" "Buyer"  "Packaged Software"                  4.641959693496038   .255057127870647 3 1
50 14335  30.21875                  .5   1.68243569474104  211551.051763738  206790.845570887 "Acquisition / Merger" "Seller" "Specialty Telecommunications"       4.641959693496038   .255057127870647 3 1
50 14396  25.34375             -1.6875  -6.24277208018244  177489.932776179   173414.45269699 "Minority Stake"       "Buyer"  "Specialty Telecommunications"       4.641959693496038   .255057127870647 3 1
50 14396  25.34375             -1.6875  -6.24277208018244  177489.932776179   173414.45269699 "Acquisition / Merger" "Buyer"  "Computer Peripherals"               4.641959693496038   .255057127870647 3 1
50 14424    29.625               1.125   3.94736909768089  207489.709582249  202725.772079836 "Acquisition / Merger" "Buyer"  "Information Technology Services"    4.641959693496038   .255057127870647 3 1
50 14424    29.625               1.125   3.94736909768089  207489.709582249  202725.772079836 "Acquisition / Merger" "Buyer"  "Information Technology Services"    4.641959693496038   .255057127870647 3 1
50 14488  41.71875                .625   1.52090905938626  292354.052859442  285524.900555465 "Acquisition / Merger" "Buyer"  "Computer Peripherals"               4.641959693496038   .255057127870647 3 1
50 14488  41.71875                .625   1.52090905938626  292354.052859442  285524.900555465 "Acquisition / Merger" "Buyer"  "Specialty Telecommunications"       4.641959693496038   .255057127870647 3 1
50 14641  50.71875                1.25   2.52684901285318  355579.018921642  347142.856630352 "Acquisition / Merger" "Buyer"  "Computer Communications"            4.680743322963766  .2215663781416206 3 1
50 14641  50.71875                1.25   2.52684901285318  355579.018921642  347142.856630352 "Acquisition / Merger" "Seller" "Building Products"                  4.680743322963766  .2215663781416206 3 1
50 14643  52.09375              2.0625   4.15239632971036  365326.753640183  356556.711030682 "Acquisition / Merger" "Buyer"  "Semiconductors"                     4.680743322963766  .2215663781416206 3 1
50 14643  52.09375              2.0625   4.15239632971036  365326.753640183  356556.711030682 "Acquisition / Merger" "Buyer"  "Electrical Products"                4.680743322963766  .2215663781416206 3 1
50 14670   57.9375              1.4375   2.54424681115148  406319.440821156  396565.592232084 "Acquisition / Merger" "Buyer"  "Packaged Software"                  4.680743322963766  .2215663781416206 3 1
50 14670   57.9375              1.4375   2.54424681115148  406319.440821156  396565.592232084 "Acquisition / Merger" "Buyer"  "Electronic Production Equipment"    4.680743322963766  .2215663781416206 3 1
68 14726     12750                  50   .393699642867706  798.850893689446  690.140729331498 "Minority Stake"       "Buyer"  "Electronic Equipment/Instruments"   4.829889494274453   .703449311377964 3 1
68 14726     12750                  50   .393699642867706  798.850893689446  690.140729331498 "Minority Stake"       "Buyer"  "Electronic Equipment/Instruments"   4.829889494274453   .703449311377964 3 1
50 14731   63.5625              .15625   .246415841297653   445777.59442404  435076.814778888 "Acquisition / Merger" "Buyer"  "Publishing: Books/Magazines"        4.680743322963766  .2215663781416206 3 1
50 14731   63.5625              .15625   .246415841297653   445777.59442404  435076.814778888 "Acquisition / Merger" "Buyer"  "Information Technology Services"    4.680743322963766  .2215663781416206 3 1
 1 14792         .                   .                  .                 .                 . "Minority Stake"       "Buyer"  "Specialty Telecommunications"        4.76110806696504 .19533376089858037 2 1
 1 14792         .                   .                  .                 .                 . "Minority Stake"       "Buyer"  "Major Telecommunications"            4.76110806696504 .19533376089858037 2 1
end
format %td Date

foreach var in DealType Role TargetIndustry {  
    bysort ID Date `var' : gen `var'Count = _N 
    separate `var'Count, by(`var') veryshortlabel 
    foreach v in `r(varlist)' { 
        bysort ID Date (`v') : replace `v' = `v'[1]
    }
    local todrop `todrop' `var'Count  
} 

drop `todrop'

duplicates drop 

describe

[Stayros Ieronymos]

Wow! That's too complicated for me but I see what you are doing.
I think I know enough to continue.
You have been a great help! Thank you Mr. Nick!