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
 

 

Sum amounts from different positions in same row

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Sum amounts from different positions in same row
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 Moderator


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

PostPosted: Sat Feb 05, 2011 4:10 am    Post subject:
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    Post subject: Reply to: Sum amounts from different positions in same row
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 Moderator


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

PostPosted: Sat Feb 05, 2011 5:07 am    Post subject:
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    Post subject:
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: 1445
Location: Azeroth

PostPosted: Mon Feb 07, 2011 12:53 pm    Post subject:
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    Post subject:
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    Post subject: Reply to: Sum amounts from different positions in same row
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    Post subject: Reply to: Sum amounts from different positions in same row
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 Moderator


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

PostPosted: Tue Feb 08, 2011 12:21 am    Post subject:
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    Post subject:
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    Post subject:
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:

http://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    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 File matching with PD key on differen... hiravibk DFSORT/ICETOOL 2 Wed Feb 17, 2016 11:40 pm
This topic is locked: you cannot edit posts or make replies. Canada mainframe systems programming ... Gary Jacek Mainframe Jobs 0 Sat Jan 30, 2016 4:26 am
No new posts Displaying records per contract and a... madishpa DFSORT/ICETOOL 2 Mon Feb 16, 2015 7:39 pm
No new posts How to identify +ve and -ve amounts sudhakar_mainframe DFSORT/ICETOOL 2 Thu Dec 04, 2014 8:25 pm
No new posts Replacing records at different positions abhijit.nayak01 DFSORT/ICETOOL 3 Wed Sep 24, 2014 12:11 am


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