topic sum volume of contracts for product within volume ranges in SAS Programming

sum volume of contracts for product within volume ranges

eberhard1945 — Thu, 12 Nov 2015 17:23:23 GMT

Hi,

what i have:

I get information for interest levels for one product like this in a dataset1:

Productcode Level in EUR Interestrate

123 999.999.999 0,10

123 100.000 0,20

123 50.000 0,30

123 10.000 0,50

this means i pay for the customer 0,50% up to 10.000 EUR between 10000 and 50000 I pay 0,30% and so on.

I have another dateset2 with all the contracts and of course also with the Productcode 123.

Now I need to now how many volume is within my 4 interest buckets over all the contracts with code 123 so to calculate my avarage interest rate i pay vor the product in total.

Example

contract1 with balance 75.000 schould end in sum 10000 for the 1st bucket 40000 for the 2nd bucket and 25000 for the 3rd bucket. I think you got the idea.

Is there any usefill funktion in SAS Base 9.2 which kan help or do I need to programm a macro up from zero.

Thank you for some ideas.

Re: sum volume of contracts for product within volume ranges

Reeza — Thu, 12 Nov 2015 17:28:10 GMT

I think you may be able to do a SQL merge. Can you provide some more sample data and preferably some sample output?

Re: sum volume of contracts for product within volume ranges

Steelers_In_DC — Thu, 12 Nov 2015 18:51:24 GMT

I think this will do the trick for you. You can also use proc sql; instead of proc freq:

data data;
infile cards dsd;
input Productcode$   Level_in_EUR;
cards;
123,1
123,2
123,999999
123,58
123,35
123,27
123,15
;

proc format;
   value eur low-10    = '50%'
              11-50     = '30%'
              51-100    = '20%'
              101-high = '10%';
;

data want;
set data;
new_val = level_in_eur;
format new_val eur.;
run;

proc freq data= want;table new_val;

Re: sum volume of contracts for product within volume ranges

Reeza — Thu, 12 Nov 2015 18:53:45 GMT

I don't think that will work, as it's a progressive implementation. For example a value of 75 has 50% on the first 10, 30% on the next 40 and 20% on the next 25. At least that's the way I understood it. So each value has to be broken down into groups.
And I think the breakdown is different for each group.

Need more data to understand the issue I think.

Re: sum volume of contracts for product within volume ranges

ndp — Thu, 12 Nov 2015 20:00:39 GMT

Let us know if following is what you are looking for:

data data1;
infile cards dsd;
input Productcode$   level rate;
cards;
123,10,10
123,50,20
123,100,30
123,999999,50
;

data data2;
infile cards dsd;
input contract Productcode$   balance;
cards;
1,123,75
2,123,12
3,123,175
;
run;

proc sql noprint;
	create table want as
	select a.*,b.level, b.rate
	from data2 as a left join data1 as b
	on a.Productcode=b.Productcode
	order by a.Productcode, a.contract, b.level;
quit;

data want;
	set want;
	by Productcode contract level;
	retain diff;
	if first.contract and balance-level>=0 then do; balbylevel=level; diff=balance-level; end;
	else if first.contract and balance-level<0 then do; balbylevel=balance; diff=0; end;
	else do;
		if diff=0 then balbylevel=0;
		else if diff>level then do; balbylevel=level-(balance-diff); diff=balance-level; end;
		else do; balbylevel=diff; diff=0; end;
	end;
run;

Re: sum volume of contracts for product within volume ranges

eberhard1945 — Fri, 13 Nov 2015 11:23:22 GMT

Thank you all,

I guess working on the way ndp recommended will lead to success.

I test it and identify one issue with the code:

data want;
	set want;
	by Productcode contract level;
	retain diff;
	if first.contract and balance-level>=0 then do; balbylevel=level; diff=balance-level; end;
	else if first.contract and balance-level<0 then do; balbylevel=balance; diff=0; end;
	else do;
		if diff=0 then balbylevel=0;
		else if diff>level then do; balbylevel=level-(balance-diff); diff=balance-level; end;
		else do; balbylevel=diff; diff=0; end;
	end;
run;

line

else if diff>level then do; balbylevel=level-(balance-diff); diff=balance-level; end;

leads to wrong results if you only compare the current diff value with the current level value. you have to compare the current diff value with the (current level value - the previous level value).

So i think i need another retaining variable I call "prelevel" for the last lavel value to substract from the actual level value for that check which leads to 100% correct results for every of my contracts:

data want;
	set want;
	by Productcode contract level;
	retain diff;
	retain prelevel;
	if first.contract and balance-level>=0 then do; balbylevel=level; diff=balance-level; prelevel=level; end;
	else if first.contract and balance-level<0 then do; balbylevel=balance; diff=0; end;
	else do;
		if diff=0 then balbylevel=0;
		else if diff>(level-prelevel) then do; balbylevel=level-(balance-diff); diff=balance-level; prelevel=level; end;
		else do; balbylevel=diff; diff=0; end;
	end;
run;

At least a simple summary query shows the total volume at each interest level of the product making calculating the avarage interest rate also very easy.

Thank you