finding odd

[rohithverma]

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

[PaigeMiller]

Please don't make us guess. Explain the logic that is used to turn the initial table into the final table.

[rohithverma]

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

[jmhorstman]

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

[rohithverma]

you can see the common chequeid .So for the common chequeid  we need to find 

[jmhorstman]

There are 3 records for CHEQUEID=4567. How did you decide that records 4 and 5 match and record 6 is a mismatch? It would be helpful if you can give the precise logic to be used.

[PaigeMiller]

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.

[ed_sas_member · Accepted solution]

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;

[rohithverma]

Hi Ed ,really it's awesome it works .Could you please explain the second datastep match2 working please .I am bit confused what's happening in that .

[ed_sas_member]

Hi @rohithverma 

Awesome!

Here is how the code works:

• Step1:

• Step2:

So the main assumption is that matching records can only be n and n+1.

[rohithverma]

thank you so much

[rohithverma]

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 

[ed_sas_member]

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,