Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- Programming
- /
- Need to adjust the paid amount from non-zero values

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 05-29-2023 09:27 AM
(261 views)

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.

13 REPLIES 13

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

--

Paige Miller

Paige Miller

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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'

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'

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.