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

Parse & Sum in a VB file with Variable Length Key Fields
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
PostPosted: Sat Apr 20, 2013 2:20 pm
 Reply with quote
Hi,

I have an Input File(RECFM=VB & LRECL=1004), in which first three fields are key. 4th field is Amount.
My output file should have no duplicates and final sum of the amount for same set of keys.
If sum of the amount is zero, then we don't need that record in output file.
Output file should also be the same,i.e. RECFM=VB and LRECL=1004.

Input File:
Code:
XX|11111|001A|16.93|JOHNNY B. GOODE||INVALL||||
XX|2222|001A|-17.12|JOHNY DEPP II||INVALL||||
XX|33333|001|10.45|JOHNNY R. CASH||INVALL||||
XX|4444444|0002|15.93|JOHNNY ENGLISH||INVALL||||
XX|2222|001A|15.12|JOHN DEPP II||INVALL||||
XX|33333|001|-9.45|JOHNNY R. CASH||INVALL||||
XX|11111|001A|-16.93|JOHNNY B. GOODE||INVALL||||


Output File:

Code:
XX|2222|001A|-2.12|JOHNY DEPP II||INVALL||||
XX|33333|001|1.00|JOHNNY R. CASH||INVALL||||
XX|4444444|0002|15.93|JOHNNY ENGLISH||INVALL||||


I searched the forum and found this post somewhat similar, in terms of Parsing.

My current approach is to change the Source Program for this Input file and make all the fields Fixed Length Format and then use SORT to get the final output.

But then I was just wondering how It can be done using just SORT, without changing the Source Program.

Any thought/suggestion is much appreciated.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Sat Apr 20, 2013 2:47 pm
 Reply with quote
You could "un-CSV" them with PARSE in INREC.

SORT and SUM

Re-CSV them in OUTREC (SQZ, FINDREP).

OUTFIL OMIT for the zero summed value.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
PostPosted: Sat Apr 20, 2013 3:59 pm
 Reply with quote
Thanks Bill for your quick response..

Some additional information:

File will have maximum 11 fields:
Code:
1|2|3|4|5|6|7|8|9|10|11


Maximum Field length would be:
Code:
1st - 02
2nd - 11
3rd - 10
4th - 12 (including Sign)
5th - 30
Rest all - 10


As per your suggestion, I have tried to create a Pseudo SORT Card as:

Code:

OPTION COPY
  PARSE=(%01=(ENDBEFR=C'|',FIXLEN=02),%02=(ENDBEFR=C'|',FIXLEN=11),   
         %03=(ENDBEFR=C'|',FIXLEN=10),%04=(ENDBEFR=C'|',FIXLEN=12),
         %05=(ENDBEFR=C'|',FIXLEN=30),%06=(ENDBEFR=C'|',FIXLEN=10),
         %07=(ENDBEFR=C'|',FIXLEN=10),%08=(ENDBEFR=C'|',FIXLEN=10),
         %09=(ENDBEFR=C'|',FIXLEN=10),%10=(ENDBEFR=C'|',FIXLEN=10),
         %11=(FIXLEN=10)),

  BUILD=(%01,C'|',%02,C'|',%03,C'|',%04,C'|',%05,C'|',
         %06,C'|',%07,C'|',%08,C'|',%09,C'|',%10,C'|',
         %11)),
 
  SORT FILEDS=(01,02,CH,A,
               04,11,CH,A,
               16,10,CH,A),
  SUM FIELDS=(27,12,ZD)

  OUTFIL INCLUDE=(27,12,ZD,NE,0)                                   
    BUILD=(1,4,5,1000,SQZ=(SHIFT=LEFT))



Is it similar to what you have suggested?
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
PostPosted: Sat Apr 20, 2013 9:07 pm
 Reply with quote
Hi Bill,

Im trying with below SORT Card, but its not giving me the desired output:

Code:
   SORT FIELDS=COPY                                               
    INREC PARSE=(%00=(ABSPOS=5,ENDBEFR=C'|',FIXLEN=02),     
                 %01=(ENDBEFR=C'|',FIXLEN=11),               
                 %02=(ENDBEFR=C'|',FIXLEN=10),               
                 %03=(ENDBEFR=C'|',FIXLEN=12),               
                 %04=(ENDBEFR=C'|',FIXLEN=30),               
                 %05=(ENDBEFR=C'|',FIXLEN=10),               
                 %06=(ENDBEFR=C'|',FIXLEN=10),               
                 %07=(ENDBEFR=C'|',FIXLEN=10),               
                 %08=(ENDBEFR=C'|',FIXLEN=10),               
                 %09=(ENDBEFR=C'|',FIXLEN=10),               
                 %10=(FIXLEN=10)),                           
     BUILD=(1,4,5:%00,C'|',                                 
                8:%01,C'|',                                 
               20:%02,C'|',                                 
               31:%03,SFF,TO=ZD,LENGTH=12,C'|',             
               44:%04,C'|',                                 
               75:%05,C'|',                                 
               86:%06,C'|',                                 
               97:%07,C'|',                                 
              108:%08,C'|',                                 
              119:%09,C'|',                                         
              130:%10)                                               
     SORT FIELDS=(05,02,CH,A,                                       
                  08,11,CH,A,                                       
                  20,10,CH,A)                                       
     SUM FIELDS=(31,12,ZD)                                           
     OUTREC IFTHEN(WHEN=INIT,BUILD=(1,4,5,1000,SQZ(SHIFT=LEFT)))


But I'm getting below error:

Code:
                SORT FIELDS=(05,02,CH,A,               
 ICE002I 0 DUPLICATE OR CONFLICTING SORT     STATEMENT
                             08,11,CH,A,               
                             $                         
 ICE007A 0 SYNTAX ERROR                               
                             20,10,CH,A,               
                             $                         
 ICE007A 0 SYNTAX ERROR                               
                             31,12,ZD)                 
                             $                         
 ICE007A 0 SYNTAX ERROR                               
                SUM FIELDS=(31,12,ZD)                 
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Sat Apr 20, 2013 11:35 pm
 Reply with quote
You have SORT FIELDS=COPY which you'll need to get rid of, because you want to sort the data.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
PostPosted: Sun Apr 21, 2013 8:32 pm
 Reply with quote
Thanks Bill..for you continuous assistance..I got it working though I'm not sure if I have done it in most efficient manner.

SORT CARD:
Code:

//SYSIN    DD *
    INREC PARSE=(%00=(ABSPOS=5,ENDBEFR=C'|',FIXLEN=02),
                 %01=(ENDBEFR=C'|',FIXLEN=11),         
                 %02=(ENDBEFR=C'|',FIXLEN=10),         
                 %03=(ENDBEFR=C'|',FIXLEN=12),         
                 %04=(ENDBEFR=C'|',FIXLEN=30),         
                 %05=(ENDBEFR=C'|',FIXLEN=10),         
                 %06=(ENDBEFR=C'|',FIXLEN=10),         
                 %07=(ENDBEFR=C'|',FIXLEN=10),         
                 %08=(ENDBEFR=C'|',FIXLEN=10),         
                 %09=(ENDBEFR=C'|',FIXLEN=10),         
                 %10=(FIXLEN=10)),                     
     BUILD=(1,4,5:%00,C'|',                           
                8:%01,C'|',                           
               20:%02,C'|',                           
               31:%03,SFF,TO=ZD,LENGTH=12,C'|',       
               44:%04,C'|',                           
               75:%05,C'|',                           
               86:%06,C'|',                           
               97:%07,C'|',                           
              108:%08,C'|',                                           
              119:%09,C'|',                                           
              130:%10)                                               
     SORT FIELDS=(05,02,CH,A,                                         
                  08,11,CH,A,                                         
                  20,10,CH,A)                                         
     SUM FIELDS=(31,12,ZD)                                           
     OUTFIL INCLUDE=(31,12,ZD,NE,0),                                 
            IFTHEN(WHEN=INIT,                                         
               OVERLAY=(31:31,12,ZD,EDIT=(SIIIIIIIT.TT),SIGNS(+,-))),
            IFTHEN(WHEN=INIT,                                         
               BUILD=(1,4,5,1000,SQZ(SHIFT=LEFT,MID=C' '))),         
            IFTHEN=(WHEN=INIT,                                       
               FINDREP=(INOUT=(C' | ',C'|',C'| ',C'|',C' |',C'|')))   
/*                                                                   



Input:
Code:
XX|11111|001A|16.93|JOHNNY B. GOODE||INVALL|||| 
XX|2222|001A|-17.12|JOHNY DEPP II||INVALL||||   
XX|33333|001|10.45|JOHNNY R. CASH||INVALL||||   
XX|4444444|0002|15.93|JOHNNY ENGLISH||INVALL||||
XX|2222|001A|15.12|JOHN DEPP II||INVALL||||     
XX|33333|001|-9.45|JOHNNY R. CASH||INVALL||||   
XX|11111|001A|-16.93|JOHNNY B. GOODE||INVALL||||



Output:
Code:
XX|2222|001A|-2.00|JOHNY DEPP II||INVALL||||     
XX|33333|001|+1.00|JOHNNY R. CASH||INVALL||||     
XX|4444444|0002|+15.93|JOHNNY ENGLISH||INVALL||||
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Mon Apr 22, 2013 3:19 am
 Reply with quote
You only need the first four fields to be fixed in length, the three you SORT on and the field you SUM. You could make the %05 just to have FIXLEN=themaximumlengthfordata.

If you look at ENDAT instead of ENDBEFR you should be able to preserve the "|" without having to reset it for the first three fields.

You don't need all the column-numbers on the BUILD, as all of those positions is where DFSORT is going to put the data anyway. Makes for maintenance problem when a field-length changes if you code the columns.

You can put the "pipe" for the amount back with the OVERLAY where you are editing.

Your PARSEd field %05 will be "too long", it will have a load of trailing blanks. Look at VLTRIM on OUTFIL to get rid of those. You've probably got some trailing blanks on your records already?

You'll no longer need the FINDREP to get rid of blanks between the pipes.

Bear in mind, if you have fields with more than one embedded blanks, you're going to change those to one. If you want to preserve those, you'll have to use PAIR= on the SQZ.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
PostPosted: Mon Apr 22, 2013 9:24 am
 Reply with quote
Hi Bill,

I have made the changes for the first three points, but when I use only VLTRIM, in some of the fields I have leading spaces:

SORT Card:
Code:
//SYSIN     DD *                                                     
    INREC PARSE=(%00=(ABSPOS=5,ENDAT=C'|',FIXLEN=03),               
                 %01=(ENDAT=C'|',FIXLEN=12),                         
                 %02=(ENDAT=C'|',FIXLEN=11),                         
                 %03=(ENDAT=C'|',FIXLEN=12),                         
                 %04=(FIXLEN=90)),                                   
     BUILD=(1,4,5:%00,                                               
                8:%01,                                               
               20:%02,                                               
               31:%03,SFF,TO=ZD,LENGTH=12,C'|',                     
               44:%04)                                               
     SORT FIELDS=(05,02,CH,A,                                       
                  08,11,CH,A,                                       
                  20,10,CH,A)                                       
     SUM FIELDS=(31,12,ZD)                                           
     OUTFIL INCLUDE=(31,12,ZD,NE,0),VLTRIM=C' ',                     
            IFTHEN(WHEN=INIT,                                       
               OVERLAY=(31:31,12,ZD,EDIT=(SIIIIIIIT.TT),SIGNS(+,-))),
            IFTHEN(WHEN=INIT,                                       
               BUILD=(1,4,5,1000,SQZ(SHIFT=LEFT,MID=C' ')))         
/*


Output:
Code:

 ------------------------------------------------------------------------------
XX|2222| 001A| -2.00|JOHNY DEPP II||INVALL||||                                 
EE4FFFF44FFFC446F4FF4DDCDE4CCDD4CC44CDECDD4444                                 
77F2222F00011F002B00F1685804577099FF955133FFFF                                 
 ------------------------------------------------------------------------------
XX|33333| 001| +1.00|JOHNNY R. CASH||INVALL||||                                 
EE4FFFFF44FFF444F4FF4DDCDDE4D44CCEC44CDECDD4444                                 
77F33333F0001F0E1B00F16855809B03128FF955133FFFF                                 
 ------------------------------------------------------------------------------
XX|4444444| 0002| +15.93|JOHNNY ENGLISH||INVALL||||                             
EE4FFFFFFF44FFFF444FF4FF4DDCDDE4CDCDCEC44CDECDD4444                             
77F4444444F00002F0E15B93F16855805573928FF955133FFFF                             
 ------------------------------------------------------------------------------
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Mon Apr 22, 2013 11:01 am
 Reply with quote
You will still need a SQZ, as the first four fields can contain blanks after the PARSE.
Back to top
View user's profile Send private message
mistah kurtz

Active User


Joined: 28 Jan 2012
Posts: 316
Location: Room: TREE(3). Hilbert's Hotel
PostPosted: Tue Apr 23, 2013 7:35 pm
 Reply with quote
Thanks a lot Bill..Sorry for replying a little late..but I was busy..people at my site find this solution "something that can be used as a temporary fix" and suggested that I should write a Program in COBOL or PL/I to do it..and I'm working on that now..

Nevertheless..it was a good learning experience..thanks again :-)
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Tue Apr 23, 2013 8:39 pm
 Reply with quote
No problem.

Funny. You have to SORT the file anyway. The processing can be done at the same time without another pass of the data. I guess there is a client "paying" for the extra load (and now extra development) :-)
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 FTP VB File from Mainframe retaining ... JCL & VSAM 1
No new posts Store the data for fixed length COBOL Programming 1
No new posts Extract the file name from another fi... DFSORT/ICETOOL 6
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts PARSE Syntax for not fix length word ... JCL & VSAM 7
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