IBM Mainframe Forum

Hi
Can anyone please assist me with this? Need to compare two files and extracting a subset of records from second file based on a criteria.

Requirement :
Basically, I do receive FILE1 and FILE2 from my existing procedure. Since it is an external procedure, I do not know exactly what value is present in FILE1 and am trying to have this file comparison and extract as a generic procedure for future runs.

I need to compare FILE1 & FILE2 and based on the value of a date field (CCYY format) in FILE1 mapping with a date field in FILE2, need to extract the records from FILE2.
Please find the files and details below :

FILE1
------

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
RCH SAM01
RCD2006SAMUEL
RCT SAM01
RCH HENDERSON01
RCD2006HENDERSON
RCT HENDERSON01
RCH VAUGHN01
RCD2006VAUGHN
RCT VAUGHN


Need to extract the year part from the above file (pos : 4-7 ;length = 4 bytes) which will only be present in the record

containing the first 3 bytes as "RCD" (pos : 1-3; length = 3 bytes) RECFM=FB
Note : FILE1 will always carry the same date value in all the RCD record.



FILE2
------

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
000083200370066 000083220071231GRVLA99100131767969131767969STEVENS
000186400540010 000186420021231GRVLA99110009660748009660748ELOS
000223100020000 000223120031231GRVLL99110075428572075428572ROBERT
000502900690000 000502920051231GRVLL99110035283553035283553JEFFREY QUIGL
000502900690000 000502920061231GRVLL99110036383087036383087SUSANNE
000502900690000 000502920081231GRVLL99110036383087036383087SUSANNE
000502900690000 000502920041231GRVLL99110050365813050365813ROSE


RECFM=FB, POS : 24 ; Length = 4 bytes is the date field


Requirement :
---------------
Need to compare FILE1 & FILE2 and extract the full record from FILE2 for which the date field in FILE2 < date field in

FILE1

i.e., if date value in FILE1 = 2006, then the records with date value "2005, 2004, 2003, 2002" from FILE2 must be

extracted.


I did a try using JOINKEYS option. It filtered out the unpaired keys (I specified the date fields as key fields ) but it

also showed the year 2007, 2008 records.

Is it possible to do using JOINKEYS/SPLICE option or any other way.

Test JCL that I used :
-----------------------

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
//STEP1 EXEC PGM=ICETOOL,COND=(0,NE)
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//INDD1 DD *
RCH SAM01
RCD2006SAMUEL
RCT SAM01
RCH HENDERSON01
RCD2006HENDERSON
RCT HENDERSON01
RCH VAUGHN01
RCD2006VAUGHN
RCT VAUGHN
/*
//CTL1JNF1 DD DSN=MY.OUTFILE1,
// DISP=(,CATLG,DELETE),UNIT=SYSDA,
// DCB=(RECFM=FB,BLKSIZE=0),SPACE=(CYL,(10,10),RLSE)
//CTL1JNF2 DD *
000083200370066 000083220071231GRVLA99100131767969131767969STEVENS
000186400540010 000186420021231GRVLA99110009660748009660748ELOS
000223100020000 000223120031231GRVLL99110075428572075428572ROBERT
000502900690000 000502920051231GRVLL99110035283553035283553JEFFREY QUIGL
000502900690000 000502920061231GRVLL99110036383087036383087SUSANNE
000502900690000 000502920081231GRVLL99110036383087036383087SUSANNE
000502900690000 000502920041231GRVLL99110050365813050365813ROSE
/*
//OUTDD DD DSN=MY.OUTFILE2,
// DISP=(,CATLG,DELETE),UNIT=SYSDA,
// DCB=(RECFM=FB,BLKSIZE=0),SPACE=(CYL,(10,10),RLSE)
//TOOLIN DD *
SELECT FROM(INDD1) TO(CTL1JNF1) ON(4,4,CH) FIRST USING(CTL#)
COPY FROM(CTL1JNF2) TO(OUTDD) USING(CTL1)
/*
//CTL#CNTL DD *
INCLUDE COND=(1,3,CH,EQ,C'RCD')
OUTFIL FNAMES=CTL1JNF1,BUILD=(1:4,4)
/*
//CTL1CNTL DD *
JOINKEYS FILE=F1,FIELDS=(1,4,A)
JOINKEYS FILE=F2,FIELDS=(24,4,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F2:1,68,24,4,F1:1,4)
SORT FIELDS=COPY
/*


OUTPUT OBTAINED :
-----------------
MY.OUTFILE1 :
--------------
----
**********************
2006


MY.OUTFILE2 :
---------------
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+-
***************************** Top of Data **********************************
000186400540010 000186420021231GRVLA99110009660748009660748ELOS 2002
000223100020000 000223120031231GRVLL99110075428572075428572ROBERT 2003
000502900690000 000502920041231GRVLL99110050365813050365813ROSE 2004
000502900690000 000502920051231GRVLL99110035283553035283553JEFFREY Q2005
000502900690000 000502920061231GRVLL99110036383087036383087SUSANNE 20062006
000083200370066 000083220071231GRVLA99100131767969131767969STEVENS 2007
000502900690000 000502920081231GRVLL99110036383087036383087SUSANNE 2008



EXPECTED OUTPUT :
-------------------
I Thought of appending FILE1 data to the last 4 byte of FILE2 (pos : 73 - 76) and then compare with pos 69 - 72 and

subset only the records which have date < 2006. But ended with blanks populated in pos : 73 - 76 except for one record having 2006.

The desired output FILE2 should contain :

----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+-
***************************** Top of Data **********************************
000186400540010 000186420021231GRVLA99110009660748009660748ELOS
000223100020000 000223120031231GRVLL99110075428572075428572ROBERT
000502900690000 000502920041231GRVLL99110050365813050365813ROSE
000502900690000 000502920051231GRVLL99110035283553035283553JEFFREY Q

Kindly advise.

Best Regards
Hariharan

Hello,

You have posted your question in the DFSORT part of the forum, but you have posted syntax that is not supported by DFSORT.

Your topic has been moved to Syncsort.

Hi
I tried again in an alternative way and got the result.
Here is the modified JCL :
//STEP1 EXEC PGM=SYNCTOOL
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//TOOLMSG DD SYSOUT=*
//INDD1 DD *
RCH SAM01
RCD2006SAMUEL
RCT SAM01
RCH HENDERSON01
RCD2006HENDERSON
RCT HENDERSON01
RCH VAUGHN01
RCD2006VAUGHN
RCT VAUGHN
/*
//INDD2 DD *
000083200370066 000083220071231GRVLA99100131767969131767969STEVENS
000186400540010 000186420021231GRVLA99110009660748009660748ELOS
000223100020000 000223120031231GRVLL99110075428572075428572ROBERT
000502900690000 000502920051231GRVLL99110035283553035283553JEFFREY
000502900690000 000502920061231GRVLL99110036383087036383087SUSANNE
000502900690000 000502920081231GRVLL99110036383087036383087SUSANNE
000502900690000 000502920041231GRVLL99110050365813050365813ROSE
/*
//OUT1CNTL DD DSN=MY.OUT1,
// DISP=(,CATLG,DELETE),UNIT=SYSDA,
// DCB=(RECFM=FB,BLKSIZE=0),SPACE=(CYL,(10,10),RLSE)
//OUTDD2 DD DSN=MY.OUT2,
// DISP=(,CATLG,DELETE),UNIT=SYSDA,
// DCB=(RECFM=FB,BLKSIZE=0),SPACE=(CYL,(10,10),RLSE)
//TOOLIN DD *
SELECT FROM(INDD1) TO(OUT1CNTL) ON(4,4,CH) FIRST USING(CTL1)
COPY FROM(INDD2) TO(OUTDD2) USING(OUT1)
/*
//CTL1CNTL DD *
INCLUDE COND=(1,3,CH,EQ,C'RCD')
SORT FIELDS=(01,07,CH,A)
OUTFIL REMOVECC,
HEADER1=(3:'SORT FIELDS=COPY'),
HEADER2=(3:'INCLUDE COND=(24,4,ZD,LT,'),
OUTREC=(2X,4,4,C')',73X)
/*

Output :
Control card built in OUT1CNTL as :
SORT FIELDS=COPY
INCLUDE COND=(24,4,ZD,LT,
2006)


Output records :
000186400540010 000186420021231GRVLA99110009660748009660748ELOS
000223100020000 000223120031231GRVLL99110075428572075428572ROBERT
000502900690000 000502920051231GRVLL99110035283553035283553JEFFREY
000502900690000 000502920041231GRVLL99110050365813050365813ROSE

Only the records with year < 2006 at pos 24 are filtered and written.

Best Regards
Hariharan.

Good to hear it is working - thanks for the update

d