jcldfsort

Merge two files using Sort in batch


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

Solution

  • 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.