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.
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;
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;
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
/*
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;
Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.