BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
skyland1991
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

3 REPLIES 3
Ksharp
Super User
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
Obsidian | Level 7

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

sas-innovate-white.png

Missed SAS Innovate in Orlando?

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.

 

Register now

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 872 views
  • 0 likes
  • 2 in conversation