I have input
sno | DEBIT | CREDIT | CHEQUEID | VDATE |
2 | 200 | 0 | 1234 | 1-Jan-20 |
3 | 0 | 200 | 1234 | 2-Jan-20 |
4 | 400 | 0 | 4567 | 1-Jan-20 |
5 | 0 | 400 | 4567 | 1-Jan-20 |
6 | 400 | 0 | 4567 | 2-Jan-20 |
and i need output like
sno | DEBIT | CREDIT | CHEQUEID | VDATE | flag | |
2 | 200 | 0 | 1234 | 1-Jan-20 | match | |
3 | 0 | 200 | 1234 | 2-Jan-20 | match | |
4 | 400 | 0 | 4567 | 1-Jan-20 | match | |
5 | 0 | 400 | 4567 | 1-Jan-20 | match | |
6 | 400 | 0 | 4567 | 2-Jan-20 | mismatch |
when debit = credit the flag should be raised as MATCH else MISMATCH
data have;
infile datalines dlm="09"x;
input sno DEBIT CREDIT CHEQUEID VDATE:date9.;
format VDATE date9.;
datalines;
2 200 0 1234 1-Jan-20
3 0 200 1234 2-Jan-20
4 400 0 4567 1-Jan-20
5 0 400 4567 1-Jan-20
6 400 0 4567 2-Jan-20
;
run;
data match1;
merge have have(keep= CREDIT CHEQUEID rename=(CREDIT=CREDIT2 CHEQUEID=CHEQUEID2) firstobs=2);
if DEBIT=CREDIT2 and DEBIT>0 and CHEQUEID=CHEQUEID2 then flagd=1;
drop CREDIT2 CHEQUEID2;
run;
data match2;
set match1 ;
if _n_>1 then set match1(keep=flagd rename=(flagd=flagc));
length flag $ 8;
if flagd or flagc then flag="match";
else flag="mismatch";
drop flagc flagd;
run;
Please don't make us guess. Explain the logic that is used to turn the initial table into the final table.
logic is when the debit amount is equals to credit amount then the flag should be raised as "MATCH"
or else "MISMATCH" should be raised
How do you identify which credit should be compared with which debit? It doesn't appear that SNO, CHEQUEID, or VDATE will help us pair up records. Should they simply be paired based on their positions within the data set? (i.e. first two records taken together, then the next two, etc.)
you can see the common chequeid .So for the common chequeid we need to find
So 4567 appears three times in the original data, how is it decided which one is the mismatch.
Please give us a full description of the logic used with this data, instead of these incredibly incomplete and partial descriptions.
data have;
infile datalines dlm="09"x;
input sno DEBIT CREDIT CHEQUEID VDATE:date9.;
format VDATE date9.;
datalines;
2 200 0 1234 1-Jan-20
3 0 200 1234 2-Jan-20
4 400 0 4567 1-Jan-20
5 0 400 4567 1-Jan-20
6 400 0 4567 2-Jan-20
;
run;
data match1;
merge have have(keep= CREDIT CHEQUEID rename=(CREDIT=CREDIT2 CHEQUEID=CHEQUEID2) firstobs=2);
if DEBIT=CREDIT2 and DEBIT>0 and CHEQUEID=CHEQUEID2 then flagd=1;
drop CREDIT2 CHEQUEID2;
run;
data match2;
set match1 ;
if _n_>1 then set match1(keep=flagd rename=(flagd=flagc));
length flag $ 8;
if flagd or flagc then flag="match";
else flag="mismatch";
drop flagc flagd;
run;
Hi @rohithverma
Awesome!
Here is how the code works:
So the main assumption is that matching records can only be n and n+1.
A small doubt
As we are doing set when _n_ >1 the at the point of time for flag c the first value will be "." .Why this will happens .Please explain this
Hi @rohithverma
There is no issue according to these assumptions as the flag for matching credit records ('flagc') can occur only from the second row.
Best,
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.