View previous topic :: :: View next topic
Author
Message
abby.qiong.zhang New User Joined: 07 Jun 2012Posts: 26 Location: China
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
Pandora-Box Moderator Joined: 07 Sep 2006Posts: 1567 Location: Andromeda Galaxy
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
chandan.inst Active User Joined: 03 Nov 2005Posts: 271 Location: Mumbai
Hi,
Check for SUM FIELDS in Sort Manual
Regards,
Chandan
Back to top
abby.qiong.zhang New User Joined: 07 Jun 2012Posts: 26 Location: China
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
Bill Woodger DFSORT Moderator Joined: 09 Mar 2011Posts: 7314
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
Pandora-Box Moderator Joined: 07 Sep 2006Posts: 1567 Location: Andromeda Galaxy
Nice shot :-)
Frankly I didnt think of SFF
Back to top
Pandora-Box Moderator Joined: 07 Sep 2006Posts: 1567 Location: Andromeda Galaxy
abby.qiong.zhang
Did you test this code??
Did you get the output you wanted ??
Back to top
abby.qiong.zhang New User Joined: 07 Jun 2012Posts: 26 Location: China
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
sqlcode1 Active Member Joined: 08 Apr 2010Posts: 578 Location: USA
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
Bill Woodger DFSORT Moderator Joined: 09 Mar 2011Posts: 7314
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
Please enable JavaScript!