IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Sum amounts from different positions in same row
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Andy85

New User


Joined: 25 Dec 2008
Posts: 35
Location: India
PostPosted: Sat Feb 05, 2011 3:15 am
 Reply with quote
Hi,

I have an input file having one or more fee amounts charged to a customer. For each record, I need to sum all the fee amounts of 'SALE' and 'MGMT' type and generate the result in single column in output file.

The input file is of variable length of max 84(+4 for storing length). The first 4 bytes of data are normal fields whereas the remaining 80 bytes gets build within an OCCURS claues that can go max upto 10 times based on value present in field "COUNT" (described later).

Fields are (as per data provided below):-
Customer Number-- from position 1 to 3 (Format: Char)
Count-- from position 4 to 4 (Format: Number) - Indicates how many fees will be there in the record, since record is of variable length

===Next two fields repeats based on the COUNT field ===
Desc-- from position 5 to 8 (Format: Char) - Indicates type of fee
Amount-- from position 9 to 12 (Format: Signed packed decimal i.e. S9(9)V9(2) COMP-3) - Indicates the amount for that description

Consider data as below

Code:

A014SALE====MGMT====SALE====SALE====
B014MGMT====SALE====MGMT====MGMT====
C015ADMN====MGMT====SALE====INSU====SALE====
D012ADMN====SALE====
E013MGMT====ADMN====SALE====


Expected output:

Sum of all SALE and MGMT in same row, to be populated in output for that row.

Code:

A01====
B01====
C01====
D01====
E01====


Anyway we can do it through DFSORT/ICEMAN/ICETOOL?

Or atleast If I could get below output, which is one record for each fee amount for same customer:
Code:

A01====
A01====
A01====
A01====
B01====
B01====
B01====
B01====
C01====
C01====
C01====
D01====
E01====
E01====


Thanks in advance for any help or guidance !!!

--- Andy
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Sat Feb 05, 2011 4:10 am
 Reply with quote
I have some questions about your description.

What is the RECFM of the input file? It's difficult to tell whether this is really a VB file, or an FB file padded with blanks. Which is it?

Quote:
Amount-- from position 9 to 12 (Format: Signed packed decimal i.e. S9(9)V9(2) COMP-3) - Indicates the amount for that description


An S(9)V9(2) COMP-3 field would be 6 bytes. But you say the amount is in positions 9-12 which is only 4 bytes. Which is correct?

Quote:
within an OCCURS claues that can go max upto 10 times based on value present in field "COUNT"

Count-- from position 4 to 4 (Format: Number)


What type of number? You can't use a ZD or PD value to get 10 into one byte, so what kind of number is it?
Back to top
View user's profile Send private message
Andy85

New User


Joined: 25 Dec 2008
Posts: 35
Location: India
PostPosted: Sat Feb 05, 2011 4:18 am
 Reply with quote
Ooops....made a mistake while mentioning the examples.

You can consider the maximum occurences as 9 and the COUNT field as of type ZD with length of 1 character.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Sat Feb 05, 2011 5:07 am
 Reply with quote
And what about my other two questions about RECFM, and 4-bytes vs 6-bytes for the PD field?
Back to top
View user's profile Send private message
Guest
PostPosted: Mon Feb 07, 2011 12:19 pm
 Reply with quote
Hi Andy85,
You can modify the below JCL and use it for your requirement:

Code:

//SORTIN   DD *                                                     
A014SALE2000MGMT2000SALE2000SALE2000                               
B014MGMT1000SALE1000MGMT1000MGMT1000                               
C015ADMN1000MGMT1000SALE1000INSU1000SALE1000                       
D012ADMN9999SALE9999                                               
E013MGMT9000ADMN9000SALE9000SALE9000                               
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
 SORT FIELDS=COPY                                                   
 OUTREC   IFTHEN=(WHEN=(4,1,ZD,EQ,2),                               
                 BUILD=(1,3,9,4,ZD,ADD,17,4,ZD,20:X)),             
          IFTHEN=(WHEN=(4,1,ZD,EQ,3),                               
                 BUILD=(1,3,9,4,ZD,ADD,17,4,ZD,ADD,25,4,ZD,20:X)), 
          IFTHEN=(WHEN=(4,1,ZD,EQ,4),                               
                 BUILD=(1,3,9,4,ZD,ADD,17,4,ZD,ADD,25,4,ZD,         
                        ADD,33,4,ZD)),                             
          IFTHEN=(WHEN=(4,1,ZD,EQ,5),                               
                 BUILD=(1,3,9,4,ZD,ADD,17,4,ZD,ADD,25,4,ZD,         
                        ADD,33,4,ZD,ADD,41,4,ZD)),                 
          IFTHEN=(WHEN=(4,1,ZD,EQ,6),                               
                 BUILD=(1,3,9,4,ZD,ADD,17,4,ZD,ADD,25,4,ZD,         
                        ADD,33,4,ZD,ADD,41,4,ZD,ADD,49,4,ZD)),     
          IFTHEN=(WHEN=(4,1,ZD,EQ,7),                               
                 BUILD=(1,3,9,4,ZD,ADD,17,4,ZD,ADD,25,4,ZD,         
                        ADD,33,4,ZD,ADD,41,4,ZD,ADD,49,4,ZD,       
                        ADD,57,4,ZD)),                             
          IFTHEN=(WHEN=(4,1,ZD,EQ,8),                               
                 BUILD=(1,3,9,4,ZD,ADD,17,4,ZD,ADD,25,4,ZD,         
                        ADD,33,4,ZD,ADD,41,4,ZD,ADD,49,4,ZD,       
                        ADD,57,4,ZD,ADD,65,4,ZD)),                 
          IFTHEN=(WHEN=(4,1,ZD,EQ,9),                               
                 BUILD=(1,3,9,4,ZD,ADD,17,4,ZD,ADD,25,4,ZD,         
                        ADD,33,4,ZD,ADD,41,4,ZD,ADD,49,4,ZD,       
                        ADD,57,4,ZD,ADD,65,4,ZD,ADD,73,4,ZD))
//*                                                         


This Jcl can be modified based on your requirement.
output:
Code:

A01           8000
B01           4000
C01           5000
D01          19998
E01          27000


devil13
There is always a better solution.
Back to top
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
PostPosted: Mon Feb 07, 2011 12:53 pm
 Reply with quote
@devil13

Quote:
BUILD=(1,3,9,4,ZD,ADD,17,4,ZD,20:X)),


Hi,
User wants to add comp3 values. so ZD should be changed to something else right?
Back to top
View user's profile Send private message
Guest
PostPosted: Mon Feb 07, 2011 2:12 pm
 Reply with quote
Hi Vasanthz,
You are right.
Actually Frank had raised the questions regarding the size of the variables:
Quote:

An S(9)V9(2) COMP-3 field would be 6 bytes. But you say the amount is in positions 9-12 which is only 4 bytes. Which is correct?


So the user can make changes in the sort card according to the data he has in the input.

- for comp3 he can use PD.

devil13
There is always a better solution
Back to top
Andy85

New User


Joined: 25 Dec 2008
Posts: 35
Location: India
PostPosted: Mon Feb 07, 2011 9:28 pm
 Reply with quote
Quote:

An S(9)V9(2) COMP-3 field would be 6 bytes. But you say the amount is in positions 9-12 which is only 4 bytes. Which is correct?


Frank,

The field picture clause is S9(9)V9(2).So the amount position would be of 6 bytes. You are correct. Incorrectly mentioned it as 4-bytes in my examples.
Back to top
View user's profile Send private message
Andy85

New User


Joined: 25 Dec 2008
Posts: 35
Location: India
PostPosted: Mon Feb 07, 2011 9:35 pm
 Reply with quote
devil13,

The parameters/job you have mentioned sums up all the amounts in the same row. However, my requirement is to sum up only those amounts for SALE & MGMT. There is no such condition in the SORTIN that you have provided. Is there any way of adding this condition too?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
PostPosted: Tue Feb 08, 2011 12:21 am
 Reply with quote
Andy,

Assuming that the PD values are 6 bytes each, and the RECFM is FB, you can use a DFSORT job like the following to do what you asked for. The result will have the customer number followed by the 6 byte PD totals for SALE and MGMT.

Code:

//S1  EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB)
//SORTOUT DD DSN=...  output file (FB/9)
//SYSIN    DD    *
  OPTION COPY
  INREC IFTHEN=(WHEN=INIT,
   PARSE=(%01=(ABSPOS=5,
    STARTAFT=C'SALE',STARTAFT=C'MGMT',FIXLEN=6),
   %02=(STARTAFT=C'SALE',STARTAFT=C'MGMT',FIXLEN=6),
   %03=(STARTAFT=C'SALE',STARTAFT=C'MGMT',FIXLEN=6),
   %04=(STARTAFT=C'SALE',STARTAFT=C'MGMT',FIXLEN=6),
   %05=(STARTAFT=C'SALE',STARTAFT=C'MGMT',FIXLEN=6),
   %06=(STARTAFT=C'SALE',STARTAFT=C'MGMT',FIXLEN=6),
   %07=(STARTAFT=C'SALE',STARTAFT=C'MGMT',FIXLEN=6),
   %08=(STARTAFT=C'SALE',STARTAFT=C'MGMT',FIXLEN=6),
   %09=(STARTAFT=C'SALE',STARTAFT=C'MGMT',FIXLEN=6)),
  BUILD=(1,3,%01,X'FF',%02,X'FF',%03,X'FF',%04,X'FF',
   %05,X'FF',%06,X'FF',%07,X'FF',%08,X'FF',%09,X'FF')),
  IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=4,
    IN=X'404040404040FF',OUT=X'00000000000C00')),
  IFTHEN=(WHEN=INIT,
    BUILD=(1,3,4,6,PD,ADD,11,6,PD,ADD,18,6,PD,ADD,25,6,PD,ADD,
     32,6,PD,ADD,39,6,PD,ADD,46,6,PD,ADD,53,6,PD,ADD,60,6,PD,
     TO=PD,LENGTH=6))
/*
Back to top
View user's profile Send private message
Guest
PostPosted: Tue Feb 08, 2011 9:13 am
 Reply with quote
Hi Andy85,
You have the solution now.

Quote:

Or atleast If I could get below output, which is one record for each fee amount for same customer:
Code:

A01====
A01====
A01====
A01====
B01====
B01====
B01====
B01====
C01====
C01====
C01====
D01====
E01====
E01====



The below JCL is just to meet the above requirement. Thought this would also be helpful for someone like me in future icon_smile.gif

Code:

//SORTIN   DD *                                                     
A014SALE2000MGMT2000SALE2000SALE2000                               
B014MGMT1000SALE1000MGMT1000MGMT1000                               
C015ADMN1000MGMT1000SALE1000INSU1000SALE1000                       
D012ADMN9999SALE9999                                               
E013MGMT9000ADMN9000SALE9000                               
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
 SORT FIELDS=COPY                                                   
 OUTFIL IFTHEN=(WHEN=(4,1,ZD,EQ,2),   
                BUILD=(1,3,5,8,/,   
                       1,3,13,8)),   
         IFTHEN=(WHEN=(4,1,ZD,EQ,3),
                BUILD=(1,3,5,8,/,   
                       1,3,13,8,/,   
                       1,3,21,8)),   
         IFTHEN=(WHEN=(4,1,ZD,EQ,4),
                BUILD=(1,3,5,8,/,   
                       1,3,13,8,/,   
                       1,3,21,8,/,   
                       1,3,29,8)),   
         IFTHEN=(WHEN=(4,1,ZD,EQ,5),
                BUILD=(1,3,5,8,/,   
                       1,3,13,8,/,   
                       1,3,21,8,/,   
                        1,3,29,8,/, 
                        1,3,37,8)), 
          IFTHEN=(WHEN=(4,1,ZD,EQ,6),
                 BUILD=(1,3,5,8,/,   
                        1,3,13,8,/, 
                        1,3,21,8,/, 
                        1,3,29,8,/, 
                        1,3,37,8,/, 
                        1,3,45,8)), 
          IFTHEN=(WHEN=(4,1,ZD,EQ,7),
                 BUILD=(1,3,5,8,/,   
                        1,3,13,8,/, 
                        1,3,21,8,/, 
                        1,3,29,8,/, 
                        1,3,37,8,/, 
                        1,3,45,8,/, 
                        1,3,53,8)), 
          IFTHEN=(WHEN=(4,1,ZD,EQ,8),
                 BUILD=(1,3,5,8,/,   
                        1,3,13,8,/, 
                        1,3,21,8,/, 
                        1,3,29,8,/, 
                        1,3,37,8,/, 
                        1,3,45,8,/, 
                        1,3,53,8,/, 
                        1,3,61,8)), 
          IFTHEN=(WHEN=(4,1,ZD,EQ,9),
                 BUILD=(1,3,5,8,/,   
                        1,3,13,8,/, 
                        1,3,21,8,/, 
                        1,3,29,8,/, 
                        1,3,37,8,/, 
                        1,3,45,8,/,
                        1,3,53,8,/,
                        1,3,61,8,/,
                        1,3,69,8)),
//*                               



Output of the same:
Code:

A01SALE2000
A01MGMT2000
A01SALE2000
A01SALE2000
B01MGMT1000
B01SALE1000
B01MGMT1000
B01MGMT1000
C01ADMN1000
C01MGMT1000
C01SALE1000
C01INSU1000
C01SALE1000
D01ADMN9999
D01SALE9999
E01MGMT9000
E01ADMN9000
E01SALE9000


devil13
There is always a better solution.
Back to top
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Tue Feb 08, 2011 10:37 pm
 Reply with quote
devil13 wrote:
The below JCL is just to meet the above requirement. Thought this would also be helpful for someone like me in future.


devil13,

With z/OS DFSORT V1R10 PTF UK90025 or z/OS DFSORT V1R12 PTF UK90026 (Oct,2010), you can now use the new RESIZE operator of DFSORT's ICETOOL to easily create smaller records from larger records or larger records from smaller records.

Code:

//STEP0100 EXEC PGM=ICETOOL                               
//TOOLMSG  DD SYSOUT=*                                     
//DFSMSG   DD SYSOUT=*                                     
//IN       DD *                                           
A014SALE2000MGMT2000SALE2000SALE2000                       
B014MGMT1000SALE1000MGMT1000MGMT1000                       
C015ADMN1000MGMT1000SALE1000INSU1000SALE1000               
D012ADMN9999SALE9999                                       
E013MGMT9000ADMN9000SALE9000                               
//OUT      DD SYSOUT=*                                     
//TOOLIN   DD *                                           
  RESIZE FROM(IN) TO(OUT) TOLEN(11) USING(CTL1)           
//CTL1CNTL DD *                                           
  INREC BUILD=(1,3,5,8,1,3,13,8,1,3,21,8,1,3,29,8,1,3,37,8,
               1,3,45,8,1,3,53,8,1,3,61,8,1,3,69,8)       
  OUTFIL OMIT=(4,1,CH,EQ,C' ')                             
//*


For complete details on the new functions for DFSORT and DFSORT's ICETOOL available with the Oct, 2010 PTF, see:

www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000242
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Replacing 'YYMMDD' with date, varying... SYNCSORT 3
No new posts Remove commas from specific positions... DFSORT/ICETOOL 10
No new posts finding anydata between positions of ... SYNCSORT 13
No new posts File matching with PD key on differen... DFSORT/ICETOOL 2
This topic is locked: you cannot edit posts or make replies. Canada mainframe systems programming ... Mainframe Jobs 0
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us