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 Eliminate records if the same key

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

New User


Joined: 07 Jun 2012
Posts: 26
Location: China

PostPosted: Fri Nov 30, 2012 7:58 am    Post subject: SUM and Eliminate records if the same key
Reply with quote

Is this can be done by SORT?

My input is like this:

Code:
KEY         Number
926304    154,963,130.67-
926304       4,092,720.00-


I want the output like this, merge the two records into one and sum the number
Code:
KEY          SUM
926304    159,055,850.67-
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1537
Location: Andromeda Galaxy

PostPosted: Fri Nov 30, 2012 10:15 am    Post subject:
Reply with quote

Yes

It can be done using SUM FIELDS

But what you need to do is

Format the input and overlay the numeric data without comma and decimal

Use SUM fields

While writing to outrec reformat
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Fri Nov 30, 2012 10:16 am    Post subject:
Reply with quote

Hi,

Check for SUM FIELDS in Sort Manual

Regards,
Chandan
Back to top
View user's profile Send private message
abby.qiong.zhang

New User


Joined: 07 Jun 2012
Posts: 26
Location: China

PostPosted: Fri Nov 30, 2012 2:05 pm    Post subject:
Reply with quote

Pandora-Box wrote:
Yes

It can be done using SUM FIELDS

But what you need to do is

Format the input and overlay the numeric data without comma and decimal

Use SUM fields

While writing to outrec reformat


Thanks for the reply, i found the method, here's it
Code:

  INREC OVERLAY(64:64,16,SFF,ZD,LENGTH=16)
  SORT FIELDS=(13,6,CH,A)
  SUM FIELDS=(64,16,ZD)
  OUTREC OVERLAY=(64:64,16,ZD,EDIT=(SIIII,III,IIT.TT),SIGNS=(+,-))


Code'd
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Fri Nov 30, 2012 2:12 pm    Post subject: Reply to: SUM and Eliminate records if the same key
Reply with quote

Please note that both your posts have had Code tags added to them to preserve the spacing of your code/data. Do that yourself next time.

Don't you now have a "leading" sign when previously you had a "trailing" one?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1537
Location: Andromeda Galaxy

PostPosted: Fri Nov 30, 2012 2:14 pm    Post subject:
Reply with quote

Nice shot :-)

Frankly I didnt think of SFF icon_redface.gif
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1537
Location: Andromeda Galaxy

PostPosted: Fri Nov 30, 2012 2:43 pm    Post subject:
Reply with quote

abby.qiong.zhang

Did you test this code??

Did you get the output you wanted ??
Back to top
View user's profile Send private message
abby.qiong.zhang

New User


Joined: 07 Jun 2012
Posts: 26
Location: China

PostPosted: Fri Nov 30, 2012 2:58 pm    Post subject:
Reply with quote

Pandora-Box wrote:
abby.qiong.zhang

Did you test this code??

Did you get the output you wanted ??


Yes, I did the test and the output is exactly what I want.
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Nov 30, 2012 10:15 pm    Post subject:
Reply with quote

abby.qiong.zhang,
As mentioned earlier, you are now getting leading sign instead of trailing sign which is what NOT you requested earlier.

Alternatively you can use below method so that you don't have to OVERLAY and do the conversion. If your input file is already sorted on key then replace SORT FIELDS= statement with OPTION COPY. If you want trailing positive (+) sign as well, change SIGNS=(,,+,-)

Code:
//STEP0001 EXEC PGM=SORT                                         
//SORTIN   DD  *                                                 
926304    154,963,130.67-                                         
926304      4,092,720.00-                                         
//SORTOUT  DD  SYSOUT=*                                           
//SYSIN DD *                                                     
 SORT FIELDS=(1,6,CH,A),EQUALS                                   
 OUTFIL REMOVECC,NODETAIL,SECTIONS=(1,6,                         
 TRAILER3=(1,10,                                                 
           TOT=(11,15,SFF,EDIT=(III,III,IIT.TTS),SIGNS=(,,,-)))) 
/*                                                               
//SYSOUT DD SYSOUT=*                                             
//*                                                               

OUTPUT
Code:
926304    159,055,850.67-

Thanks,
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Fri Nov 30, 2012 11:20 pm    Post subject: Reply to: SUM and Eliminate records if the same key
Reply with quote

abby.qiong.zhang,

Consult the manual for the difference between SUM and the OUTFIL reporting functions and the influence of EQUALS or NOEQUALS on the process, if this requirement is part of a larger one dealing with records of the same key but different information.
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 Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm


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