turn on suggestions

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

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- How to create value-weighted returns from equal-we...

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-27-2015 05:43 AM

Hey SAS users!

I'm creating a dataset where I want to calculate the value-weighted returns on 10 volatility portfolios (the portfolios are called 'volr', see section 3), but in my current file the returns are equal-weighted. For each stock I have the number of shares outstanding (shrout), the share price (prc) and the market capitalizations (ME = shrout, created in section 4) on each date. To create the portfolios I calculate the monthly volatility over the past 12 months (J=12) and then I sort the stocks into ten portfolios from low volatility to high volatility. I want to hold those portfolios for 1 month (K=1). Then, after that month, I rebalance again, which means I also want to have new weights in the portfolios. The weight of a stock in a portfolio should be its market capitalization divided by the sum of all the market capitalizations in the portfolio at that time. In each portfolio on each date I want the sum of the weights to add up to 1. The weights should be lagged so that the stock returns in the portfolios at time t are multiplied by the stock weights in the portfolios at time t-1. However, I don't know where exactly in my file I can create the weights of the portfolios and how I can define this. In section 5 I calculate the equal-weighted returns, so I guess I should add/change something there? I hope someone can help me with this and show me some commands that will do the job for me! I posted my SAS file below. Thanks a lot!!!

*****************************************************************************;

*options obs=200000;

*****************************************************************************

1. Specifying Options;

libname crsp '...\SAS Data\Datasets';

*****************************************************************************;

*** NUMBER OF PRIOR MONTHS USED TO CREATE VOLATILITY PORTFOLIOS;

%let J=12; * J can be between 3 to 12 months;

*** HOLDING PERIOD IN MONTHS AFTER PORTFOLIO CREATION;

%let K=1; * K can be between 3 to 12 months;

*** Footnote 4 page 69: 1965-1989 are the dates of portfolio holding periods;

*** BEGINING SAMPLE PERIOD;

%let begyear=1965;

*** ENDING SAMPLE PERIOD;

%let endyear=1989;

*****************************************************************************

2. Get Historical Exchange Codes and Share Codes for Common Stocks

*****************************************************************************;

* Merge historical codes with CRSP Monthly Stock File;

**proc** **sql**;

create table msex1

as select a.permno, a.date, a.ret, a.shrout, a.prc, b.exchcd, b.shrcd

from crsp.msfwithshrout(keep=date permno ret shrout prc) as a

left join crsp.mseall(keep=date permno exchcd shrcd) as b

on a.permno=b.permno and a.date= b.date;

**quit**;

* Complete the time series for exchcd & shrcd and select all common stocks;

**proc** **sort** data=msex1; by permno date; **run**;

**data** msex2;

set msex1;

by permno date;

prc = abs(prc);

retain lexchcd lshrcd;

if first.permno then

do;

lexchcd = exchcd ;

lshrcd = shrcd;

end;

else

do;

if missing(exchcd) then exchcd = lexchcd;

else lexchcd = exchcd;

if missing(shrcd) then shrcd = lshrcd;

else lshrcd = shrcd;

end;

* Subsample after filling missing Exchange and Share Codes;

if exchcd in (**1**,**2**,**3**); * NYSE, AMEX and NASDAQ securities only;

if shrcd in (**10**,**11**) and not missing(ret); * Common Stocks only;

* Additional years of lagged return for portfolio formation and holding;

if (&begyear-**2**)<=year(date)<=&endyear;

drop lexchcd lshrcd shrcd exchcd;

**run**;

********************************************************************************

3. CREATE VOLATILITY MEASURES;

********************************************************************************

*;

**proc** **sql**;

create table vol

as select distinct a.permno, a.date, std(ret) as std_ret

from msex2 (keep=permno date) as a, msex2 as b

where a.permno=b.permno and **0**<=intck('month', b.date, a.date)<&J

group by a.permno, a.date

having count(b.ret)=&J;

**quit**; * Keep stocks with available return info in the formation period;

**proc** **sort** data=vol; by date; **run**;

**proc** **rank** data=vol out=vol1 group=**10**;

by date;

var std_ret;

ranks volr;

**run**;

**data** vol2;

set vol1(drop=std_ret);

volr=volr+**1**;

* VOLR is the portfolio rank variable taking values between 1 and 10:

1 - the lowest volatility group

10 - the highest volatility group;

label volr = "Volatility Portfolio";

**run**;

********************************************************************************

4. Assign Ranks to the Next 1 (K) Month After Portfolio Formation

********************************************************************************;

* Portfolio return are average monthly returns rebalanced monthly;

**proc** **sql**;

create table msfx2

as select distinct a.volr, a.date as form_date, a.permno, b.date, b.ret, b.shrout, b.prc, b.shrout*b.prc as ME

from vol2 as a, msex2 as b

where a.permno=b.permno

and **0**<intck('month',a.date,b.date)<=&K;

**quit**;

********************************************************************************

5. Calculate Equally-Weighted Average Monthly Returns

********************************************************************************;

**proc** **sort** data=msfx2; by date volr form_date shrout prc ME; **run**;

* Portfolio monthly return series;

**proc** **means** data = msfx2 noprint;

by date volr form_date shrout prc ME;

var ret;

output out = msfx3 mean=ret;

**run**;

* Portfolio average monthly return;

**proc** **sort** data=msfx3; by date volr;

where year(date) between &begyear and &endyear;

**run**;

**proc** **means** data = msfx3 noprint;

by date volr;

var ret;

output out = ewretdat mean= ewret std = ewretstd;

**run**;

**proc** **sort** data=ewretdat; by volr ; **run**;

Title "Table 1: Returns of Volatility Portfolios";

Title2 "Portfolios based on &J month lagged return and held for &K months";

**proc** **means** data=ewretdat mean std t probt;

class volr;

var ewret;

**run**;

********************************************************************************

*****

- 6. Calculate Buy-Sell Portfolio Returns

********************************************************************************

*****;

**proc** **sort** data=ewretdat; by date volr; **run**;

**proc** **transpose** data=ewretdat out=ewretdat2

(rename = (_1=BUY _2=PORT2 _3=PORT3 _4=PORT4 _5=PORT5

_6=PORT6 _7=PORT7 _8=PORT8 _9=PORT9 _10=SELL));

by date;

id volr;

var ewret;

**run**;

**data** ewretdat3;

set ewretdat2;

BUY_SELL=Buy-Sell;

**run**;

**proc** **means** data=ewretdat3 n mean std t probt;

var Sell Buy Buy_Sell;

**run**;

********************************************************************************

*****;