Need to adjust the paid amount from non-zero values

[Decay2020]

Hi all,

I have the below month wise dataset 'have'.

I want data 'final' such that the column 'paid' gets completely netted off (adjust) against non-zero column 'amount_due' and we arrive at coloumn 'final' as shown below in 'final data'.

For Example:- In may-22, SAM paid 100, this amount should get distributed to non-zero value in 'amount_due' previous months (amount_due - paid). 

data Have;
infile cards delimiter = ",";
input date name$ amount_due paid;
cards;

44592,Sam,10,0,10
44620,Sam,20,0,20
44651,Sam,30,0,20
44681,Sam,40,0,0
44712,Sam,50,100,0
44592,Sally,30,0,30
44620,Sally,40,0,40
44651,Sally,50,0,50
44681,Sally,60,0,30
44712,Sally,0,10,0
44742,Sally,20,40,0

;

run;

Final data :-

Date	Name	Amount due	Paid	Final
January-22	Sam	10	0	10
February-22	Sam	20	0	20
March-22	Sam	30	0	20
April-22	Sam	40	0	0
May-22	Sam	50	100	0
January-22	Sally	30	0	30
February-22	Sally	40	0	40
March-22	Sally	50	0	50
April-22	Sally	60	0	30
May-22	Sally	0	10	0
June-22	Sally	20	40	0

I hope, I am able to explain the problem. Thanks in advance. 

[ballardw]

I think that  you need to provide a few more rules, not examples, but rules as to how each of your Final values is calculated.

Also, those 'dates' look suspiciously like the numeric values behind Excel or other Microsoft products. To show a value anywhere near "January 22" you need to adjust them. Search the forum for "EXCEL Date" and you should find a number of examples.

BTW, full dates or month and 4-digit year are clearer to understand.

[Decay2020]

please feel free to take any date as an example, it hardly matters.

we just need see how much a person paid extra and adjust this excessive amount in the past months due (i.e. previous non-zero value).  

[Tom]

---

@Decay2020 wrote:

please feel free to take any date as an example, it hardly matters.

we just need see how much a person paid extra and adjust this excessive amount in the past months due (i.e. previous non-zero value).  

---

That is your disconnect.  I do not see any variable in your dataset that has this "past months due" amount.

Or do you also need to add up the values in the "Amount Due" variable from period to period?

NOTE: SAS variable names do not allow spaces.  So you probably will want to define some actual variable names for these before doing any real programming.

[Decay2020]

please ignore the space in the variable name. 

past months due mean - amount_due in previous months.

So you see, in may-22, SAM paid 100, and only 50 was due.. so the extra 50 should get adjusted in the previous months. and likewise.

I hope, this makes any sense to you. 

[PaigeMiller]

Again, asking for a more complete and comprehensive explanation.

Why on row 4 is FINAL equal to 0?

Why does SALLY have a FINAL equal to zero for May 22. This has not been explained and is not at all obvious.

[Decay2020]

In row 4, SAM owed 40 (amount due) which got adjusted from what he paid in row 5. 
In row 5, he owed 50 (amount due) but he paid 100 so this excess amount i.e. 60 (100-40) gets adjusted in row 4. and further the rest  that is 20 (60-40) gets adjusted in row 3

And for Sally, in may22, she owed 0 so the final amount is 0. as stated multiple times, we need to adjust the excessive amount to only non-zero previous dues. 

[PaigeMiller]

Agreeing with @ballardw, I don't see how column FINAL is calculated, we need more comprehensive explanation.

[Decay2020]

Okay, in essence we are computing 'amount_due - paid'.

but if the person has paid more than he due for that month, then the excessive amount gets adjusted in past months due.

[Tom]

I still don't get what you are saying.  It suspect you want to somehow re-write history by modifying an observation you already moved past.  That is very difficult I am not at all sure why would care to do that.

Also your data step does not make much sense to me.  Your input statement is reading fewer variables than you have in your data lines.  So it sounds like the first number is the amount they charged today and the last number is the amount they paid back today.  I cannot make any sense of that middle number.

To make a running total that reflects charges and payments just try something like this.

data Have;
  input name $ date :yymmdd. charged paid due ;
  format date yymmdd10.;
cards;
Sally 2022-01-31 30 30 0
Sally 2022-02-28 40 40 0
Sally 2022-03-31 50 50 0
Sally 2022-04-30 60 30 0
Sally 2022-05-31 0 0 10
Sally 2022-06-30 20 0 40
Sam 2022-01-31 10 10 0
Sam 2022-02-28 20 20 0
Sam 2022-03-31 30 20 0
Sam 2022-04-30 40 0 0
Sam 2022-05-31 50 0 100
;

data want ;
  set have;
  by name date ;
  total + charged ;
  if first.name then total=charged;
  total + - paid ;
run;

proc print;
run;

Result

OBS    name           date    charged    paid    due    total

  1    Sally    2022-01-31       30       30       0       0
  2    Sally    2022-02-28       40       40       0       0
  3    Sally    2022-03-31       50       50       0       0
  4    Sally    2022-04-30       60       30       0      30
  5    Sally    2022-05-31        0        0      10      30
  6    Sally    2022-06-30       20        0      40      50
  7    Sam      2022-01-31       10       10       0       0
  8    Sam      2022-02-28       20       20       0       0
  9    Sam      2022-03-31       30       20       0      10
 10    Sam      2022-04-30       40        0       0      50
 11    Sam      2022-05-31       50        0     100     100

But I do not know what to do with those numbers you had in the variable I have labeled "due".

[Decay2020]

please ignore he last column or you can name that column as final.
we have date, name, amount_due, paid.
amount_due is the amount due in each month and paid is the amount a person paid.
we want to subtract the paid amount from amount_due and get the final due amount as given in column 'Final'

[Tom]

---

@Decay2020 wrote:
please ignore he last column or you can name that column as final.
we have date, name, amount_due, paid.
amount_due is the amount due in each month and paid is the amount a person paid.
we want to subtract the paid amount from amount_due and get the final due amount as given in column 'Final'

---

It still does not make sense. And the numbers you shared do not add up.  Please post some more realistic numbers and explain exactly what you want to happen.

What is the relationship between the amount due on a given date and the amount due on a prior date?  Isn't the amount due something that should be calculated based on the previous balance plus any new charges and minus any payments?

How can someone's amount due change from month to month without them either incurring new charges or paying off old debts?

Are you trying to figure out which of the individual charges have been paid off?  If so why?  How does that add any extra information over just knowing what amount is currently due?  For example are you planning on charging interest on the older charges?  Or charging different interest rates based on when the charge was incurred?

[Ksharp]

/*
Assuming I understood what you are looking for.
*/
data Have;
infile cards delimiter = ",";
input date name$ amount_due paid;
format date yymmdd10.;
cards;
44592,Sam,10,0,10
44620,Sam,20,0,20
44651,Sam,30,0,20
44681,Sam,40,0,0
44712,Sam,50,100,0
44592,Sally,30,0,30
44620,Sally,40,0,40
44651,Sally,50,0,50
44681,Sally,60,0,30
44712,Sally,0,10,0
44742,Sally,20,40,0
;

run;
proc sort data=have out=temp;
by name descending date;
run;
data temp2;
 set temp;
 by name;
 if first.name then do;cum_amount_due=0; cum_paid=0;end;
 cum_amount_due+amount_due; cum_paid+paid;
 balance=cum_paid-cum_amount_due;
run;
data temp3;
 set temp2;
 by name;
want=ifn(balance>=0,0,amount_due);

retain found 0;
if first.name then found=0;
if balance<0 and not found then do;found=1;want=-balance;end;
drop cum_: balance found;
run;
proc sort data=temp3 out=want;
by name date;
run;

[Decay2020]

Thanks Ksharp. I will surely try this. but is there any way of doing this witthout sorting as the data have millions of rows.