Help with rangestat calculating moving average results in all missing values

[Bob Honda]

Hello I am trying to calculate the rolling average of the past 36 months (periods) and when I run the code using rangestat the new variable results in all missing values. I am not sure how to fix this and output the correct calculation.

Here is an example of my sample:

input double date long _ID float country_pdsi
4.733856e+11 9  1.5002956
 4.76064e+11 9  1.7449586
4.784832e+11 9  1.6589595
4.811616e+11 9  1.4847733
4.837536e+11 9  1.0025214
 4.86432e+11 9   .8517559
 4.89024e+11 9   .7509255
4.917024e+11 9   .7988663

format %tc date

This is my code to calculate the 36 month moving average.

xtset _ID date
rangestat (mean) country_pdsi, interval(date -36 -1) by(_ID)

[Andrew Musau]

I think the issue is with your time variable. A datetime variable is expressed in milliseconds and you really do not want to do the math to convert milliseconds to months. I would suggest creating a daily time variable and then calculate months on the basis of that.

gen date2= dofc(date)
format date2 %td

Then you can express 36 months as 36*30 = 1080 days.

[Nick Cox]

I agree with Andrew Musau in the problem being your datetime variable.

Let's reinstate a few details edited out of dataex output. and see what's going on.

clear
input double date long _ID float country_pdsi
4.733856e+11 9  1.5002956
 4.76064e+11 9  1.7449586
4.784832e+11 9  1.6589595
4.811616e+11 9  1.4847733
4.837536e+11 9  1.0025214
 4.86432e+11 9   .8517559
 4.89024e+11 9   .7509255
4.917024e+11 9   .7988663
end 
format date %tc 

list , sep(0)

     +-------------------------------------+
     |               date   _ID   countr~i |
     |-------------------------------------|
  1. | 01jan1975 00:00:00     9   1.500296 |
  2. | 01feb1975 00:00:00     9   1.744959 |
  3. | 01mar1975 00:00:00     9    1.65896 |
  4. | 01apr1975 00:00:00     9   1.484773 |
  5. | 01may1975 00:00:00     9   1.002521 |
  6. | 01jun1975 00:00:00     9   .8517559 |
  7. | 01jul1975 00:00:00     9   .7509255 |
  8. | 01aug1975 00:00:00     9   .7988663 |
     +-------------------------------------+

So, somehow or other you have monthly dates. Your command says average all measurements that happened between 1 and 36 milliseconds before the present datetime, but there aren't any, which is why rangestat returns missings. Unlike its authors, rangestat is just a robot incapable of working out what you mean when it is not what you say.

The remedy is to map to monthly dates (here I differ slightly from Andrew)

gen mdate = mofd(dofc(date)) 
format mdate %tm

  
 rangestat (mean) country_pdsi, interval(mdate -36 -1) by(_ID)

[Bob Honda]

Thank you very much for you help! I was able to get the correct output by fixing the format of the date variable.

[Nick Cox]

It's not the format that needs to be changed; it's the contents!