Loading

Easytrieve File matching : Synchronized Files

File1

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
A1
B1
D1

File2

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
XXX A1 YYYY ZZZZ
CCC A1 DDDDDDDDD
DDD A2 UUUUUUUUU
RRR B1 TTTTTTTTT
XXX B1 YYYY ZZZZ
CCC C1 DDDDDDDDD
DDD C2 UUUUUUUUU
RRR D1 TTTTTTTTT

Output File Should have only the records from file2, with second column values which are present in File1.
File1 has unique records
File2 should be in sorted order (Second column)
Ie,
OUTFILE

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
XXX A1 YYYY ZZZZ
CCC A1 DDDDDDDDD
RRR B1 TTTTTTTTT
XXX B1 YYYY ZZZZ
RRR D1 TTTTTTTTT

This Can be achieved in two ways. The first way is,

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
 LIST ON
 FILE FILE1
   F1-REC 1 50 A
   F1-KEY 1 2 A
 FILE FILE2
   F2-REC 1 50 A
   F2-KEY 5 2 A
 FILE OUTFILE
     OUT-REC 1 50 A
 JOB INPUT NULL
 GET FILE1
 GET FILE2
 DO WHILE NOT EOF FILE1 AND NOT EOF FILE2
   DO WHILE F2-KEY LE F1-KEY
      IF F1-KEY EQ F2-KEY
         PUT OUTFILE FROM FILE2
      END-IF
      GET FILE2
      IF EOF FILE2
         STOP
      END-IF
   END-DO
   GET FILE1
 END-DO
 STOP.

and the second way is,
Using JOB INPUT (FILENAME KEY(KEYNAME) FILENAME KEY(KEYNAME))

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
 LIST ON
 FILE FILE1
          F1-REC           1    50   A
          F1-KEY           1     2   A
 FILE FILE2
          F2-REC           1    50   A
          F2-KEY           5     2   A
 FILE OUTFILE
 OUT-REC     1  50 A
 JOB INPUT (FILE1 KEY(F1-KEY)  FILE2 KEY(F2-KEY))
 IF MATCHED
     PUT OUTFILE FROM FILE2
 END-IF
 GO TO JOB

Matching the files in another way

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
 LIST ON
 FILE FILE1
          F1-REC           1    50   A
          F1-KEY           1     2   A
 FILE FILE2
          F2-REC           1    50   A
          F2-KEY           5     2   A
 FILE OUTFILE
 OUT-REC     1  50 A
 JOB INPUT (FILE1 KEY(F1-KEY)  FILE2 KEY(F2-KEY))
 IF FILE1
   IF FILE2
     PUT OUTFILE FROM FILE2
  END-IF
 END-IF
 GO TO JOB

Matching Multiple keys
(FILENAME KEY(KEY1,KEY2) FILENAME KEY(KEY1,KEY2))

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
 LIST ON
 FILE FILE1
          F1-REC           1    50   A
          F1-KEY1          1     2   A
          F1-KEY2          3     2   A
 FILE FILE2
          F2-REC           1    50   A
          F2-KEY1           5     2   A
          F2-KEY2           9     2   A
 FILE OUTFILE
 OUT-REC     1  50 A
 JOB INPUT (FILE1 KEY(F1-KEY1F1-KEY2)  +
 FILE2 KEY(F2-KEY1,F2-KEY2))
 IF FILE1
   IF FILE2
     PUT OUTFILE FROM FILE2
  END-IF
 END-IF
 GO TO JOB

Related Posts




blog comments powered by Disqus

There's 13 Comments So Far

  •   Olli Johansson
    January 27th, 2012 at 6:48 pm

    I think that the F2-key should start from the position 5 not 7?

  •   Karthik
    January 28th, 2012 at 5:09 am

    Oh yes, thats correct. changing it. Thanks for pointing out!

  •   Sushmitha
    May 17th, 2012 at 2:40 pm

    hi.
    I ve been using this matched command for storing the matched and unmatched data into two different output files. the code is as follows

    JOB INPUT (INFILE1 KEY(INFILE-REC1) INFILE2 KEY(INFILE-REC2))
    IF MATCHED
    PUT OUTFILE1 FROM INFILE1
    END-IF
    IF NOT MATCHED
    IF INFILE1
    PUT OUTFILE2 FROM INFILE2
    END-IF
    END-IF

    could you tell me what is the use of the statement IF INFILE1 in the above set.

  •   Karthik
    May 17th, 2012 at 4:08 pm

    Hi Sushmitha,

    IF NOT MATCHED and IF INFILE1 together mean that, the key was present in INFILE1, but no matching key was present in INFILE2.

    To filter out such records, you must use
    IF NOT MATCHED
    IF INFILE1
    PUT OUTFILE FROM INFILE1
    END-IF
    END-IF

    Hope that helps!

  •   Sushmitha
    May 18th, 2012 at 5:13 am

    hi Karthik

    Thank you for that reply, it was helpfull.
    Just wanted to know why we are using that command IF INFILE1 within the next IF loop.

    When i tried a code without giving that statement, was getting wrong data into the OUTFILE2.

    IF MATCHED
    PUT OUTFILE1 FROM INFILE1
    ELSE
    PUT OUTFILE2 FROM INFILE2
    END-IF

    For example.

    INFILE1 has the following data:
    1
    2
    3
    4
    5

    INFILE2 has:
    2
    3
    4

    my output is as follows:
    OUTFILE1
    2
    3
    4

    OUTFILE2
    2
    4

    where as it should have been

    1
    3
    in the OUTFILE2

  •   Karthik
    May 18th, 2012 at 5:44 am

    for that, you should code

    IF MATCHED
    PUT OUTFILE1 FROM INFILE1
    ELSE
    PUT OUTFILE2 FROM INFILE1 /* not INFILE2 */
    END-IF

  •   Sushmitha
    May 18th, 2012 at 5:58 am

    Thank you for that.

    ya i did code as INFILE1 earlier but my PL wanted to try different angles of the code and so asked me to try out with INFILE2.

    sorry dint mention that earlier i think.

  •   Sathish
    August 31st, 2012 at 7:25 am

    In SAS, first it will read the entire records from the files and it will do its process. How are you reading the records in easytrieve for matching?. Also can u pls explain me the logic behind the matching with an example.

    Thanks
    Sathish

  •   Devaru
    February 12th, 2013 at 5:20 am

    Hi All,
    Out of the above three methods, which method is more efficient when we are dealing with more than 100 millions of records.

  •   suresh
    March 8th, 2013 at 10:29 pm

    Hi karthik,

    I have question ,I am using compare and matched logic in my program
    FILE A & FILEB

    here FILEA is huge , my question is i am comparing file a and file b ,Here the sutiation is in a fileA when ever matched records the first occurs of the matched record is either zero/bank in the filed so i should avoid that first record and countine the process to read the next immediate record from the FILEA, in easytrieve how to that reda the FILEA logic
    but,the pointer reads in fileb should be stand in the same pointer it should not read next record…

    thanks
    kesh

  •   nikhil
    August 31st, 2013 at 9:15 pm

    i want to compare two files using the key and want to write matched records in ouput file and also want to delete to matched record from one of the the input file and write to another ouput file.

    i have to code this in ezytrive. Please let me know how can i code this.

  •   Joel
    September 3rd, 2013 at 3:00 am

    is it ok to declare an elementary items declared as packed under a group item declared as Alphanumeric? like this one

    FILE JBGPRJ VS CREATE
    PROJ-KEY 1 10 N
    PROJ-NUMBER PROJ-KEY 5 N
    TASK-NUMBER PROJ-KEY +5 5 N
    PROJ-DATA 11 45 A
    PD-NAME PROJ-DATA 40 A
    PD-SHORT-NAME PROJ-DATA +40 5 A
    TASK-DATA 11 45 A
    TD-NAME TASK-DATA 20 A
    TD-RES-NAME TASK-DATA +20 10 A
    TD-EST-START TASK-DATA +30 3 P
    TD-EST-END TASK-DATA +33 3 P
    TD-ACT-START TASK-DATA +36 3 P
    TD-ACT-END TASK-DATA +39 3 P
    TD-PERCENT TASK-DATA +42 3 N

  •   Al Deleon
    August 12th, 2014 at 7:08 pm

    What code would I use if the files do not have a key and they are delimited by ‘~’. I want the records that do not match.

Share your thoughts, leave a comment!