Please note that this content has already been posted in statalist. Re-posted here beacuse link to my dta.file doesn't work in statalist. here's the link on that posting: http://www.statalist.org/forums/forum/general-stata-discussion/general/852325-generating-cumulative-sum-of-deal-values-for-3-year-intervals
I'm trying to generate cumulative sum of deal values (£ mn.) for acquisitions of target companies in an interval of 3 years prior to any acquisition announcement. I've tried using a similar code as given in Stata Journal (2007) 7: Events in intervals, but can't get desired result.
The code i've used to generate this sum (i'm not sure how to generate cumulative sum) as yr3_cum_dealval in an interval of 3 years:
gen yr3_cum_dealval = .
quietly forval i = 1/`= _N' {
sum (deal_value_mn) if lspdno == lspdno[`i'] & inrange(event_date[`i'] – event_date, 1, 1098)
replace yr3_cum_dealval = r(sum) in `i'
}
Here, deal_value_mn is the amount paid in £ mn. for each acquisition, lspdno is the unique id for each acquiring company, event_date date of announcement of each deal/acquisition, 1098 = 366*3 (3 years interval).
I don't understand in the column yr3_cum_dealval, why i always get '0' as starting deal value in the span of 3 years, when it should rather be the actual amount paid in the first acquisition, the sum of 1st deal amount and 2nd deal amount for the second acquisition, likewise the sum of first two amounts paid plus that paid for the 3rd in case of cumulative deal value for third acquisition (provided the first two acquisitions took place within 3 years of the third deal) and so on. Also, when there were 2 acquisitions announced on the same date, the cumulative deal value for each of these dates should be different (i get same yr3_cum_dealval for 2 same date acquisitions), the 1st same date should have the sum of amounts paid for the prior 3 year deals as well as current deal amount, whereas the 2nd same date should have the first same event_date amount paid included in the cumulative sum with prior 3 year amounts, as well as the amount corresponding to the current acquisition.
I'm pasting dropbox link to dta.file with a snapshot of my data for just 1 company doing many acquisitions from year 1994 until 2006, where the column with name yr3_cum_dealval shows what i got using the above code, while Correct_yr3_cum_dealval shows what i need to get.
https://dl.dropboxusercontent.com/u/64545449/cumul_dealvalue_ranks.dta
Please help me to correct this code.
Thanks, Suparna Ray
The relevant part of your sample data is given here together with another variable wanted. (Posting sample data directly like this is often more helpful than posting a link to a file.)
. l lspdno deal_value_mn event_date yr3 correct event_date wanted
+--------------------------------------------------------------------------+
| lspdno deal_v~n event_d~e yr3_cu~l correc~l event_d~e wanted |
|--------------------------------------------------------------------------|
1. | 286 214.1 03oct1994 0 214.1 03oct1994 214.1 |
2. | 286 100 11jul1995 214.1 314.1 11jul1995 314.1 |
3. | 286 126.1 19oct1998 0 126.1 19oct1998 126.1 |
4. | 286 214.1 01nov1999 126.1 340.2 01nov1999 340.2 |
5. | 286 214.1 26may2000 340.2 554.3 26may2000 554.3 |
|--------------------------------------------------------------------------|
6. | 286 214.1 06jul2000 554.3 768.4 06jul2000 768.4 |
7. | 286 5.6 01aug2000 768.4 774 01aug2000 774 |
8. | 286 58.4 04jul2002 647.9 492.2 04jul2002 706.3 |
9. | 286 170.6 08oct2002 706.3 662.8 08oct2002 876.9 |
10. | 286 214.1 31dec2002 662.8 876.9 31dec2002 876.9 |
|--------------------------------------------------------------------------|
11. | 286 214.1 22jul2004 443.1 657.2 22jul2004 1395.4 |
12. | 286 738.2 22jul2004 443.1 1395.4 22jul2004 1395.4 |
13. | 286 214.1 25sep2005 1337 1551.1 25sep2005 1551.1 |
14. | 286 299.8 19may2006 1166.4 1466.2 19may2006 1680.3 |
15. | 286 214.1 19may2006 1166.4 1680.3 19may2006 1680.3 |
+--------------------------------------------------------------------------+
I see three confusions here:
First, as already indicated in comments, as you want to include the current transaction, your inrange() condition should be
inrange(event_date[`i'] - event_date, 0, 1098)
N.B. 0 not 1.
Second, your indications of what is correct seem to be partly incorrect: in particular observations 11 and 12 are for the same date, as are 14 and 15. As I understand it, the values of the variable you want should therefore be the same, not different.
Third, you are thinking of this as a cumulative sum, but as least as far as Stata is concerned it's a sum in an interval and cumulative sum syntax is irrelevant. Note that the function (not command) sum() is what returns cumulative sums.
The variable wanted was calculated by
gen wanted = .
quietly forval i = 1/`=_N' {
sum (deal_value_mn) if lspdno == lspdno[`i'] & inrange(event_date[`i'] - event_date, 0, 1098)
replace wanted = r(sum) in `i'
}