Summing decimals and whole numbers in same file



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Summing decimals and whole numbers in same file

Postby sathish guru » Mon Nov 26, 2012 6:16 pm

I have an input file like below. Records are appended to the file for every sebsequent run.

lrecl = 61, recfm = FB

Input file:

----+----1----+----2----+----3----+----4----+----5----+----6----
********************************* Top of Data ******************
 1 Daily Aged Debt MI Summary,No.                               
 2 Total number of debts identified                  ,       31
 3 Total number of LTSB debts identified             ,       22
 4 Total number of HBOS debts identified             ,        9
 5 Total amount of debt                              ,  8183.79
 6 Total amount of debt for HDA                      ,   837.53
 7 Total amount of debt for HIA                      ,  5676.34
 8 Total amount of debt for HHH                      ,  1233.24
 9 Total amount of debt for SBY                      ,   410.23
10 Total amount of debt for MOR                      ,    26.45
11 Total number of LTSB exceptions identified        ,       22
12 Total number of HBOS exceptions identified        ,        9
 1 Daily Aged Debt MI Summary,No.                               
 2 Total number of debts identified                  ,        5
 3 Total number of LTSB debts identified             ,        2
 4 Total number of HBOS debts identified             ,        4
 5 Total amount of debt                              ,  5000.00
 6 Total amount of debt for HDA                      ,  1000.00
 7 Total amount of debt for HIA                      ,   500.00
 8 Total amount of debt for HLH                      ,  1500.00
 9 Total amount of debt for COV                      ,   800.00
10 Total amount of debt for MOR                      ,  1200.00
11 Total amount of debt for SBY                      ,  1200.00
12 Total number of LTSB exceptions identified        ,        2
13 Total number of HBOS exceptions identified        ,        4


Pos 1,2 - record no in each run
Pos 3,1 - Space
Pos 4,50 - metrics text
Pos 54,1 - Comma
Pos 55,9 - count or amount, format = ZD

I need to sum the values for each metrics based on its text. The values are in decimals for amounts and whole numbers for counts.
Number metrics can have any record no.

I used below Sort JCL:

//TEST     EXEC PGM=ICETOOL,COND=(4,LT)                             
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN1      DD DISP=SHR,DSN=G974650.LPB083.TEST                     
//OUT1     DD DSN=G974650.LPB081.DUALSUM.OUTPUT,                   
//         DISP=(,CATLG,DELETE),                                   
//         SPACE=(CYL,(500,500),RLSE),                             
//         DCB=(RECFM=F,LRECL=80,BLKSIZE=0),                       
//         UNIT=SYSDA                                               
//TEMP1    DD DSN=G974650.LPB081.DUALSUM.TEMP1,                     
//         DISP=(,CATLG,DELETE),                                   
//         SPACE=(CYL,(500,500),RLSE),                             
//         DCB=(RECFM=F,LRECL=80,BLKSIZE=0),                       
//         UNIT=SYSDA                                               
//TOOLIN   DD *                                                     
  COPY FROM(IN1) TO(TEMP1) USING(CTL1)                             
  COPY FROM(TEMP1) TO(OUT1) USING(CTL2)                             
//CTL1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(1,54,55:55,9,UFF,TO=ZD,LENGTH=09,
                                    64:64,17))                     
  SORT FIELDS=(4,50,CH,A)                             
  SUM FIELDS=(55,9,ZD)                                 
  OUTFIL BUILD=(1,80)                                 
//CTL2CNTL DD *                                       
  SORT FIELDS=(1,2,CH,A)                               
  OUTREC BUILD=(1:4,51,52:55,9,ZD,EDIT=(TTTTTT.TT))   
/*                                                     


I expect the output to be like this.

Expected output:

----+----1----+----2----+----3----+----4----+----5----+----6
Daily Aged Debt MI Summary,No.                     000000.00
Total number of debts identified                  ,       36
Total number of LTSB debts identified             ,       24
Total number of HBOS debts identified             ,       13
Total amount of debt                              , 13183.79
Total amount of debt for HDA                      ,  1837.53
Total amount of debt for HIA                      ,  6176.34
Total amount of debt for HHH                      ,  1233.24
Total amount of debt for HLH                      ,  1500.00
Total amount of debt for COV                      ,   800.00
Total amount of debt for SBY                      ,  1610.23
Total amount of debt for MOR                      ,  1226.45
Total number of LTSB exceptions identified        ,       24
Total number of HBOS exceptions identified        ,       13


But, on running above Sort, values are summed up correctly, but count fields are being displayed as decimals

Actual Output:

----+----1----+----2----+----3----+----4----+----5----+----6
Daily Aged Debt MI Summary,No.                     000000.00
Total number of debts identified                  ,000000.36
Total number of LTSB debts identified             ,000000.24
Total number of HBOS debts identified             ,000000.13
Total amount of debt                              ,013183.79
Total amount of debt for HDA                      ,001837.53
Total amount of debt for HIA                      ,006176.34
Total amount of debt for HHH                      ,001233.24
Total amount of debt for HLH                      ,001500.00
Total amount of debt for COV                      ,000800.00
Total amount of debt for SBY                      ,001610.23
Total amount of debt for MOR                      ,001226.45
Total number of LTSB exceptions identified        ,000000.24
Total number of HBOS exceptions identified        ,000000.13


Is there any thing I could try some other way? Please help.
sathish guru
 
Posts: 1
Joined: Mon Nov 26, 2012 4:46 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Summing decimals and whole numbers in same file

Postby BillyBoyo » Mon Nov 26, 2012 6:46 pm

You need to use IFTHEN=(WHEN=(logexp) to identify the different types of data, and use a different EDIT for those which are counts. Sort does not know about decimal places, so you have to deal with it yourself.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Summing decimals and whole numbers in same file

Postby skolusu » Mon Nov 26, 2012 11:43 pm

sathish guru ,

Your description and the data doesn't match.

1.You say your input LRECL is 61 , and you say that count/amount starts at Pos 55 for a length of 9 bytes which makes the length of the file as 63.
2. The output you have shown , shows "number" being sorted before "amount" . I am not sure as to how you got that, but with EBCDIC sorting that is not possible.

As Bill mentioned you need to identify the decimal dot in the records and perform the edit masking.
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post