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

Author Message
raak

Active User

Joined: 23 May 2006
Posts: 166
Location: chennai

 Posted: Thu Oct 11, 2007 6:51 pm    Post subject: Summing fields based on the last three digits I have a field which is a 9(09)v99. my input is like 4693.83 ABC 2983.54 ABC 5635.39 ABC 2053.03 ABC 2053.03 DEF I want to sum all these fields based on the last three digits.. i.e my output should be 13311.96 ABC 2053.03 DEF i tried giving SORT FIELDS=(12,3,ch,a) SUM FIELDS=(1,4,ZD,6,2,ZD) I gave like this because When i tried to give the full length of the amount field, it was giving SOC7 because of the decimal point present. Now, I am facing a problem. When the positions after decimal point(6,2) adds up to a valur greater than 99, then that record is not getting summed up. i.e. in this case, the first record has a value of .83 and second record has .54 after the decimal point. So these records are not getting summed and are coming individually.

krisprems

Active Member

Joined: 27 Nov 2006
Posts: 649
Location: India

Posted: Thu Oct 11, 2007 7:13 pm    Post subject:

raak
Here is the SORT JOB, that does what you want
 Code: //*******************************************************                //STEP1    EXEC PGM=SORT                                                //SYSOUT   DD SYSOUT=*                                                  //SORTIN   DD *                                                          4693.83    ABC                                                          2983.54    ABC                                                          5635.39    ABC                                                          ----+----1----+----2----+----3----+----4----+----5----+----6----+----7-- 2053.03    ABC                                                          2053.03    DEF                                                          /*                                                                      //SORTOUT  DD SYSOUT=*                                                  //SYSIN    DD *                                                                     INREC BUILD=(1,8,UFF,12:12,3)                                           SORT FIELDS=(12,3,CH,A)                                                 SUM FIELDS=(1,8,ZD)                                                     OUTREC OVERLAY=(1,10,UFF,EDIT=(TTTTTT.TT))                    /*

SORTOUT contains:
 Code: 015365.79  ABC      002053.03  DEF
Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

Posted: Thu Oct 11, 2007 9:46 pm    Post subject:

raak,

This DFSORT job would be better than Kriprems:

 Code: //S1 EXEC PGM=SORT                                      //SYSOUT   DD SYSOUT=*                                  //SORTIN   DD *                                          4693.83    ABC                                          2983.54    ABC                                          5635.39    ABC                                          2053.03    ABC                                          2053.03    DEF                                          /*                                                      //SORTOUT  DD SYSOUT=*                                  //SYSIN    DD *                                            INREC OVERLAY=(1,7,UFF,TO=ZD,LENGTH=7)                  SORT FIELDS=(12,3,CH,A)                                  SUM FIELDS=(1,7,ZD)                                      OUTREC BUILD=(1:1,7,ZD,EDIT=(TTTTT.TT),12:12,3,80:X)  /*

Alternatively, you could do it more simply with this DFSORT job:

 Code: //S2 EXEC PGM=SORT                                          //SYSOUT   DD SYSOUT=*                                      //SORTIN   DD *                                              4693.83    ABC                                              2983.54    ABC                                              5635.39    ABC                                              2053.03    ABC                                              2053.03    DEF                                              /*                                                          //SORTOUT  DD SYSOUT=*                                      //SYSIN    DD *                                                SORT FIELDS=(12,3,CH,A)                                      OUTFIL REMOVECC,NODETAIL,                                      SECTIONS=(12,3,                                               TRAILER3=(TOT=(1,7,UFF,EDIT=(TTTTT.TT)),12:12,3))      /*
krisprems

Active Member

Joined: 27 Nov 2006
Posts: 649
Location: India

Posted: Fri Oct 12, 2007 11:45 am    Post subject:

Frank
This quote is not required
 Quote: This DFSORT job would be better than Kriprems:

You are always best!!
raak

Active User

Joined: 23 May 2006
Posts: 166
Location: chennai

Posted: Fri Oct 12, 2007 12:19 pm    Post subject:

Small change in the input file structure..

i/p is a 90 byte file and the fields are as given below.
Field1: columns 1-2
Field2: columns 3-9
Field3: columns 11-22
Field4: columns 85-90

 Code: 01      1    813100.00       ABCDEF                                                  01      4     58200.00        ABCDEF                                          01      1    850000.00        ABCDEF                                          01      1     24265.22        ABCDEF                                          01      9   2046165.44        DEFGHI 01      1     89100.00        DEFGHI

The fields to be summed are field no:2 and field no:3. depending on the last field

i.e. the o/p should be

 Code: 01      7 001745565.22    ABCDEF 01     10 002135265.44    DEFGHI

Frank, I used the code u gave above by making small change like this.

 Code: SORT FIELDS=(85,6,CH,A)                          OUTFIL REMOVECC,NODETAIL,                        SECTIONS=(85,6,                                  TRAILER3=(1:1,2,TOT=(3,7,ZD),                    TOT=(11,12,UFF,EDIT=(TTTTTTTTT.TT)),85:85,6))

But i am facing a small issue here. the problem is the second field which is of 7 byte lenfth should start from output file's 3rd column.(same as in input file).
but when i use the above card, the second field is getting displaced and is starting from column no 11.

I need the 2nd field to start from column no:3 and third field to start from column no:11. ( as in the input file.) also 2nd field is of length 7 bytes and 3rd field is of length 9(09)v99.
What change should i make to the code to correct this?

hope i am clear in communicating my problem.[/code]
krisprems

Active Member

Joined: 27 Nov 2006
Posts: 649
Location: India

Posted: Fri Oct 12, 2007 1:21 pm    Post subject:

raak
This SORT JOB, does what you want
 Code: //*******************************************************                //STEP1    EXEC PGM=SORT                                                //SYSOUT   DD SYSOUT=*                                                  //SORTIN   DD i/p file                                                                      //SORTOUT  DD o/p file //SYSIN    DD *                       OPTION ZDPRINT                                             INREC OVERLAY=(3:3,9,UFF,TO=ZD,LENGTH=07,                               11:11,12,UFF,TO=ZD,LENGTH=12)                                     SORT FIELDS=(85,6,CH,A)                                           SUM FIELDS=(3,7,ZD,11,12,ZD)                                       OUTREC OVERLAY=(3:3,7,UFF,M10,LENGTH=7,                                 11:11,12,UFF,EDIT=(TTTTTTTTT.TT))            /*
Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

Posted: Fri Oct 12, 2007 8:34 pm    Post subject:

raak,

You can use these DFSORT control statements:

 Code: SORT FIELDS=(85,6,CH,A)   OUTFIL REMOVECC,NODETAIL,     SECTIONS=(85,6,       TRAILER3=(1:1,2,3:TOT=(3,7,ZD,EDIT=(IIIIIIT)),         11:TOT=(11,12,UFF,EDIT=(TTTTTTTTT.TT)),85:85,6))
Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

Posted: Fri Oct 12, 2007 8:46 pm    Post subject:

Krisprems,

You have:

 Code: SUM FIELDS=(3,7,ZD,11,12,ZD)   OUTREC OVERLAY=(3:3,7,UFF,M10,LENGTH=7,       11:11,12,UFF,EDIT=(TTTTTTTTT.TT))

Since you are converting the values to ZD and summing on the values as ZD, why are you using UFF instead of ZD in OVERLAY? UFF is for non-standard values (like those with a decimal point). ZD is a standard value. And ZD is actually more efficient than UFF. So your statements should be:

 Code: SUM FIELDS=(3,7,ZD,11,12,ZD)   OUTREC OVERLAY=(3:3,7,ZD,M10,LENGTH=7,       11:11,12,ZD,EDIT=(TTTTTTTTT.TT))
krisprems

Active Member

Joined: 27 Nov 2006
Posts: 649
Location: India

Posted: Sat Oct 13, 2007 9:11 am    Post subject:

Hello Frank
 Quote: And ZD is actually more efficient than UFF.

I didnt really realize that even the specification of data format would mark the efficiency.

And am really thankful to you, for being patient and pointing out the facts.
Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

Posted: Sat Oct 13, 2007 10:31 pm    Post subject:

 Quote: I didnt really realize that even the specification of data format would mark the efficiency.

Yes, the "fancy" formats like FS, UFF and SFF while very useful for non-standard types of data involve more processing than the "standard" types of data like BI, FI, ZD and PD. So if you have a choice, the standard types are preferable.

Also, note that once you convert values to ZD, using non-ZD formats for the converted values can cause problems. For example, if you used SFF to convert '-123.41' to ZD, you'd get X'F1F2F3F4D1' = '1234J'. This is a ZD value of -12341, but it is NOT an SFF value of -12341 - in fact, '1234J' would be interpreted as +1234 with SFF.
krisprems

Active Member

Joined: 27 Nov 2006
Posts: 649
Location: India

 Posted: Sun Oct 14, 2007 8:48 am    Post subject: once again, it was a learning.. thanks for that Frank.
dick scherrer

Site Director

Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

Posted: Mon Oct 15, 2007 1:41 am    Post subject:

Hello,

 Quote: For example, if you used SFF to convert '-123.41' to ZD, you'd get X'F1F2F3F4D1' = '1234J'. This is a ZD value of -12341, but it is NOT an SFF value of -12341 - in fact, '1234J' would be interpreted as +1234 with SFF.
Is there some way to tell DFSORT to abend if this condition (i.e. letters in a numeric field) is found while processing (U0016?)?

For my \$.02 an abend with an explanatory message would be far preferable to an incorrect calculation being used.
Frank Yaeger

DFSORT Moderator

Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

Posted: Mon Oct 15, 2007 9:32 pm    Post subject:

 Quote: Is there some way to tell DFSORT to abend if this condition (i.e. letters in a numeric field) is found while processing (U0016?)?

No. SFF is signed free form format. It's purpose is to pick out the digits from any set of characters so letters, commas, decimal points, etc would be expected. If you want straight digits, use ZD, not SFF. Sounds like you're asking for a format where you can specify exactly which characters you want to be considered valid and which you don't. I'm not sure that would actually be practical and it certainly wouldn't be very efficient.
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Sort numeric fields with trailing spaces sancraig16 SYNCSORT 7 Tue Apr 03, 2018 1:21 am SORT 3 files,extract specific fields ... Kiransr DFSORT/ICETOOL 13 Fri Mar 23, 2018 10:54 am Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am Copy set of records based on condition krish.deepu SYNCSORT 2 Thu Mar 08, 2018 11:39 am How to select record x+7 or x+2 based... JoAnn Kulcyk SYNCSORT 6 Tue Jan 16, 2018 10:49 pm

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