Stock return regression

[onhoaian]

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

[PGStats · Accepted solution]

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