How to create value-weighted returns from equal-weighted returns when I have market capitalizations?

Robbert_SAS — Wed, 27 May 2015 09:43:12 GMT

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;

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

*****;

topic How to create value-weighted returns from equal-weighted returns when I have market capitalizations? in SAS Procedures

How to create value-weighted returns from equal-weighted returns when I have market capitalizations?