SD of lagged variable

[Joshua Tievoor]

Good day all,

I have a dataset consisting of monthly prices per id. I need to calculate the standard deviation of prices over 60 months prior to the end of year t-1.

I tried the below but it is not working for me.

Does anyone know how I can best do this in Stata?

by id egen sdprice=sd(L72.price+L71.price+...  ...+L13.price+L12.price)

[Carlo Lazzaro]

Joshua:
you might be interested in the -rowsd()- function available with -egen-.

[Marcos Almeida]

Hello Joshua,

Just as a start up,albeit not related to lagged variables, instead of "by id egen newvar=sd(oldvar)" you should type:

. by id, sort: egen newvar = sd(oldvar)

Best,

Marcos

[Robert Picard]

Also, take a look at tsegen and rangestat, both available from SSC.

[Joshua Tievoor]

Thanks Robert!

This seems exactly what I need.

Just to double check if my understanding is correct.

In order to resolve my example "the standard deviation of prices over 60 months prior to the end of year t-1", I would use?

tsegen newvar = rowsd(L(12/71).price)

[Robert Picard]

I think that's right. Watch out for missing data; you can request that the results be set to missing if they are based on less than 60 months of data. Here's a quick illustration of how to use both tsegen and rangestat (both from SSC). It's always good to spot check the results to make sure that you set up the commands correctly. I also include the min/max of the monthly date that fall within each observation's 60 month window. This makes it easier to check the offsets used.

* monthly price data for 2 firms over 30 years
clear
set obs 2
gen firm = _n
expand 360
bysort firm: gen yearm = ym(1986,6)  + _n
format %tm yearm
xtset firm yearm
gen price = 100 + _n

* get the sd over 60 month, the number of non-missing obs, and the
* start and end value of yearm
tsegen double sd60 = rowsd(L(12/71).price)
tsegen double sd60b = rowsd(L(12/71).price,60)
tsegen n = rownonmiss(L(12/71).price)
tsegen low_ym = rowmin(L(12/71).yearm)
tsegen high_ym = rowmax(L(12/71).yearm)
format *_ym %tm

* double-check a case
list in 100
sum price if firm == firm[100] & inrange(yearm,yearm[100]-71,yearm[100]-12), format
assert abs(sd60[100]-r(sd)) < 1e-15

* you can do all of the above in a single rangestat call
rangestat (sd) price (count) price (min) yearm (max) yearm, by(firm) interval(yearm -71 -12)
format yearm_* %tm

assert abs(sd60-price_sd) < 1e-15 if !mi(sd60)

The output for the spot check is:

. * double-check a case
. list in 100

     +-------------------------------------------------------------------------+
     | firm     yearm   price        sd60       sd60b    n    low_ym   high_ym |
     |-------------------------------------------------------------------------|
100. |    1   1994m10     200   17.464249   17.464249   60   1988m11   1993m10 |
     +-------------------------------------------------------------------------+

. sum price if firm == firm[100] & inrange(yearm,yearm[100]-71,yearm[100]-12), format

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
       price |         60       158.5    17.46425        129        188

. assert abs(sd60[100]-r(sd)) < 1e-15

.