I'm using the LST function to try to retrieve the last time a document was received, not received, etc. The rec'd time field is in HYYMDS format. Everything is sorted so that I can get the last time. However, in some cases, the last rec'd time is blank so it gives me the previous time (that is actually not missing). I understand that, but still want to show the last entry, even if missing because that indicates the document was not received. I've tried using SET NODATA=* hoping this would give me the last record, but it doesn't work. I've also tried a define to place a 0 format time if the time is missing, but I receive an invalid format specification error. How can I retrieve the last entry for a HYYMDS field if there is no actual time indicated?

DOC_RECV_TIME_NEW/HYYMDS MISSING ON=IF DOC_RECV_TIME EQ MISSING THEN ?? ELSE DOC_RECV_TIME;

?? - I'm not sure how to specifiy the time format so that it would appear as 0's.

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

I am not able to test this, but I think if you simply define a field with missing off, the missing dates will be blank and you should then get the last value, whether it's blank or not.

DOC_RECV_TIME_NEW/HYYMDS MISSING OFF = DOC_RECV_TIME;

If you are dealing with a relational database, the LST prefix gets translated to MAX in SQL. You may need to HOLD the data before applying the LST prefix.

Just use DT

DEFINE FILE CAR
DATETIME/HYYMDs = DT(0000/00/00 00:00:00.000) ;
END
TABLE FILE CAR
 PRINT DATETIME
BY COUNTRY
END

.LST with Oracle (at least in 7.1.3) turns off optimization, which in effect turns a relational source into a hold file, then the .LST operation is done on the hold file. So, if there are 2 or more records that satisfy WHERE/IF statements, .LST will return the last not missing/null value. The only way I have found to reliably and efficiently get around this is to PRINT the data without and save in a hold file, using a COMPUTE to change the field(s) to not null fields, the use TABLEF with SUM and the .LST function. For example for find the last foster care placement for child 123456:

TABLEF FILE KDSPLCMT
 PRINT PL_NDATE PL_XDATE NOPRINT
       COMPUTE WK_XDATE/YYMD=PL_XDATE;
    BY PL_CLID
    BY PL_NDTTM
 WHERE PL_CLID EQ 123456
    ON TABLE HOLD
END
-*
TABLEF FILE HOLD
   SUM LST.PL_NDATE LST.WK_XDATE
    BY PL_CLID
    ON TABLE PCHOLD FORMAT WP
END

PL_XDATE is MISSING/NULL if the placement is still open. If I don't do it this way and the child has had multiple placements in foster care, LST.PL_XDATE will have the exit date of the prior placement not the last placement.