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

[skyland1991]

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

Thank you for your help.

[Ksharp · Accepted solution]

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;

[skyland1991]

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]

/*
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;