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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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