topic Re: Create rolling 12 month window for daily data in SAS Procedures

Create rolling 12 month window for daily data

yotsuba88 — Thu, 17 Nov 2016 04:06:36 GMT

Hi all,

I used the code to create rolling window for 12 months (t-12) to calculate R2, residual... for month t .

For ex,

01JAN2000 to 31DEC2000 --> JAN2001

01FEB2000 to 31JAN2001 ---> FEB 2001

01MAR2000 to 31FEB2001 ---> MAR 2001

.....

I can run with monthly data, but for DAILYdata I cannot use this code:

proc sql;
create table Form100
as select distinct a.permno, a.date as end, b.ret, b.date
from crsp3 (keep=permno date) as a, crsp3 as b
where a.permno=b.permno and 1<=intck("month", b.date, a.date)<&J and 0<=intck("year",b.date,a.date)<=1
group by a.permno, a.date;
quit;

Could you please me with this? THank you very much.

Re: Create rolling 12 month window for daily data

Shmuel — Thu, 17 Nov 2016 04:49:43 GMT

Do you mean:

where a.permno=b.permno and /* 1<=intck("month", b.date, a.date)<&J and 0<=intck("year",b.date,a.date)<=1 */

b.date <= intnx('year',a.date,1,same)

what do you have in &j ? - did you assigned it a value ?

Re: Create rolling 12 month window for daily data

yotsuba88 — Thu, 17 Nov 2016 05:05:07 GMT

Yes, I did assign it J=12

Re: Create rolling 12 month window for daily data

Shmuel — Thu, 17 Nov 2016 05:21:34 GMT

have you tried change the where clause as offered ?

Re: Create rolling 12 month window for daily data

yotsuba88 — Thu, 17 Nov 2016 05:27:14 GMT

Thank you so much, I did try, but a.date just shifts one day, and I need it shift for one month.

Re: Create rolling 12 month window for daily data

Shmuel — Thu, 17 Nov 2016 05:35:48 GMT

sorry it is not clear enough to me.

give an example by values of a.date= ... b.date= .... valid period from date= to date=

Re: Create rolling 12 month window for daily data

yotsuba88 — Thu, 17 Nov 2016 05:42:32 GMT

I mean

b. date from 01JAN2000 to 31DEC2000 --> it will be used for hold year to calculate in JAN2001 (a.date, can be 01Jan2001 or 31 jan2001, it is not important)

b. date from 01FEB2000 to 31JAN2001 ---> will be used for next month in FEB 2001 (a.date shift one month)

b. date from 01MAR2000 to 31FEB2001 ---> MAR 2001

Re: Create rolling 12 month window for daily data

Reeza — Thu, 17 Nov 2016 06:49:05 GMT

You could also consider creating a table with the start/end dates that feed your intervals and then use that in your query when joining.

Re: Create rolling 12 month window for daily data

yotsuba88 — Thu, 17 Nov 2016 07:39:37 GMT

Thank you, I am a beginner of Sas so I am trying to find how to create table as you said. But I am running daily stock return for US market, could it use for very large data.

Re: Create rolling 12 month window for daily data

Reeza — Thu, 17 Nov 2016 09:56:12 GMT

How large is your data? Do you need to add in all the data for the 12 months or are you trying to calculate some sort of statistics across the interval. Otherwise you're going to be multiplying the size of your data significantly since each record will belong to multiple intervals.

Also, do you have SAS/ETS? Have you looked at PROC TIMESERIES or EXPAND?

Re: Create rolling 12 month window for daily data

Ksharp — Thu, 17 Nov 2016 10:13:20 GMT

it is good for SQL to get ROLLING window.

code not tested.

proc sql;
create table Form100
as
 select a.permno, a.date as end, 

(select sum(residual) from crsp3 where permno=a.permno 
 and date between a.date and intnx('year',a.date,1,'s')-1 )
as sum_residual

from crsp3 (keep=permno date) as a

group by a.permno, a.date;
quit;

Re: Create rolling 12 month window for daily data

yotsuba88 — Thu, 17 Nov 2016 13:17:53 GMT

Thank you, I am going to check now. But I want create rolling window to calculate R2, residual (must use proc preg for regression market model) and skewness.

So can i combine proc preg and proc sql? and how about skewness?

Thank you so much,

Re: Create rolling 12 month window for daily data

yotsuba88 — Thu, 17 Nov 2016 13:25:11 GMT

My data has 27.612.898 observations. I want to create to run proc preg for R2 and residual (CAPM model) and skewness (has formula). If data is too large, I just use it for calculate one by one, if I can find solution.

Thank you so much for suggestion, I am just gg search and reading how to use but I have no idea until now.

Re: Create rolling 12 month window for daily data

mkeintz — Fri, 18 Nov 2016 02:27:40 GMT

How many stocks, for how many windows, do you intend to process? The CRSP (Center for Research in Stock Prices) that you are using has daily data on thousands of stocks going back to 1929, so your potential universe is big.

SQL is NOT the solution to such a problem. It's going to compare EVERY record in "crsp as b" to each record in "crsp as a" to determine which records to put in the window. In other words, SQL will ignore the data order (PERMNO/DATE) in most CRSP data sets. Even if SAS marks the dataset as sorted, I suspect SQL won't take advantage (but that could be tested).

And consider the sheer size of the data you want. I suppose you want a new window every month, so that's 12 windows per year. With about 200 trading days per 12 months in each window, you are asking for each stock about 2400 records for a year of data. So multiply N(stocks)*N(years)*2400*(record size) to determine your disk space requirements.

But wait -- there's more! You apparently plan to do a regression on each of the windows, meaning you intend to recalculate sums of squares for 12 month windows, even though you could inherit 11 of those monthly sum-of-squares from the prior window.

Unless you have an unusually small portfolio and date range, try to avoid making a data set of the base data for every windows.

Now that I've sounded the alarm, it is possible that you might get away with making a data set VIEW as opposed to a data set FILE. You could then submit that view to a PROC REG, with a BY WINDOWID statement. It would still be calculating sums-of-squares 12 times as much as needed, but you'd reduce disk space requirements.

Regards,
Mark

Re: Create rolling 12 month window for daily data

mkeintz — Fri, 18 Nov 2016 03:03:08 GMT

These two papers I presented at NESUG discuss (1) how to make rolling windows of the original data, and (2) how to make rolling SSCP matrices ready for use in PROC REG. Did you know that there is a sas data set TYPE=SSCP that proc reg accepts? Take a look at these:

I'd probably avoid making your own implementation of Cramer's rule (I wrote that mostly as an exercise), but even so, the second paper probably provides a better guide to making the rolling TYPE=SSCP data set for to PROC REG. Now remember, submitting an SSCP matrix means you can directly produce TOTAL residuals for each window, but not day-by-day residuals. To do that, you'd have to take the intercept and coefficients estimates for each window and run them against your data.

Regards,

Mark

Re: Create rolling 12 month window for daily data

yotsuba88 — Fri, 18 Nov 2016 03:04:11 GMT

Thank you very much for your suggestion. So I think I will try to another code to solve this.

Re: Create rolling 12 month window for daily data

yotsuba88 — Fri, 18 Nov 2016 03:13:00 GMT

Hi Mark,

I really appreciate your support, I am going to find out my code based on your papers now. Hope I can do it.

Have a good day.

Re: Create rolling 12 month window for daily data

mkeintz — Fri, 18 Nov 2016 03:20:08 GMT

@yotsuba88: what other code do you imagine would solve this problem?

Re: Create rolling 12 month window for daily data

yotsuba88 — Fri, 18 Nov 2016 03:31:36 GMT

I dont know, I read your alarm message first then your 2 papers. So it is misunderstood, I am reading your papers and trying to apply them.

Best,

Re: Create rolling 12 month window for daily data

Ksharp — Fri, 18 Nov 2016 07:22:34 GMT

Oh. You need use IML or make a macro to call PROC REG.
Some skeleton code like.


data date;
 set have(keep=date);
end_date=intnx('month',date,12,'e');
run;

data _null_;
 set date;
call execute('proc reg data=have(where=(date between '|| date ||' and '||end_date ||' ))  outest=xxx'|| strip(_n_)||'  ;');
..............
run;

data want;
 set xxx:;
run;