## how to save the r2 from regressions as a new variable--rolling basis

Hello,

I have a question about how to retrieve the r-square from regressions and store it as a new variable on a rolling basis.

I have daily stock return and daily market return data. For each day, I would like to generate a new variable called sync, and that variable will be the r-square from this regression: Rit = Rmt + Rmt-1 in the next 90-day window, where Rit is the daily stock return and Rmt is the daily market return. The final dataset would look like this:

FirmID     Date      Rit      Rmt      Rmt-1      Sync

1        1/1/23    0.01    0.005    0.006       a1

1        1/2/23    0.02    0.006    0.005       a2

1        1/3/23    0.05    0.003    0.006       a3

...

2        1/1/23    0.06    0.005    0.006       a4

2        1/2/23    0.07    0.006    0.005       a5

...

where a1 is the r-square of the regression Rit = Rmt + Rmt-1 from 1/1/23 to 4/1/23

a2 is the r-square of the regression Rit = Rmt + Rmt-1 from 1/2/23 to 4/2/23

a3 is the r-square of the regression Rit = Rmt + Rmt-1 from 1/3/23 to 4/3/23

1 ACCEPTED SOLUTION

Accepted Solutions  Ksharp
Super User

## Re: how to save the r2 from regressions as a new variable--rolling basis

``````data have;
set sashelp.stocks(keep=stock open high low
rename=(stock=FirmID open=Rit high=Rmt low=Rmt_1));
by FirmID;
if first.FirmID then date=0;
date+1;
format date date9.;
run;

data all;
if _n_=1 then do;
declare hash h(dataset:'have');
h.definekey('FirmID','date');
h.definedata(all:'y');
h.definedone();
end;
set have;
id+1;
_FirmID=FirmID; _date=date; _Rit=Rit; _Rmt=Rmt; _Rmt_1=Rmt_1;
do i=date+1 to date+90;
if h.find(key:FirmID,key:i)=0 then output;
end;
format _date date9.;
drop i;
run;

proc reg data=all OUTEST=want(keep=id _FirmID _date _Rit _Rmt _Rmt_1 _RSQ_) rsquare noprint;
by id _FirmID _date _Rit _Rmt _Rmt_1;
model Rit=Rmt Rmt_1;
quit;``````
3 REPLIES 3  Ksharp
Super User

## Re: how to save the r2 from regressions as a new variable--rolling basis

``````data have;
set sashelp.stocks(keep=stock open high low
rename=(stock=FirmID open=Rit high=Rmt low=Rmt_1));
by FirmID;
if first.FirmID then date=0;
date+1;
format date date9.;
run;

data all;
if _n_=1 then do;
declare hash h(dataset:'have');
h.definekey('FirmID','date');
h.definedata(all:'y');
h.definedone();
end;
set have;
id+1;
_FirmID=FirmID; _date=date; _Rit=Rit; _Rmt=Rmt; _Rmt_1=Rmt_1;
do i=date+1 to date+90;
if h.find(key:FirmID,key:i)=0 then output;
end;
format _date date9.;
drop i;
run;

proc reg data=all OUTEST=want(keep=id _FirmID _date _Rit _Rmt _Rmt_1 _RSQ_) rsquare noprint;
by id _FirmID _date _Rit _Rmt _Rmt_1;
model Rit=Rmt Rmt_1;
quit;``````

## Re: how to save the r2 from regressions as a new variable--rolling basis

Hi Ksharp,

Thank you very much for your codes. They work well until the last step. Because of the missing stock return variables, SAS log keeps giving me: "ERROR: No valid observations are found" and the log window will be full. I have to clear the log window every few seconds and because I have millions of obs, it will take me forever. I wonder do you have any suggestions for this? Thank you very much.

Best,

skyland1991  Ksharp
Super User

## Re: how to save the r2 from regressions as a new variable--rolling basis

``````/*
You can remove these missing value before
running my code.
*/
data have;
set sashelp.stocks(keep=stock open high low
rename=(stock=FirmID open=Rit high=Rmt low=Rmt_1));
by FirmID;
if first.FirmID then date=0;
date+1;
format date date9.;

if nmiss(Rit,Rmt,Rmt_1) then delete;  /*<--------*/
run;``````
Discussion stats
• 3 replies
• 194 views
• 0 likes
• 2 in conversation