Sort Format Comma separated values

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

Post Reply
nachi
Member
Posts: 22
Joined: Wed Mar 25, 2009 5:16 pm

Sort Format Comma separated values

Post by nachi » Wed May 20, 2009 9:08 pm

Hi,

I have a comma separated file which needs formatting before loading into a DB2 table. A new value needs to be introduced between 2 fields.
Example:

Input file (LRECL 80, FB):
ROBYN ARIAS,QTR2:05-19-2009,PROG1,LINE1,
NACHI KATHIR,QTR2:05-20-2009,PROG2,LINE2,
SOLAI SAKTHI,QTR2:05-21-2009,PROG3,LINE3,

I want the output to look as follows
Output file (LRECL 80, FB):
ROBYN ARIAS,QTR2:05-19-2009,PROG1,05-19-2009,LINE1,
NACHI KATHIR,QTR2:05-20-2009,PROG2,05-20-2009,LINE2,
SOLAI SAKTHI,QTR2:05-21-2009,PROG3,05-21-2009,LINE3,

If you notice, on first line, between PROG1 and LINE1, a date of 05-19-2009 is introduced which is nothing but the date taken from second column value 'QTR2:05-19-2009'. In similar, second line is introduced with date taken from 2nd column value 'QTR2:05-20-2009'.

I used the foll. SYSIN card which introduced the date

Code: Select all

OPTION COPY
OUTREC PARSE=(%00=(ENDBEFR=C',',FIXLEN=25),
             %01=(ENDBEFR=C':',FIXLEN=04),
             %02=(ENDBEFR=C',',FIXLEN=10),
             %03=(ENDBEFR=C',',FIXLEN=5),
             %04=(ENDBEFR=C',',FIXLEN=5)),
       BUILD=(1:%00,26:C',',
       27:%01,31:C':',
       32:%02,42:C',',
       43:%03,48:C',',
       49:%02,59:C',',
       60:%04)
And the output looks like
ROBYN ARIAS ,QTR2:05-19-2009,PROG1,05-19-2009,LINE1
NACHI KATHIR ,QTR2:05-20-2009,PROG2,05-20-2009,LINE2
SOLAI SAKTHI ,QTR2:05-21-2009,PROG3,05-21-2009,LINE3

Now I have to remove the trailing spaces without altering the space in between names in the first column.

How can this be done? Can you please help me?

Thanks,
Nachi

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Wed May 20, 2009 9:24 pm

You can use these DFSORT control statements to do what you asked for:

Code: Select all

  OPTION COPY                                                      
  OUTREC IFOUTLEN=80,                                              
    IFTHEN=(WHEN=INIT,                                             
      PARSE=(%00=(ENDBEFR=C',',FIXLEN=25),                         
             %01=(ENDBEFR=C':',FIXLEN=04),                         
             %02=(ENDBEFR=C',',FIXLEN=10),                         
             %03=(ENDBEFR=C',',FIXLEN=5),                          
             %04=(ENDBEFR=C',',FIXLEN=5)),                         
      BUILD=(1:%00,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",'),         
       27:%01,31:C':',                                             
       32:%02,42:C',',                                             
       43:%03,48:C',',                                             
       49:%02,59:C',',                                             
       60:%04)),                                                   
    IFTHEN=(WHEN=INIT,BUILD=(1,64,SQZ=(SHIFT=LEFT,PAIR=QUOTE))),   
    IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=1,ENDPOS=25,               
      IN=C'"',OUT=C''))                                            
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

nachi
Member
Posts: 22
Joined: Wed Mar 25, 2009 5:16 pm

Post by nachi » Wed May 20, 2009 9:43 pm

Frank,

Wonderful! It is working. I was trying to remove blanks with 'PREBLANK = QUOTE'. It didn't work.

As always, thanks for all your help!

Thanks,
Nachi

nachi
Member
Posts: 22
Joined: Wed Mar 25, 2009 5:16 pm

Post by nachi » Thu May 21, 2009 12:36 am

Frank,

I have another question.

If I have to eliminate few records while writing to output, how can that be done.

For example:
Input: FB 80
ROBYN ARIAS,QTR2:05-19-2009,1,PROG1,LINE1,
NACHI KATHIR,QTR2:05-20-2009,2,PROG2,LINE2,
SOLAI SAKTHI,QTR2:05-21-2009,1,PROG3,LINE3,

If I need only records which contain '1' before PROG1 which means I need only the 1st and 3rd records as in above example, how can this be accomplished?

Required Output:
ROBYN ARIAS,QTR2:05-19-2009,1,PROG1,05-19-2009,LINE1,
SOLAI SAKTHI,QTR2:05-21-2009,1,PROG3,05-21-2009,LINE3,

I believe it can be done in 2 steps. But curious to know whether it can be done in a single step?

Thanks,
Nachi

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Thu May 21, 2009 12:53 am

'1' before PROG1 would only give you the 1st record, so I'll assume you mean '1' before PROG. Here's a DFSORT job that will do what you asked for in one pass:

Code: Select all

//S1    EXEC  PGM=SORT
//SYSOUT    DD  SYSOUT=*
//SORTIN DD *
ROBYN ARIAS,QTR2:05-19-2009,1,PROG1,LINE1,
NACHI KATHIR,QTR2:05-20-2009,2,PROG2,LINE2,
SOLAI SAKTHI,QTR2:05-21-2009,1,PROG3,LINE3,
//SORTOUT DD SYSOUT=*
//SYSIN    DD    *
  OPTION COPY
  OUTREC IFOUTLEN=80,
    IFTHEN=(WHEN=INIT,
      PARSE=(%00=(ENDBEFR=C',',FIXLEN=25),
             %01=(ENDBEFR=C':',FIXLEN=04),
             %02=(ENDBEFR=C',',FIXLEN=10),
             %03=(ENDBEFR=C',',FIXLEN=1),
             %04=(ENDBEFR=C',',FIXLEN=5),
             %05=(ENDBEFR=C',',FIXLEN=5)),
      BUILD=(1:%00,JFY=(SHIFT=LEFT,LEAD=C'"',TRAIL=C'",'),
       27:%01,C':',
       32:%02,C',',
       43:%03,C',',
       45:%04,C',',
       51:%02,C',',
       62:%05,C','))
  OUTFIL INCLUDE=(43,6,CH,EQ,C'1,PROG'),
    IFTHEN=(WHEN=INIT,BUILD=(1,80,SQZ=(SHIFT=LEFT,PAIR=QUOTE))),
    IFTHEN=(WHEN=INIT,FINDREP=(STARTPOS=1,ENDPOS=25,
      IN=C'"',OUT=C''))
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

nachi
Member
Posts: 22
Joined: Wed Mar 25, 2009 5:16 pm

Post by nachi » Thu May 21, 2009 1:44 am

Frank,

That's exactly right. It has to be PROG and not PROG1.

The solution that you gave also worked! I got the required output and very happy about it. Thanks for all your help!

Thanks,
Nachi

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Thu May 21, 2009 2:09 am

Ah, my crystal ball is working well today. :lol: Glad I could help.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others