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!
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;
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;
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;
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!
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.