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

To convert char to signed numeric, then SUM it


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Fri Apr 15, 2011 3:10 am
Reply with quote

Hi all,

I have a flat file of FB 436 bytes out of which the amount field starts from 422nd byte and is stored as character format (15bytes).
I want to sum the amount based on Policy number (6 bytes char starting from 19th byte) and Effective date (10 bytes char starting from 39th byte).
I tried to make use of below mentioned SORT card but is abending because amount field is not numeric.

Input (putting the amount field alone from 422nd byte - 436th byte)
Code:
          83.33
          83.33
          -8.09
       26995.00
          14.44
         -13.12
         153.07
         -17.23
         200.98
Output (Need to have the whole file with summed amount from 422nd byte)
For example if the first 2 records have same policy number and effective date, we should get it as

Code:
         166.66
          -8.09
       26995.00
          14.44
         -13.12
         153.07
         -17.23
         200.98

Code:

  SORT FIELDS=(19,6,CH,A,39,10,CH,A)
  SUM FIELDS=(422,15,CH)           


Can anybody please help me in getting the Sort card corrected. I also want to see the resultant amount field in char format (for downloading to excel sheet)

Thanks
Vinu
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: Fri Apr 15, 2011 3:44 am
Reply with quote

You can use a DFSORT job like the following to do what you asked for:

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=...  input file (FB/436)
//SORTOUT DD DSN=...  output file (FB/436)
//SYSIN DD *
  SORT FIELDS=(19,6,CH,A,39,10,CH,A)
  OUTFIL REMOVECC,NODETAIL,
    SECTIONS=(19,6,SKIP=0L,
       39,10,TRAILER3=(1,421,
        422:TOT=(422,15,SFF,EDIT=(SIIIIIIIIIIT.TT),SIGNS=(,-))))
/*
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Fri Apr 15, 2011 3:57 am
Reply with quote

Thanks Frank for the code
I have run the SOrt card and got the below mentioned error.

Code:
 SYSIN :                                                                     
    SORT FIELDS=(19,6,CH,A,39,10,CH,A)                                   00150
    OUTFIL REMOVECC,NODETAIL,                                            00180
     SECTIONS=(19,6,SKIP=0L,39,10,                                       00190
     TRAILER3=(1,421,                                                    00191
                 *                                                           
               422:TOT=(422,15,SFF,EDIT=(SIIIIIIIIIIT.TT),SIGNS=(,-))))  00192
 WER903I  SYNCSORT 1.3.2.0 IS NOT LICENSED FOR SERIAL 6D972, TYPE 2097 711, LP
 WER903I  PRODUCT WILL STOP WORKING IN  26 DAYS UNLESS A VALID KEY IS INSTALLE
 WER271A  OUTFIL STATEMENT  : NUMERIC FIELD ERROR                             
 WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                               
 WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                               


The SORT card used by me is
Code:
//STEP02  EXEC PGM=SORT                                               
 //SYSOUT    DD SYSOUT=*                                               
 //SYSPRINT  DD SYSOUT=*                                               
 //SORTIN    DD DSN=<input dsn>,DISP=SHR       
 //SORTOUT   DD DSN=<Output dsn>,                   
 //             DISP=(,CATLG,DELETE),                                 
 //             UNIT=DISK,                                             
 //             SPACE=(CYL,(20,20),RLSE),                             
 //             DCB=(RECFM=FB,LRECL=436,BLKSIZE=0)                     
 //SYSIN     DD *                                                     
    SORT FIELDS=(19,6,CH,A,39,10,CH,A)                                 
    OUTFIL REMOVECC,NODETAIL,                                         
     SECTIONS=(19,6,SKIP=0L,39,10,                                     
     TRAILER3=(1,421,                                                 
               422:TOT=(422,15,SFF,EDIT=(SIIIIIIIIIIT.TT),SIGNS=(,-))))
 /*                                                                   



It is mentioned as numeric erro but could not figure out how to correct it.

Thanks
Vinu
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: Fri Apr 15, 2011 4:09 am
Reply with quote

The job works fine with DFSORT. However, the WER messages indicate you're using Syncsort, not DFSORT. I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.

Please post questions on Syncsort in the JCL Forum, not in the DFSORT Forum.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Fri Apr 15, 2011 5:36 am
Reply with quote

Thanks for the info Frank.
I think I can see ICETOOL in my company.
Whether this requirement can be accomplished using ICETOOL ?

Thanks
Vinu
Back to top
View user's profile Send private message
superk

Global Moderator


Joined: 26 Apr 2004
Posts: 4652
Location: Raleigh, NC, USA

PostPosted: Fri Apr 15, 2011 5:40 am
Reply with quote

Try it, but I'll bet you find out you have Synctool installed instead.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Fri Apr 15, 2011 7:58 am
Reply with quote

Hi Frank / Kevin,

I have tried using ICETOOL but not getting an answer.
Is it possible to just convert the character format of amount field to just numeric ?

Appreciate your help.

Thanks
Vinu
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Fri Apr 15, 2011 10:01 am
Reply with quote

Vinu - you have been told that you are using SYNCSORT so why do you keep posting in the DFSORT section? SYNCSORT nd DFSORT are NOT the same product and their control cards are not necessarily 100% compatible.
Back to top
View user's profile Send private message
Anuj Dhawan

Superior Member


Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India

PostPosted: Fri Apr 15, 2011 12:42 pm
Reply with quote

vinu78 wrote:
I have tried using ICETOOL but not getting an answer.
Did you mean - in the above Job, you've used PGM=ICETOOL instead of PGM=SORT?

You get, WER271A, NUMERIC FIELD ERROR, which means a numeric field has been improperly specified on the indicated SyncSort control statement. See, if the position you 've used in Trailer3 actually have the numeric fields in those positions...
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: Fri Apr 15, 2011 9:50 pm
Reply with quote

Quote:
Thanks for the info Frank.
I think I can see ICETOOL in my company.
Whether this requirement can be accomplished using ICETOOL ?


Since you have Syncsort installed, you would be using Syncsort's SYNCTOOL, not DFSORT's ICETOOL. But whether you're using PGM=SORT or PGM=ICETOOL the solution would essentially be the same.

The root of your problem is that DFSORT supports large trailer fields, but Syncsort does not. I'll leave it to others to tell you how to get around that Syncsort limitation.
Back to top
View user's profile Send private message
vinu78

Active User


Joined: 02 Oct 2008
Posts: 179
Location: India

PostPosted: Tue Apr 19, 2011 8:22 pm
Reply with quote

Thanks Frank for the information.
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 -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Need to convert date format DFSORT/ICETOOL 20
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Keep leading zero(s) after convert fl... SYNCSORT 7
No new posts Convert single row multi cols to sing... DFSORT/ICETOOL 6
Search our Forums:

Back to Top