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).
infile cards delimiter = ",";
input date name$ amount_due paid;
Final data :-
I hope, I am able to explain the problem. Thanks in advance.
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.
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.
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.
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.
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;
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".
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?
/* 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;
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.
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.