# How to create value-weight returns from equal-weight returns when I have market caps?

#### SAS_robbert

##### New Member
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;
********************************************************************************
*****
1. 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;