Page 1 of 1

Is it possible to subtract matching records from a file?

PostPosted: Mon Jul 18, 2016 7:17 pm
by deucalion0
Hello all,

So I have been trying to figure out how to subtract matching records from a dataset.

So in file 1 there would be the following

AAA
AAA
AAA
BBB
BBB
CCC
DDD
EEE

And in file 2:

AAA
AAA
BBB
EEE

So anything found in file 2 should be removed from file 1 which would leave me with:

AAA
BBB
CCC
DDD

I have been racking my brains trying to figure out how to do this, I was thinking along the lines of using sequence numbers or maybe using an OMIT, but I am not sure how to only OMIT record by record.

I have not found any examples of SORTS like this, is this even possible?

I have read the manual for a while but there is so many possibilities with DFSORT I have no idea which route to go down.

I would appreciate any advice or pointers on this.


Thanks!

Re: Is it possible to subttract matching records from a file

PostPosted: Mon Jul 18, 2016 9:36 pm
by BillyBoyo
You are partly on the right lines. You need a JOINKEYS. In the JOINKEYS you need to temporarily append/prepend (fixed vs variable-length records) a sequence number which "restarts" on the key. This code will be in the JNF1CNTL and JNF2CNTL datasets which respectively allow amending of the F1 and F2 files prior to the JOINKEYS.

In the JOINKEYS match on the key and the sequence number. JOIN UNPAIRED,F1,ONLY will just get the mismatches, and then in the REFORMAT statement define an output record from the F1 which ignores the sequence number.

Re: Is it possible to subttract matching records from a file

PostPosted: Tue Jul 19, 2016 8:58 pm
by deucalion0
BillyBoyo wrote:You are partly on the right lines. You need a JOINKEYS. In the JOINKEYS you need to temporarily append/prepend (fixed vs variable-length records) a sequence number which "restarts" on the key. This code will be in the JNF1CNTL and JNF2CNTL datasets which respectively allow amending of the F1 and F2 files prior to the JOINKEYS.

In the JOINKEYS match on the key and the sequence number. JOIN UNPAIRED,F1,ONLY will just get the mismatches, and then in the REFORMAT statement define an output record from the F1 which ignores the sequence number.


Hi Billy!

Thanks a lot, that was a great help and I managed to get it to work!

You saved me a lot of time thanks!!

:)