topic Re: reconcilation of amount in SAS Programming

reconcilation of amount

rohithverma — Sat, 01 Feb 2020 06:57:06 GMT

i have input data like as mentioned below . i want to filter the matched debit and credit observations into one dataset and the discrepent one into another dataset.

input

sno	DEBIT	CREDIT	CHEQUEID	VDATE
1	200	0	1234	1-Jan-20
2	200	0	1234	1-Jan-20
3	0	200	1234	2-Jan-20
4	400	0	4567	1-Jan-20
5	400	0	4567	1-Jan-20
6	400	0	4567	1-Jan-20
7	0	400	4567	2-Jan-20
8	600	0	6789	1-Jan-20

The output should be

table 1 output

sno	DEBIT	CREDIT	CHEQUEID	VDATE	FLAG
2	200	0	1234	1-Jan-20	MATCHED
3	0	200	1234	2-Jan-20	MATCHED
6	400	0	4567	1-Jan-20	MATCHED
7	0	400	4567	2-Jan-20	MATCHED

table 2 output

sno	DEBIT	CREDIT	CHEQUEID	VDATE
1	200	0	1234	1-Jan-20
4	400	0	4567	1-Jan-20
5	400	0	4567	1-Jan-20
7	600	0	6789	1-Jan-20

Re: reconcilation of amount

Ksharp — Sat, 01 Feb 2020 08:07:07 GMT

data have;
infile cards expandtabs truncover;
input sno 	DEBIT 	CREDIT 	CHEQUEID 	VDATE $20.;
cards;
1 	200 	0 	1234 	1-Jan-20
2 	200 	0 	1234 	1-Jan-20
3 	0 	200 	1234 	2-Jan-20
4 	400 	0 	4567 	1-Jan-20
5 	400 	0 	4567 	1-Jan-20
6 	400 	0 	4567 	1-Jan-20
7 	0 	400 	4567 	2-Jan-20
8 	600 	0 	6789 	1-Jan-20
;

data debit;
 set have;
 if debit ne 0;
 drop credit;
run;
data credit;
 set have;
 if credit ne 0;
 drop debit;
run;
data temp1;
 ina=0;inb=0;
 merge debit(in=ina) credit(in=inb rename=(
 credit=debit sno=_sno vdate=_vdate));
 by debit CHEQUEID;
 if ina and inb then output ;
 run;
data want1;
 set temp1;
 credit=0;output;
 credit=debit;debit=0;sno=_sno;vdate=_vdate;output;
 drop _: ;
run;
proc sql;
create table want2 as
select * from have
 where sno not in (select sno from want1);
quit;

Re: reconcilation of amount

rohithverma — Sat, 01 Feb 2020 10:27:54 GMT

NO, here you are creating a duplicate observations

Re: reconcilation of amount

Ksharp — Sat, 01 Feb 2020 10:38:52 GMT

Here is what I get .

Isn't it what you are looking for ?