Counting values over a rolling time window with unregular observations frequency

[David Alexander]

Hello,

I intend to count every positive outcome of a dummy variable over a rolling time window for each company in the dataset. The difficulty is that the my observations are based on transactions that do not occur on a regular frequency. Therefore I cannot use the following code which I found in the internet as this one assumes that there is for every day a observation for each company.

bysort CompanyID (Date) : gen Rolling_Sum_Dummy_per_Company = sum(Dummy) - sum(Dummy[_n-3])

Is there a way to calculate Rolling_Sum_Dummy_per_Company for such a dataset structure? I simply assumed that every month has 30 days for this example and the rolling variable counts all positive outcomes within the last 30 days.

CompanyID----------------Date----------------------Dummy----------Sum_Dummy_per_Company---------------Rolling_Sum_Dummy_per_Company
1----------------------5. January 2000--------------------1-------------------------------1-------------------------------------------------------------1
1----------------------1. February 2000-------------------1-------------------------------2-------------------------------------------------------------2
1----------------------15. February 2000----------------- 0------------------------------2-------------------------------------------------------------1
1----------------------1. March 2000-----------------------1-------------------------------3-------------------------------------------------------------2
1----------------------14. March 2000---------------------1-------------------------------4-------------------------------------------------------------2
1----------------------13. April 2000------------------------0-------------------------------4------------------------------------------------------------1
1----------------------14. Mai 2000-------------------------0-------------------------------4-------------------------------------------------------------0
2 ...
2 ...
3 ...
3 ...
3 ...
...

Many thanks!

[Clyde Schechter]

So, of course, Date needs to be a Stata internal date variable in order for anything good to come of it. I assume you have it that way.

rangestat (sum) Dummy, interval(Date, -29, 0) by(CompanyID) describe

Note: This calculation uses a window that includes the current date and goes back 30 days from there. If you want the preceding 30 days but not including the current one, change -29, 0 to -30, -1.

To get the -rangestat- command, run -ssc install rangestat-. It is a new command designed for purposes like this by Robert Picard, Roberto Ferrer, and Nick Cox.

In the future, you are requested to post example data using the -dataex- command so that those who would like to help you can easily and faithfully reproduce the data situation you actually face. Listings of the type shown in #1 are not helpful: it takes longer to clean them up and put them into Stata than to solve your problem! Please see FAQ #12 for additional advice on the best way to post code and results on this forum.