Create net columns by day and customer - arrays

[t34]

Hello! I need to create in SAS net columns of other columns group by day and customer. I don´t know how to use information data that is previous, maybe it will be useful with arrays.. but I don´t know accuracy the method.

Thanks in advance!

DATA have;
infile datalines dsd delimiter=',';
informat Effective_date1 ddmmyy10.;
format Effective_date1  ddmmyy10.;
input Agreement	Customer	Effective_date1	Credit	Debit	Balance AmountArrears;
datalines;
1,0,01/01/2020,100,0,10000,100,			
2,0,02/01/2020,0,10,500,10,			
1,0,03/02/2020,0,50,10000,50,			
2,0,03/02/2020,0,10,490,20,			
1,0,05/02/2020,50,0,9950,0,			
2,0,05/02/2020,0,10,480,30,			
1,0,08/02/2020,0,30,9920,30,			
2,0,08/02/2020,30,0,450,0,			

;;;;
RUN;

DATA want;
infile datalines dsd delimiter=',';
informat Effective_date1 ddmmyy10.;
format Effective_date1  ddmmyy10.;
input Agreement	Customer Effective_date1 Credit	Debit Balance AmountArrears CreditNet	DebitNet	BalanceNet AmountArrearsNet	;
datalines;
1,0,01/01/2020,100,0,10000,0,100,0,10000,0,			
2,0,02/01/2020,0,10,500,10,0,10,500,10,		
1,0,03/02/2020,0,50,10000,50,0,60,10490,70,			
2,0,03/02/2020,0,10,490,20,0,60,10490,70,			
1,0,05/02/2020,50,0,9950,0,50,10,10430,30,		
2,0,05/02/2020,0,10,480,30,50,10,10430,30,			
1,0,08/02/2020,0,30,9920,30,30,30,10370,30,			
2,0,08/02/2020,30,0,450,0,30,30,10370,30,			

;;;;
RUN;






			

[PaigeMiller]

Using words and/or plain math formulae, how do you want to compute CreditNet, DebitNet, BalanceNet, and AmountArrearsNet.

[t34]

Sum the values of each column for same customer and same day

[ballardw]

One of the functions available is SAS is the LAG function that allows you to get values from previous records (carefully conditions do not work as you might expect).

Here is a short demo of the generic approach I think you may be looking for.

data have;
   input customer $ value;
datalines;
A   123
A   456
A   3
B   44
B   22
;

data want;
   set have;
   by customer;
   lastvalue= lag(value);
   if first.customer then newvalue=.;
   else newvalue = sum(value, lastvalue);
run;

You could drop the lastvalue variable but I left it in to show what happens.

You would likely need several "last" variables to keep track and the first.customer likely needs a " then do; end; " with several variables initialized to either 0 or missing as desired.

You would need to make sure the data is sorted by customer and date if it isn't already.

[t34]

Thanks a lot for your answer!!!
The problem is that in the real table- on same day, there are 3, 4 or... n transactions

[ballardw]

---

@t34 wrote:
Thanks a lot for your answer!!!
The problem is that in the real table- on same day, there are 3, 4 or... n transactions

---

And the problem with my example is what exactly?

You didn't provide any actual description of what calculation is needed. I show how to use previous value for a calculation with the option of resetting when a key value changes.

Try it with ONE variable and see.

If AGREEMENT has any role in the problem you really need to describe it as well.

[t34]

First,apologies for the explanation
A customer can have several agreeements
I need the net value for each day by agreement in every row.
Os in your example (without date) , I need the value 459 for every A and 66 for every B

[ballardw]

---

@t34 wrote:
First,apologies for the explanation
A customer can have several agreeements
I need the net value for each day by agreement in every row.
Os in your example (without date) , I need the value 459 for every A and 66 for every B

---

So  still waiting for definitions of how to get the "net" anything.

[t34]

Sorry @ballardw 

For each customer and effective_date 

• NetCredit= sum of values of credits of the same day and effective day
• NetDebit= sum of values of debits of the same day and effective day
• NetAmountArrears= sum of values of amount arrearsof the same day and effective day

When there are records in the same day, all net columns have to value the same in all records of these days.

If effective_date are differents for each customer

amount_arrears = last_amount_arrears + cr - dr

You can forget about balance (it is not interested here)

Thanks a lot

[ballardw]

---

@t34 wrote:

Sorry @ballardw 

For each customer and effective_date 

• NetCredit= sum of values of credits of the same day and effective day
• NetDebit= sum of values of debits of the same day and effective day
• NetAmountArrears= sum of values of amount arrearsof the same day and effective day

When there are records in the same day, all net columns have to value the same in all records of these days.

---

You have one date value. So what is the difference between "same day" and "effective day"? The way you phrase that there should be another variable involved. Please phrase things in terms of VARIABLES.

[t34]

I mean "same day" when a record has got the equal Effective_date1 than other record