## Calculate new variable - accumulation beyond limit

Solved
Regular Contributor
Posts: 247

# Calculate new variable - accumulation beyond limit

[ Edited ]

Hi.....I have a dataset where clients have been overcharging their fee. The client is allowed to charge their fee for a supply that is equal to the 'Allowance' for that particular Product. The 'Allowance" Amount will always be the same for that particular Product. I want to calculate a revised fee so that the revised fee is the same as the fee submitted on the first record and 0 (zero) otherwise until the "Allowance" Amount has been reached at which point I would like to choose the maximum fee that was submitted for those records collectively so that the "Allowance" Amount was reached. Any suggestions. Thanks.

Have:

 Client Product Date Fee Supply Allowance 2569 02293243 20160102 25.00 7 30 2569 02293243 20160109 25.00 7 30 2569 02293243 20160117 26.00 8 30 2569 02293243 20160126 26.00 9 30 2569 02293243 20160201 26.00 6 30 2569 02293243 20160209 26.00 8 30 2569 02293243 20160214 26.00 5 30 2569 02293243 20160222 26.00 8 30 2569 02343451 20160201 28.00 5 15 2569 02343451 20160205 28.00 3 15 2569 02343451 20160207 28.00 8 15 2569 02343451 20160214 28.00 8 15

Want:

 Client Product Date Fee Revised Fee Supply Allowance 2569 02293243 20160102 25.00 25.00 7 30 2569 02293243 20160109 25.00 0.00 7 30 2569 02293243 20160117 26.00 0.00 8 30 2569 02293243 20160126 26.00 26.00 9 30 2569 02293243 20160201 26.00 0.00 6 30 2569 02293243 20160209 26.00 0.00 8 30 2569 02293243 20160214 26.00 0.00 5 30 2569 02293243 20160222 26.00 0.00 8 30 2569 02343451 20160201 28.00 28.00 5 15 2569 02343451 20160205 28.00 0.00 3 15 2569 02343451 20160207 28.00 28.00 8 15 2569 02343451 20160214 28.00 0.00 8 15

Accepted Solutions
Solution
‎02-22-2017 01:40 PM
Super Contributor
Posts: 268

## Re: re:

Sorry about the delay - it was 4am when you sent your response, and I was (mostly) fast asleep!

Ah, I see now. It all falls into place.

I think this is close to what you want:

``````data have;
infile cards dsd dlm='09'x firstobs=2;
attrib client length=\$ 4;
attrib product length=6;
attrib date length=4 informat=yymmdd8. format=ddmmyy10.;
attrib fee length=8 format=10.2;
attrib supply length=3;
attrib allowance length=3;
input client
product
date
fee
supply
allowance;
cards;
Client	Product	Date	Fee	Supply	Allowance
2569	2293243	20160102	25.00	7	30
2569	2293243	20160109	25.00	7	30
2569	2293243	20160117	26.00	8	30
2569	2293243	20160126	26.00	9	30
2569	2293243	20160201	26.00	6	30
2569	2293243	20160209	26.00	8	30
2569	2293243	20160214	26.00	5	30
2569	2293243	20160222	26.00	8	30
2569	2343451	20160201	28.00	5	15
2569	2343451	20160205	28.00	3	15
2569	2343451	20160207	28.00	8	15
2569	2343451	20160214	28.00	8	15
;
run;

proc sort data=have noequals;
by client product date;
run;

data want;
retain client product date fee revised_fee supply allowance;
set have;
attrib revised_fee max_fee length=4 format=10.2;
attrib accumulated_supply length=3;
retain revised_fee max_fee accumulated_supply;
by client product;
if first.product then do;
revised_fee = fee;
accumulated_supply = supply;
max_fee = fee;
return;
end;
max_fee = max(fee, max_fee);
accumulated_supply + supply;
if accumulated_supply < allowance
then revised_fee = 0;
else do;
revised_fee = max_fee;
accumulated_supply = 0;
end;
keep client product date fee revised_fee supply allowance;
run;
``````

I wasn't sure what to do with accumulated_supply when it ticked over - reset it to zero as I've done, or accumulated_supply - allowance.

All Replies
Super Contributor
Posts: 268

## Re: re:

I'm having a little difficulty understanding your requirements.

Questions:

• What is the relationship between supply and allowance?
• What are the accumulation rules for fee?
• Why is revised_fee updated on row 4, but not 5-9?
• Additionally, why is it updated on row 13 for the new product?
Regular Contributor
Posts: 247

## Re: re:

[ Edited ]

Hi....what is happening is that the client is submitting their fees each day that the product is supplied. The fee that is charge is for the Allowance number of units that must be supplied

Questions:

• What is the relationship between supply and allowance?
• Supply is the number of units Supplied on that date and Allowance is the number of units that must be supplied before the client is allowed to charge a fee.
• What are the accumulation rules for fee?
• The client must supply the Allowance Amount for a fee to be charged.
• Why is revised_fee updated on row 4, but not 5-9?
• Because on row 4 the fee is for the next 30 units that they have supplied ( any fee amount on rows 5-9 would be an overcharge).
• Additionally, why is it updated on row 13 for the new product?
• Because on row 13, the total number of units supplied is 16 and since a fee is charged for 15 units that is supplied, a fee is charged for the next 15 units that is supplied.
Solution
‎02-22-2017 01:40 PM
Super Contributor
Posts: 268

## Re: re:

Sorry about the delay - it was 4am when you sent your response, and I was (mostly) fast asleep!

Ah, I see now. It all falls into place.

I think this is close to what you want:

``````data have;
infile cards dsd dlm='09'x firstobs=2;
attrib client length=\$ 4;
attrib product length=6;
attrib date length=4 informat=yymmdd8. format=ddmmyy10.;
attrib fee length=8 format=10.2;
attrib supply length=3;
attrib allowance length=3;
input client
product
date
fee
supply
allowance;
cards;
Client	Product	Date	Fee	Supply	Allowance
2569	2293243	20160102	25.00	7	30
2569	2293243	20160109	25.00	7	30
2569	2293243	20160117	26.00	8	30
2569	2293243	20160126	26.00	9	30
2569	2293243	20160201	26.00	6	30
2569	2293243	20160209	26.00	8	30
2569	2293243	20160214	26.00	5	30
2569	2293243	20160222	26.00	8	30
2569	2343451	20160201	28.00	5	15
2569	2343451	20160205	28.00	3	15
2569	2343451	20160207	28.00	8	15
2569	2343451	20160214	28.00	8	15
;
run;

proc sort data=have noequals;
by client product date;
run;

data want;
retain client product date fee revised_fee supply allowance;
set have;
attrib revised_fee max_fee length=4 format=10.2;
attrib accumulated_supply length=3;
retain revised_fee max_fee accumulated_supply;
by client product;
if first.product then do;
revised_fee = fee;
accumulated_supply = supply;
max_fee = fee;
return;
end;
max_fee = max(fee, max_fee);
accumulated_supply + supply;
if accumulated_supply < allowance
then revised_fee = 0;
else do;
revised_fee = max_fee;
accumulated_supply = 0;
end;
keep client product date fee revised_fee supply allowance;
run;
``````

I wasn't sure what to do with accumulated_supply when it ticked over - reset it to zero as I've done, or accumulated_supply - allowance.

Posts: 1,337

## Re: re:

I think you can combine the test and variable reset for both conditions that generate non-zero revised_fee:

``````data want (drop=consumed_from_current_allowance);
set have;
by client product;

retain revised_fee;
consumed_from_current_allowance+supply;
if first.product or consumed_from_current_allowance>=allowance then do;
revised_fee=fee;
if first.product then consumed_from_current_allowance=supply;
else consumed_from_current_allowance=0;
/*else consumed_from_current_allowance + (-allowance) ;*/
end;
else revised_fee=0;
run;``````

Regular Contributor
Posts: 247

## Re: re:

Hi LaurieF...Thank you so much.....it worked perfectly....ended up resetting the accumulated_supply with accumulated_supply - allowance

☑ This topic is solved.