Compare and write non-matching records to output file

monasu1998 · Active User Joined: 23 Dec 2005 Posts: 176 Location: India

Hi,

I want to compare 2 files and write the records those are in file 1 but not in file to a output file.

Could someone help with a code snippet please?

Thanks,

Garry Carroll · Posted: Thu May 02, 2013 7:55 pm

Google SUM FIELDS=NONE

Garry

monasu1998 · Active User Joined: 23 Dec 2005 Posts: 176 Location: India

Hi Garry,
SUM FIELDS=NONE will only remove the duplicates I mean the 2nd occurances.. I donot want the record in output if exists in both the files.

Thanks,

enrico-sorichetti · Posted: Thu May 02, 2013 8:06 pm

search the forum,
there are gazillions of examples doing it using JOINKEYS

Garry Carroll · Posted: Thu May 02, 2013 8:08 pm

Are the 2nd occurrences not duplicates???

You might want to use JOINKEYS which enables you to select records which are only in File1, only in File2 or are in both.

Garry.

Bill Woodger · Posted: Thu May 02, 2013 8:17 pm

Largely butchered from an example today. You'll need to change your keys, and explain better if this is not what you want. If your input(s) are not sortted, you'll need to remove the SORTED,NOSEQCK.

monasu1998 · Active User Joined: 23 Dec 2005 Posts: 176 Location: India

Hi Garry,

I am glad that you are trying to help me with a solution. But I donot want any of those occurances. I just need that is in 1 and not in 2.

background: I am doing some testing where in in my test run there should be few records less from the requirements from business. Now Business wants me to send a file with just what has been removed.
I am comparing the prod vs test file.

Note: Both the files are sorted with same key.

Thanks,

Garry Carroll · Posted: Thu May 02, 2013 8:24 pm

Mishra,

Bill's job suitable changed for the FIELDS= values should do what you want. You will want FIELDS=(1,lrecl,A) where lrecl matches the LRECL of your records.

Garry.

monasu1998 · Active User Joined: 23 Dec 2005 Posts: 176 Location: India

Hi Garry,

I'm close to the result.

I used
JOINKEYS FILES=F1,FIELDS=(1,66,A)
JOINKEYS FILES=F2,FIELDS=(1,66,A)
JOIN UNPAIRED,ONLY,F1
OPTION COPY

what I see in the sortout is the records those are common in both the files. But not the ones which are removed in my test file.

Just need to reverse the sort card so that the diff comes in sortout..

Thanks,

Bill Woodger · Posted: Thu May 02, 2013 8:45 pm

Did you typo F1,ONLY to ONLY,F1?

The example show should give you all records which are on file 1, but not on file 2, based on the key you provided.

mistah kurtz · Posted: Thu May 02, 2013 8:54 pm

Hi Mishra,

check this link for a better understanding.

monasu1998 · Active User Joined: 23 Dec 2005 Posts: 176 Location: India

Hi Bill,

yes, that was a typo. But changing that is not giving me correct result..

SORTJNF1 is the prod file with records 651911
SORTJNF2 is the test file with records 650933

I want the 978 records which are in prod and not in test in the sortout file.

With the current following sort card,

JOINKEYS FILES=F1,FIELDS=(1,66,A)
JOINKEYS FILES=F2,FIELDS=(1,66,A)
JOIN UNPAIRED,F1,ONLY
OPTION COPY

all the reocrds from the prod file is written to sortout. which is not I want..

Thanks,

Bill Woodger · Posted: Thu May 02, 2013 9:12 pm

Well, show some sample data (masked, if necessary) the key field should be enough. RECFM and LRECL of both input files.

dick scherrer · Posted: Thu May 02, 2013 11:31 pm

Hello,

Show some sample values from both files. If a file can have duplicates, show some duplicates in your sample data.

Show the output you want from the sample data you post.

Only the "match" keys need to be shown - not the rest of the data, unless it is needed for the selection.