topic Re: Need to adjust the paid amount from non-zero values in SAS Programming

Need to adjust the paid amount from non-zero values

Decay2020 — Mon, 29 May 2023 13:27:23 GMT

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.

Re: Need to adjust the paid amount from non-zero values

ballardw — Mon, 29 May 2023 15:01:41 GMT

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.

Re: Need to adjust the paid amount from non-zero values

PaigeMiller — Mon, 29 May 2023 15:09:25 GMT

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

Re: Need to adjust the paid amount from non-zero values

Decay2020 — Mon, 29 May 2023 15:13:07 GMT

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.

Re: Need to adjust the paid amount from non-zero values

Decay2020 — Mon, 29 May 2023 15:15:05 GMT

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).

Re: Need to adjust the paid amount from non-zero values

Tom — Mon, 29 May 2023 16:05:41 GMT

@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.

Re: Need to adjust the paid amount from non-zero values

Decay2020 — Mon, 29 May 2023 17:23:42 GMT

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.

Re: Need to adjust the paid amount from non-zero values

PaigeMiller — Mon, 29 May 2023 17:30:39 GMT

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.

Re: Need to adjust the paid amount from non-zero values

Decay2020 — Mon, 29 May 2023 17:54:58 GMT

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.

Re: Need to adjust the paid amount from non-zero values

Tom — Mon, 29 May 2023 18:45:56 GMT

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".

Re: Need to adjust the paid amount from non-zero values

Decay2020 — Tue, 30 May 2023 04:35:21 GMT

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'

Re: Need to adjust the paid amount from non-zero values

Ksharp — Tue, 30 May 2023 12:38:56 GMT

/*
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;

Re: Need to adjust the paid amount from non-zero values

Decay2020 — Tue, 30 May 2023 12:43:09 GMT

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

Re: Need to adjust the paid amount from non-zero values

Tom — Tue, 30 May 2023 15:34:48 GMT

@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?