topic Re: Create net columns by day and customer - arrays in SAS Programming

Create net columns by day and customer - arrays

t34 — Wed, 25 Nov 2020 18:22:10 GMT

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;

Re: Create net columns by day and customer - arrays

PaigeMiller — Wed, 25 Nov 2020 16:40:35 GMT

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

Re: Create net columns by day and customer - arrays

t34 — Wed, 25 Nov 2020 16:50:21 GMT

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

Re: Create net columns by day and customer - arrays

ballardw — Wed, 25 Nov 2020 16:50:36 GMT

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.

Re: Create net columns by day and customer - arrays

t34 — Wed, 25 Nov 2020 16:54:28 GMT

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

Re: Create net columns by day and customer - arrays

ballardw — Wed, 25 Nov 2020 17:03:51 GMT

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

Re: Create net columns by day and customer - arrays

t34 — Wed, 25 Nov 2020 17:51:09 GMT

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

Re: Create net columns by day and customer - arrays

ballardw — Wed, 25 Nov 2020 18:01:02 GMT

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

Re: Create net columns by day and customer - arrays

t34 — Wed, 25 Nov 2020 18:21:11 GMT

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

Re: Create net columns by day and customer - arrays

ballardw — Wed, 25 Nov 2020 18:27:23 GMT

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

Re: Create net columns by day and customer - arrays

t34 — Wed, 25 Nov 2020 21:23:08 GMT

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