IBM Mainframe Forum

Hi,

I have a file which is pipe delimited (Record format: FB)

E.g of data

Date|Code|DESC|SRC|A1|A2|A3
20110430||SAS|989|100|10001|34567
20110430||SALKKKJ|989|100|10002|78976
20110430|ABC|SHGYUILLLLL|989|100|10002|0987777


I want to select all the records where the A2 is equal to 10002 and I want the output file to look like the input file with just records filtered on Salary.
So Expected O/p is
20110430||SALKKKJ|989|100|10002|78976
20110430|ABC|SHGYUILLLLL|989|100|10002|0987777
Is it possible using Syncsort? The header is only informational. My file does not contain the header. So no need of an omit statement.

I used the INREC,PARSE,BUILD and Include COND conditions, but it converted each column to a fixed length it is no more a delimited file. So I'm not sure how to proceed further.

(Unable to share screen shots coz working for a secured client)

Based on your sample input, why can't you use a substring (SS) for the string '|10002|' appearing anywhere in the record?

You could use PARSE to get hold of the field, stick it at the beginning or end of each record, then INCLUDE on that field in a seperate step, drop the field to have your original back.

I suspect it can be cut down to one step after that.

Hello Sarala.

Since you have already done the record selection with INREC and PARSE, you should be able to use OUTREC SQZ to remove the extra spaces in the fixed-length fields. Can you please show us what JCL you have already coded so that we can provide you with the appropriate control statements for your request?

Thank you.

I/P:
INPUT.FILE

20110430||SAS|989|100|10001|34567
20110430||SALKKKJ|989|100|10002|78976
20110430|ABC|SHGYUILLLLL|989|100|10002|0987777

/* JCL */

//PS0200 EXEC PGM=SORT
//SYSUDUMP DD SYSOUT=I
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN DD DSN=INPUT.FILE,DISP=SHR
//SORTOUT DD DSN=SAMPLE.SORT1,DISP=(,CATLG,DELETE),
// UNIT=SYSALLDA,SPACE=(CYL,(5,5),RLSE),
// LRECL=597,RECFM=FB
//SYSIN DD DSN=MY.PARM(VBFSORT),DISP=SHR
// IF (PS0200.RC GT 0) THEN
//PS0201 EXEC PGM=ABENDPGM
// ENDIF
//*
//PS0300 EXEC PGM=SORT
//SYSUDUMP DD SYSOUT=I
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN DD DSN=SAMPLE.SORT1,DISP=SHR
//SORTOUT DD DSN=SAMPLE.SORT2,DISP=(,CATLG,DELETE),
// UNIT=SYSALLDA,SPACE=(CYL,(5,5),RLSE),
// LRECL=597,RECFM=FB
//SYSIN DD DSN=MY.PARM(SAMPSORT),DISP=SHR
// IF (PS0300.RC GT 0) THEN
//PS301 EXEC PGM=ABENDPGM
// ENDIF

/* SORT CARD */
VBFSORT: (VARIABLE FILE SORT)

SORT FIELDS=COPY
INREC PARSE=(%01=(ENDBEFR=C'|',FIXLEN=008),
%02=(ENDBEFR=C'|',FIXLEN=005),
%03=(ENDBEFR=C'|',FIXLEN=015),
%04=(ENDBEFR=C'|',FIXLEN=004),
%05=(ENDBEFR=C'|',FIXLEN=003),
%06=(ENDBEFR=C'|',FIXLEN=005),
%07=(ENDBEFR=C'|',FIXLEN=010)),
BUILD=(01:%01,
09:%02,
14:%03,
29:%04,
33:%05,
36:%06,
41:%07)

SAMPSORT: (SAMPLE SORT WITH INCLUDE COND)
SORT FIELDS=COPY
INCLUDE COND=(36,4,CH,EQ,C'1002')

O/P:
SAMPLE.SORT1
********************************* Top of Data **********************************************************************************************
20110430 SAS 989 1001000134567
20110430 SALKKKJ 989 1001000278976
20110430ABC SHGYUILLLLL 989 100100020987777

SAMPLE.SORT2
********************************* Top of Data **********************************************************************************************
20110430 SALKKKJ 989 1001000278976
20110430ABC SHGYUILLLLL 989 100100020987777

Hello MRSPOCK,
Substring may not be false proof, because that value may appear anywhere in the record but I want only one particular field
with this value to be selected. But that was a good option. Thanks. I tried that and it worked.

Hello BillyBoyo,
Yours was the idea that I followed. Thanks. But I did not know how to stick the required column to the beginning of each record.
After some research in the forums, the SEQNUM came handy and I got the solution. Thanks again.

Logic followed was:
1. The field on which the filter has to be applied is brought as the first field in the file (with Parse and build options)
2. Sequence number is applied to both input file and the file created in step1(Using the SEQNUM option)
3. Merge the file created in step1 with the input file on the sequence number and bring the first field in the file1 as the first field in file2. (Merge option and Reformat fields)
4. Apply include condition on the first field and using outrec remove the appended first field to get the original file format back.

Here is the working code:
I/p file details (FB with LRECL 408 and pipe delimited file): Same input as said before

//JOBCARD
//*********************************************************************
//* *
//* STEP1: MAKE THE FIELD(S) ON WHICH THE FILTER HAS TO BE APPLIED AS *
//* THE FIRST FIELD(S) - REPEAT THE FIELDS *
//* LRECLS SHOULD BE ORIGINAL LRECL + REPEAT FIELD LENGTH + SEQNUM LEN*
//* *
//*********************************************************************
//PS0100 EXEC PGM=SORT
//SYSUDUMP DD SYSOUT=I
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN DD DSN=INPUT.FILE,DISP=SHR
//SORTOUT DD DSN=INPUT.FILE.SORT1,DISP=(,CATLG,DELETE),
// UNIT=SYSALLDA,SPACE=(CYL,(5,5),RLSE),
// LRECL=421,RECFM=FB
//SYSIN DD *
SORT FIELDS=COPY
INREC PARSE=(%01=(ENDBEFR=C'|',FIXLEN=008),
%02=(ENDBEFR=C'|',FIXLEN=005),
%03=(ENDBEFR=C'|',FIXLEN=015),
%04=(ENDBEFR=C'|',FIXLEN=003),
%05=(ENDBEFR=C'|',FIXLEN=004),
%06=(ENDBEFR=C'|',FIXLEN=005),
%07=(ENDBEFR=C'|',FIXLEN=007)),
BUILD=(01:%06)
OUTREC FIELDS=(1:1,5,403:X)
/*
// IF (PS0100.RC GT 0) THEN
//PS0101 EXEC PGM=ABENDPGM
// ENDIF
//*********************************************************************
//* *
//* STEP2: ADDS SEQUENCE NUMBER TO THE SOURCE FILE *
//* ADD ORIGINAL LRECL + 6 (FOR SEQNUM) *
//* *
//*********************************************************************
//PS0200 EXEC PGM=SORT
//SYSUDUMP DD SYSOUT=I
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN DD DSN=INPUT.FILE,DISP=SHR
//SORTOUT DD DSN=INPUT.FILE.SEQ1,DISP=(,CATLG,DELETE),
// UNIT=SYSALLDA,SPACE=(CYL,(5,5),RLSE),
// LRECL=421,RECFM=FB
//SYSIN DD *
OPTION COPY
OUTREC FIELDS=(1:SEQNUM,6,ZD,7:1,408)
/*
// IF (PS0200.RC GT 0) THEN
//PS0201 EXEC PGM=ABENDPGM
// ENDIF
//*********************************************************************
//* *
//* STEP3: ADDS SEQUENCE NUMBER TO THE REPEATED SORT FILE *
//* ADD ORIGINAL LRECL + 6 (FOR SEQNUM) *
//* *
//*********************************************************************
//PS0300 EXEC PGM=SORT
//SYSUDUMP DD SYSOUT=I
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN DD DSN=INPUT.FILE.SORT1,DISP=SHR
//SORTOUT DD DSN=INPUT.FILE.SEQ2,DISP=(,CATLG,DELETE),
// UNIT=SYSALLDA,SPACE=(CYL,(5,5),RLSE),
// LRECL=421,RECFM=FB
//SYSIN DD *
OPTION COPY
OUTREC FIELDS=(1:SEQNUM,6,ZD,7:1,408)
/*
// IF (PS0300.RC GT 0) THEN
//PS0301 EXEC PGM=ABENDPGM
// ENDIF
//*********************************************************************
//* *
//* STEP4: MERGE FILE2 WITH FILE1 ON THE SEQNUM. *
//* LRECL WILL INCLUDE THE FIRST FIELD + SEQNUM *
//* *
//*********************************************************************
//PS0400 EXEC PGM=SORT
//SORTJNF1 DD DSN=INPUT.FILE.SEQ2,DISP=SHR
//SORTJNF2 DD DSN=INPUT.FILE.SEQ1,DISP=SHR
//SORTOUT DD DSN=INPUT.FILE.MERGE,DISP=(NEW,CATLG,DELETE),
// UNIT=SYSALLDA,SPACE=(CYL,(5,5),RLSE),
// LRECL=428,RECFM=FB
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,6,A)
JOINKEYS FILES=F2,FIELDS=(1,6,A)
REFORMAT FIELDS=(F1:7,5,F2:7,415)
SORT FIELDS=COPY
/*
// IF (PS0400.RC GT 0) THEN
//PS0401 EXEC PGM=ABENDPGM
// ENDIF
//*********************************************************************
//* *
//* STEP5: INCLUDE COND STATEMENT TO FILTER THE REQUIRED VALUES *
//* *
//*********************************************************************
//PS0500 EXEC PGM=SORT
//SYSUDUMP DD SYSOUT=I
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN DD DSN=INPUT.FILE.MERGE,DISP=SHR
//SORTOUT DD DSN=INPUT.FILE.SAMPLE,DISP=(,CATLG,DELETE),
// UNIT=SYSALLDA,SPACE=(CYL,(5,5),RLSE),
// LRECL=408,RECFM=FB
//SYSIN DD *
SORT FIELDS=COPY
INCLUDE COND=(1,5,CH,EQ,C'10002')
OUTREC FIELDS=(1:6,407)
/*
// IF (PS0500.RC GT 0) THEN
//PS0501 EXEC PGM=ABENDPGM
// ENDIF

Good work. I'm sure you've picked up useful stuff for the future.

I don't understand, but maybe I'm missing something, why you have done the SEQNUM stuff and the JOIN. Can't you just do the INCLUDE on your file and then drop off the, by then, redundant extra field6?

Some examples for others, anyway.

Hi Billy,
I wanted to retain the original format of my source file. That's the reason I have included the SEQNUM merge join. If i use the parse and build options it is converting the delimited field to a field of fixed length with spaces padded to it. Thats the reason I had to join this field to my original delimited file and then use the include to filter and the outrec to drop the first redundant record.

OK, I understand now. I didn't know that would happen If I find a way around it, I'll post it here. Thanks for the explanation. In the end you did the whole thing yourself