Create flag to connect records

[t34]

Hi, 

I need to create a flag to identify records. The aim is to avoid transactions that credit=debit on the same day. It´s a bit tricky because I have a count of days and I try to connect to the 2nd debit the credit, to avoid mistakes of count of days.

Agreement	Effective_Date1	Debit	Credit	Flag
A1	10/09/2019	0	30
A1	11/09/2019	0	750
A1	11/09/2019	0	750
A1	11/09/2019	0	260,75	1
A1	11/09/2019	260,75	0	1
A1	11/09/2019	260,75	0
A1	11/09/2019	260,75	0
A1	11/09/2019	260,75	0
A2	04/08/2020	0	1
A2	11/08/2020	150	0
A2	11/08/2020	150	0	2
A2	11/08/2020	150	0	3
A2	11/08/2020	0	150	2
A2	11/08/2020	0	150	3
A3	12/09/2020	100	0
A3	21/09/2020	100	0	5
A3	21/09/2020	100	0	4
A3	21/09/2020	0	100	4
A3	21/09/2020	0	100	5
A3	21/09/2020	0	100

Thanks in advance!

[ChrisNZ]

Please give us some data we can run.

How to convert datasets to data steps

[Astounding]

Perhaps you could explain a bit better why these are the FLAG values, and why they would be helpful.  They seem both illogical and useless, although I'm sure you would not agree.

Here is a different set of flags that could serve as a starting point for whatever you want to accomplish:

data want;
   set have;
   by agreement effective_date1 debit credit notsorted;
   if first.credit then flag=1;
   else flag + 1;
run;

[Ksharp · Accepted solution]

Are you coming from Europe ? Germany ?

data have;
infile cards truncover expandtabs;
input Agreement $	Effective_Date1 : ddmmyy10.	(Debit	Credit) (: commax32.);
format Effective_Date1 ddmmyy10.;
cards;
A1	10/09/2019	0	30	 
A1	11/09/2019	0	750	 
A1	11/09/2019	0	750	 
A1	11/09/2019	0	260,75	1
A1	11/09/2019	260,75	0	1
A1	11/09/2019	260,75	0	 
A1	11/09/2019	260,75	0	 
A1	11/09/2019	260,75	0	 
A2	04/08/2020	0	1	 
A2	11/08/2020	150	0	 
A2	11/08/2020	150	0	2
A2	11/08/2020	150	0	3
A2	11/08/2020	0	150	2
A2	11/08/2020	0	150	3
A3	12/09/2020	100	0	 
A3	21/09/2020	100	0	5
A3	21/09/2020	100	0	4
A3	21/09/2020	0	100	4
A3	21/09/2020	0	100	5
A3	21/09/2020	0	100
;


data debit(drop=credit) credit;
 set have;
 if debit=0 then do;debit=credit;output credit;end;
 if credit=0 then  output debit;
run;
proc sort data=debit;by Agreement 	Effective_Date1 debit;run;
proc sort data=credit;by Agreement 	Effective_Date1 debit;run;
data temp;
 ina=0;inb=0;
 merge debit(in=ina) credit(in=inb);
 by Agreement 	Effective_Date1 debit;
 if not ina then debit=0;
 if not inb then credit=0;
 if ina and inb then flag=1;
 run;
 data temp;
  set temp;
  if flag then n+1;
run;
data want;
 set temp;
 if flag then do;dummy=n;debit=0;output;debit=credit;credit=0;output;end;
  else output;
  drop flag n;
run;