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
To join records in two files on common fields, you can use the DFSORT JoinKeys command.
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
000001 John Doe
000002 Sam Maguire
000003 Jane Doe
000006 Jackson
000007 James Bond
000008 Spiderman
000019 Not an Employee
* 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
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.
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.