Calculate new variable - accumulation beyond limit

[twildone]

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

[LaurieF]

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?

[twildone]

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.

[LaurieF · Accepted solution]

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.

[mkeintz]

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;

[twildone]

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