How to create three month rolling with trading data

Reply
Contributor
Posts: 37

How to create three month rolling with trading data

[ Edited ]

Hi everyone,

 

I have a data structured like this (This is a sample, the original one is much larger). Note that the dates here are trading dates. Trading dates do not contain weekends, holiday or others.

StocksdateAB
……………..……………..……………………
ABC01/04/2011102
ABC04/04/2011115
ABC05/04/2011158
ABC06/04/2011174
ABC………………………515
ABC28/04/2011229
ABC29/04/20113610
ABC02/05/20111215
ABC03/05/20111511
ABC…………………………………….……
XYZ02/12/2010616
XYZ03/12/2010618
XYZ05/12/2010620
XYZ06/12/2010621
………..…………..619
XYZ03/01/2011812
XYZ04/01/201185
XYZ05/01/201188
XYZ06/01/201189
…………………………….

 

Now I would like to create a dataset that contains three-month rolling window for each month. For example, the window for 5/2011 contains all the trading days in May-April-March/2011; the window for 4/2011 contain all trading days for April-March-February/2011.....

Then I need to retain only windows that have at least 30 observations.

StocksWindowDate within windowAB
………….…………………………………
ABC05/201131/05/2011….….
ABC05/201130/05/2011….….
ABC05/201129/05/2011….….
ABC05/201128/05/2011….….
ABC05/201127/05/2011  
ABC05/201126/05/2011  
ABC05/201125/05/2011  
ABC05/201124/05/2011  
ABC05/201123/05/2011  
…………………………..……….………..
XYZ01/201130/01/2011  
XYZ01/201129/01/2011  
XYZ01/201128/01/2011  
XYZ01/201127/01/2011  
XYZ01/201126/01/2011  
XYZ01/201123/01/2011  
XYZ01/201122/01/2011  
XYZ01/201121/01/2011  
XYZ01/201120/01/2011  
……………………..…………….

 

Could anyone show me how to do this. Thank you very much 

Super User
Posts: 21,546

Re: three month roll

Posted in reply to trungcva112

https://communities.sas.com/t5/Base-SAS-Programming/standard-deviation-at-the-end-of-each-month-base...

 

This is 12 months and STD but you should be able to modify it for 3 months. 

Contributor
Posts: 37

Re: three month roll

Hi, it seems that this code could not produce my desired output. Do you have any other ideas?

Super User
Posts: 10,323

Re: How to create three month rolling with trading data

Posted in reply to trungcva112

Here could give you a start.

After running the following code, check the frequency of each window. I think it is easy for you .

 

data have;
infile cards expandtabs truncover;
input Stocks $ date	: ddmmyy10. A	B;
format date	 ddmmyy10.;
cards;
ABC	01/04/2011	10	2
ABC	04/04/2011	11	5
ABC	05/04/2011	15	8
ABC	06/04/2011	17	4
ABC	28/04/2011	22	9
ABC	29/04/2011	36	10
ABC	02/05/2011	12	15
ABC	03/05/2011	15	11
XYZ	02/12/2010	6	16
XYZ	03/12/2010	6	18
XYZ	05/12/2010	6	20
XYZ	06/12/2010	6	21
XYZ	03/01/2011	8	12
XYZ	04/01/2011	8	5
XYZ	05/01/2011	8	8
XYZ	06/01/2011	8	9
;
run;
proc sql;
create table temp as
 select distinct stocks,year(date) as year,month(date) as month,
  mdy(calculated month,1,calculated year) as window format=mmyys7.,
  intnx('month',calculated window,-2) as start format=yymmdd10.,
  intnx('month',calculated window,0,'e') as end format=yymmdd10.
  from have;
  
create table want as
 select a.window,b.*
  from temp as a,have as b 
   where a.stocks=b.stocks and b.date between a.start and a.end;
quit;
Trusted Advisor
Posts: 1,149

Re: How to create three month rolling with trading data

[ Edited ]
Posted in reply to trungcva112

Given an average of 20 trading days per month and one observation per trading day, you want to multiply the size of your dataset by 60 3

 

Why?

 

What do you want to do with the rolling windows?  If you are creating rolling window statistics  (i.e. one observation per rolling window, which is 5% of the original number of observations), then there are many ways to generate them without writing all that data to disk.

 

 

Ask a Question
Discussion stats
  • 4 replies
  • 155 views
  • 0 likes
  • 4 in conversation