Merge two files using Sort in batch

Question

So i have two files that i need to merge. File A contains the key. Im unsure how to do this using SORT in batch (JCL). I know I need to use joinkey or ifthen. Would anyone know a solution to this?

Any help is greatly appreciated.

File A:

000001EMPLOYEE ID # 1
000002EMPLOYEE ID # 2
000003EMPLOYEE ID # 3
000004EMPLOYEE ID # 4
000005EMPLOYEE ID # 5
000006EMPLOYEE ID # 6
000007EMPLOYEE ID # 7
000008EMPLOYEE ID # 8
000009EMPLOYEE ID # 9
000010EMPLOYEE ID # 10

File B:

000001 John Doe
000002 Sam Maguire
000003 Jane Doe
000006 Jackson
000007 James Bond
000008 Spiderman
000019 Not an Employee

Desired output:

000001 EMPLOYEE ID # 1    John Doe
000002 EMPLOYEE ID # 2    Sam Maguire
000003 EMPLOYEE ID # 3    Jane Doe
000004 EMPLOYEE ID # 4
000005 EMPLOYEE ID # 5
000006 EMPLOYEE ID # 6    Jackson
000007 EMPLOYEE ID # 7    James Bond
000008 EMPLOYEE ID # 8    Spiderman
000009 EMPLOYEE ID # 9
000010 EMPLOYEE ID # 10
000019                    Not an Employee

This Q&A should give you hints to start with [JOINKEYS to combine two files](https://stackoverflow.com/questions/4560960/joinkeys-to-combine-two-files). Would you please add either the `dfsort`, or the `syncsort` tag. — phunsoft, Jan 12 '22 at 09:41

score 0 · Accepted Answer · answered Jan 15 '22 at 13:32

To join records in two files on common fields, you can use the DFSORT JoinKeys command.

Input file: empData.txt

000001EMPLOYEE ID # 1                        
000002EMPLOYEE ID # 2                        
000003EMPLOYEE ID # 3                        
000004EMPLOYEE ID # 4                        
000005EMPLOYEE ID # 5                        
000006EMPLOYEE ID # 6                        
000007EMPLOYEE ID # 7                        
000008EMPLOYEE ID # 8                        
000009EMPLOYEE ID # 9                        
000010EMPLOYEE ID # 10

Input file: empNames.txt

000001 John Doe                        
000002 Sam Maguire                        
000003 Jane Doe                        
000006 Jackson                        
000007 James Bond                        
000008 Spiderman                        
000019 Not an Employee

Input file: control.txt

* Employee Number in 1-6-EmpData.txt
 JOINKEYS FILE=F1,FIELDS=(1,6,A) 
* Employee Number in 1-6-EmpNames.txt
 JOINKEYS FILE=F2,FIELDS=(1,6,A) 
* Copy Name to EmpData
* Put file indicator (?) in column1
* This will be either 1,2 or B
 REFORMAT FIELDS=(?,F1:1,26,F2:1,23) 
 JOIN UNPAIRED,F1,F2
* Use Change to see if record was only in file 2
* and replace employee number from file2 in output
 OUTREC FIELDS=(1,1,CHANGE=(6,
  C'2',28,6),NOMATCH=(2,6),
  X,8,19,35,15)
 END

Output File: joined.txt

000001 EMPLOYEE ID # 1    John Doe       
000002 EMPLOYEE ID # 2    Sam Maguire    
000003 EMPLOYEE ID # 3    Jane Doe       
000004 EMPLOYEE ID # 4                   
000005 EMPLOYEE ID # 5                   
000006 EMPLOYEE ID # 6    Jackson        
000007 EMPLOYEE ID # 7    James Bond     
000008 EMPLOYEE ID # 8    Spiderman      
000009 EMPLOYEE ID # 9                   
000010 EMPLOYEE ID # 10                  
000019                    Not an Employee

The REFORMAT FIELDS ? places a '1','2' or 'B' in the output record to indicate how the joined record was built. If it is '2', then the record only occurred in the second file so we use the CHANGE function to get the Employee Id from the second file and place it in the output record.

Command Line:

ahlsort control.txt "empData.txt,dcb=(recfm=T,lrecl=100),empNames.txt,dcb=(recfm=T,lrecl=100)" "joined.txt,dcb=(recfm=T,lrecl=200)"

This was tested with AHLSORT v14r3-227 for Windows but should work the same on AHLSORT for Linux or DFSORT on the mainframe.