DATA Step, Macro, Functions and more

Calculate new variable - accumulation beyond limit

Accepted Solution Solved
Reply
Regular Contributor
Posts: 222
Accepted Solution

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: 251

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.

 

View solution in original post


All Replies
Super Contributor
Posts: 251

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: 222

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: 251

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.

 

Valued Guide
Posts: 797

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: 222

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 159 views
  • 0 likes
  • 3 in conversation