BookmarkSubscribeRSS Feed
Decay2020
Fluorite | Level 6

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

DateNameAmount duePaidFinal
January-22Sam10010
February-22Sam20020
March-22Sam30020
April-22Sam4000
May-22Sam501000
January-22Sally30030
February-22Sally40040
March-22Sally50050
April-22Sally60030
May-22Sally0100
June-22Sally20400

 

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

13 REPLIES 13
ballardw
Super User

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
Fluorite | Level 6

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
Super User Tom
Super User

@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
Fluorite | Level 6

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
Diamond | Level 26

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
Decay2020
Fluorite | Level 6

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
Diamond | Level 26

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

--
Paige Miller
Decay2020
Fluorite | Level 6

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
Super User Tom
Super User

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
Fluorite | Level 6
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
Super User Tom
Super User

@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
Super User
/*
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
Fluorite | Level 6
Thanks Ksharp. I will surely try this. but is there any way of doing this witthout sorting as the data have millions of rows.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1085 views
  • 0 likes
  • 5 in conversation