IBM Mainframe Forum

by **cbrio** » Fri Aug 07, 2009 11:14 pm

I have a file that contains duplicate values and missing values on the key variable. I want to end up with 3 files:

File 1: All records with a unique value on the key variable
File 2: All records with duplicate values on the key variable
File 3: All records with a blank in the key variable

I also would like to know if this can be done when the key is a concatenation of two non adjacent fields.

Examples: simple single field key

input
Id last name first name date of birth
12345 smith john 022678
12345 smith john 022678
23456 jones mary 030802
andrews hillary 072699
34567 lewis louis 052798
brand ellen 122900
34567 lewis louis 052798
45678 gold harry 112599
78912 fred

desired result
File 1 unique value on key
23456 jones mary 030802
45678 gold harry 112599
78912 fred

File 2 duplicate value on key
12345 smith john 022678
12345 smith john 022678
34567 lewis louis 052798
34567 lewis louis 052798

File 3 blank key
andrews hillary 072699
brand ellen 122900

Example 2: using the same input file, key = last name and date of birth (non-adjacent fields)

File 1: Unique value on key
23456 jones mary 030802
45678 gold harry 112599
andrews hillary 072699
brand ellen 122900

File 2: Duplicate value on key
12345 smith john 022678
12345 smith john 022678
34567 lewis louis 052798
34567 lewis louis 052798

File 3: Blank key
78912 fred

Thank you in advance for your help.

by **Frank Yaeger** » Sat Aug 08, 2009 1:11 am

You can use DFSORT/ICETOOL jobs like the following:

Example 1

Select all

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
12345 smith john 022678
12345 smith john 022678
23456 jones mary 030802
andrews hillary 072699
34567 lewis louis 052798
brand ellen 122900
34567 lewis louis 052798
45678 gold harry 112599
78912 fred
//UNIQ DD SYSOUT=*
//DUPS DD SYSOUT=*
//BLKS DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN) TO(BLKS) USING(CTL1)
SELECT FROM(IN) TO(UNIQ) ON(1,5,CH) NODUPS DISCARD(DUPS) USING(CTL2)
//CTL1CNTL DD *
INCLUDE COND=(1,5,CH,EQ,C' ')
//CTL2CNTL DD *
OMIT COND=(1,5,CH,EQ,C' ')
/*

Example 2

Select all

//S2 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
12345 smith john 022678
12345 smith john 022678
23456 jones mary 030802
andrews hillary 072699
34567 lewis louis 052798
brand ellen 122900
34567 lewis louis 052798
45678 gold harry 112599
78912 fred
//UNIQ DD SYSOUT=*
//DUPS DD SYSOUT=*
//BLKS DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN) TO(BLKS) USING(CTL1)
SELECT FROM(IN) TO(UNIQ) ON(11,10,CH) ON(36,6,CH) NODUPS DISCARD(DUPS)-
USING(CTL2)
//CTL1CNTL DD *
INCLUDE COND=(11,10,CH,EQ,C' ',AND,36,6,CH,EQ,C' ')
//CTL2CNTL DD *
OMIT COND=(11,10,CH,EQ,C' ',AND,36,6,CH,EQ,C' ')
/*

by **cbrio** » Sat Aug 08, 2009 2:49 am

Thank you very much!

IBM Mainframe Forum

Split a file into unique vs duplicate records

Split a file into unique vs duplicate records

Re: Split a file into unique vs duplicate records

Re: Split a file into unique vs duplicate records