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 |
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;
NO, here you are creating a duplicate observations
Here is what I get .
Isn't it what you are looking for ?
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.