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
 

 

Summing fields based on the last three digits

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
raak

Active User


Joined: 23 May 2006
Posts: 166
Location: chennai

PostPosted: Thu Oct 11, 2007 6:51 pm    Post subject: Summing fields based on the last three digits
Reply with quote

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.
Back to top
View user's profile Send private message

krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Thu Oct 11, 2007 7:13 pm    Post subject:
Reply with quote

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     
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Thu Oct 11, 2007 9:46 pm    Post subject:
Reply with quote

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))     
/*                                                         
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Fri Oct 12, 2007 11:45 am    Post subject:
Reply with quote

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

You are always best!! icon_biggrin.gif
Back to top
View user's profile Send private message
raak

Active User


Joined: 23 May 2006
Posts: 166
Location: chennai

PostPosted: Fri Oct 12, 2007 12:19 pm    Post subject:
Reply with quote

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]
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Fri Oct 12, 2007 1:21 pm    Post subject:
Reply with quote

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))           
/*                                                                     
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Fri Oct 12, 2007 8:34 pm    Post subject:
Reply with quote

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))
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Fri Oct 12, 2007 8:46 pm    Post subject:
Reply with quote

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))
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Sat Oct 13, 2007 9:11 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Sat Oct 13, 2007 10:31 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Sun Oct 14, 2007 8:48 am    Post subject:
Reply with quote

once again, it was a learning.. thanks for that Frank. icon_razz.gif
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Mon Oct 15, 2007 1:41 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


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

PostPosted: Mon Oct 15, 2007 9:32 pm    Post subject:
Reply with quote

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.
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts DFSORT MUL FIELDS tspr52 DFSORT/ICETOOL 16 Fri Mar 03, 2017 11:53 pm
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm
No new posts Sort two file having same field, one ... himanshu malik DFSORT/ICETOOL 7 Thu Feb 02, 2017 10:09 am
No new posts SYNCSORT SEQNUM and FI fields. nartcr SYNCSORT 7 Sat Jan 21, 2017 4:02 am


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