Posted: Mon Aug 25, 2003 10:12 am Post subject: Unique records.
Hi All,
I have a unique requirement. My requirement is as follows.
I have a file with the following infomation.
ID optedseries Y/N
---- -------------- ----
112233 abcd Y
112233 cdef Y
112233 kefg N
112244 kkkk Y
112244 ccdd y
113311 aaaa y
113311 aabb n.
I would like to pull out all the participants who are only N. A participant can have any number of entries (with either Y or N). If atleast one of them is "Y" I need to ignore them. I need to extract the data only if on all the records the Y/N flag is N.
Is there any way to do this?
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Mon Aug 25, 2003 10:32 am Post subject:
By "participant", do you mean the ID field? Do you want one record per participant, or all of the records per participant, for the participants with all N flags? Can the flags really be uppercase or lowercase as you show, or are they always uppercase?
If I understand what you want correctly, none of the records in the example you gave would be kept because none of the participants have an N flag on all their records.
Please show a better example of your input and what you want the output to look like. _________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Hi Frank,
Thanks a lot for your quick response. When I said by participant its by ID. All are upper case (actually I was about to type everrthing in CAPS, but I thought typing in caps is yelling). The data I typed in was just a sample data. I have a very big file with around 600,000 records. I need to extract only one record. I.e if for an ID, in the file if he has 4 records, all with N, then i need to extract only one record. I basically need the ID. If I get one records that is enough for me.
Thanks.
Krk123.
6461124 ALPHA Y
6461124 BETA N
11223344 ALPHA Y
11223344 BETA Y
11226677 ALPHA N
11226677 BETA N
11226677 ALPH2 N
88997788 ALPHA Y
88997788 BETA Y
11229900 ALPH2 Y
Joined: 26 Nov 2002 Posts: 12369 Topics: 75 Location: San Jose
Posted: Mon Aug 25, 2003 11:39 am Post subject:
kRK123,
The following DFSORT/ICETOOL JCl will give you the desired results. I assumed the following layout(LRECL=80) for your input file
Code:
ID PIC X(08)
FILLER PIC X(01)
DESC PIC X(05)
FILLER PIC X(01)
IND PIC X(01)
FILLER PIC X(64)
A brief explanation of the job. we first take your input file and sumsort on the ID and IND
and eliminate the duplicates.we also split the sumsorted file into 2 temp files.
Temp file T1 will have all the records which has 'N' as IND. we also will add a constant of '1' at the end.
Temp file T2 will have all the records which has 'Y' as IND.we also will add a constant of '2' at the end.
Now we concatenate T1 & T2 and sort on just the ID and sum on the constants.By doing so any matched record will have a constant value of '3'. so we can eliminate these records because one of the records had an ind of 'Y'. We write out only records with constant value of '1' which is all the unmatched records and have an ind value of 'N'.
Code:
//STEP0100 EXEC PGM=ICETOOL
//*
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
6461124 ALPHA Y
6461124 BETA N
11223344 ALPHA Y
11223344 BETA Y
11226677 ALPHA N
11226677 BETA N
11226677 ALPH2 N
88997788 ALPHA Y
88997788 BETA Y
11229900 ALPH2 Y
//TOOLIN DD *
SORT FROM(IN) USING(CTL1)
SORT FROM(CON) USING(CTL2)
//T1 DD DSN=&T1,DISP=(,CATLG),SPACE=(CYL,(X,Y),RLSE)
//T2 DD DSN=&T2,DISP=(,CATLG),SPACE=(CYL,(X,Y),RLSE)
//CON DD DSN=&T1,DISP=(OLD),VOL=REF=*.T1
// DD DSN=&T2,DISP=(OLD),VOL=REF=*.T2
//OUT DD SYSOUT=*
//CTL1CNTL DD *
SORT FIELDS=(1,8,CH,A,16,1,CH,A) $ SORT ON ID, IND
SUM FIELDS=NONE $ ELIMINATE DUPES
OUTFIL FNAMES=T1,INCLUDE=(16,1,CH,EQ,C'N'),OUTREC=(1,80,C'1')
OUTFIL FNAMES=T2,INCLUDE=(16,1,CH,EQ,C'Y'),OUTREC=(1,80,C'2')
//CTL2CNTL DD *
SORT FIELDS=(1,8,CH,A) $ SORT ON ID
SUM FIELDS=(81,1,ZD) $ SUM ON CONSTANT
OUTFIL FNAMES=OUT,INCLUDE=(81,1,CH,EQ,C'1'), $ INCLUDE ONLY IND N
OUTREC=(1,80) $ STRIP OFF CONSTANT
/*
Joined: 02 Dec 2002 Posts: 1618 Topics: 31 Location: San Jose
Posted: Mon Aug 25, 2003 12:29 pm Post subject:
krk123,
For those who have DFSORT R14 PTF UQ90053 (Feb, 2003), you can also use SELECT with USING and SPLICE to do this. Here's the DFSORT/ICETOOL job, which will work for any LRECL:
Code:
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DSN=... input file
//TOOLIN DD *
* Get the first id with Y and the first id with N
SELECT FROM(IN) TO(T1) ON(1,8,CH) ON(16,1,CH) FIRST USING(CTL1)
* Get the first id of those with N only
SPLICE FROM(CON) TO(OUT) ON(1,8,CH) WITH(16,1) -
KEEPNODUPS USING(CTL2)
//T1 DD DSN=&T1,DISP=(,PASS),SPACE=(CYL,(5,5)),UNIT=SYSDA
//T2 DD DSN=&T2,DISP=(,PASS),SPACE=(CYL,(5,5)),UNIT=SYSDA
//CON DD DSN=*.T1,VOL=REF=*.T1,DISP=(OLD,PASS)
// DD DSN=*.T2,VOL=REF=*.T2,DISP=(OLD,PASS)
//OUT DD DSN=... output file
//CTL1CNTL DD *
OUTFIL FNAMES=T1,INCLUDE=(16,1,CH,EQ,C'N')
OUTFIL FNAMES=T2,INCLUDE=(16,1,CH,EQ,C'Y')
//CTL2CNTL DD *
OUTFIL FNAMES=OUT,INCLUDE=(16,1,CH,EQ,C'N')
/*
_________________ Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Last edited by Frank Yaeger on Mon Aug 25, 2003 4:28 pm; edited 1 time in total
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
