Compare two files and include both match and non match records

Question

I need to merge two files into one . Suppose I have 2 input files FILE1 and FILE2. And I need to non-matching records from FILE1 and FILE2 into FILE 3 as well as I want to write matching records also into FILE3.If there is matching based on key in FILE1 and FILE2 then matching record to be written must be picked from FILE1/FILE2 on basis of some condition.

The key position in both the Input Files is same.

Can anybody please help me to write SORTCARD, how Can I get this in single step in SyncSort or DFSort??

Possible duplicate of [Compare two files and write it to "match" and "nomatch" files](http://stackoverflow.com/questions/792432/compare-two-files-and-write-it-to-match-and-nomatch-files) — Bill Woodger, Oct 26 '15 at 23:01
Thanks Bill. My problem was not exactly the same as you mentioned in link but helped me to reach at solution. — bp89, Oct 30 '15 at 19:21

score 0 · Answer 1 · edited Oct 29 '15 at 05:48

0

Try using join keys

SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(1,5,A)
JOINKEYS FILES=F2,FIELDS=(1,5,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,6,F2:1,80)

in reformat fields, you can mention the fields as you want, i.e.., if you want matching records to be picked from file2 then mention those fileds beside F2:

edited Oct 29 '15 at 05:48

hata

11,633
6
46
69

answered Oct 29 '15 at 05:44

shravan kumar

1

And how do you tell there was a match, or a mismatch on F1, or a mismatch on F2? – Bill Woodger Nov 02 '15 at 10:02

score 0 · Accepted Answer · answered Oct 30 '15 at 19:25

0

I got my solution using following sort card:

    JOINKEYS F1=IN1,FIELDS=(1,7,A,13,7,A)                     
    JOINKEYS F2=IN2,FIELDS=(1,7,A,13,7,A)                     
    JOIN UNPAIRED,F1,F2                                       
    REFORMAT FIELDS=(F1:1,239,F2:1,239,?)                     
    OPTION COPY                                               
    OUTFIL FNAMES=OUT1,INCLUDE=(479,1,SS,EQ,C'1,2'),          
    IFTHEN=(WHEN=(479,1,CH,EQ,C'1'),                    
          BUILD=(1,239,479,1)),                       
    IFTHEN=(WHEN=NONE,                                  
          BUILD=(240,239,479,1))                      
    OUTFIL FNAMES=OUT2,INCLUDE=(479,1,SS,EQ,C'B'),            
    IFTHEN=(WHEN=(111,1,FS,EQ,NUM,AND,175,1,FS,EQ,NUM),
           BUILD=(1,239)),                            
    IFTHEN=(WHEN=(350,1,FS,EQ,NUM,AND,414,1,FS,EQ,NUM),
           BUILD=(240,239)),                          
    IFTHEN=(WHEN=NONE,                                 
          BUILD=(1,239))

answered Oct 30 '15 at 19:25

bp89

91
1
13

For OUT2 you can use CH instead of SS, then on OUT1 use SAVE instead of the INCLUDE. On OUT2 you can use IFOUTLEN=239, and in the second IFTHEN combine with the negative of the first IFTHEN, then remove the first and third IFTHENs. – Bill Woodger Nov 02 '15 at 10:06
The titles are almost word-for-word the same. It is the top of the search-engine results when you use your keywords. – Bill Woodger Nov 02 '15 at 10:07
@BillWoodger, I agree the title is almost similar sorry for that. One thing can you explain how I can remove first and third if then using IFOUTLEN=239. I checked the DFSORT manual, IFOUTLEN overrides the OUTREC LRECL for IFTHEN.I think I am already giving length of records in BUILD i think. – bp89 Nov 03 '15 at 07:31
Glad you agree. The point is, if you use IFOUTLEN you can remove two BUILDs. If you have lots of data, you'll notice the difference. If you don't have lots of data, you'll benefit when you do. – Bill Woodger Nov 03 '15 at 08:44

Compare two files and include both match and non match records

2 Answers2