Stata The Stata listserver
[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

Re: st: How to roll up data with a time window and condition


From   "Eric G. Wruck" <ewruck@econalytics.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: How to roll up data with a time window and condition
Date   Mon, 7 Nov 2005 14:10:28 -0500

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:   ewruck@econalytics.com
       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/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index