I'm not a FOCUS pro, but I thought this would be fairly simple; however, I just can't seem to hold my mouth right.

Goal: define a field that is equal to the most recent (i.e. closest to today) date field in the table.

Say I have 5 date fields (in my real data I have 50) that are USAGE=MDYY, ACTUAL=DATE:

DATE_1
DATE_2
DATE_3
DATE_4
DATE_5
 

These fields are always sequential: DATE_1 < DATE_2 < DATE_3... Furthermore, it's possible that the last N dates are null [e.g. Dates 1-3 have data, while 4 and 5 are null).

So, starting with the last date field (5 in this case, 50 in my real date) and working backward, I want my DEFINE field to equal the first one that is prior to today.

Here's what I have right now that isn't working (I get an "IF...ELSE...THEN syntax error.")

 DEFINE FILE MY_GREAT_TABLE
	MOST_RECENT_DATE/MDYY WITH DATE_1 = IF MDY(&MDYY, DATE_5) < 0 THEN DATE_5 ELSE 
		IF MDY(&MDYY, DATE_4) < 0 THEN DATE_4 ELSE
		IF MDY(&MDYY, DATE_3) < 0 THEN DATE_3 ELSE
		IF MDY(&MDYY, DATE_2) < 0 THEN DATE_2 ELSE
		IF MDY(&MDYY, DATE_1) < 0 THEN DATE_1 ELSE
		MISSING;
END 

I'm not sure if I can use &MDYY in the function, or if the MDY() function is even the right one to use, and I'm also not sure what I can set in the final ELSE to make the DEFINE null.

This message has been edited. Last edited by: J.Hines,

As a first step, try the following.
To be able to assign a Null (MISSING) value to a DEFINEd field you must first define the field to be "nullable"
Second you must use "LT" and not "<"

DEFINE FILE MY_GREAT_TABLE
 MOST_RECENT_DATE/MDYY WITH DATE_1 MISSING ON = IF MDY(&MDYY, DATE_5) LT 0 THEN DATE_5 ELSE 
		IF MDY(&MDYY, DATE_4) LT 0 THEN DATE_4 ELSE
		IF MDY(&MDYY, DATE_3) LT 0 THEN DATE_3 ELSE
		IF MDY(&MDYY, DATE_2) LT 0 THEN DATE_2 ELSE
		IF MDY(&MDYY, DATE_1) LT 0 THEN DATE_1 ELSE
		MISSING;
END

I'm sure you have a very good reason for this DEFINE. However, depending on the DBMS you're reading and the number of rows in your database, your report may take a lot longer than it should. There's always a temptation to fix data issues with a report. When I run into situations like this, I try to see if there's a better way of deconstructing the data so the users don't have to suffer the wait time. This might be a good case for creating a batch job that creates an extract of the data you'd like using HOLD syntax. If you're talking a couple of hundred rows no big deal, but if the data is going to grow and in a year you're looking at Millions of rows, consider caching the result so your reports don't get blamed for inefficiencies in the data.

BabakNYC: appreciate the concern, but this DEFINE is within a report that has certain WHERE conditions that, combined with the nature of the data, will result in a couple hundred rows max, and this DEFINE is only applicable for that query, not the table as a whole.

Martin:
Thanks, that at least gets the report running, but I can't get it to return anything other than zero.

In part, I was making it harder than it needed to be trying to convert and use date diff functions, because both my table date and my current date field are Smart Dates, so I can just use an arithmetic operator, e.g.:

CURR_DATE/MDYY = &MDYY;
MY_DT/MDYY = IF CURR_DATE - DATE_5 GT 0 THEN VEST_DATE_5 ELSE VEST_DATE_1;

However, the problem is that if DATE_5 is blank/null, then it all fails silently and the field gets set to MISSING. I'm guessing this is due to the whole IF statement exiting if one of the dates is null, when It would be better if it just returned 0, or moved on because NULL/MISSING/etc doesn't satisfy the condition "GT 0".

And apparently I can't just do

IF (DATE_5 IS NOT MISSING AND CURR_DATE - DATE_5 GT 0)

because FOCUS doesn't seem to have shortcut exits, and will still evaluate the equality even if DATE_5 is missing!

This message has been edited. Last edited by: J.Hines,

Okay, think I finally solved it. The important part, in addition to checking for missing, was to have
NEEDS ALL so that the expression would continue.

Here then is my final code (now it just remains to be seen if this still works when I take all 50 date fields into consideration):

DEFINE FILE T_SPS_GRANT_LOAD
  CURR_DATE/MDYY = &MDYY;
  MOST_RECENT_DATE/MDYY WITH DATE_1 MISSING ON NEEDS ALL = 
    IF (DATE_5 NE MISSING) AND (CURR_DATE - DATE_5) GT 0 THEN DATE_5 ELSE 
    IF (DATE_4 NE MISSING) AND (CURR_DATE - DATE_4) GT 0 THEN DATE_4 ELSE
    IF (DATE_3 NE MISSING) AND (CURR_DATE - DATE_3) GT 0 THEN DATE_3 ELSE
    IF (DATE_2 NE MISSING) AND (CURR_DATE - DATE_2) GT 0 THEN DATE_2 ELSE
    IF (DATE_1 NE MISSING) AND (CURR_DATE - DATE_1) GT 0 THEN DATE_1 ELSE
    MISSING;
END

Maybe use a -REPEAT loop? This sample code should carry the latest date forward to the last xdate field.

DEFINE FILE T_SPS_GRANT_LOAD
-REPEAT ENDREPEAT1 FOR &J FROM 1 TO 50
-SET &I = &J - 1 ;
 XDATE_&J/MDYY = IF (&J = 1) THEN DATE_1 ELSE
                 IF (DATE_&J NE MISSING) THEN DATE_&J ELSE XDATE_&I ;
-ENDREPEAT1
-*
 MOST_RECENT_DATE/MDYY = XDATE_50 ;
END

@Dan:
That's very interesting! I had no idea something like that was even possible. I'll give that a whirl too. However, I still have to check whether DATE_&J is before the current date.

EDIT:
I got it working, just had to tweak it a bit and add XDATE_0 as a define, because apparently even though XDATE_&I is in the first ELSE, it still runs that part of the code, and was complaining about it being undefined, and therefore each subsequent loop the XDATE_&I was also undefined.
So here's the code:

-REPEAT ENDREPEAT1 FOR &J FROM 1 TO 50
-SET &I = &J - 1 ;
 XDATE_0/MDYY MISSING ON = MISSING;
 XDATE_&J/MDYY = IF (&J = 1) THEN DATE_1 ELSE
                 IF (DATE_&J NE MISSING AND CURR_DATE - DATE_&J GT 0) THEN DATE_&J ELSE XDATE_&I ;
-ENDREPEAT1

This message has been edited. Last edited by: J.Hines,

Ah yes....XDATE_0 would need to be previously defined despite the fact that it will never be used. However, by placing the DEFINE for XDATE_0 inside the loop it is being executed 50 times when once is sufficient. I would place the DEFINE for XDATE_0 before the -REPEAT loop.

Jess,
I think that your loop should be reversed. Quoting you:
"DATE_1 < DATE_2 < DATE_3"
I suggest:

  
CURR_DATE/MDYY='&DATEMDYY';
MOST_RECENT_DATE/MDYY MISSING ON=
-REPEAT #LDATE FOR &I FROM 50 TO 2 STEP -1;
IF (DATE_&I NE MISSING) AND (CURR_DATE LT DATE_&I) THEN DATE_&I ELSE
-#LDATE
DATE_1;