egen count variable based on dates

[Gianni Spolver]

Dear Statalist,

I'm relatively new to stata and am struggling with the following situation.

I have a dataset where each company has its own id (AcquirorID). The dataset consist of a set of transactions where a company buys another company together with the date of the transaction (Completeddate).

Basically, I want to generate a new variable called Acq_exp5, which counts the number of times this company appears again as an acquiror in a transaction up to 5 years prior to the respective deal. I have tried by using this code ...

egen Acq_exp5 = count(AcquirorID) if Completeddate < Completeddate & Completeddate > (Completeddate - (365*5))  , by (AcquirorID)

... but it returns a type mismatch. Could anyone help me in pointing out my mistake?


Below I provide an example of my dataset

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 DealNumber str17 TargetID str16 AcquirorID int Completeddate
"1909620718" "AT9050064867"   "AT9010001013" 21042
"428323"     ""               "AT9010001013" 17607
"1601361353" "DE7330001161"   "AT9010001013" 19829
"139304"     "AT9050021036"   "AT9010001013" 16411
"36845"      ""               "AT9010001013" 14077
"1909559724" "DE8190496081"   "AT9010001013" 21673
"1601429472" "DE7270093528"   "AT9010001013" 20070
"279719"     "AT9110289384"   "AT9010001013" 17066
"428159"     ""               "AT9010001013" 17607
"1909052627" "AT9110222298"   "AT9010001013" 19705
"1941168400" "DE8230598147"   "AT9010001732" 21608
"1941025510" "DE8290162551"   "AT9010001732" 21185
"1909023921" "DE7331037629"   "AT9010002302" 19359
"127909"     "AT9010041204"   "AT9010003139" 16300
"1909579108" "CHCHE101392565" "AT9010008647" 20940
"129944"     "AT9150011918"   "AT9010008647" 15592
"87362"      ""               "AT9010008647" 15060
"1601020282" "AT9010007615"   "AT9010010464" 17918
"1909036895" "CHCHE101016257" "AT9010010464" 20392
"516334"     "AT9010016115"   "AT9010010464" 17440
"1909472120" "CHCHE107818120" "AT9010010464" 20671
"1909556236" "AT9010016115"   "AT9010010464" 21678
"1909047257" "DE7290278251"   "AT9010010464" 20418
"1601476515" "AT9050053195"   "AT9010012047" 20200
"378745"     "AT9010015861"   "AT9010015034" 17442
"1601440537" ""               "AT9010015034" 20113
"1601241049" "CHCHE106320403" "AT9010016240" 18616
"1909061042" "CHCHE106840163" "AT9010027783" 19711
end
format %tdnn/dd/CCYY Completeddate

Kind regards,

Gianni

[Nick Cox]

I can't immediately see why a type mismatch is returned here as count() allows a string argument. But that's the least of your problems.

The if qualifier just won't do what you want. You want Stata to apply it group-wise so that

 Completeddate < Completeddate & Completeddate > (Completeddate - (365*5))

means

if any Completeddate in the same group < this Completeddate & any such Completeddate > (this Completeddate - (365 * 5))

but nowhere does Stata promise to do anything like that. Stata applies such conditions only within the same observation and it's impossible (using a little algebra now plus the fact that we are talking about integer dates) that

d lies within the interval (d - (365 * 5), d - 1]

which is what your code implies. So far, so negative, but using

ssc install rangestat

is this what you want?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 DealNumber str17 TargetID str16 AcquirorID int Completeddate
"1909620718" "AT9050064867"   "AT9010001013" 21042
"428323"     ""               "AT9010001013" 17607
"1601361353" "DE7330001161"   "AT9010001013" 19829
"139304"     "AT9050021036"   "AT9010001013" 16411
"36845"      ""               "AT9010001013" 14077
"1909559724" "DE8190496081"   "AT9010001013" 21673
"1601429472" "DE7270093528"   "AT9010001013" 20070
"279719"     "AT9110289384"   "AT9010001013" 17066
"428159"     ""               "AT9010001013" 17607
"1909052627" "AT9110222298"   "AT9010001013" 19705
"1941168400" "DE8230598147"   "AT9010001732" 21608
"1941025510" "DE8290162551"   "AT9010001732" 21185
"1909023921" "DE7331037629"   "AT9010002302" 19359
"127909"     "AT9010041204"   "AT9010003139" 16300
"1909579108" "CHCHE101392565" "AT9010008647" 20940
"129944"     "AT9150011918"   "AT9010008647" 15592
"87362"      ""               "AT9010008647" 15060
"1601020282" "AT9010007615"   "AT9010010464" 17918
"1909036895" "CHCHE101016257" "AT9010010464" 20392
"516334"     "AT9010016115"   "AT9010010464" 17440
"1909472120" "CHCHE107818120" "AT9010010464" 20671
"1909556236" "AT9010016115"   "AT9010010464" 21678
"1909047257" "DE7290278251"   "AT9010010464" 20418
"1601476515" "AT9050053195"   "AT9010012047" 20200
"378745"     "AT9010015861"   "AT9010015034" 17442
"1601440537" ""               "AT9010015034" 20113
"1601241049" "CHCHE106320403" "AT9010016240" 18616
"1909061042" "CHCHE106840163" "AT9010027783" 19711
end
format %tdnn/dd/CCYY Completeddate

gen one = 1
rangestat (sum) one, int(Completeddate -1825 -1) by(AcquirorID)

sort Acq Completeddate

list, sepby(Acq)

    +-------------------------------------------------------------------------+
     | DealNumber         TargetID     AcquirorID   Complete~e   one   one_sum |
     |-------------------------------------------------------------------------|
  1. |      36845                    AT9010001013    7/17/1998     1         . |
  2. |     139304     AT9050021036   AT9010001013    12/6/2004     1         . |
  3. |     279719     AT9110289384   AT9010001013    9/22/2006     1         1 |
  4. |     428159                    AT9010001013    3/16/2008     1         2 |
  5. |     428323                    AT9010001013    3/16/2008     1         2 |
  6. | 1909052627     AT9110222298   AT9010001013   12/13/2013     1         . |
  7. | 1601361353     DE7330001161   AT9010001013    4/16/2014     1         1 |
  8. | 1601429472     DE7270093528   AT9010001013   12/13/2014     1         2 |
  9. | 1909620718     AT9050064867   AT9010001013    8/11/2017     1         3 |
 10. | 1909559724     DE8190496081   AT9010001013     5/4/2019     1         2 |
     |-------------------------------------------------------------------------|
 11. | 1941025510     DE8290162551   AT9010001732     1/1/2018     1         . |
 12. | 1941168400     DE8230598147   AT9010001732    2/28/2019     1         1 |
     |-------------------------------------------------------------------------|
 13. | 1909023921     DE7331037629   AT9010002302     1/1/2013     1         . |
     |-------------------------------------------------------------------------|
 14. |     127909     AT9010041204   AT9010003139    8/17/2004     1         . |
     |-------------------------------------------------------------------------|
 15. |      87362                    AT9010008647    3/26/2001     1         . |
 16. |     129944     AT9150011918   AT9010008647     9/9/2002     1         1 |
 17. | 1909579108   CHCHE101392565   AT9010008647     5/1/2017     1         . |
     |-------------------------------------------------------------------------|
 18. |     516334     AT9010016115   AT9010010464    10/1/2007     1         . |
 19. | 1601020282     AT9010007615   AT9010010464    1/21/2009     1         1 |
 20. | 1909036895   CHCHE101016257   AT9010010464   10/31/2015     1         . |
 21. | 1909047257     DE7290278251   AT9010010464   11/26/2015     1         1 |
 22. | 1909472120   CHCHE107818120   AT9010010464     8/5/2016     1         2 |
 23. | 1909556236     AT9010016115   AT9010010464     5/9/2019     1         3 |
     |-------------------------------------------------------------------------|
 24. | 1601476515     AT9050053195   AT9010012047    4/22/2015     1         . |
     |-------------------------------------------------------------------------|
 25. |     378745     AT9010015861   AT9010015034    10/3/2007     1         . |
 26. | 1601440537                    AT9010015034    1/25/2015     1         . |
     |-------------------------------------------------------------------------|
 27. | 1601241049   CHCHE106320403   AT9010016240   12/20/2010     1         . |
     |-------------------------------------------------------------------------|
 28. | 1909061042   CHCHE106840163   AT9010027783   12/19/2013     1         . |
     +-------------------------------------------------------------------------+

[Gianni Spolver]

Thank you Nick. I did not know about the rangestat command, but thus gives me exactly what I wanted.

Best,

Gianni