I have two data files (File A and File B) and I need to determine which records that were in File A have been changed in File B. By changed, I mean the values in the sort field have been changed.

Will a Match command allow me to do this?

Lee,

The short answer is: Almost certainly it can.

The long answer - the how - requires a little more information. Is B a mirror of A, in the sense that both files have the same fields? Will they always have the same number of records or are you trying to detect addtions and deletions also? Are you looking for any change to any field? Are there 'key' fields that uniquely identify each record? How many fields are there?

It would help to see the master file descriptions for both files.

Are you only wanting the records that are in A and B? If you are why don't you just join the two files?

DWF,
Yes, A and B are mirrors of one another with exactly the same field. They are basically weekly data dumps from our HRIS system. They should not have the same number of records as each week records for new hire employees are added and records for terminated employees are moved to a file for inactives. Ultimately, I would like to also identify additions and deletions. But in the short term, my goal is to identify changes in any field. This may not be possible as the file currently contains over 400 fields. If necessary, I can limit it down to a number of key fields.

I would have thought that coming from an HR system that one of the columns would be a date/timestamp relating to last update. If that is the case all you need is a
SET ALL=PASS
match old with new
HOLD OLD-OR-NEW
then TABLE with
where new timestamp ne old timestamp

Lee,

I'm with Curtis on this one. Assuming two key fields (A and B) you might try something like this:

JOIN A AND B IN FILE1 TO A AND B IN FILE2

(They have to be sorted the same, of course)

DEFINE FILE FILE1
COUNTER/I5 = 0;
COUNTER/I5 = IF A.FIELD1 EQ B.FIELD1 THEN COUNTER ELSE COUNTER + 1;
COUNTER/I5 = IF A.FIELD2 EQ B.FIELD2 THEN COUNTER ELSE COUNTER + 1;
COUNTER/I5 = IF A.FIELD3 EQ B.FIELD3 THEN COUNTER ELSE COUNTER + 1;
.
.
.
COUNTER/I5 = IF A.FIELD400 EQ B.FIELD400 THEN COUNTER ELSE COUNTER + 1;
END

TABLE FILE FILE1
PRINT whatever you want to see
WHERE COUNTER GT 0
END


Frankly, I've never tried redefining the same field 400 times. Makes me a little nervous. You could always create 400 counnters:

COUNTER1/I5 = IF A.FIELD1 EQ B.FIELD1 THEN 0 ELSE 1;
COUNTER2/I5 = IF A.FIELD1 EQ B.FIELD1 THEN 0 ELSE 1;
etc

then:

COUNTER/I% = COUNTER1 + COUNTER2 + ... + COUNTER400;

except that you'd have to break that up, because I serious doubt you can have a define that long.

If this doesn't get you all the way there, it might at least get you close enough to see where you need to be. If JOIN doesn't work and you still need help with MATCH, just say so.

Obviously, that last compute should have been:

COUNTER/I5 = COUNTER1 + COUNTER2 + ... + COUNTER400;

quote:

DEFINE FILE FILE1
COUNTER/I5 = 0;
COUNTER/I5 = IF A.FIELD1 EQ B.FIELD1 THEN COUNTER ELSE COUNTER + 1;
COUNTER/I5 = IF A.FIELD2 EQ B.FIELD2 THEN COUNTER ELSE COUNTER + 1;
. . .
COUNTER/I5 = IF A.FIELD400 EQ B.FIELD400 THEN COUNTER ELSE COUNTER + 1;
END

Alternatively:


COUNTER/I5=
(A.FIELD1 NE B.FIELD1)
+ (A.FIELD2 NE B.FIELD2)
+ ...
+ (A.FIELD400 NE B.FIELD400)
;

This message has been edited. Last edited by: <Mabel>,

Very slick solution, Jack. I like it.