BookmarkSubscribeRSS Feed
0 Likes

The ability to calculate product for an aggregated by group. 

Requirement: Calculate compounded rate of return when you are given monthly rate of return

Say, 

 

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 am using 12 months for ease of defining but the period can be more than 12 months too.
 
I WANT:
 
A slider with "Date" variable. When you move the slider and select a time period say May2016 to Oct2016 you get the output , which is product of Variable (Return+1) i.e 0.95*1.03*1.00*1.03*1.02 *1.05 = 1.079.
 
This way I can calculate compunded return for each product for same time period in this case May2016 to Oct2016.
 
This can be achieved in BASE SAS by creating a table htat comtains pre calculated returns for all the date combunations. 
What do I men by date combinations: for 12 months- ther will be 66 distinct combinations say 
 
Jan-Feb
Jan march
..
.. 
jan-dec
 
feb-march
 
..
..
..
.
.
nov-dec
 
 
 May to october -- as explained in this case is only 1 of those combinations. 

Once you have the dataset, instead of using Slider, we can use two drop-down's for Start_Date and End_date variable. It is little cucumebrsome but the idea is to calculate product of observations selected/aggregated by group/sub-group.
   
 
3 Comments
AnnaBrown
Community Manager

Thanks for your idea. To give us some context, what version were you using when you encountered this? 

 

In SAS VA 8.x, we added more advanced aggregation functions, see related doc: Aggregated "Tabular" Operators. 

ksachar
Obsidian | Level 7

Anna,

 

Were are using SAS VA 7.X. I was told at the time by SAS Tech support that SAS VA Viya it will have the feature soon.I will keep it in mind when we upgrade.

 

Thanks.  

 

AnnaBrown
Community Manager
Status changed to: Suggestion Implemented

Hi there @ksachar, Sounds good on keeping this in mind when you upgrade to VA on SAS Viya. Let us know if you have any trouble with the functionality.

 

Anna