topic Re: How to create three month rolling with trading data in SAS Programming

How to create three month rolling with trading data

trungcva112 — Thu, 04 Jun 2020 00:52:32 GMT

Re: three month roll

Reeza — Mon, 12 Feb 2018 03:40:43 GMT

https://communities.sas.com/t5/Base-SAS-Programming/standard-deviation-at-the-end-of-each-month-based-on-daily/m-p/436118

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

Re: three month roll

trungcva112 — Mon, 12 Feb 2018 08:47:21 GMT

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

Re: How to create three month rolling with trading data

Ksharp — Mon, 12 Feb 2018 11:25:09 GMT

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;

Re: How to create three month rolling with trading data

mkeintz — Mon, 12 Feb 2018 15:50:31 GMT

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.