Steped fee calculation

[deleted_user]

Hi SAS experts,

I'm trying to design a loop or a macro that will help me calculate transaction-based fees that are applied in a 'stepped' manner.

Example;

00001-10000 transactions - 0.25c per transaction
10001-25000 transactions - 0.21c per transaction
25001-50000 transactions - 0.20c per transaction
50001 and above - 0.18c per transaction

If 78,000 transactions were processed this month, charge would be,

10,000 x 0.25 = 2,500
15,000 x 0.21 = 3,150
25,000 x 0.20 = 5,000
28,000 x 0.18 = 5,040

Total charges for 78,000 transactions = 15,690

My data set looks something like this;

Date...........type............transactions

Jan-10........MC..............78,000
Feb-10........MC..............72,987
Mar-10........MC..............81,214
Apr-10.........MC.............79,914

Is there a way to transalate this calculation into a sas code/macro. I have many transaction types with their own unique stepped fee rates that I need to apply to the relevant transaction volumes. The stepped fees are available to me in a separate dataset that I can join. Or if its easier I can hard-code the fee bands into the code/macro.

Thanks for any help you can offer.

[andreas_lds]

1. Create a dataset containing the charge-transaction-relationship:
[pre]data work.fee;
input min max charge;
datalines;
1 10000 0.25
10001 25000 0.21
25001 50000 0.2
50001 . 0.18
;
run;[/pre]

2. Combine the transaction dataset with the fee dataset:
[pre]data work.charged;
set work.transactions;

length total helper 8;
retain total helper;
drop helper min max charge;

total = 0;
helper = 0;

do i = 1 to 4;
set work.fee point=i;

if transaction > min then do;
if not missing(max) then do;
total = (max - helper) * charge + total;
helper = max;
end;
else do;
total = (transaction - helper) * charge + total;
end;
end;
end;
run;[/pre]

Still to do: create a macro containing the calculating data-step, with parameters for the three datasets involved.

[deleted_user]

Thank you andreas_lds

This looks like a good start to me. i already have a separate dataset with the bandings and the rates, so will adapt your code and see how it works.

Appreciate your help.

[art297]

You could turn the following into a macro, but I think that you can do what you want in just a couple of if then statements. For example:

data have;
informat Date monyy6.;
format Date date9.;
informat transactions comma10.;
input Date type $ transactions;
cards;
Jan-10 MC 78,000
Feb-10 MC 72,987
Mar-10 MC 81,214
Apr-10 MC 79,914
;

data want (drop=hold_transactions);
set have;
cost=0;
hold_transactions=transactions;
if hold_transactions gt 50000 then do;
cost=(hold_transactions-50000)*.18;
hold_transactions=50000;
end;
if hold_transactions gt 25000 then do;
cost=(hold_transactions-25000)*.20+cost;
hold_transactions=25000;
end;
if hold_transactions gt 10000 then do;
cost=(hold_transactions-10000)*.21+cost;
hold_transactions=10000;
end;
cost=hold_transactions*.25+cost;
run;

HTH,
Art

[deleted_user]

Thank you art297

Another very good approach. I'm going to try this out, I believe one of these solutions should help me resolve my query.

Much appreciated.

[deleted_user]

Art, I'm using your approach, its brilliant for what I want now, while im still designing my model, gives me more control over tweaking the bands etc.

Andreas, I wll eventually use the loop, although for now I'm hard-coding the rates as number of bands vary by transation type so I cant restrict the loop to 4 iterations.

Thanks again for both solutions, very educational.