Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

sum and append the sum to each record in the group

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Anand Kumar

New User


Joined: 29 Aug 2007
Posts: 24
Location: chennai

PostPosted: Tue May 24, 2011 8:41 pm    Post subject: sum and append the sum to each record in the group
Reply with quote

Hi,

I have a requirement to find the sum of accounts like below:

Input file looks like this:
act no- 1:3
amt - 4: 8
Name - 12:5

Code:
00100000052Anand
00100000061Kumar
00100000007Aruna
00200000004Anand
00300000002Prave
00300000001Veena

for each act no, we need to sum the amt's in this case 52+61+7 = 120 and write all the three records with 120 in end.

so my output file should be as follows:

Code:
00100000052Anand00000000120
00100000061Kumar00000000120
00100000007Aruna00000000120
00200000004Anand00000000004
00300000002Prave00000000003
00300000001Veena00000000003

Hence the same for act 002 the sum is 4 and the act 003 the sum is 2+1= 3.


Please can any one help in such a scenario?

topic edited to provide a[n almost] meaningful title
Back to top
View user's profile Send private message

sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Tue May 24, 2011 9:39 pm    Post subject:
Reply with quote

Anand Kumar,
See if below works for you...

Code:

//STEP0100 EXEC PGM=SORT                           
//SYSOUT   DD SYSOUT=*                             
//SORTJNF1 DD *                                     
00100000052ANAND                                   
00200000004ANAND                                   
00100000061KUMAR                                   
00300000002PRAVE                                   
00300000001VEENA                                   
00100000007ARUNA                                   
/*                                                 
//SORTJNF2 DD *                                     
00100000052ANAND                                   
00200000004ANAND                                   
00100000061KUMAR                                   
00300000002PRAVE                                   
00300000001VEENA                                   
00100000007ARUNA                                   
/*                                                 
//SORTOUT  DD SYSOUT=*                             
//SYSIN DD *                                       
 JOINKEYS FILES=F1,FIELDS=(01,03,A)                 
 JOINKEYS FILES=F2,FIELDS=(01,03,A)                 
 REFORMAT FIELDS=(F1:01,16,                         
                  F2:81,08)                         
 OPTION COPY                                       
/*                                                 
//JNF2CNTL DD *                                     
 INREC OVERLAY=(81:04,08)                           
 SUM FIELDS=(81,8,ZD)                               
/*                                                 


OUTPUT
Code:

00100000052ANAND00000120
00100000061KUMAR00000120
00100000007ARUNA00000120
00200000004ANAND00000004
00300000002PRAVE00000003
00300000001VEENA00000003


Thanks,
Back to top
View user's profile Send private message
Anand Kumar

New User


Joined: 29 Aug 2007
Posts: 24
Location: chennai

PostPosted: Sun May 29, 2011 6:09 pm    Post subject: Reply to: sum and append the sum to each record in the group
Reply with quote

Thank you..
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Mon May 30, 2011 5:58 am    Post subject:
Reply with quote

I was thinking of two stage solution without joinkeys.. but this one looks better... Good one sqlcode1
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon May 30, 2011 6:53 am    Post subject:
Reply with quote

Hello,

If the "real" account number is 10 bytes (instead of the 3 shown in the example) and there are 200 million records, duplicating the file may not be desirable. . .
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Tue May 31, 2011 8:15 pm    Post subject:
Reply with quote

Escapa wrote:
I was thinking of two stage solution without joinkeys..


Without JOINKEYS,something like below might work as a single pass solution but I doubt the efficiency in this case... This was given by Kolusu on other board. In fact,I think the one with JOINKEYS was also provided by him icon_smile.gif
Code:

//STEP0100 EXEC PGM=SORT                                               
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD *                                                       
$$$                                                                   
/*                                                                     
//         DD *                                                       
00100000052ANAND                                                       
00200000004ANAND                                                       
00100000061KUMAR                                                       
00300000002PRAVE                                                       
00300000001VEENA                                                       
00100000007ARUNA                                                       
/*                                                                     
//         DD *                                                       
$$$                                                                   
/*                                                                     
//         DD *                                                       
00100000052ANAND                                                       
00200000004ANAND                                                       
00100000061KUMAR                                                       
00300000002PRAVE                                                       
00300000001VEENA                                                       
00100000007ARUNA                                                       
/*                                                                     
//SORTOUT  DD SYSOUT=*                                                 
//SYSIN    DD *
  INREC IFTHEN=(WHEN=INIT,                                             
                OVERLAY=(81:4,8,ZD)),                                 
        IFTHEN=(WHEN=GROUP,                                           
                BEGIN=(1,3,CH,EQ,C'$$$'),PUSH=(91:ID=1,SEQ=8)),       
        IFTHEN=(WHEN=(91,1,ZD,EQ,1),OVERLAY=(92:7C'0',C'1'))           
  SORT FIELDS=(1,3,CH,A,92,8,ZD,A),EQUALS                                   
  SUM FIELDS=(81,8,ZD)                                                 
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(91,1,ZD,EQ,1),PUSH=(81:81,8))       
  OUTFIL INCLUDE=(91,1,ZD,EQ,2),                                       
    BUILD=(1,16,X,81,8,ZD,TO=ZD,LENGTH=8)                             
/*                                                                     

OUTPUT
Code:

00100000052ANAND 00000120
00100000061KUMAR 00000120
00100000007ARUNA 00000120
00200000004ANAND 00000004
00300000002PRAVE 00000003
00300000001VEENA 00000003


Thanks,
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts sort with previous record anatol DFSORT/ICETOOL 9 Thu Oct 06, 2016 2:36 am
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us