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: 1529
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: 7234

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: 1529
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: 1529
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: 7234

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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 1 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm


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