BookmarkSubscribeRSS Feed
Robbert_SAS
Calcite | Level 5

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;

BUY_SELL=Buy-Sell;

run;

proc means data=ewretdat3 n mean std t probt;

var Sell Buy Buy_Sell;

run;

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

*****;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 0 replies
  • 4109 views
  • 0 likes
  • 1 in conversation