Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Stock Beta using proc expand ?

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 12-26-2016 10:29 PM
(2056 views)

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

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

Plus this method for calculating rolling values will help

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

```
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
>
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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:

- 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
- 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.
- 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
and**SET HAVE**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.**IF WID>0 THEN OUTPUT**

--------------------------

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for

Allow PROC SORT to output multiple datasets

--------------------------

The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for

Allow PROC SORT to output multiple datasets

--------------------------

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Thanks all, was very useful & informative

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. **

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.