BookmarkSubscribeRSS Feed
kimx0961
Obsidian | Level 7

Hi,

 

I would like to compute annual buy and hold return using monthly return like the following.

annual ruturn = (1+January return)(1+February return)(1+March return)-----(1+December return) - 1

The data structure I have is as follows:

gvkey    year month monthly return

00001   2000    1       0.002

00001   2000    2       0.001

00001   2000    3       0.003

 

Do you have a simple SAS code I can refer using retain statement (without using macro)?

Thank you for your help

 

4 REPLIES 4
ballardw
Super User

This should do what you want but the value is only going to be for the entire year if the last value in each year corresponds to Dec.

I assumed that since you have a key value that you wanted this for each key and that the data is sorted by gvkey year and month.

If you only want the final value add at the end of the datastep: If Last.year then output;

 

data want;
   set have;
   by gvkey year;
   retain AnReturn;
   if first.year then AnReturn= (1+ MonthReturn);
   else AnReturn= AnReturn*(1+MonthReturn);
run;

However I suspect you are going to run into precision problems (under flow). you may need to consider an approach with logs or shift ot integer arithmetic and shift back.

kimx0961
Obsidian | Level 7
Thank you for your kind response

It will be grateful if you can provide further guidance regarding how to obtain annual holding return in three months after fiscal year-end. How can I revise your code that produces annual buy and hold return using monthly return.

For instance, if fiscal year-end is 12/31/2014,

beginning date (begdate): 04/01/2014
ending date (enddate)? : 03/31/2015
Thanks again for your help


xiaobao
Calcite | Level 5
data want;
set have;
ln_return = log(monthlyreturn+1);
run;

proc sort data = want by permno year month;

proc means data = have noprint;
by gvkey year;
var ln_return;
output out = want1 sum = ln_annreturn;
annreturn = exp(ln_annreturn) - 1;
run;
mkeintz
PROC Star

If YEAR designates fiscal year, then you want to accumulate up through the 3rd record following the last month record for a given year, as in:

 

data bhret;
  set have;
  by gvkey year;
  bhret+log(1+ret);
  if lag3(last.year);
  bhret=exp(bhret)-1;
  output;
  call missing bhret;
run;

Note this assumes that there are no missing monthly returns.

 

But maybe (you're using Compustat data right?) you have a variable FYR indicating the month number for the end of fiscal year (i.e. if FYR=6 then fiscal year ends in June).  If so, then

 

data bhret;
  set have;
  by gvkey;
  bhret+log(1+ret);
  if lag3(month)=fyr;
  bhret=exp(bhret)-1;
  output;
  call missing bhret;
run;

This also assume no missing months.

 

A couple notes:

  The  "bhret+log(1+ret);" is a "summing statement", which tells sas to retain the resulting value in variable bhret.  I.e. don't automatically reset bhret to missing.

 

  The "if lag3(...)" statement is a subsetting if, keeping only those records in which the 3rd preceding record satisfy the specified condition.  

 

  One a record is output, the programmer now needs to reset the bhret to missing, so every record has only results built on record following the previous annual return.

 

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

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 3394 views
  • 1 like
  • 4 in conversation