MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Unique records.

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
krk123
Beginner


Joined: 03 Jun 2003
Posts: 58
Topics: 19

PostPosted: Mon Aug 25, 2003 10:12 am    Post subject: Unique records. Reply with quote

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?

Regards,
krk123.
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose

PostPosted: Mon Aug 25, 2003 10:32 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
krk123
Beginner


Joined: 03 Jun 2003
Posts: 58
Topics: 19

PostPosted: Mon Aug 25, 2003 10:40 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
krk123
Beginner


Joined: 03 Jun 2003
Posts: 58
Topics: 19

PostPosted: Mon Aug 25, 2003 10:49 am    Post subject: Reply with quote

Hi Frank,
Here is how the data looks.

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

In the output file I need something like this.

11226677 ALPHA N

regards,
krk123
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose

PostPosted: Mon Aug 25, 2003 11:39 am    Post subject: Reply with quote

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
/*


The output from this job is
Code:

11226677 ALPHA N


Hope this helps....

cheers

kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
krk123
Beginner


Joined: 03 Jun 2003
Posts: 58
Topics: 19

PostPosted: Mon Aug 25, 2003 12:00 pm    Post subject: Reply with quote

Thanks Kolusu. We dont have ICETOOL at our shop. I will try this logic with SYNCSORT and will let you know how it worked.
Thanks a lot for your time.

Regards,
KRK123
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose

PostPosted: Mon Aug 25, 2003 12:01 pm    Post subject: Reply with quote

krk123,


change the pgm name SYNCTOOL and see if it works.

Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose

PostPosted: Mon Aug 25, 2003 12:29 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website
krk123
Beginner


Joined: 03 Jun 2003
Posts: 58
Topics: 19

PostPosted: Mon Aug 25, 2003 1:19 pm    Post subject: Reply with quote

Hi Kolusu and Frank,
I used SYNCSORT with the logic given by kolusu and it worked exactly the way I wanted.

Kolusu and Frank thanks a lot for your time..

Regards,
KRK123.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
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


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group