Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SORT Matching records to one file and nonmatching to other

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
shreyashighosh

New User


Joined: 02 Dec 2005
Posts: 5
Location: India

PostPosted: Tue Dec 13, 2005 3:51 pm    Post subject: SORT Matching records to one file and nonmatching to other
Reply with quote

I have two files.
I need to match the first 20 characters of file1 with that of file2 using SORT and write the records matching in both the files in one o/p file and the non-matching records in another file.
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Dec 13, 2005 9:25 pm    Post subject:
Reply with quote

It's not clear what exactly you want for the output records, so I'll take a guess. Here's a DFSORT/ICETOOL job that shows how to put ALL of the dups (from both files) in one output file and ALL of the nondups (from both files) in another output file. I've used some sample records for input and output to show what it does. If that's not what you want, then show sample records of your own for input and output to indicate what you do want.

Code:

//S1   EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//CON  DD *
33333333333333333333
88888888888888888888
44444444444444444444
11111111111111111111
55555555555555555555
/*
//     DD *
55555555555555555555
22222222222222222222
33333333333333333333
99999999999999999999
/*
//DUPS DD SYSOUT=*
//NODUPS DD SYSOUT=*
//TOOLIN   DD *
SELECT FROM(CON) TO(DUPS) ON(1,20,CH) ALLDUPS DISCARD(NODUPS)
/*


DUPS will have:

Code:

33333333333333333333 F1   
33333333333333333333 F2   
55555555555555555555 F1   
55555555555555555555 F2   


NODUPS will have:

Code:

11111111111111111111 F1 
22222222222222222222 F2 
44444444444444444444 F1 
88888888888888888888 F1 
99999999999999999999 F2 
Back to top
View user's profile Send private message
shreyashighosh

New User


Joined: 02 Dec 2005
Posts: 5
Location: India

PostPosted: Thu Dec 15, 2005 5:12 pm    Post subject:
Reply with quote

This is working. Thanks a lot icon_smile.gif
Back to top
View user's profile Send private message
annujp

New User


Joined: 31 Aug 2005
Posts: 39
Location: St Paul,MN

PostPosted: Fri Nov 03, 2006 6:56 pm    Post subject:
Reply with quote

I have a similar issue.
I need to match the first 15 characters of first file with 15 characters of another file which is from position 20.
How can i specify this in the select function above?
Do i have to use SPLICE command?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Nov 03, 2006 9:58 pm    Post subject:
Reply with quote

It depends on exactly what you need to do.

Please show an example of the records in each of your input files and what you expect for output. If input file1 can have duplicates within it, show that in your example. If input file2 can have duplicates within it, show that in your example. Also, tell me the RECFM and LRECL of each input file.
Back to top
View user's profile Send private message
annujp

New User


Joined: 31 Aug 2005
Posts: 39
Location: St Paul,MN

PostPosted: Mon Nov 06, 2006 12:46 pm    Post subject:
Reply with quote

My first file looks like this
12345123456789012345
----------------------------
11111000000030000001
11111000000030000003
11111000000030000005
11111000000030000005
11111000000030000005
11111000000030000007
11111000000030000008
11111000000030000008
11111000000030000008
11111000000030000009
11111000000030000010

and second file is like this
123456789012345
---------------------
000000030000001
000000030000002
000000030000003
000000030000004
000000030000005
000000030000006
000000030000007
000000030000008
000000030000009
000000030000010

My requirement is
1) Compare the 15 bytes of the first file (starting posn =5) with the 15 bytes of the second file (starting posn =1)
2)records of the first file that have a match in the second file has to be written into a match file. Even the duplicate records should be written into the match file.
3)records of the first file that dont have a match in the second file has to be written into an unmatch file. This includes the duplicate records also.

is this possible in ICETOOL?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Nov 06, 2006 9:45 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/20)
//IN2 DD DSN=...  input file2 (FB/15)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//MATCH DD DSN=...  output file1 (FB/20)
//NOMATCH DD DSN=...  output file2 (FB/20)
//TOOLIN DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(MATCH) ON(6,15,CH) -
  KEEPBASE KEEPNODUPS WITHALL WITH(1,21) USING(CTL3)
/*
//CTL1CNTL DD *
  OUTREC BUILD=(6:1,15,21:C'BB')
/*
//CTL2CNTL DD *
  OUTREC OVERLAY=(21:C'VV')
/*
//CTL3CNTL DD *
  OUTFIL FNAMES=MATCH,INCLUDE=(21,2,CH,EQ,C'VB'),
    BUILD=(1,20)
  OUTFIL FNAMES=NOMATCH,INCLUDE=(21,2,CH,EQ,C'VV'),
    BUILD=(1,20)
/*
Back to top
View user's profile Send private message
rini

New User


Joined: 16 May 2006
Posts: 6

PostPosted: Tue Nov 07, 2006 10:29 am    Post subject:
Reply with quote

Frank,
can you explain me what function does this code actually do?
Code:
SPLICE FROM(T1) TO (MATCH) ON(6,15,CH) -
   KEEPBASE  KEEPNODUPS WITHALL WITH(1,21) USING(CTL3)


And can you explain me
Code:
OUTREC BUILD=(6:1,15,21:C'BB')

what is 'BB" mean?
Back to top
View user's profile Send private message
rajesh_1183

Active User


Joined: 24 Nov 2005
Posts: 121
Location: Tadepalligudem

PostPosted: Tue Nov 07, 2006 11:50 am    Post subject:
Reply with quote

Infact u can do this using SAS also.....

here is the JCL to execute....
Code:

//JOBNAME# JOB (00126,'SAS TST'),'SASTST',
//         CLASS=A,MSGCLASS=Z
//STEP001  EXEC SAS
//DD1 DD DSN=input1.dsn,DISP=SHR
//DD2 DD DSN=INPUT2.DSN,DISP=SHR
//DD3 DD DSN=OUTPUT.MATCH.DSN,DISP=SHR
//DD4 DD DSN=OUTPUT.MISMATCH.DSN,DISP=SHR
//SYSIN  DD *
DATA EXAMPLE1;
     INFILE DD1;
     INPUT @1 CONT_NO $10.
           @11 AGE $2.
     ;
RUN;
DATA EXAMPLE2;
     INFILE DD2;
     INPUT @1 CODE $4.
           @5 CONT_NO $10.
           @15 AGE $2.
      ;
RUN;
DATA EXAMPLE3
     EXAMPLE4;
     LENGTH RECORD $80.;
      MERGE EXAMPLE1(IN=INA)
            EXAMPLE2(IN=INE);
            BY CONT_NO;
 IF INA AND INE THEN DO;
      RECORD=TRIM(CODE)||TRIM(CONT_NO)||TRIM(AGE);
      OUTPUT EXAMPLE3;
 END;
 ELSE DO;
      RECORD=TRIM(CODE)||TRIM(CONT_NO)||TRIM(AGE);
      OUTPUT EXAMPLE4;
 END;
RUN;
FILENAME DD3 'OUTPUT.MATCH.DSN';
DATA _NULL_;
     SET EXAMPLE3;
     FILE DD3;
     PUT @1 RECORD $80.
     ;
RUN;
FILENAME DD4 'OUTPUT.MISMATCH.DSN';
DATA _NULL_;
     SET EXAMPLE4;
     FILE DD4;
     PUT @1 RECORD $80.
     ;
RUN;
/*
//


Thanks,
Rajesh
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Nov 07, 2006 10:26 pm    Post subject:
Reply with quote

rini,

You can find complete information about the SPLICE operator of DFSORT's ICETOOL and all of its parameters at:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/6.13?DT=20060615185603

For more details on matching records with DFSORT, see the "Create files with matching and non-matching records" Smart DFSORT Trick at:

http://www.ibm.com/servers/storage/support/software/sort/mvs/tricks/

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

www.ibm.com/servers/storage/support/software/sort/mvs/srtmpub.html
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am
No new posts SORT to unpack a feild shr_amar DFSORT/ICETOOL 7 Fri Jul 07, 2017 4:32 am
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts How to write Specific Fields from Mul... Padhu SYNCSORT 6 Thu Jul 06, 2017 10:26 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us