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 

matching records between two files and summing a field

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


Joined: 01 Mar 2005
Posts: 105
Topics: 58
PostPosted: Wed Jul 19, 2006 5:10 am    Post subject: matching records between two files and summing a field Reply with quote
Members,

I have a requirement with 2 files each having Headers individually and some records in it say 10 records in File 1 and 20 records in File 2 and there is a count which is present in the headers record .I want to generate an ouput File 3 as shown below with a record matching with first 10 bytes from File 1 in File2 and in the output i want the matching records as well the header with the summation of the count of (35 + 40 = 75 ).Can anybody help me in this requirement ?


FILE 1
Code:

ABC REPORT FOR UNIVERSITY EMPLOYEES    35
RECORD1
RECORD2
....
....

FILE 2
Code:

XYZ REPORT FOR UNIVERSITY EMPLOYEES    40
RECORD1
RECORD2
....
....

OUTPUT DESIRED FILE 3
Code:

FIN REPORT FOR UNIVERSITY EMPLOYEES    75
RECORD MATCHING IN FILE 1 WITH FILE2 ONLY 10 BYTES
RECORD MATCHING IN FILE 1 WITH FILE2 ONLY 10 BYTES
....
....
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Wed Jul 19, 2006 6:05 am    Post subject: Reply with quote
Mfuser,

Try this

Code:

//STEP0100 EXEC  PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD *                                           
ABC REPORT FOR UNIVERSITY EMPLOYEES    35                 
RECORD1                                                   
RECORD2                                                   
//SORTOUT  DD DSN=&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE) 
//SYSIN    DD *                                           
  SORT FIELDS=COPY                                         
  INREC FIELDS=(01,35,                                     
                36,6,FS,EDIT=(TTTTTT),                     
                80:X,                                     
                01,10,                                     
                SEQNUM,8,ZD)                               
  OUTREC FIELDS=(1,80,                                     
                 81:91,8,CHANGE=(10,C'00000001',C' '),     
                 NOMATCH=(81,10),                         
                 7C'0',                                   
                 C'1')                                     
                                                           
/*                                                         
//STEP0200 EXEC  PGM=SORT                               
//SYSOUT   DD SYSOUT=*                                   
//SORTIN   DD *                                         
XYZ REPORT FOR UNIVERSITY EMPLOYEES    40               
RECORD1                                                 
RECORD2                                                 
RECORD3                                                 
//SORTOUT  DD DSN=&T2,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)
//SYSIN    DD *                                         
  SORT FIELDS=COPY                                       
  INREC FIELDS=(01,35,                                   
                36,6,FS,EDIT=(TTTTTT),                   
                80:X,                                   
                01,10,                                   
                SEQNUM,8,ZD)                             
  OUTREC FIELDS=(1,80,                                   
                 81:91,8,CHANGE=(10,C'00000001',C' '),   
                 NOMATCH=(81,10),                       
                 7C'0',                                 
                 C'1')                                   
                                                         
/*                                                       
//STEP0300 EXEC  PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD DSN=&T1,DISP=SHR                                     
//         DD DSN=&T2,DISP=SHR                                     
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  SORT FIELDS=(81,10,CH,A)                                         
  SUM FIELDS=(36,6,ZD,91,8,ZD)                                     
  OUTFIL INCLUDE=(91,8,ZD,GT,1),                                   
  OUTREC=(1:81,10,CHANGE=(50,C' ',                                 
                          C'FIN REPORT FOR UNIVERSITY EMPLOYEES'), 
    NOMATCH=(C'RECORD MATCHING IN FILE 1 WITH FILE2 ONLY 10 BYTES'),
    36,6,ZD,EDIT=(IIIIII),80:X)                                     
/*                                                                 


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
mfuser
Banned


Joined: 01 Mar 2005
Posts: 105
Topics: 58
PostPosted: Wed Jul 19, 2006 6:35 am    Post subject: Reply with quote
Kolusu,

Thanks for you immediate reply.A slight modification is that i need only the count of two values ( 30 + 45 = 75 ) instead of the record matching conditions that means i would need only the sum of the count and display in header so can u please tell me the modified JCL.
FILE 1
Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
ABC REPORT FOR UNIVERSITY EMPLOYEES    35                               
RECORD1
RECORD2
....
....

FILE 2
Code:
 
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
XYZ REPORT FOR UNIVERSITY EMPLOYEES    40                               
RECORD1
RECORD2
....
....

OUTPUT DESIRED FILE 3
Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
FIN REPORT FOR UNIVERSITY EMPLOYEES     75                             
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Wed Jul 19, 2006 7:20 am    Post subject: Reply with quote
Quote:

Thanks for you immediate reply.A slight modification is that i need only the count of two values ( 30 + 45 = 75 ) instead of the record matching conditions that means i would need only the sum of the count and display in header so can u please tell me the modified JCL.


Mfuser,

Why can't you put your requirements all at once? I just cannot spend time posting solutions everytime you change your requirement. Evil or Very Mad

Try this

Code:

//STEP0100 EXEC  PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD *                                           
ABC REPORT FOR UNIVERSITY EMPLOYEES    35                 
RECORD1                                                   
RECORD2                                                   
//SORTOUT  DD DSN=&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE) 
//SYSIN    DD *                                           
  SORT FIELDS=COPY                                         
  OPTION STOPAFT=1                                         
/*                                                         
//STEP0200 EXEC  PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD *                                           
XYZ REPORT FOR UNIVERSITY EMPLOYEES    40                 
RECORD1                                                   
RECORD2                                                   
RECORD3                                                   
//SORTOUT  DD DSN=&T2,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE) 
//SYSIN    DD *                                           
  SORT FIELDS=COPY                                         
  OPTION STOPAFT=1                                         
/*                                                         
//STEP0300 EXEC  PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD DSN=&T1,DISP=SHR                             
//         DD DSN=&T2,DISP=SHR                             
//SORTOUT  DD SYSOUT=*                                     
//SYSIN    DD *                                           
  SORT FIELDS=COPY                                         
  OUTFIL REMOVECC,NODETAIL,                               
  TRAILER1=(C'FIN REPORT FOR UNIVERSITY EMPLOYEES',       
            TOT=(36,6,FS,EDIT=(IIIIII)),80:X)             
/*                                                         


Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
mfuser
Banned


Joined: 01 Mar 2005
Posts: 105
Topics: 58
PostPosted: Wed Jul 19, 2006 7:51 am    Post subject: Reply with quote
Thanks Kolusu,

I am sorry about my mistake and i have got the desired ouput and thanks a lot for u timely help.
Back to top
View user's profile Send private message
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