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
Use this code 
I think that the F2-key should start from the position 5 not 7?
Oh yes, thats correct. changing it. Thanks for pointing out!
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.
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!
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
for that, you should code
IF MATCHED
PUT OUTFILE1 FROM INFILE1
ELSE
PUT OUTFILE2 FROM INFILE1 /* not INFILE2 */
END-IF
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.
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
Hi All,
Out of the above three methods, which method is more efficient when we are dealing with more than 100 millions of records.
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
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.
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
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.