I would like to estimate b coefficients for each stock by each year based on a regression model as follows: r = a + b1 x rm(-2) + b2 x rm(-1) +b3 x rm + b4 x rm(1) + b5 x rm(2), where r and rm are stock and market return respectively. The data is represented below, please kindly help. Thank you for the support.
STOCK | DATE | STOCKRETURN | MARKETRETURN |
AAA | 6/15/2012 | 0.19 | 0.29 |
AAA | 6/16/2012 | 0.19 | 0.29 |
AAA | 6/17/2012 | 0.2 | 0.3 |
AAA | 6/18/2012 | 0.2 | 0.3 |
AAA | 6/19/2012 | 0.21 | 0.31 |
AAA | 6/20/2012 | 0.21 | 0.31 |
AAA | 6/21/2012 | 0.2 | 0.3 |
AAA | 6/22/2012 | 0.19 | 0.29 |
AAA | 6/23/2012 | 0.19 | 0.29 |
AAA | 6/24/2012 | 0.19 | 0.29 |
AAA | 6/25/2012 | 0.19 | 0.29 |
AAA | 6/26/2012 | 0.2 | 0.3 |
AAA | 6/27/2012 | 0.2 | 0.3 |
AAA | 6/28/2012 | 0.2 | 0.3 |
AAA | 6/29/2012 | 0.2 | 0.3 |
ABC | 6/15/2012 | 0.19 | 0.29 |
ABC | 6/16/2012 | 0.21 | 0.31 |
ABC | 6/17/2012 | 0.21 | 0.31 |
ABC | 6/18/2012 | 0.19 | 0.29 |
ABC | 6/19/2012 | 0.2 | 0.3 |
ABC | 6/20/2012 | 0.21 | 0.31 |
ABC | 6/21/2012 | 0.21 | 0.31 |
ABC | 6/22/2012 | 0.21 | 0.31 |
ABC | 6/23/2012 | 0.24 | 0.34 |
ABC | 6/24/2012 | 0.25 | 0.35 |
ABC | 6/25/2012 | 0.25 | 0.35 |
ABC | 6/26/2012 | 0.25 | 0.35 |
ABC | 6/27/2012 | 0.24 | 0.34 |
ABC | 6/28/2012 | 0.24 | 0.34 |
ABC | 6/29/2012 | 0.24 | 0.34 |
Two ways to do this, with or without SAS/ETS :
data have;
format date yymmdd10.;
input STOCK $ DATE :mmddyy10. STOCKRETURN MARKETRETURN;
datalines;
AAA 6/15/2012 0.19 0.29
AAA 6/16/2012 0.19 0.29
AAA 6/17/2012 0.2 0.3
AAA 6/18/2012 0.2 0.3
AAA 6/19/2012 0.21 0.31
AAA 6/20/2012 0.21 0.31
AAA 6/21/2012 0.2 0.3
AAA 6/22/2012 0.19 0.29
AAA 6/23/2012 0.19 0.29
AAA 6/24/2012 0.19 0.29
AAA 6/25/2012 0.19 0.29
AAA 6/26/2012 0.2 0.3
AAA 6/27/2012 0.2 0.3
AAA 6/28/2012 0.2 0.3
AAA 6/29/2012 0.2 0.3
ABC 6/15/2012 0.19 0.29
ABC 6/16/2012 0.21 0.31
ABC 6/17/2012 0.21 0.31
ABC 6/18/2012 0.19 0.29
ABC 6/19/2012 0.2 0.3
ABC 6/20/2012 0.21 0.31
ABC 6/21/2012 0.21 0.31
ABC 6/22/2012 0.21 0.31
ABC 6/23/2012 0.24 0.34
ABC 6/24/2012 0.25 0.35
ABC 6/25/2012 0.25 0.35
ABC 6/26/2012 0.25 0.35
ABC 6/27/2012 0.24 0.34
ABC 6/28/2012 0.24 0.34
ABC 6/29/2012 0.24 0.34
;
proc sql;
create table have0 as
select a.stock, a.date, a.stockreturn, intck("DAY", a.date, b.date) as lag,
b.marketReturn as mr
from have as a inner join have as b
on a.stock=b.stock and intck("DAY", a.date, b.date) between -2 and 2
order by a.stock, a.date, lag;
proc transpose data=have0
out=want(drop=_name_) prefix=mr;
by stock date stockreturn;
id lag;
var mr;
run;
/* Or, if you have SAS/ETS, use proc expand */
proc expand data=have out=want;
by stock;
id date;
convert marketreturn=mr_2 / transform=(lag 2);
convert marketreturn=mr_1 / transform=(lag 1);
convert marketreturn=mr0;
convert marketreturn=mr1 / transform=(lead 1);
convert marketreturn=mr2 / transform=(lead 2);
run;
/* Do the regressions, requesting the Durbin-Watson test for autocorrelation */
proc reg data=want;
by stock;
model stockreturn = mr_2 mr_1 mr0 mr1 mr2 / dwProb;
run;
PG
Two ways to do this, with or without SAS/ETS :
data have;
format date yymmdd10.;
input STOCK $ DATE :mmddyy10. STOCKRETURN MARKETRETURN;
datalines;
AAA 6/15/2012 0.19 0.29
AAA 6/16/2012 0.19 0.29
AAA 6/17/2012 0.2 0.3
AAA 6/18/2012 0.2 0.3
AAA 6/19/2012 0.21 0.31
AAA 6/20/2012 0.21 0.31
AAA 6/21/2012 0.2 0.3
AAA 6/22/2012 0.19 0.29
AAA 6/23/2012 0.19 0.29
AAA 6/24/2012 0.19 0.29
AAA 6/25/2012 0.19 0.29
AAA 6/26/2012 0.2 0.3
AAA 6/27/2012 0.2 0.3
AAA 6/28/2012 0.2 0.3
AAA 6/29/2012 0.2 0.3
ABC 6/15/2012 0.19 0.29
ABC 6/16/2012 0.21 0.31
ABC 6/17/2012 0.21 0.31
ABC 6/18/2012 0.19 0.29
ABC 6/19/2012 0.2 0.3
ABC 6/20/2012 0.21 0.31
ABC 6/21/2012 0.21 0.31
ABC 6/22/2012 0.21 0.31
ABC 6/23/2012 0.24 0.34
ABC 6/24/2012 0.25 0.35
ABC 6/25/2012 0.25 0.35
ABC 6/26/2012 0.25 0.35
ABC 6/27/2012 0.24 0.34
ABC 6/28/2012 0.24 0.34
ABC 6/29/2012 0.24 0.34
;
proc sql;
create table have0 as
select a.stock, a.date, a.stockreturn, intck("DAY", a.date, b.date) as lag,
b.marketReturn as mr
from have as a inner join have as b
on a.stock=b.stock and intck("DAY", a.date, b.date) between -2 and 2
order by a.stock, a.date, lag;
proc transpose data=have0
out=want(drop=_name_) prefix=mr;
by stock date stockreturn;
id lag;
var mr;
run;
/* Or, if you have SAS/ETS, use proc expand */
proc expand data=have out=want;
by stock;
id date;
convert marketreturn=mr_2 / transform=(lag 2);
convert marketreturn=mr_1 / transform=(lag 1);
convert marketreturn=mr0;
convert marketreturn=mr1 / transform=(lead 1);
convert marketreturn=mr2 / transform=(lead 2);
run;
/* Do the regressions, requesting the Durbin-Watson test for autocorrelation */
proc reg data=want;
by stock;
model stockreturn = mr_2 mr_1 mr0 mr1 mr2 / dwProb;
run;
PG
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.