Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
abby.qiong.zhang

New User

Joined: 07 Jun 2012
Posts: 26
Location: China

Posted: Fri Nov 30, 2012 7:58 am    Post subject: SUM and Eliminate records if the same key

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-

Pandora-Box

Moderator

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

 Posted: Fri Nov 30, 2012 10:15 am    Post subject: 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
chandan.inst

Active User

Joined: 03 Nov 2005
Posts: 271
Location: Mumbai

 Posted: Fri Nov 30, 2012 10:16 am    Post subject: Hi, Check for SUM FIELDS in Sort Manual Regards, Chandan
abby.qiong.zhang

New User

Joined: 07 Jun 2012
Posts: 26
Location: China

Posted: Fri Nov 30, 2012 2:05 pm    Post subject:

 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
Bill Woodger

DFSORT Moderator

Joined: 09 Mar 2011
Posts: 7314

 Posted: Fri Nov 30, 2012 2:12 pm    Post subject: Reply to: SUM and Eliminate records if the same key 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?
Pandora-Box

Moderator

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

 Posted: Fri Nov 30, 2012 2:14 pm    Post subject: Nice shot :-) Frankly I didnt think of SFF
Pandora-Box

Moderator

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

 Posted: Fri Nov 30, 2012 2:43 pm    Post subject: abby.qiong.zhang Did you test this code?? Did you get the output you wanted ??
abby.qiong.zhang

New User

Joined: 07 Jun 2012
Posts: 26
Location: China

Posted: Fri Nov 30, 2012 2:58 pm    Post subject:

 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.
sqlcode1

Active Member

Joined: 08 Apr 2010
Posts: 578
Location: USA

Posted: Fri Nov 30, 2012 10:15 pm    Post subject:

abby.qiong.zhang,

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,
Bill Woodger

DFSORT Moderator

Joined: 09 Mar 2011
Posts: 7314

 Posted: Fri Nov 30, 2012 11:20 pm    Post subject: Reply to: SUM and Eliminate records if the same key 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.
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Copy Group of Records scorp_rahul23 DFSORT/ICETOOL 1 Sun Jan 13, 2019 9:17 am Joining 2 records to 1 using Sort cmuncan DFSORT/ICETOOL 1 Sat Dec 08, 2018 8:53 am DFSORT output in CSV for data spans i... hiravibk DFSORT/ICETOOL 3 Tue Nov 13, 2018 7:55 pm Writing multiple records into a CSV r... hiravibk DFSORT/ICETOOL 2 Wed Nov 07, 2018 2:35 pm Can records in IMS/DB be related usin... SRICOBSAS IMS DB/DC 2 Fri Oct 19, 2018 3:53 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us