# st: cumulative moving sum when there are repeated date values

 From "P. Rao Sahib" <[email protected]> To [email protected] Subject st: cumulative moving sum when there are repeated date values Date Thu, 05 Feb 2009 11:23:14 +0100

```Dear Statalist users,
```
I have a panel dataset of acquisition attempts made by firms in which I would like to calculate the cumulative number of acquisitions a firm completes in the past 5 years prior to
```the current acquisition.
```
Firms enter the dataset if they make an acquisition attempt but as some acquisitions are later abandoned, I have an acquisition completion dummy “acdum” that is 1 if the
```acquisition is completed, 0 otherwise” .

My data looks like this:
Acdum is the Acquisition completion dummy.

Firmid Date Acdum Cumulative

Shell 24 Jan 1993 1 0

Shell 1 Dec 1995 0 1

Shell 3 Aug 1996 1 1

Shell 3 Aug 1996 1 2

Mittal Steel 3 Jan 1993 1 0

Mittal Steel 4 March 2000 1 0

Mittal Steel 5 April 2001 1 1

```
I would like to count the cumulative number of acquisitions made by the firm in the past 5 years prior to the current acquisition (even if they occured in the same year ). By searching Statalist, I found a solution using “ tssmooth, egen and a filter. However, these solutions involving tsseting the data and as it can be seen from the example above, I have firms that sometimes announce two acquisitions as being completed on the same day (and I don’t have any finer time details like hours etc.) to tsset the data properly. As a result I get an error message saying that I have repeated time values within my panel. The actual ordering of acquisitions that occur on the same day does not matter, I simply need to sum up the number of acquisitions prior to the current one as long as they occurred in a 5 year interval, and it is OK if the ones on the same day are
```off by one acquisition.

I tried something like this:
bysort firmid (date):gen prevacq = sum(acdum)
by firmid: gen prevacq5 = prevacq - prevacq[_n-4)

```
(also borrowed and adapted from a solution to a related question on statalist) but this does not quite work and seems to cap the number of previous acquisitions at 4.
```
```
Out of desperation, I thought of manually adding a day to the date announced as it is the year that is important but there are too many observations for this to be feasible…I think I need a loop of some sort but my STATA loop knowledge is somewhat deficient..
```
```
If anyone has any suggestions on how I can solve this problem, I would be very grateful!
```

Department of International Economics and Business
University of Groningen
The Netherlands

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```

• Follow-Ups: