Calculating the number of subsequent observations fulfilling certain criteria

[Pascal Meier]

Hello,

I have data on M&A transactions for publicly listed firms (identifier gvkey_n). Thus, my dataset is on the "deal" / "transaction" level. Conditional on the focal deal (observation) of a company identified by gvkey_n, I want to calculate the number of deals of this company, i.e., the number of observations, within the next, let's say, two years. The variable dateannounced identifies the date of the deals.

Is there a way to calculate for each observation the number of subsequent observations within the next years (variable numberofdealsnext2years)? In a similar spirit, I would need to sum up the value of these deals, i.e., the values from valueoftransactionmil in a new variable (variable valueofdealsnext2years).

Guidance is much appreciated! I hope the problem is clear.

Best regards,
Pascal

* Example generated by -dataex-. For more info, type help dataex
clear
input long gvkey_n float dateannounced double valueoftransactionmil
1004 17588       40
1004 18346      200
1004 18920      280
1013 14735       80
1013 16155      350
1013 17461      169

[Clyde Schechter]

gen `c(obs_t)' obs_no = _n
local 2years = 365*2
rangestat (count) obs_no, by(gvkey_n) interval(date 1 `2years')

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

Note: This code does not adjust for the possibility that one of the two years following a date is a leap year. If this matters, post back, and I will add some code to handle that.

It also assumes that either there are never two deals for the same gvke on the same date, or, if there are, you don't want to count them. Adjust the -interval()- option if they do exist and should be counted.

[Pascal Meier]

gen `c(obs_t)' obs_no = _n
local 2years = 365*2
rangestat (count) obs_no, by(gvkey_n) interval(date 1 `2years')

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

Note: This code does not adjust for the possibility that one of the two years following a date is a leap year. If this matters, post back, and I will add some code to handle that.

It also assumes that either there are never two deals for the same gvke on the same date, or, if there are, you don't want to count them. Adjust the -interval()- option if they do exist and should be counted.

Dear Clyde,

thank you very much for your help! I somehow get the error message "no observations r(2000)" - do you know what the problem might be with the code?

Best regards,
Pascal

[Nick Cox]

The code looks fine to me.

* Example generated by -dataex-. For more info, type help dataex
clear
input long gvkey_n float dateannounced double valueoftransactionmil
1004 17588       40
1004 18346      200
1004 18920      280
1013 14735       80
1013 16155      350
1013 17461      169
end 

gen `c(obs_t)' obs_no = _n
local 2years = 365*2
rangestat (count) obs_no, by(gvkey_n) interval(date 1 `2years')

l , sepby(gvkey_n)

     +---------------------------------------------------+
     | gvkey_n   datean~d   valueo~l   obs_no   obs_no~t |
     |---------------------------------------------------|
  1. |    1004      17588         40        1          . |
  2. |    1004      18346        200        2          1 |
  3. |    1004      18920        280        3          . |
     |---------------------------------------------------|
  4. |    1013      14735         80        4          . |
  5. |    1013      16155        350        5          . |
  6. |    1013      17461        169        6          . |
     +---------------------------------------------------+

Here missing means zero. I would look more carefully at your data.

[Pascal Meier]

The code looks fine to me.

* Example generated by -dataex-. For more info, type help dataex
clear
input long gvkey_n float dateannounced double valueoftransactionmil
1004 17588 40
1004 18346 200
1004 18920 280
1013 14735 80
1013 16155 350
1013 17461 169
end

gen `c(obs_t)' obs_no = _n
local 2years = 365*2
rangestat (count) obs_no, by(gvkey_n) interval(date 1 `2years')

l , sepby(gvkey_n)

+---------------------------------------------------+
| gvkey_n datean~d valueo~l obs_no obs_no~t |
|---------------------------------------------------|
1. | 1004 17588 40 1 . |
2. | 1004 18346 200 2 1 |
3. | 1004 18920 280 3 . |
|---------------------------------------------------|
4. | 1013 14735 80 4 . |
5. | 1013 16155 350 5 . |
6. | 1013 17461 169 6 . |
+---------------------------------------------------+

Here missing means zero. I would look more carefully at your data.

Dear Nick,

thanks for your fast reply! I just realized the code works fine as long as I only have the three specified variables (gvkey_n, dateannounced, valueoftransactionmil). In the original dataset, I have more variables. As a little workaround, I merge the results back to the original dataset based on the identifier and the date.

Best,
Pascal