I saw that Nick gave you some suggestions that are probably far more elegant than my primitive approach but here goes:
. l, noobs
+------------------------------+
| company year qtr sales |
|------------------------------|
| ABC 1990 1 10 |
| ABC 1990 2 11 |
| ABC 1990 3 15 |
| ABC 1990 4 10 |
| ABC 1991 1 20 |
|------------------------------|
| BBC 1989 1 20 |
| BBC 1989 2 10 |
| BBC 1989 3 20 |
| BBC 1989 4 10 |
| BBC 1990 1 15 |
|------------------------------|
| BBC 1990 3 10 |
+------------------------------+
. gen sales4q = sales + sales[_n-1] + sales[_n-2] + sales[_n-3] if company == company[_n-3]
(6 missing values generated)
. l, noobs
+----------------------------------------+
| company year qtr sales sales4q |
|----------------------------------------|
| ABC 1990 1 10 . |
| ABC 1990 2 11 . |
| ABC 1990 3 15 . |
| ABC 1990 4 10 46 |
| ABC 1991 1 20 56 |
|----------------------------------------|
| BBC 1989 1 20 . |
| BBC 1989 2 10 . |
| BBC 1989 3 20 . |
| BBC 1989 4 10 60 |
| BBC 1990 1 15 55 |
|----------------------------------------|
| BBC 1990 3 10 55 |
+----------------------------------------+
I typed one value in wrong (11 instead of 10) & so I'm off by one but otherwise I seem to produce the results you had in mind. I assumed the data were sorted by company year & quarter. I should point out that my method (& I don't know what Nick's suggestions would do on this score) disregards any gaps in the data. For your BBC example, your data go from 1990.Q1 to 1990.Q3 -- a fact my <gen sales4q> statement ignores.
Hope this helps.
Eric
>I would like to roll up the past 4 quarters of sales for each company:
>
>Company Year Quarters Sales 4_Mo_Sales
>ABC 1990 1 10
>ABC 1990 2 10
>ABC 1990 3 15
>ABC 1990 4 10 45
>ABC 1991 1 20 55
>BBC 1989 1 20 BBC 1989 2 10 BBC
>1989 3 20
>BBC 1989 4 10 60
>BBC 1990 1 15 55
>BBC 1990 3 10 35
>
>The resulting calculation I am looking to get is in 4_Mo_Sales.
>
>Note the following:
>
>1) The first 3 quarters don't have 4 full quarters, so I don't calculate the 4_Mo_Sales for that 3 quarters
>
>2) For BBC, quarter 2 in 1990 is missing, so I only sum up 3 quarters and got 35 in the past 4 quarters
>
>Thanks so much!
>
>Lynda
>
>*
>* For searches and help try:
>* http://www.stata.com/support/faqs/res/findit.html
>* http://www.stata.com/support/statalist/faq
>* http://www.ats.ucla.edu/stat/stata/
--
===================================================
Eric G. Wruck
Econalytics
2535 Sherwood Road
Columbus, OH 43209
ph: 614.231.5034
cell: 614.330.8846
eFax: 614.573.6639
eMail: [email protected]
website: http://www.econalytics.com
====================================================
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
