Page 1 of 1
Merge records from two file, one by one.
Posted: Tue Sep 06, 2016 8:18 pm
by Imran Lamba
Hi,
I need to create a temporary file for a testing purpose. But creating it is somewhat tricky. I am assuming some SORT expert can help me on this.
I basically want to merge values from two input files but the values from input should come in alternate way in the final output file.
File 1:
File 2:
Output File:
Please advise on any logic and I shall try to work it out.
Re: Merge records from two file, one by one.
Posted: Tue Sep 06, 2016 9:17 pm
by Magesh_j
Imran Lamba,
Assuming LRECL=80.
Code: Select all
//SORT010 EXEC PGM=SORT
//SORTIN DD *
BEGIN
// DD DSN=FIRST.FIL,DISP=SHR
// DD *
BEGIN
// DD DSN=SECOND.FIL,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,5,CH,EQ,C'BEGIN'),
PUSH=(81:SEQ=8))
SORT FIELDS=(81,8,CH,A)
OUTFIL INCLUDE=(1,5,CH,NE,C'BEGIN'),BUILD=(1,80)
you may remove the "Begin" record in DD Statement, if you have any unique way of identifying 1st and 2nd file. Then you need to adjust IFTHEN=(WHEN=GROUP,BEGIN/KEYBEGIN ?
Regards,
Magesh
Re: Merge records from two file, one by one.
Posted: Tue Sep 06, 2016 9:29 pm
by Magesh_j
Also you may use JOINKEYS, but consumes three pass without SORTing, where my previous solution consumes sorting with two pass.
Code: Select all
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//INA DD DSN=FILE1,DISP=SHR
//INB DD DSN=FILE2,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
JOINKEYS F1=INA,FIELDS=(81,08,A),SORTED,NOSEQCK
JOINKEYS F2=INB,FIELDS=(81,08,A),SORTED,NOSEQCK
REFORMAT FIELDS=(F1:1,80,F2:1,80)
OUTFIL BUILD=(1,80,/,81,80)
//JNF1CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,ZD)
//JNF2CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,ZD)
Re: Merge records from two file, one by one.
Posted: Tue Sep 06, 2016 9:31 pm
by nicc
As there is only one record in each dataset (not file) why are you sorting at all?
Re: Merge records from two file, one by one.
Posted: Tue Sep 06, 2016 9:36 pm
by Magesh_j
Sorry Nic, I couldn't understand,
Nic wrote:
As there is only one record in each dataset (not file) why are you sorting at all?
As per the requirement, the input and output dataset has multiple records.
Imran Lamba wrote:
Code: Select all
File 1:
AAA
BBB
CCC
File 2:
9999
8888
7777
Output File:
AAA
9999
BBB
8888
CCC
7777
Thanks
Magesh
Re: Merge records from two file, one by one.
Posted: Tue Sep 06, 2016 9:39 pm
by nicc
Sorry - I am in the wrong topic!
Re: Merge records from two file, one by one.
Posted: Tue Sep 06, 2016 9:40 pm
by Magesh_j
No problem Nic.
Thanks
Magesh
Re: Merge records from two file, one by one.
Posted: Tue Sep 06, 2016 10:04 pm
by Imran Lamba
Magesh_j wrote: you may remove the "Begin" record in DD Statement, if you have any unique way of identifying 1st and 2nd file. Then you need to adjust IFTHEN=(WHEN=GROUP,BEGIN/KEYBEGIN ?
Thanks Magesh. If I know from which file I want the first record in output and keep that file as first in the SORT, then also I would not need BEGIN?
Re: Merge records from two file, one by one.
Posted: Tue Sep 06, 2016 10:09 pm
by Imran Lamba
Sorry no it won't work. How would I know where to start second sequence...hmm
Re: Merge records from two file, one by one.
Posted: Wed Sep 07, 2016 3:16 am
by Magesh_j
Imran Lamba,
I think You missed my second solution. I would prefer second solution because it just do a copy and no sorting.
Still if you prefer my first solution, On sort fields, please add EQUALS to preserve the order of the same key, because DFSORT comes with EQUALS=NO by default.
Also you have to consider what happen if you have different number of records, Say first file is having 3 records and second file is having 4 records.
Second solution will fails, if you have difference in number of records, because you dont have a matching record. You can make second solution work by handling
Code: Select all
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,80,F2:1,80,?)
OUTFIL IFTHEN=(WHEN=(161,1,CH,EQ,C'B'),BUILD=(1,80,/,81,80)),
IFTHEN=(WHEN=(161,1,CH,EQ,C'1'),BUILD=(1,80)),
IFTHEN=(WHEN=NONE,BUILD=(81,80))
Re: Merge records from two file, one by one.
Posted: Wed Sep 07, 2016 2:10 pm
by Chandan Yadav
Magesh_j wrote: Imran Lamba,
Assuming LRECL=80.
Code: Select all
//SORT010 EXEC PGM=SORT
//SORTIN DD *
BEGIN
// DD DSN=FIRST.FIL,DISP=SHR
// DD *
BEGIN
// DD DSN=SECOND.FIL,DISP=SHR
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,5,CH,EQ,C'BEGIN'),
PUSH=(81:SEQ=8))
SORT FIELDS=(81,8,CH,A)
OUTFIL INCLUDE=(1,5,CH,NE,C'BEGIN'),BUILD=(1,80)
you may remove the "Begin" record in DD Statement, if you have any unique way of identifying 1st and 2nd file. Then you need to adjust IFTHEN=(WHEN=GROUP,BEGIN/KEYBEGIN ?
Regards,
Magesh
Not sure If I am missing anything here., correct me if I am wrong
With this solution will we have sequence number populated for first file because my grouping will being when I find a record with BEGIN
As per my understanding only second file will have the sequence number populated and out put will be both files concatenated.
I tried this and its not giving me the expected result
It worked for me when I changed IFTHEN as below
Code: Select all
INREC IFTHEN=(WHEN=GROUP,END=(1,5,CH,EQ,C'BEGIN'),
PUSH=(81:SEQ=8))
Thanks,
Chandan
Re: Merge records from two file, one by one.
Posted: Wed Sep 07, 2016 8:54 pm
by Imran Lamba
JOIN worked for me. As of now I have equal number of records.
Re: Merge records from two file, one by one.
Posted: Wed Sep 07, 2016 9:51 pm
by Magesh_j
Chandan Yadav wrote: Not sure If I am missing anything here., correct me if I am wrong
With this solution will we have sequence number populated for first file because my grouping will being when I find a record with BEGIN
As per my understanding only second file will have the sequence number populated and out put will be both files concatenated.
I tried this and its not giving me the expected result
It worked for me when I changed IFTHEN as below
CODE: SELECT ALL
INREC IFTHEN=(WHEN=GROUP,END=(1,5,CH,EQ,C'BEGIN'),
PUSH=(81:SEQ=8))
You might have missed first BEGIN record in your testing, but still if you have a solution without having first begin statement, then you are good.
Regards,
Magesh
Re: Merge records from two file, one by one.
Posted: Wed Sep 07, 2016 11:17 pm
by Chandan Yadav
Magesh_j wrote:
You might have missed first BEGIN record in your testing, but still if you have a solution without having first begin statement, then you are good.
Regards,
Magesh
Yup you are correct..my bad..I overlooked the first begin statement
[ Post made via Android ] 
Re: Merge records from two file, one by one.
Posted: Thu Sep 08, 2016 12:38 pm
by Imran Lamba
Thanks Magesh for your help.