BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ksachar
Obsidian | Level 7
I need to calculate modified deitz return (Which is basically geometric mean of periodic return after adjusting Cash Flow and weighted Cash flow). You calculate monthly return, and for larger periods take geometric mean for it. Say you have monthly return and you can calculate annual return by taking Geometric mean.  
 
I am manipulating data in SAS EG so I can use the dataset in SAS VA for a Report.
 
Geometric Mean=(1+r1)*(1+r2)...... -1
r1, r2== are monthly returns.
 
The idea is to select a random period Date_From and Date_To and the pre-calculated Rate of return come up.
 
I have calculated the monthly return with adjusted cash flow, I am strugling to get a Geometric mean/Cumulative Return.
 
I HAVE:
 
ClientFinancial ProductDateReturnReturn +1
Customer AProduct AJan20162%$1.02
 Product AFeb20163%$1.03
 Product AMar20161%$1.01
 Product AApr20167%$1.07
 Product AMay2016-5%$0.95
 Product AJun20163%$1.03
 Product AJul2016-0.50%$1.00
 Product AAug20163%$1.03
 Product ASep20162%$1.02
 Product AOct20165%$1.05
 Product ANov20161%$1.01
 Product ADec2016-1%$0.99
Customer AProduct B   
........   
..........   
..............   
..................   
..................   
 
 
 
I WANT:
 
Financial ProductDate_FromDate_ToGM_FormulaeCumulative Return 
Product AJan2016Feb2016(1+r2)-12.00%
Product AJan2016Mar2016(1+r2)*(1+r3)-15.06%
Product AJan2016Apr2016........
Product AJan2016May2016..........
Product AJan2016Jun2016..............
Product AJan2016Jul2016..................
Product AJan2016Aug2016..................
Product AJan2016Sep2016........
Product AJan2016Oct2016..........
Product AJan2016Nov2016..............
Product AJan2016Dec2016(1+r1)*(1+r2)....*(1+r11)-123.16%
Product AFeb2016Mar2016..................
Product AFeb2016Apr2016........
Product AFeb2016May2016..........
Product AFeb2016Jun2016..............
Product AFeb2016Jul2016..................
Product AFeb2016Aug2016..................
Product AFeb2016Sep2016  
Product AFeb2016Oct2016  
Product AFeb2016Nov2016  
Product AFeb2016Dec2016  
     
For 12 months there will be 66 combinations of months    
 
 
The date combinations are easy to create. But I am having trouble calculating Rate of return for all teh 66 combinations. Since it is teh same variable.
There are ways to this in excel. Since, actual table is millions of rows thats not a possibility. Can this be done in SAS.

 

1 ACCEPTED SOLUTION

Accepted Solutions
lakshmi_74
Quartz | Level 8

data customer_dup(keep=cum_sum date_from date_to return prev_return) ;
retain cum_sum prev_return date_from start_pos position;
set customer_dup;
by customer product year;
if (first.customer or first.product or first.year) then
do;
put obs=;
prev_return=return_cal;cum_sum=return_cal;date_from=month;date_to=month;start_pos=obs;
end;
else
do;
date_to=month;
cum_sum=prev_return*return_cal;
prev_return=cum_sum;
end;final_cum=cum_sum-1;output;
if (last.customer or last.product or last.year) then
do;
do num=tot_group-1 to 1 by -1; position=start_pos+1;
do i= 1 to num by 1;
set customer_dup point=position;
if (i eq 1) then
do;date_from=month;date_to=month; prev_return=return_cal;cum_sum=return_cal;end;
else
do;
date_to=month;cum_sum=prev_return*return_cal;prev_return=cum_sum;
end;final_cum=cum_sum-1; output;
position=position+1;
end; start_pos=start_pos+1;
end;
end;
informat date_from date_to MONYY7.;
format date_from date_to MONYY7.;
run;

View solution in original post

10 REPLIES 10
Reeza
Super User

Of course you can do this in SAS 🙂

I'm assuming your question is how do you do this in SAS? 

When googling I suggest limiting search to lexjansen.com or include SAS NOTES. 

 

 

1. Why are you missing months in your data? 

2. How do you account for missing months?

3. How do calculate the rate at the yearly boundaries, does it reset at some point? 

4. How big is your dataset - 5 million is different than 200 million.

 

There's a geomean function but that would mean transposing your dataset. The best methods are to look at PROC EXPAND, usually under time series in EG or convert to logs and then use proc means and revert back to regular scale. The second option is detailed here: http://support.sas.com/kb/25/366.html

Ksharp
Super User

If I was right, that is not geometric mean.

GM should like :  ((1+r1)*(1+r2)) **1/2

 

 

data have;
infile cards expandtabs truncover;
input Client & $20.	FinancialProduct & $20.	 Date : monyy5.	Return : percent8.;
format date monyy. return percentn8.2;
cards;
Customer A	Product A	Jan2016	2%	$1.02
Customer A 	Product A	Feb2016	3%	$1.03
Customer A 	Product A	Mar2016	1%	$1.01
Customer A 	Product A	Apr2016	7%	$1.07
Customer A 	Product A	May2016	-5%	$0.95
Customer A 	Product A	Jun2016	3%	$1.03
Customer A 	Product A	Jul2016	-0.50%	$1.00
Customer A 	Product A	Aug2016	3%	$1.03
Customer A 	Product A	Sep2016	2%	$1.02
Customer A 	Product A	Oct2016	5%	$1.05
Customer A 	Product A	Nov2016	1%	$1.01
Customer A 	Product A	Dec2016	-1%
;
run;
proc sql ;
create table temp as 
select a.client,a.financialproduct,a.date,b.date as _date,b.return
 from have as a,have as b
  where a.client=b.client and a.financialproduct=b.financialproduct
        and a.date lt b.date
   order by a.client,a.financialproduct,a.date,b.date ;
quit;
data want;
 set temp;
 by client financialproduct date;
 retain cum 1;
 if first.date then cum=1;
 cum=cum*(1+return);
 mean=cum-1;
run;
Reeza
Super User

It looks like a compound return rate? 

ksachar
Obsidian | Level 7

Thanks a lot @Ksharp. You are correct. It is compounded return. If the time period exceeds 12 months , then I have to calculate the annualized return too.
My real problem is far more complicated than what I presented in "I HAVE". I tried to adjust your code but I am missing something.

Can you help me modify your code for the data bellow (I removed the last variable since you didn't used in your code)




Customer A ClassA Product A Jan2016 2% $1.02
Customer A ClassA Product A Feb2016 3% $1.03
Customer A ClassA Product A Mar2016 1% $1.01
Customer A ClassA Product A Apr2016 7% $1.07
Customer A ClassA Product A May2016 -5%
Customer A ClassA Product A Jun2016 3%
Customer A ClassA Product A Jul2016 -0.50%
Customer A ClassA Product A Aug2016 3%
Customer A ClassA Product A Sep2016 2%
Customer A ClassA Product A Oct2016 5%
Customer A ClassA Product A Nov2016 1%
Customer A ClassA Product A Dec2016 -1%
Customer A ClassA Product A Jan2015 -2%
Customer A ClassA Product A Feb2015 0.5%
Customer A ClassA Product A Mar2015 3%
Customer A ClassA Product A Apr2015 4%
Customer A ClassA Product A May2015 6%
Customer A ClassA Product A Jun2015 -1%
Customer A ClassA Product A Jul2015 5%
Customer A ClassA Product A Aug2015 8%
Customer A ClassA Product A Sep2015 -6%
Customer A ClassA Product A Oct2015 4%
Customer A ClassA Product A Nov2015 1.3%
Customer A ClassA Product A Dec2015 .8%
Customer A ClassA Product B Jan2016 3%
Customer A ClassA Product B Feb2016 7%
Customer A ClassA Product B Mar2016 9%
Customer A ClassA Product B Apr2016 -2%
Customer A ClassA Product B May2016 -1%
Customer A ClassA Product B Jun2016 -4%
Customer A ClassA Product B Jul2016 -4%
Customer A ClassA Product B Aug2016 1%
Customer A ClassA Product B Sep2016 -2%
Customer A ClassA Product B Oct2016 3%
Customer A ClassA Product B Nov2016 -5%
Customer A ClassA Product B Dec2016 6%
Customer A ClassA Product B Jan2015 -1%
Customer A ClassA Product B Feb2015 3%
Customer A ClassA Product B Mar2015 -1%
Customer A ClassA Product B Apr2015 2%
Customer A ClassA Product B May2015 -3%
Customer A ClassA Product B Jun2015 3%
Customer A ClassA Product B Jul2015 -9%
Customer A ClassA Product B Aug2015 2%
Customer A ClassA Product B Sep2015 1%
Customer A ClassA Product B Oct2015 -2%
Customer A ClassA Product B Nov2015 3%
Customer A ClassA Product B Dec2015 -2%




Thanks once again.

Ksharp
Super User

Add one more code.

CODE NOT TESTED.

 

data want;
 set temp;
 by client financialproduct date;
 retain cum 1;
 if first.date or month(_date)=1 then cum=1;
 cum=cum*(1+return);
 mean=cum-1;
run;
mkeintz
PROC Star

Let's say you have data set have, sorted by customer/product/date, where date is a true sas date variable.  You also have the additional vars RET and RETPLUS1, and there are no gaps in the time series.

 

You want rolling cumulative returns for 2-months, 3-months, .... 12 months:

 

 

Erroneous program removed.

 

This program takes advantage of temporary arrays, which have their values automatically retained.  We also use the fact that

       3-month cumret =  (prior 2-month cumret + 1) * (1 + ret) - 1

  and generally

       J-month cumret = (prior {J-1}-month cumret + 1) * (1+ret) - 1

 

Editted. See the comments below.  So the program first updates 2-month-cumret, then 3-month-cumret, ... 12-month-cumret

 

Thanks to @ksachar's response, I realize that I wan't paying attention to my own advice.  First the program should loop from 12 to 2, not 2 to 12.  I.e. update 12 month cumret first, then 11 month, etc.   And I should update R{1} AFTER the loop, not before.  The corrected program follows:

 

data want (drop=N nmonths);
  set have (rename=(date=todate));
  by prod;
  format fromdate yymmddn8.;

  array r {12} _temporary_;
  if first.prod then do;
    N=0;
    call missing(of r{*});
  end;
  N+1;

  do nmonths=12 to 2 by -1;
    if N<nmonths then continue;
    r{nmonths}=(1+ret)*(1+r{nmonths-1})-1;
    cumret=r{nmonths};
    fromdate=intnx('month',todate,1-nmonths,'end');
    output;
  end;

  r{1}=ret;
run;

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

--------------------------
ksachar
Obsidian | Level 7

@mkeintz Thank you so very much for responding to my post. I tried the code but it is not giving me correct cumret. Will you be able to provide me the modified code on this sample data (I have added another variable). Also, Can you share the output so I can compare? want to make sure I am not making any mistake

 

If I need to extend the code to 24 months, will it work if I change :

 

array r {12} _temporary_;

to

array r {24} _temporary_;

 

 

AND

 do nmonths=2 to 12;

to

 do nmonths=2 to 24;

 

lakshmi_74
Quartz | Level 8

data customer_dup(keep=cum_sum date_from date_to return prev_return) ;
retain cum_sum prev_return date_from start_pos position;
set customer_dup;
by customer product year;
if (first.customer or first.product or first.year) then
do;
put obs=;
prev_return=return_cal;cum_sum=return_cal;date_from=month;date_to=month;start_pos=obs;
end;
else
do;
date_to=month;
cum_sum=prev_return*return_cal;
prev_return=cum_sum;
end;final_cum=cum_sum-1;output;
if (last.customer or last.product or last.year) then
do;
do num=tot_group-1 to 1 by -1; position=start_pos+1;
do i= 1 to num by 1;
set customer_dup point=position;
if (i eq 1) then
do;date_from=month;date_to=month; prev_return=return_cal;cum_sum=return_cal;end;
else
do;
date_to=month;cum_sum=prev_return*return_cal;prev_return=cum_sum;
end;final_cum=cum_sum-1; output;
position=position+1;
end; start_pos=start_pos+1;
end;
end;
informat date_from date_to MONYY7.;
format date_from date_to MONYY7.;
run;

ksachar
Obsidian | Level 7

This works perfectly. My initial requirement was for a data for 6 years (i.e. 72 months and different combinations for 72 months-- which is nCr =(72C2) = 2556 ). After discussing with @lakshmi_74lakshmi i made minor changes to the code; removed YEAR in BY statement, anywere in first.year, last.year etc. and the retun was calculated for each Product group (date combinations).

 

 

ksachar
Obsidian | Level 7

@lakshmi_74 @Ksharp @mkeintz @Reeza Thank you for finding time and helping me resolve this.

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
  • 10 replies
  • 1524 views
  • 1 like
  • 5 in conversation