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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.