Create a counter that depends by amount

[t34]

Hello everyone,

I need to create a counter that has to count if is the same agreement, same day and it depends if is credit or debit and the amount of debit or credit. They have to be dynamic because I don´t know times that there is a different amount on the same day.So, It may be a solution is with arrays... but I don´t know how to do it.

Below, you can find the counters that I need: Count credit and Count debit.

Agreement	Effective_date1	Debit	Credit	Count credit	Count debit	Amount
a1	01/02/2020	100		0	1	100
a1	01/02/2020	632		0	1	632
a1	01/02/2020		100	1	1	100
a1	01/02/2020		632	1	1	632
a1	01/02/2020	100		1	2	100
a1	01/02/2020	632		1	2	632
a1	01/02/2020		3	1	0	3
a1	01/02/2020	3		0	1	3
a1	22/02/2020	3		0	1	3
a2	02/03/2020	50		0	1	50

data have;
infile datalines delimiter=',';
informat Agreement $char4. Effective_Date1 ddmmyy10. Debit Credit;
format Agreement $char4. Effective_Date1 ddmmyy10. Debit Credit;
input Agreement Effective_Date1 Debit Credit;
datalines;
A1,01/02/2020,100,0	 
A1,01/02/2020,632,0	 
A1,01/02/2020,0, 100	 
A1,01/02/2020,0,632
A1,01/02/2020,100,0
A1,01/02/2020,632,0	 
A1,01/02/2020,0,3	 
A1,01/02/2020,3,0	 
A1,22/02/2020,3,0	 
A2,02/03/2020,50,0
;;

Thanks a lot!

Regards,

[PeterClemmensen]

So, count_debit should increment when the debit amount is not missing, correct?

Please explain why count_debit = 1 in obs 2 and 3.

And please be very specific in you logic 🙂

[t34]

Thanks!

Count_debit should increment when the debit amount is not missing. I need to link count_debit with the amount.

If there is a debit of 100, I need to count=1 in the counter_debit_100 but if there is a debit of 635 , I cannot count for 100, I need to count for 635.

For example:

Debit= 100 --> count_debit=1, amount=100

Debit= 100 --> count_debit=2, amount=100

Debit= 635 --> count_debit=1, amount=635

[Kurt_Bremser]

Either count_debit in obs 3&4 should be 0, or count_credit in obs 8 should be 1.

[Kurt_Bremser · Accepted solution]

Following your logic as I understand it, try this:

data have;
infile datalines delimiter=',';
informat Agreement $char4. Effective_Date1 ddmmyy10. Debit Credit;
format Agreement $char4. Effective_Date1 ddmmyy10. Debit Credit;
input Agreement Effective_Date1 Debit Credit;
datalines;
A1,01/02/2020,100,0  
A1,01/02/2020,632,0  
A1,01/02/2020,0, 100   
A1,01/02/2020,0,632
A1,01/02/2020,100,0
A1,01/02/2020,632,0  
A1,01/02/2020,0,3  
A1,01/02/2020,3,0  
A1,22/02/2020,3,0  
A2,02/03/2020,50,0
;;

data expanded;
set have;
n = _n_;
cr = (credit not in (0,.));
amount = sum(debit,credit);
run;

proc sort data=expanded;
by agreement effective_date1 cr amount n;
run;

data counted;
set expanded;
by agreement effective_date1 cr amount;
array ct (0:1) count_debit count_credit;
if first.cr
then do;
  ct{0} = 0;
  ct{1} = 0;
end;
if first.amount
then ct{cr} = 1;
else ct{cr} + 1;
run;

proc sort
  data=counted
  out=want (drop=cr n)
;
by n;
run;