Joined: 21 Nov 2008 Posts: 31 Location: United Kingdom
We receive 2 files from different sources that contain car registration numbers. I want to compare these 2 files and if matches are found, write these matches to a 3rd file.
File information as follows -
I have 2 files - FileA 237bytes FB. FileB 100bytes FB.
FileA pos 2 for 7chars contains an alphnumeric(ABC1234).
FileB pos 55 for 7chars contains an alphanumeric(ABC1234). I do not know what these alphanumeric chars will be.
Can you explain how to do this, if it can be done? Thanks
Joined: 21 Nov 2008 Posts: 31 Location: United Kingdom
Thanks for your help. It works fine when all fields are populated but I have since noticed that in my files not all are populated see my example below -
ABC RECORD 2 ----------------------------------
CDE ABC125 RECORD 2 ----------------------------------
ABC ABC128 RECORD 2 ----------------------------------
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
Because you have a file A record with blanks in the key field, and you also have a file B record with blanks in the key field, so as the keys match - the record gets selected.
Also - you have duplicates in file B which do not exist in file A, and these will also be selected. As you can see from your example. I guess that's my fault for not asking the right questions before coding
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Richard Galley,
Please answer the following questions
1. What is the RECFM of the files? FB or VB?
2. What is the format of the key at pos 2 for 6 bytes in file 1 and at pos 55 in file2?
3. Which file has duplicates on the key? file 1 or file 2 or both?
4. Please show a sample input data (including duplicates if you have) and desired output
Joined: 21 Nov 2008 Posts: 31 Location: United Kingdom
Hello Skolusu
My original post was slightly inaccurate as I had misunderstood my own information. I will start again....
We receive 2 files from different sources that contain car registration numbers. I want to compare FileB against FileA and if matches are found, write the full matched record to FileC.
File information as follows -
FileA 100bytes FB. FileB 237bytes FB.
FileA pos 55 for 7chars contains an alphnumeric(ABC1234).
FileB pos 2 for 7chars contains an alphanumeric(ABC1234).
FileA can contain blanks at pos 55.
FileB will not contain any blanks.
There will not be any duplicates in fileA but there may be duplicates in fileB. If any duplicates are found these need including in fileC. I have added examples of the files including fileC, what I would expect the end result to be -
If your intent is to match 2 files then the above keys do not have a matching key. So how did you get them in the output? or did you just show me a partial input?
Anywayz here is a DFSORT/ICETOOL job which will give you the matching records along with the duplicates if any in file2. Also the output is sorted on the key.
ABC123 VINF0RBB58AAA 1029062006LE712 rest of data
ABS19 VINF0RBB58AAB 1029062007LE710 rest of data
AGR1W VINF0RBB58AAC 1029062006DC520 rest of data
AGR1W VINF0RBB58AAF 1029062006DA127 rest of data
AG1 VINF0RBB58AAD 1029062006DA107 rest of data
AJ1 VINF0RBB58AAE 1029062006R6191 rest of data
AJ1 VINF0RBB58AAG 1029062006LP130 rest of data
let me know if you want to preserve the original order of the file B
Joined: 21 Nov 2008 Posts: 31 Location: United Kingdom
Hello Skolusu
What you provided me with in your last post worked great thanks with the info I had provided but I must appologise again as the information I had been given was still incorrect. This is definitely the correct spec now.
We receive 2 files from different sources that contain car registration numbers. I want to compare FileB against FileA and if matches are found, write the full matched record from FileB to FileC.
File information as follows -
FileA 237bytes FB. FileB 100bytes FB.
FileA pos 2 for 7chars contains an alphnumeric(ABC1234).
FileB pos 55 for 7chars contains an alphanumeric(ABC1234).
FileA will not contain any blanks.
FileB can contain blanks at pos 55.
There may be duplicates in fileA
There will not be any duplicates in fileB.
I have attached examples of FileA, FileB & what I would expect to find in FileC once the process has run.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Richard Galley,
Ok now you went back to just getting the unique matching records from the unique record file. If that is what you want you don't even need SPLICE. The following DFSORT/ICETOOL JCL will give you the desired results. A brief explanation of the job.
step0100 will eliminate the duplicates from 237 byte file and create a 100 byte file populating the key from pos 2 for 7 bytes onto pos 55.
Step0200 will concatenate 100 orginal file and the file created in step above and pick all the duplicates as any matching key will be a duplicate. Using OMIT on outfil we drop the records we created in step0100 there by resulting in the desired output
Joined: 21 Nov 2008 Posts: 31 Location: United Kingdom
The previous resolution worked great but the file spec has changed again. I have tried using the jcl you supplied and adapting that but the result is my output file is out by one position(62 instead of 61).
I am comparing FileB with FileA. If any matches are found in FileB write these to an output dataset.
File information as follows -
FileA 237bytes FB. FileB 1000bytes FB.
FileA pos 2 for 7chars contains an alphnumeric(ABC1234).
FileB pos 61 for 7chars contains an alphanumeric(ABC1234).
FileA will not contain any blanks.
FileB can contain blanks at pos 61.
There may be duplicates in fileA
There will not be any duplicates in fileB.
//**********************************************************************
//*
//* STEP0100 WILL ELIMINATE THE DUPLICATES FROM 237 BYTE FILE
//* AND CREATE A 1000 BYT FILE POPULATING THE KEY FROM POS 2 FOR 7 BYTES
//* ONTO POS 61.
//*
//*
//**********************************************************************
//STEP0100 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=DTSX018.C1000.MASTFILE.REFVRM.SORTED,DISP=SHR,
// UNIT=SYSDA,VOL=SER=ZL5008
//SORTOUT DD DSN=&&I2,DISP=(,PASS),SPACE=(CYL,(500,200),RLSE),
// UNIT=(WCART,5)
//SYSIN DD *
INREC BUILD=(2,7)
SORT FIELDS=(1,7,CH,A)
SUM FIELDS=NONE
OUTREC BUILD=(61:1,7,1000:X)
//**********************************************************************
//*
//* STEP0200 WILL CONCATENATE 1000 ORGINAL FILE AND THE FILE CREATED
//* IN STEP ABOVE AND PICK ALL THE DUPLICATES AS ANY MATCHING KEY
//* WILL BE A DUPLICATE. USING OMIT ON OUTFIL DROP THE RECORDS CREATED
//* IN STEP0100 THERE BY RESULTING IN THE DESIRED OUTPUT
//*
//**********************************************************************
//STEP0200 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DSN=TCSRGA.ADAPH2.FILE30.DUMP.TEST2.FB,DISP=SHR
// DD DSN=&&I2,DISP=SHR
//OUT DD SYSOUT=*
//TOOLIN DD *
SELECT FROM(IN) ON(61,7,CH) TO(OUT) ALLDUPS USING(CTL1)
//CTL1CNTL DD *
OMIT COND=(61,7,CH,EQ,C' ')
OUTFIL FNAMES=OUT,OMIT=(1,60,CH,EQ,C' ',AND,68,39,CH,EQ,C' ')
/*
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Richard Galley wrote:
The previous resolution worked great but the file spec has changed again. I have tried using the jcl you supplied and adapting that but the result is my output file is out by one position(62 instead of 61).
I am comparing FileB with FileA. If any matches are found in FileB write these to an output dataset.
Richard ,
Unless I am missing something, it is the same as previous requirement. The JCL posted by you should give you the desired results. Show me a sample input data and desired output