Matching Data

[jhammouda]

Hi,

I am understanding your problem as you are having a data set that contains a list of transactions that have been paid, and another data set that contains a list of transactions that reversed charges in the first list. Your objective is to purge all the transactions that have been reversed and keep only charges that were paid but never reversed. If I understood your problem correctly, then the following should address it.  I have included verbose comments to explain the logic, and have used the data set you have provided to test the code. You should be able to cut and paste the code into sas and run it, and look at the log, and the output to see if it is producing what you want.

I hope this helps.

Good luck.

** Select everything below this line, and paste it in sas and run it;

data Reverse_transactions;
input id seqnum pay;
datalines;
1 7 -10
1 12 -10
1 13 -5
2 16 -15
3 20 -20
;

data Charges;
input id seqnum pay;
datalines;
1 8 10
1 9 10
1 10 10
1 11 5
1 14 15
2 15 15
2 17 25
2 18 15
3 19 20
3 21 25
;
run;

/* It is more useful to have the reverse transaction
with pay being positive, it will make the matching more straightforwad.*/
/* I am also going to rename seqnum to reverse_seqnum, this is not needed
but will be helpful for debugging; later it will show which transactions were cancelled
by which transactions*/
data pos_reverse_trans (rename=(seqnum=Reverse_seqnum));
* remove the rename once you finish

debugging or drop the column in you final data set;
set reverse_transactions;
pay=-pay;
run;

/* now the idea is to consider the pos_reverse_trans like a look up table
we will consider each transaction in the charges data set, if we find a matching
transaction in the pos_reverse_trans, then we considere that transaction reveresed
and we do not keep it. Basically the problem is essentially do a match merge of
the charges and the pos_reverse_trans and keep only the oservations that are in the
charges data set but not in the pos_reverse_transaction set.*/
* Need to prepare the data set for merge by sorting on by ID and Pay;

/* I am sorting the data sets in place here, instead of making a sorted copy.
Please do that if you want to preserve the original data set*/
proc sort data=charges;
by ID pay;
run;

proc sort data=pos_reverse_trans;
by ID pay;
run;

data non_reversed_charges;
merge charges(in=C) pos_reverse_trans(in=R);
putlog _ALL_;

/* take a look at the log to see how the matching was done, remove once you know
it is working as desired and drop reverse_seqnum column; */
if R=0;
* if the transaction was not reversed implicit write it to the non_reversed_charges;
run;

[jhammouda]

need to add by ID pay; right after the merge statement;