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 

Calculating Count and Sum after match merge

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


Joined: 07 Apr 2004
Posts: 43
Topics: 31
PostPosted: Wed Mar 25, 2026 9:59 am    Post subject: Calculating Count and Sum after match merge Reply with quote
Hi,
My requirement is to join two files and find count, get Sum of the fields.
Please help me how can I achieve this using SYNCSORT.

File-1 is FB 80 bytes. Key is in 1-4 positions (4 bytes Number). Another field CITY is in 6-15 positions (10 bytes Character)
File-2 is FB 1000 bytes. Key is in 1-4 positions (4 bytes Number). Another field AMOUNT is in 6-10 (5 bytes Character).

Both files are in sorted order.
File-1 normally has 1000 records, but File-2 has millions of records.

Code:
File-1 (Key, City)
==============
1000 CHENNAI
2000 DELHI
3000 HYDERABAD
4000 KOLKATA
5000 MUMBAI


Code:
File-2 (Key, Amount)
==========
1000 111
1000 222
2000 333
3000 400
3000 500
4000 100
5000 200
5000 300
5000 400


Output file required (Record format FB, LRECL 80)

Code:
Key     Count     Total
========================
1000     2           333
2000     1           333
3000     2           900
4000     1           100
5000     3           900
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12403
Topics: 75
Location: San Jose
PostPosted: Thu Mar 26, 2026 5:24 pm    Post subject: Reply with quote
kris_madras,

Try this untested JCL
Code:

//STEP0100 EXEC PGM=SORT                             
//SYSOUT   DD SYSOUT=*                               
//SORTJNF1 DD *                                     
1000 111                                             
1000 222                                             
2000 333                                             
3000 400                                             
3000 500                                             
4000 100                                             
5000 200                                             
5000 300                                             
5000 400                                             
//SORTJNF2 DD *                                     
1000 CHENNAI                                         
2000 DELHI                                           
3000 HYDERABAD                                       
4000 KOLKATA                                         
5000 MUMBAI                                         
//SORTOUT  DD SYSOUT=*                               
//SYSIN    DD *                                     
  OPTION COPY                                       
  JOINKEYS FILES=F1,FIELDS=(1,4,A)                   
  JOINKEYS FILES=F2,FIELDS=(1,4,A)                   
  REFORMAT FIELDS=(F1:1,80,?)                       
                                                     
  OUTFIL REMOVECC,NODETAIL,                         
     BUILD=(80X),                                   
   HEADER1=('KEY |   COUNT|     SUM|',/,             
            '----|--------|--------|'),               
  SECTIONS=(01,04,                                   
  TRAILER3=(01,04,'|',                               
            COUNT=(M10,LENGTH=8),'|',               
            TOTAL=(06,03,ZD,M10,LENGTH=8),'|'))     
/*

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


Joined: 07 Apr 2004
Posts: 43
Topics: 31
PostPosted: Fri Mar 27, 2026 12:33 am    Post subject: Reply with quote
Thank You for providing solution. Let me test and confirm the solution.
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