View previous topic :: View next topic
|
Author |
Message |
abby.qiong.zhang
New User
Joined: 07 Jun 2012 Posts: 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
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 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 2005 Posts: 275 Location: Mumbai
|
|
|
|
Hi,
Check for SUM FIELDS in Sort Manual
Regards,
Chandan |
|
Back to top |
|
|
abby.qiong.zhang
New User
Joined: 07 Jun 2012 Posts: 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
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Nice shot :-)
Frankly I didnt think of SFF |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 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 2012 Posts: 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 2010 Posts: 577 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
Moderator Emeritus
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
|
|
|
|
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 |
|
|
|