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
 

 

Compare and sort.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
shankar_at

New User


Joined: 21 Feb 2013
Posts: 4
Location: USA

PostPosted: Mon Apr 15, 2013 8:17 pm    Post subject: Compare and sort.
Reply with quote

Hi,
I searched some of the topics here, but did not find what I am looking for, so posting my query. I am sorry if there is any post which is same as mine.

I have 2 files and I need to match file 1 field with file 2 field.

File 1.
Code:
0210010013         001840551930201 3330101   1 
0210010013         001840551930201 3220101   2
0210010013         001840551930201 3110101   3
....
....



File 2.

Code:
0210010013         001840551930201 AAAAAA      3330101   1 
0210010013         001840551930201 BBBBBB      3220101   2
0210010013         001840551930201 CCCCCC      3110101   3
....
....


I want to compare 3330101 (column starts 35) from file 1 with 3330101 (column starts from 65) in file 2.
And also I want to compare '1' (at pos 45) from file 1 with '1' from file 2 (pos at 75) again both are at different position.


I have many thousand records in the files, I want to compare both the files and want to write file 1 record without duplicates.
The unmatched records should be written to other file.

My outfile should look like...
Code:

0210010013         001840551930201 3330101   1 
0210010013         001840551930201 3220101   2
0210010013         001840551930201 3110101   3
......
......

I have searched and found below query, I have changed according to my need, but I am not getting like expected.

Code:
JOINKEYS FILES=F1,FIELDS=(35,7,A)                             
JOINKEYS FILES=F2,FIELDS=(65,7,,A)                             
JOIN UNPAIRED                                                 
REFORMAT FIELDS=(F1:1,300,F2:1,300)                           
SORT FIELDS=COPY                                               
OUTFIL FNAMES=MATCH,                                           
   INCLUDE=(1,1,CH,NE,C' ',AND,301,1,CH,NE,C' '),BUILD=(1,300)
OUTFIL FNAMES=UNMATCH,SAVE,                                   
  IFTHEN=(WHEN=(1,1,CH,NE,C' '),BUILD=(01,300)),               
  IFTHEN=(WHEN=(1,1,CH,EQ,C' '),BUILD=(301,300))



Code'd
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Mon Apr 15, 2013 8:37 pm    Post subject: Reply to: Compare and sort.
Reply with quote

Code:
//S1 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DUMMY
//SYSIN   DD   *
  OPTION COPY
/*


Please run this step and post the full output, which will help us determine the SORT level you have at your site.

Please also use the Code tags in your posts to preserve spacing.
Back to top
View user's profile Send private message
shankar_at

New User


Joined: 21 Feb 2013
Posts: 4
Location: USA

PostPosted: Mon Apr 15, 2013 9:01 pm    Post subject: Re: Reply to: Compare and sort.
Reply with quote

Bill Woodger wrote:
Code:
//S1 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//SORTIN DD *
RECORD
/*
//SORTOUT DD DUMMY
//SYSIN   DD   *
  OPTION COPY
/*


Please run this step and post the full output, which will help us determine the SORT level you have at your site.

Please also use the Code tags in your posts to preserve spacing.


Please see below,
Code:
SYNCSORT FOR Z/OS  1.4.0.1R    U.S. PATENTS: 4210961, 5117495   (C) 2010 SYNCSOR
                                                      z/OS   1.12.0             
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 671A7, MODEL 2827 707             LICENS
SYSIN :                                                                         
  OPTION COPY                                                                   
WER276B  SYSDIAG= 3055684, 4564455, 4564455, 1923675                           
WER164B  9,916K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,             
WER164B     64K BYTES RESERVE REQUESTED, 332K BYTES USED                       
WER146B  4K BYTES OF EMERGENCY SPACE ALLOCATED                                 
WER108I  SORTIN   : RECFM=FB   ; LRECL=    80; BLKSIZE=    80                   
WER073I  SORTIN   : DSNAME=LIDO7PP.TTSTSORT.J0320504.D0000101.?                 
WER110I  SORTOUT  : RECFM=FB   ; LRECL=    80; BLKSIZE=    80                   
WER074I  SORTOUT  : DSNAME=NULLFILE                                             
WER410B  7,864K BYTES OF VIRTUAL STORAGE AVAILABLE ABOVE THE 16MEG LINE,       
WER410B     0 BYTES RESERVE REQUESTED, 216K BYTES USED                         
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                                   
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                                   
WER416B  BSAM WAS USED FOR SORTIN                                               
WER416B  BSAM WAS USED FOR SORTOUT                                             
WER054I  RCD IN          3, OUT          3                                     
WER169I  RELEASE 1.4 BATCH 0518 TPF LEVEL 0.1                                   
WER052I  END SYNCSORT - TTSTSORT,STEP0100,,DIAG=E000,6208,C8A8,00C4,A856,4C83,8A
 ********************************** End of Data ********************************
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Mon Apr 15, 2013 9:11 pm    Post subject: Reply to: Compare and sort.
Reply with quote

The WER messages (and the words SyncSort) indicate you have SyncSort. Moved to JCL, where SyncSort questions live.

So, you should be getting the matched and unmatched records. What is your actual problem, what output do you get, compared to what you want?

You mention duplicates. You want all duplicates gone? Just those that are matched? Where do you want the duplicates, anywhere, or evaporated?
Back to top
View user's profile Send private message
shankar_at

New User


Joined: 21 Feb 2013
Posts: 4
Location: USA

PostPosted: Mon Apr 15, 2013 9:49 pm    Post subject: Re: Reply to: Compare and sort.
Reply with quote

Bill Woodger wrote:
The WER messages (and the words SyncSort) indicate you have SyncSort. Moved to JCL, where SyncSort questions live.

So, you should be getting the matched and unmatched records. What is your actual problem, what output do you get, compared to what you want?

You mention duplicates. You want all duplicates gone? Just those that are matched? Where do you want the duplicates, anywhere, or evaporated?


Hi,
I am not getting unmatched records in the unmatched file (it is empty), and also even if there is only one matching record, it is writing same record multiple times in matching output file.
I want duplicates to be evaporated.

If there is one record matched I want file 1 record to be written only once in the matched file.
Back to top
View user's profile Send private message
shankar_at

New User


Joined: 21 Feb 2013
Posts: 4
Location: USA

PostPosted: Mon Apr 15, 2013 11:49 pm    Post subject: Re: Reply to: Compare and sort.
Reply with quote

shankar_at wrote:
Bill Woodger wrote:
The WER messages (and the words SyncSort) indicate you have SyncSort. Moved to JCL, where SyncSort questions live.

So, you should be getting the matched and unmatched records. What is your actual problem, what output do you get, compared to what you want?

You mention duplicates. You want all duplicates gone? Just those that are matched? Where do you want the duplicates, anywhere, or evaporated?


Hi,
I am not getting unmatched records in the unmatched file (it is empty), and also even if there is only one matching record, it is writing same record multiple times in matching output file.
I want duplicates to be evaporated.

If there is one record matched I want file 1 record to be written only once in the matched file.


Hello, there were spaces at the same position at the end of file, which lead me to bring back some unexpected results. I have stripped the file for my testing and ran the same sort option and went fine..
thanks for the quick responses and trying to help me..
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7241

PostPosted: Mon Apr 15, 2013 11:59 pm    Post subject: Reply to: Compare and sort.
Reply with quote

Thanks for letting us know.
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm


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