sum volume of contracts for product within volume ranges

[eberhard1945]

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.

[Reeza]

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

[Steelers_In_DC]

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;

[Reeza]

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.

[ndp]

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;

[eberhard1945]

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