Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 7315

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: 7315

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: 7315

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 sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts JOINKEYS Paired and Unpaired Sort MFwhiz DFSORT/ICETOOL 4 Fri Sep 29, 2017 1:48 am
No new posts File Aid tool to compare numeric data balaji81_k Compuware & Other Tools 2 Tue Sep 26, 2017 3:35 am
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm
No new posts Difference in SORT & ICETOOL Mohan Kothakota DFSORT/ICETOOL 5 Fri Sep 22, 2017 4:56 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us