SUM FIELDS with leading spaces, comma, trailing signs



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

SUM FIELDS with leading spaces, comma, trailing signs

Postby sarathikg1 » Fri Mar 06, 2020 10:57 pm

I have an input file that I want to sort with as shown below.
I want to sum up the numeric fields for TESTCUSPn (col 129,length 9) . The first numeric field starts in col 144 and length is 15 (including trailing sign). The second field starts in col 166 and length is 21 (including trailing sign).
As you can see the numeric fields includes commas and leading spaces. Please let me know how to sort this in order to obtain the output below. We have SYNCSORT FOR Z/OS 2.1.8.


Input


Command ===>                                            
-3----+----4----+----5----+----6----+----7----+----8----+-
TESTCUSP1                 660-                     66,363.00-
TESTCUSP2             30,000-                     37,959.17-
TESTCUSP2             30,000                      37,963.33
TESTCUSP3             50,000-                     51,534.72-
 

Expected Output

Command ===>                                            
-3----+----4----+----5----+----6----+----7----+----8----+-
TESTCUSP1                 660-                     66,363.00-
TESTCUSP2                    0                               4.16
TESTCUSP3            50,000-                      51,534.72-



coded, to show the proper spacing,
please do it Yourself, the next time

the input was already clobbered
if You apply the code tags when submitting a post
the spacing will still be good
use the preview button to verify
sarathikg1
 
Posts: 4
Joined: Fri Mar 06, 2020 8:58 pm
Has thanked: 2 times
Been thanked: 0 time

Re: SUM FIELDS with leading spaces, comma, trailing signs

Postby steve-myers » Fri Mar 06, 2020 11:32 pm

  1. JCL, by itself, has no ability to process data and create user reports. The sole purpose of JCL is to specify the resources required to run a program, and then to allocate these resources and run the program.
  2. Obviously, since JCL cannot process data, the program must process the data and create the report.
  3. The program can be a program you write yourself in a programming language of your choice, or it can be an existing program, such as DFSort (an IBM program), or Syncsort or, perhaps, a program like SAS.. We do not know if DFSort or Syncsort is available in your environment - the odds are at least one of them is available.
Afterr you have determined what program you want to use, prepare the input for the program and the JCL to run the perogram, and at least attempt to run the JCL. Then we can help you.

These users thanked the author steve-myers for the post:
sarathikg1 (Sat Mar 07, 2020 2:48 am)
steve-myers
Global moderator
 
Posts: 2105
Joined: Thu Jun 03, 2010 6:21 pm
Has thanked: 4 times
Been thanked: 243 times

Re: SUM FIELDS with leading spaces, comma, trailing signs

Postby sarathikg1 » Sat Mar 07, 2020 12:15 am

Steve,
Thanks for your response.I apologize, if I am not clear as to what I want to do. My establishment has SYNCSORT FOR Z/OS 2.1.8.

I have an input file that I want to sort with as shown below. I want to use SYNCSORT to sum up fields in my input file. But the numeric fields have leading spaces, commas, decimal and trailing sign.

The sort field starts in col 129 and is 9 bytes long
The first numeric fields I want to sum starts in col 144 and 15 bytes long (including trailing sign)
The Second numeric fields I want to sum starts in col 166 and is 21 bytes long (including trailing sign)

Input File
Command ===>                                                  Scr
=COLS> -3----+----4----+----5----+----6----+----7----+----8----+-
000104 TESTCUSP1             805,000-                 805,074.56-
000105 TESTCUSP2              30,000-                  37,959.17-
000106 TESTCUSP2              30,000                   37,963.33
000107 TESTCUSP3                   0                      855.00-
000108 TESTCUSP4              20,000                   20,788.40


I want my output file to look like this after the sort

Output File

Command ===>                                                  Scr
=COLS> -3----+----4----+----5----+----6----+----7----+----8----+-
000104 TESTCUSP1             805,000-                 805,074.56-
000105 TESTCUSP2              30,000-                  37,959.17-
000106 TESTCUSP3                   0                        4.16
000107 TESTCUSP4              20,000                   20,788.40


This is the sample sort step I want to use. I know it does not work as there are leading spaces, commas etc . and it abends with S0C7. I looked up the manuals and googled it, but still not able to find the solution.

Command ===>                                                  Scroll ===> CSR
000007 //STEP010 EXEC PGM=SYNCSORT,PARM='VSCORE=5000K'                        
000008 //*                                                                    
000009 //*********************************************************************
000010 //**********       PROGRAM - SORT                             *********
000011 //**********                                                  *********
000012 //*********************************************************************
000013 //*                                                                    
000014 //SYSOUT   DD  SYSOUT=*                                                
000015 //SYSUDUMP DD  SYSOUT=*                                                
000016 //SORTIN   DD  DSN=MY.INPUT.FILE,DISP=SHR              
000017 //SORTOUT  DD  DSN=MY.OUTPUT.FILE,            
000018 //             DISP=(,CATLG,DELETE),                                  
000019 //             SPACE=(CYL,(20,5),RLSE)                                
000020 //SYSIN    DD  *                                                      
000021     SORT FIELDS=(129,9,CH,A)                                          
000022     SUM FIELDS=(144,15,ZD,166,21,ZD)                                  
000023 /*                                                                    
000024 //*                                                                    
sarathikg1
 
Posts: 4
Joined: Fri Mar 06, 2020 8:58 pm
Has thanked: 2 times
Been thanked: 0 time

Re: SUM FIELDS with leading spaces, comma, trailing signs

Postby sergeyken » Sat Mar 07, 2020 12:53 am

Use data format SFF instead of ZD in any of SORT utility statements.

IMPORTANT:
Decimal point will also be ignored, and the values to become multiplied by 100, - you'll need to edit your final total values accordingly.
Javas and Pythons come and go, but JCL and SORT stay forever.
User avatar
sergeyken
 
Posts: 408
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 6 times
Been thanked: 40 times

Re: SUM FIELDS with leading spaces, comma, trailing signs

Postby sergeyken » Sat Mar 07, 2020 1:44 am

Highly likely, nowadays only a ready-to-use example would help...

I hope that field positions can be adjusted to the original needs?

//*====================================================================
//* SUMMARIZE NON-CALCULATABLE FIELDS BY KEY SECTIONS                  
//*====================================================================
//SORT     EXEC  PGM=SORT                                              
//SYSOUT   DD    SYSOUT=*                                              
//SORTIN   DD    *                                                    
*---+----1----+----2----+----3----+----4----+----5----+----6----+----7-
       TESTCUSP1             805,000-                 805,074.56-      
       TESTCUSP2              30,000-                  37,959.17-      
       TESTCUSP2              30,000                   37,963.33      
       TESTCUSP3                   0                      855.00-      
       TESTCUSP4              20,000                   20,788.40      
//SORTOUT  DD    SYSOUT=*                                              
//SYSIN    DD *                                                        
 OMIT COND=(1,1,CH,EQ,C'*')                  EXCLUDE COMMENTS          
*                                                                      
 INREC OVERLAY=(81:25,15,SFF,ZD,LENGTH=15,   CONVERT TO ZD            
                96:45,21,SFF,ZD,LENGTH=21)   CONVERT TO ZD            
*                                                                      
 SORT FIELDS=(8,9,CH,A)                      SORT BY KEY FIELD(S)      
*                                                                      
 OUTFIL REMOVECC,                            EXCLUDE CONTROL CHARS    
        NODETAIL,                            EXCLUDE ALL EXCEPT TOTALS
        SECTIONS=(8,9,                       GROUP BY KEY FIELD(S)    
        TRAILER3=(1,7,                       PRE-FIELD1                
                  8,9,                       KEY FIELD                
                 17,6,                       POST-FIELD1              
          TOTAL=(81,15,ZD,                   SUMMARIZE FIELD2 AS ZD    
                 EDIT=(II,III,III,IITS),SIGNS=(,,,-)),                
                 38,7,                       POST-FIELD2              
          TOTAL=(96,21,ZD,                   SUMMARIZE FIELD3 AS ZD    
                 EDIT=(I,III,III,III,IIT.TTS),SIGNS=(,,,-)),          
                 66,15))                     POST-FIELD3              
*                                                                      
 END                                                                  
//*====================================================================


********************************* TOP OF DATA *************************
       TESTCUSP1             805,000-                 805,074.56-      
       TESTCUSP2                   0                        4.16      
       TESTCUSP3                   0                      855.00-      
       TESTCUSP4              20,000                   20,788.40      
******************************** BOTTOM OF DATA ***********************
Javas and Pythons come and go, but JCL and SORT stay forever.

These users thanked the author sergeyken for the post:
sarathikg1 (Sat Mar 07, 2020 2:47 am)
User avatar
sergeyken
 
Posts: 408
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 6 times
Been thanked: 40 times

Re: SUM FIELDS with leading spaces, comma, trailing signs

Postby sarathikg1 » Sat Mar 07, 2020 1:53 am

Thanks Sergeyken for your response ,

Will try it out. I couldn't respond earlier as I was in a meeting
sarathikg1
 
Posts: 4
Joined: Fri Mar 06, 2020 8:58 pm
Has thanked: 2 times
Been thanked: 0 time

Re: SUM FIELDS with leading spaces, comma, trailing signs

Postby sarathikg1 » Sat Mar 07, 2020 2:47 am

Sergeyken,

Your Solution worked like a charm. Thanks a lot, If you had not given me a ready to use example I would have researched it, but I cannot lie, the Example helped me a lot and save me lots of time.
sarathikg1
 
Posts: 4
Joined: Fri Mar 06, 2020 8:58 pm
Has thanked: 2 times
Been thanked: 0 time


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post