Hello guys,

I have a situation that I don't quite understand, and I haven't found anything by searching the documentation. Sometimes when I do a WRITE and the prefix operator FST, if the FST record is missing, it skips it and goes for the next value that has something in it.

So if my data looked like this:

ID Period CODE
1 3 .
1 2 .
1 1 X

and I did FST.CODE, I would get the X instead of the missing value. Is there some sort of way to override this behavior? I've worked around it by creating DEFINE FILES that look like

CODE1/A1 = IF CODE IS MISSING THEN ' ' ELSE CODE;

and calling that on my WRITE command instead of the actual field, but there seems like there should be a better way to do it. Does anyone else experience this behavior, and have a solution?

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

Sort the data:

BY ID
BY HIGHEST PERIOD

If you did BY HIGHEST 1 PERIOD, wouldn't need FST.

I tried that. I'm doing this in a MATCH (this is key, and probably is the reason none of this is working as expected) I get too many records, even with a BY HIGHEST 1.
This is what the structures of the hold files I'm matching together look like:

(THE TABLE I NEED THE SINGLE HIGHEST RECORD FROM)
ID|PERIOD|CODE
1|3|.
1|2|.
1|1|X

(THE TABLE I'M MATCHING TO)
ID|LASTNAME|FIRSTNAME
1|TEST|BOB



The match logic looks like this:

MATCH FILE HOLD2
WRITE
PERIOD
CODE
BY ID
BY HIGHEST 1 PERIOD
RUN
FILE HOLD1
PRINT *
BY ID
AFTER MATCH HOLD AS HOLD3 NEW
END

When I do a PRINT * to look at the structure it looks like this:

ID|PERIOD|CODE|LASTNAME|FIRSTNAME
1|3|.|TEST|BOB
1|2|.|.|.
1|1|X|.|.

MATCH FILE HOLD2
WRITE
PERIOD
CODE
BY ID
BY HIGHEST 1 PERIOD
RUN
FILE HOLD1
PRINT *
BY ID
AFTER MATCH HOLD AS HOLD3 NEW
END
-RUN
TABLE FILE HOLD3
PRINT *
BY ID
HIGHEST 1 PERIOD
WHERE LASTNAME IS-NOT MISSING;
END
-EXIT

Now if I do this:

MATCH FILE HOLD2
PRINT
PERIOD
CODE
BY ID
BY HIGHEST PERIOD
RUN
FILE HOLD1
PRINT ID
BY ID
AFTER MATCH HOLD AS SORT1 NEW
END

DEFINE FILE SORT1
CODE1/A4 = IF CODE IS MISSING THEN ' ' ELSE CODE;
END

MATCH FILE SORT1
WRITE
FST.CODE1
FST.PERIOD
BY ID
RUN
FILE HOLD1
PRINT *
BY ID
AFTER MATCH HOLD AS HOLD3 NEW
END

Then the output is like this:

ID|PERIOD|CODE|LASTNAME|FIRSTNAME
1|3|.|TEST|BOB

Which is what I want, but it seems like a wonky workaround more than the actual approach I should be taking.

Edit: Let me try that Tom. I'm still having to do an extra hold after my match, but I don't have to create a DEFINE FILE so that is probably a better method.

Double Edit: Alright that works. Now I realize that it isn't the extra hold that is important, it is the WHERE clause where you are excluding missing LASTNAME.

This message has been edited. Last edited by: rray9895,

FYI, After a MATCH, you do usually have to do 1 more HOLD to get to the data you want...

Another option that may work for you is to work out which record to keep.

e.g. [untested]

TABLE FILE {file}
SUM
COMPUTE Cntr/I9 = IF ID NE LAST ID THEN 1 ELSE LAST Cntr + 1 ;
CODE
BY ID 
BY Period
WHERE TOTAL Cntr EQ 1
END