MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Merging records from two datasets based Primary and secondar

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 44
Topics: 11
PostPosted: Tue Nov 11, 2025 10:51 am    Post subject: Merging records from two datasets based Primary and secondar Reply with quote
Requirement is to match from file1 and file2

Matching criteria for File1 field1 positions 1-5,field2 16-20, field3 positions 21-24
if no match found with field1 positions 1-5, we have to match with 11-15

File2 : matching positions field1 positions 1-5,field2 6-10, field3 positions 11-14

File1:
Code:

1234,copilot,2345,Plan1,Ctr1
4321,Chatgpt,plan2,ctr2
3421,copilot1,3452,Plan3,Ctr3

File2
Code:

1234,Plan1,Ctr1,d111125
4321,Plan2,Ctr2,d111025
3452,Plan3,Ctr3,d110925

Output should be
Code:

1234,copilot,2345,Plan1,Ctr1,d111125
4321,Chatgpt,plan2,ctr2,,d111025
3421,copilot1,3452,Plan3,Ctr3,d110925

Code:

//STEP01 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=YOUR.FILE1.INPUT,DISP=SHR
//SORTIN2 DD DSN=YOUR.FILE2.INPUT,DISP=SHR
//SORTOUT DD DSN=YOUR.MATCHED.PRIMARY,DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(5,5)),UNIT=SYSDA
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,5,A,16,5,A,21,4,A)
JOINKEYS FILE=F2,FIELDS=(1,5,A,6,5,A,11,4,A)
REFORMAT FIELDS=(F1:1,80,F2:15,8)
SORT FIELDS=COPY
/*
//STEP02 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=YOUR.FILE1.INPUT,DISP=SHR
//SORTIN2 DD DSN=YOUR.FILE2.INPUT,DISP=SHR
//SORTOUT DD DSN=YOUR.MATCHED.FALLBACK,DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(5,5)),UNIT=SYSDA
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(11,5,A,16,5,A,21,4,A)
JOINKEYS FILE=F2,FIELDS=(1,5,A,6,5,A,11,4,A)
REFORMAT FIELDS=(F1:1,80,F2:15,8)
SORT FIELDS=COPY
/*


Currently i am using two sorts, Please advise if it can be done with one sort card
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12397
Topics: 75
Location: San Jose
PostPosted: Tue Nov 11, 2025 11:39 am    Post subject: Reply with quote
Suchay,

bonk You have been member for a long time and you still miss providing the basic information like what are the DCB properties of the input files? Are we supposed to figure out looking at your control cards?

Suchay wrote:
if no match found with field1 positions 1-5, we have to match with 11-15


and the sample you showed for file-1
Code:

----+----1----+----2----+----3---
1234,COPILOT,2345,PLAN1,CTR1     
4321,CHATGPT,PLAN2,CTR2         
3421,COPILOT1,3452,PLAN3,CTR3   


The position 11 has strings that do NOT match your requirements. bonk

Also what happens when there is match on BOTH fields ?

Suchay wrote:
Currently i am using two sorts, Please advise if it can be done with one sort card


What is this fascination with 1 step process? If you are so concerned about the one step processing, sit down and write a programmer in the language of your choice.
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 44
Topics: 11
PostPosted: Tue Nov 11, 2025 3:10 pm    Post subject: Reply with quote
Hi Kolusu
My apologies

File1 Actno 1-5, Prev_actno 16-20, plan no 21-25, cntr_no 27-30
Code:

1234 copilot   2345 Plan1 Ctr1
4321 Chatgpt        plan2 ctr2
3421 copilot1  3452 Plan3 Ctr3


File2 Actno 1-5, plan no 6-10, cntr_no 11-14, dte 15-22
Code:

1234 Plan1Ctr1d111125
4321 Plan2Ctr2d111025
3452 Plan3Ctr3d110925


Code:

//STEP01 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=YOUR.FILE1.INPUT,DISP=SHR
//SORTIN2 DD DSN=YOUR.FILE2.INPUT,DISP=SHR
//SORTOUT DD DSN=YOUR.MATCHED.PRIMARY,DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(5,5)),UNIT=SYSDA
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,5,A,21,5,A,27,4,A)
JOINKEYS FILE=F2,FIELDS=(1,5,A,6,5,A,11,4,A)
REFORMAT FIELDS=(F1:1,80,F2:15,8)
SORT FIELDS=COPY
/*
//STEP02 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=YOUR.FILE1.INPUT,DISP=SHR
//SORTIN2 DD DSN=YOUR.FILE2.INPUT,DISP=SHR
//SORTOUT DD DSN=YOUR.MATCHED.FALLBACK,DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(5,5)),UNIT=SYSDA
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(16,5,A,21,5,A,27,4,A)
JOINKEYS FILE=F2,FIELDS=(1,5,A,6,5,A,11,4,A)
REFORMAT FIELDS=(F1:1,80,F2:15,8)
SORT FIELDS=COPY
/*



We will eliminate in the next step
Quote:

Also what happens when there is match on BOTH fields ?



Just curious
Quote:

What is this fascination with 1 step process? If you are so concerned about the one step processing, sit down and write a programmer in the language of your choice.


Please advise
Back to top
View user's profile Send private message
Suchay
Beginner


Joined: 29 Jun 2017
Posts: 44
Topics: 11
PostPosted: Wed Nov 12, 2025 7:40 am    Post subject: Reply with quote
Input Datasets are 80 bytes
Output dataset is 88 bytes
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12397
Topics: 75
Location: San Jose
PostPosted: Wed Nov 12, 2025 7:54 am    Post subject: Reply with quote
Suchay,

Sigh ! You still did not provide the DCB properties of the input file.

Assuming your input file-1 has LRECL=80, RECFM=FB, here is an untested DFSORT solution. The trick is to put the key at common position either from position 1 or position 16. In order to do that we just concatenate file-1 twice but separate them with an identifier. Here I used the string "HEAD" to distinguish the files. So using When=Group, we number the files and we use that Id number to pick the key from position from 1 or 16
Code:

//STEP0100 EXEC PGM=SORT                             
//SYSOUT   DD SYSOUT=*                               
//INA      DD *                                       
HEAD                                                 
//         DD DISP=SHR,DSN=your.input.file-1         
//         DD *                                       
HEAD                                                 
//         DD DISP=SHR,DSN=your.same.input.file-1
//*     
//INB      DD DISP=SHR,DSN=your.input.file-2     
//SORTOUT  DD SYSOUT=*                               
//SYSIN    DD *                                                   
  OPTION COPY                                                     
  JOINKEYS F1=INA,FIELDS=(81,05,A,          # ACTNO (POS 11 OR 16)
                          21,05,A,          # PREVIOUS ACCT-NUM   
                          27,04,A)          # CNTR_NO             
                                                                   
  JOINKEYS F2=INB,FIELDS=(01,05,A,          # ACTNO               
                          06,05,A,          # PREV ACTNO           
                          11,04,A)          # CNTR_NO             
                                                                   
  REFORMAT FIELDS=(F1:01,80,                # F1-RECORD           
                   F2:15,10)                # DTE                 
/*                                                                 
//JNF1CNTL DD *                                                   
  INREC IFOUTLEN=85,                                               
        IFTHEN=(WHEN=GROUP,                                       
               BEGIN=(01,05,CH,EQ,C'HEAD'),                       
                PUSH=(86:ID=1)),                                   
                                                                   
        IFTHEN=(WHEN=(86,01,ZD,EQ,1),                             
             OVERLAY=(81:01,05)),           # ACTNO               
                                                                   
        IFTHEN=(WHEN=(86,01,ZD,EQ,2),                             
             OVERLAY=(81:16,05))            # PREV ACTNO           
/*                                                                 

_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group