Stock Beta using proc expand ?

[Inquisitive101]

Hi

Is there a way to calculate 5 day rolling beta ie., returns relative to market return using Proc expand ? See data below - Ret is the individual stock return and mkt_ret is the market return

data temp;
input DAT1 mmddyy11. ID $ RET MKT_RET ;
cards;
9/1/2016 AA 0.0001 0.0001
9/2/2016 AA 0.000279 -0.002973
9/6/2016 AA -0.006091 0.000146
9/7/2016 AA 0.026914 0.002228
9/8/2016 AA 0.023175 0.025139
9/9/2016 AA -0.021908 -0.014465
9/12/2016 AA -0.023251 0.015054
9/13/2016 AA -0.034177 0.000588
9/14/2016 AA -0.032881 -0.010008
9/15/2016 AA 0.005656 0.003787
9/1/2016 ADM 0.0001 0.0001
9/2/2016 ADM 0.000023 -0.002973
9/6/2016 ADM 0.000094 0.000146
9/7/2016 ADM 0.000023 0.002228
9/8/2016 ADM 0.000023 0.025139
9/9/2016 ADM 0.000023 -0.014465
9/12/2016 ADM 0.00007 0.015054
9/13/2016 ADM 0.000023 0.000588
9/14/2016 ADM 0.000023 -0.010008
9/15/2016 ADM 0.000023 0.003787

;
run;

If not, any do loop macros with proc reg would also be helpful.

thanks

[Reeza]

I don't think it does, but you may be able to calculate the components. 

I wrote the code to calculate regression coefficient for a row. Using an array this could be modified accordingly. 

https://communities.sas.com/t5/Base-SAS-Programming/slope-across-variables-in-each-row/m-p/309420/hi...

Plus this method for calculating rolling values will help

http://support.sas.com/kb/41/380.html

[Ksharp]

data temp;
input DAT1 : mmddyy11. ID $ RET MKT_RET ;
format dat1 date9.;
cards;
9/1/2016 AA 0.0001 0.0001
9/2/2016 AA 0.000279 -0.002973
9/6/2016 AA -0.006091 0.000146
9/7/2016 AA 0.026914 0.002228
9/8/2016 AA 0.023175 0.025139
9/9/2016 AA -0.021908 -0.014465
9/12/2016 AA -0.023251 0.015054
9/13/2016 AA -0.034177 0.000588
9/14/2016 AA -0.032881 -0.010008
9/15/2016 AA 0.005656 0.003787
9/1/2016 ADM 0.0001 0.0001
9/2/2016 ADM 0.000023 -0.002973
9/6/2016 ADM 0.000094 0.000146
9/7/2016 ADM 0.000023 0.002228
9/8/2016 ADM 0.000023 0.025139
9/9/2016 ADM 0.000023 -0.014465
9/12/2016 ADM 0.00007 0.015054
9/13/2016 ADM 0.000023 0.000588
9/14/2016 ADM 0.000023 -0.010008
9/15/2016 ADM 0.000023 0.003787
;
run;
proc sql;
create table x as
 select id,count(*) as count
  from temp
   group by id;
quit;
data key;
 set x;
 do start=1 to count-4;
  end=start+4;output;
 end;
run;
data temp;
 set temp;
 by id;
 if first.id then n=0;
 n+1;
run;

%macro reg(id,start,end);
 proc reg data=temp(where=(id="&id" and n between &start and &end)) outest=outest noprint;
  model RET=MKT_RET;
 quit;
 data outest;
  set outest;
  length id $ 40;
  id="&id";start=&start;end=&end;
 run;
 proc append base=want data=outest force;run;
%mend;

data _null_;
 set key;
 call execute(cats('%reg(',id,',',start,',',end,')'));
run;

[Reeza]

I think this would technically do it, wasn't sure which was x and which was y for the regression.

I checked that this is error free but did not check if it was doing the correct calculation required. 

I checked this and it works as expected. If you're window is longer change the numbers to reflect this.

data want;
	set temp;
	by id;
	array _r(0:4) _temporary_;
	array _m(0:4) _Temporary_;

	if first.id then
		do;
			call missing(of _r(*));
			call missing(of _m(*));
			count=0;
		end;
	count+1;
	_r(mod(_n_, 5))=ret;
	_m(mod(_n_, 5))=mkt_ret;

	if count>4 then
		do;
			xbar=mean(of _r(*));
			ybar=mean(of _m(*));

			do i=0 to dim(_r)-1;
				num=sum(num, (_r(i)-xbar)*(_m(i)-ybar));
				den=sum(den, (_r(i)-xbar)**2);
			end;
			slope=num/den;
		end;
run;

[rogerjdeangelis]

You can run the R packages below using IML

If nothing else you can use R to check your SAS code

I am out of my comfort zone, it has been a long time since i worked with financial data.
Does not exactly answer your question but you should be able to replicate
easily in SAS and check against R. Looks like what you are asking


inspired by

https://goo.gl/ls5L0a
https://communities.sas.com/t5/SAS-Procedures/Stock-Beta-using-proc-expand/m-p/321224

also see
https://goo.gl/XVg97I
http://stackoverflow.com/questions/32186233/r-calculating-a-stocks-beta-using-performanceanalytics-capm-beta-function-or

also check



HAVE (CLOSING DATA FOR 'ACAD' and 'SPY')
========================================

Note you can move tha data to SAS from R if you want


           ACAD.Open ACAD.High ACAD.Low ACAD.Close ACAD.Volume ACAD.Adjusted

2016-12-16     25.72     26.51    25.65      25.98     2089500         25.98
2016-12-19     26.15     26.46    25.30      25.43     1144400         25.43
2016-12-20     29.26     30.14    27.26      28.57    18508600         28.57
2016-12-21     28.46     28.95    27.42      27.46     3964600         27.46
2016-12-22     27.44     28.40    27.09      27.67     2036800         27.67
2016-12-23     27.63     29.98    27.51      29.75     3467900         29.75

           SPY.Open SPY.High SPY.Low SPY.Close SPY.Volume SPY.Adjusted

2016-12-16   226.01   226.08  224.67    225.04  156420200       225.04
2016-12-19   225.25   226.02  225.08    225.53   90341100       225.53
2016-12-20   226.15   226.57  225.88    226.40   89838800       226.40
2016-12-21   226.25   226.45  225.77    225.77   67909000       225.77
2016-12-22   225.60   225.74  224.92    225.38   56219100       225.38
2016-12-23   225.43   225.72  225.21    225.71   36251400       225.71


WANT  (slope of 36 months adjusted month-end close lags 2:37?)

Beta calculated for 36 months of adjusted month-end close

 (Intercept) r(spy)[2:37]
  0.08600758   2.62416671

WORKING SOLUTION
----------------

  r<-function(x) {m<-to.monthly(x[,6])[,4];diff(m)/lag(m)}
  coef(lm(r(acad)[2:37]~r(spy)[2:37]))

FULL SOLUTION (YOU CAN RUN R FROM IML)


%utl_submit_r64('
library(PerformanceAnalytics);
library(quantmod);
start_date <- "2012-7-01";
acad <- getSymbols("ACAD", from = start_date, auto.assign = F);
spy <- getSymbols("SPY", from = start_date, auto.assign = F);
r<-function(x) {m<-to.monthly(x[,6])[,4];diff(m)/lag(m)};
coef(lm(r(acad)[2:37]~r(spy)[2:37]));
');


> library(PerformanceAnalytics);library(quantmod);start_date <- "2012-7-01";
acad <- getSymbols("ACAD", from = start_date, auto.assign = F);spy <- getSymbols("SPY", from =
start_date, auto.assign = F);r<-function(x) {m<-to.monthly(x[,6])[,4];diff(m)/lag(m)};
coef(lm(r(acad)[2:37]~r(spy)[2:37]));

 (Intercept) r(spy)[2:37]
  0.08600758   2.62416671
>

[mkeintz]

PROC EXPAND only generates some univariate rolling statistics, but you would also need (1) rolling sums-of-cross-products, and (2) a process to invert the rolling SSCP matrix to generate the beta coefficient.  For five-day rolling windows, it's just better (both faster and smipler) to make data sets with 5 day windows, as in:

data need / view=need;
  /* Generate WID - windows id number */
  merge have (keep=id)
        have (firstobs=5 keep=id rename=(id=id5);
  if id^=lag(id) then wid=0;   /* same as a first.id check */
  wid+1;                       /* about to write 5 more records, increment wid */
  if id^=id5 then wid=.;       /* Too near end of this stock ticker to have a complete window */

  /* Now, for each WID, read and write 5 obs  */
  set have;              if wid>0 then output;
  set have (firstobs=2); if wid>0 then output;
  set have (firstobs=3); if wid>0 then output;
  set have (firstobs=4); if wid>0 then output;
  set have (firstobs=5); if wid>0 then output;
run;

proc reg data=need;
  by id wid;
  model .... ;
run;
quit;

Notes:

1. Data set NEED is a data set VIEW, not a data set FILE.  So it is generated only on demand, i.e. when it is called by the subsequent PROC REG.  The result is that the data set is never written to disk - instead it is streamed directly to the proc reg, saving lots of disk input/output. Unless you plan to use data set need multiple times, this is considerably faster for large data sets
   
   
2. The five SET statements create 5 synchronized streams of data from HAVE, but each stream is offset by one record from the prior stream.  Except for the first 4 and last 4 records in HAVE, each record is read in 5 times.  And except for the first 4 and last 4 for every stock ticker, each is written out 5 times, once in each of 5 windows.
3. You could expand this easily to large windows sizes, but I suppose at some size, you would convert this code to a macro, which would generate all the needed SET HAVE and IF WID>0 THEN OUTPUT statements.  It would be a pretty easy macro to create.  And, primarily because it's a data set view, it can be just as efficient as making my own rolling SSCP matrix for window sizes of several dozen.

[Inquisitive101]

Thanks all, was very useful & informative