To compare two files (SSN fields)

[vikasbs]

I have a requirement to compare two files .The field i am trying to compare is SSN .

The requirement is : There are two files FILEA and FILEB

I need to compare FILEA with FILEB where in the output file i need the SSN's found on FILEA not on FILEB

I am trying to use COMPAREX but not able to get the desired result.
Can somebody help me out in this.

Regards
Vikas

[Frank Yaeger]

Can you use DFSORT instead of COMPAREX? If so, you can use a variation of the Smart DFSORT Trick discussed at:

http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/srtmst02.html#t05
_________________
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort/

[Guest]

Hi Frank

Kinldy confirm do you want me to use the trick discussed for the topic
"Create files with matching and non-matching records (without and with duplicates)"

Request you to confirm .

Thanks and Regards
Vikas

[Guest]

Hi Frank

This is my exact requirement.

In the production file (FILE A) i've the data like this

SSN STATUS
---------------------------
123456789 ACTIVE
884347737 WAITING
937384999 ACTIVE

In the second file (FILE B) i've have data like this

POLICY NUMBER SSN LNAME FNAME ADDRESS (...etc)
---------------------------------------------------------------


Now the LRECL for both the files are 423

The out put file should have the

1)SSN on FILEA but not on FILEB and in the next step it is the other way round

2)SSN on FILEB but not on FILEA.

Please let me know if you want me to be more clear of the requirement.

Regards
Vikas

[Frank Yaeger]

Vikas,

Here's a DFSORT/ICETOOL job that will do what I think you asked for. I assumed the SSN is in positions 1-9 in input file1 and in positions 19-27 in input file2, and that you just wanted the SSN in the output files. I also assumed that there are no duplicate SSNs within input file1, and no duplicate SSNs within input file2.

[Guest]

Hi Frank,

Thanks a ton for your assistance it worked.

I've another question

1)could you please let me know what is that i need to change to get the entire record in the output files.

Frank i've another requirement which is very similar to the one i have posted

2)The requirement is to get the records which does not match during compare.Can i use NE in the include card.Will that work.

Thanks a ton again please do the needful.

Regards
Vikas

[Frank Yaeger]

1) The way you described your requirement, I thought you only wanted the SSN in the output data sets. So that's what I showed you how to do.
If you want the entire record in each output data set, you can use a DFSORT/ICETOOL job like this:

[vikasbs]

Hi Frank,

I just tried the new JCL but it ends with MAXCC=16 stating following message

1) OUTFIL FNAMES=OUT1,INCLUDE=(433,2,CH,EQ,C'11'),BUILD=(1,423)
$
INVALID OUTFIL STATEMENT OPERAND
OUTFIL FNAMES=OUT2,INCLUDE=(433,2,CH,EQ,C'22'),BUILD=(1,423)
$
INVALID OUTFIL STATEMENT OPERAND

I also tried using OUTREC instead of BUILD even that didn't work.

2)Frank the second requirement which i coated in my previous post was for the next step which uses one of the file created by the above step which we are currently working on .


Kindly reply and do the neeedful.

Regards
Vikas

[Frank Yaeger]

OVERLAY and BUILD require z/OS DFSORT V1R5 PTF UQ95214 or DFSORT R14 PTF UQ95213 (Dec, 2004). So if the $ is pointing to BUILD, that's why. Ask your System Programmer to install the appropriate PTF.

[Guest]

Hi Frank,

I check in the DFSMSG we have DFSORT REL 14.0 with us not the one you've mentioned i guess.

We i guess i will come to the point 2 later once we resolve this since it would be confusing sorry for that.

We when i used OUTREC=(1,423) instead of BUILD i got the following message
"ICE027A 1 END OF FIELD BEYOND MAXIMUM RECORD LENGTH"

I forget to mention one thing
//IN1 DD DSN=... input file1 (FB/423) -- > My IN1 file has LRECL = 80
//IN2 DD DSN=... input file2 (FB/423) ---> My IN2 file has LRECL = 423
//OUT1 DD DSN=... output file1 (FB/423)
//OUT2 DD DSN=... output file2 (FB/423)

Is it ok to have IN1 LRECL = 80 ...please let me know.

Vikas

[vikasbs]

Hi Frank

I would like to explain the requirement 2

As you know we get two output file from the step which we are working

FILEA and FILEB

Now in the next step the above mentioned file would be compared to another file FILEC

Now the compare once again will be on the SSN so i am writing out the all the information about all the three files

FILEA - LRECL - 80,SSN position 1 and the length (9) so (1,9)

FILEB - LRECL - 423,SSN position 8 and the length (9) so (8,9)
(I've given the position and LRECL assume we get all the fields associated with the SSN)

FILEC - LRECL - 1042,SSN position (11)and length (9) so (11.9 )


Frank this is the requirement ,

1)I need to compare FILEA with FILEC and write the records not found in FILEC to output file with full details to output file.

2)In the next step or in the same step i need to compare FILEB with FILEC and write the records not found in FILEC to output file with full details output file.

I Kinldy request you to help me out with this and do the needful.

Regards
Vikas

[Frank Yaeger]

[Frank Yaeger]

For requirement 2:

Let's call FILED the output file for FILEA vs FILEC, and FILEE the output file for FILEB vs FILEC. Do you just need FILED and FILEE, or do you need FILEA, FILEB, FILED and FILEE? In other words, do you need to keep FILEA and FILEB or are they just temporary data sets you use to get FILED and FILEE?

Also, am I right in assuming that FILED has LRECL=80 and has the records from FILEA not found in FILEC, and that FILEE has LRECL=423 and has the records from FILEB not found in FILEC?
_________________
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort/

[vikasbs]

Hi Frank

The last JCL which you gave worked fine and my profound thanks to you.

Frank your understanding is right about the requirement no 2.

Please find the answers to your questions.

We need to keep FILEA and FILEB and in the output i would just need FILED and FILEE.

The specification of FILEC are

FILEC - LRECL - 1042,SSN position (11)and length (9) so (11.9 )

Yes Frank you are right the FILED would have LRECL=80 and has the records from FILEA not found in FILEC, and that FILEE has LRECL=423 and has the records from FILEB not found in FILEC.

Frank can we do the above process in two different steps

a)one for FILEA and FILEC compare

b)another for FILEB and FILEC compare

Kindly do the needful.

Regards
Vikas

[Frank Yaeger]

Vikas,

Here's a DFSORT/ICETOOL job that produces FILEA, FILEB, FILED and FILEE as requested in one step. If you really need it in two steps instead of one step, let me know.