topic Re: finding odd in SAS Programming

finding odd

rohithverma — Sat, 01 Feb 2020 11:23:22 GMT

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

Re: finding odd

PaigeMiller — Sat, 01 Feb 2020 11:27:54 GMT

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

Re: finding odd

rohithverma — Sat, 01 Feb 2020 11:36:54 GMT

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

Re: finding odd

jmhorstman — Sat, 01 Feb 2020 11:51:57 GMT

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

Re: finding odd

rohithverma — Sat, 01 Feb 2020 11:53:23 GMT

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

Re: finding odd

jmhorstman — Sat, 01 Feb 2020 11:59:09 GMT

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.

Re: finding odd

PaigeMiller — Sat, 01 Feb 2020 11:59:33 GMT

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.

Re: finding odd

ed_sas_member — Sun, 02 Feb 2020 09:13:54 GMT

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;

Re: finding odd

rohithverma — Sat, 01 Feb 2020 18:35:19 GMT

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 .

Re: finding odd

ed_sas_member — Sun, 02 Feb 2020 09:48:47 GMT

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.

Re: finding odd

rohithverma — Sun, 02 Feb 2020 17:48:53 GMT

thank you so much

Re: finding odd

rohithverma — Mon, 03 Feb 2020 07:27:59 GMT

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

Re: finding odd

ed_sas_member — Mon, 03 Feb 2020 08:07:44 GMT

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,