I have 4 date fields. I need to determine the most recent date prior to a 'seed' date and then retrieve the code that corresponds to the date.

For example, I have the following fields:

DATE1 CODE1
DATE2 CODE2
DATE3 CODE3
DATE4 CODE4

Seed date: 20071101

DATE1 20061231 CODE1 AAAAAA
DATE2 20071231 CODE2 BBBBBB
DATE3 blank CODE3 blank
DATE4 20070801 CODE4 DDDDDD

I want the value of CODE4 because the DATE4 (20070801) is the most recent prior date to the seed date (20071101)

Anyone have any ideas how I might code this monstrosity?

Thanks in advance!

This might do it:

TABLE FILE BLAHBLAH
PRINT CODE
BY HIGHEST 1 DATE
WHERE DATE LE '&SEED_DATE'
ON TABLE SAVE
END

Thank you Francis for the rapid reply. I see what your code is doing; however, there are 4 'separate' date fields in the dataset. That is, the dates are not contained in a single field so the HIGHEST syntax would not work.

Unless I am missing something in your example?

I was misled by your example, you showed multiple rows of data.

To determine the maximum value within multiple columns of a single row, try MAX():

MAX_DATE/YYMD = MAX(DATE1, DATE2, DATE3, DATE4);
MAX_CODE/A10  =
  IF DATE1 EQ MAX_DATE THEN CODE1 ELSE
  IF DATE2 EQ MAX_DATE THEN CODE2 ELSE
  IF DATE3 EQ MAX_DATE THEN CODE3 ELSE CODE4;

Hopefully this helps a little.

Nice one Francis. I did not know about the MAX() function. I have now learned my new trick for the day.

  
-SET SEEDDATE = 20071101;

DEFINE FILE ...
MAXDATE1/MDYY = IF DATE1 LE &SEEDDATE AND DATE1 GT LAST MAXDATE1 THEN DATE1 ELSE LAST MAXDATE1;

MAXDATE2/MDYY = IF DATE2 LE &SEEDDATE AND DATE2 GT LAST MAXDATE2 THEN DATE2 ELSE LAST MAXDATE2;

MAXDATE3/MDYY = IF DATE3 LE &SEEDDATE AND DATE3 GT LAST MAXDATE3 THEN DATE3 ELSE LAST MAXDATE3;

MAXDATE4/MDYY = IF DATE4 LE &SEEDDATE AND DATE4 
GT LAST MAXDATE4 THEN DATE4 ELSE LAST MAXDATE4;

MAXDATE0/MDYY = IF MAXDATE1 GT MAXDATE2 AND MAXDATE1 GT MAXDATE3 AND MAXDATE1 GT MAXDATE4 THEN MAXDATE1 
ELSE IF MAXDATE2 GT MAXDATE1 AND MAXDATE2 GT MAXDATE3 AND MAXDATE2 GT MAXDATE4 THEN MAXDATE2 
ELSE IF MAXDATE3 GT MAXDATE1 AND MAXDATE3 GT MAXDATE2 AND MAXDATE3 GT MAXDATE4 THEN MAXDATE3 
ELSE MAXDATE4; 
END

TABLE FILE ....
PRINT MAXDATE0
BY HIGHEST 1 MAXDATE0
END

Tex
Hope this helps or gives you some idea of what to do.

Old dogs - new tricks, huh? We can still learn!

Thanks Francis,

Other than having to change the DATE fields into numerics, it worked like a dream.

I use MAX all of the time; however, I never thought of using it the way you recommended.

I had started coding something similar to TexasStingray's recommendation; however, I did not want to have to explain it to the user or to have to support the code.

I too have learned a new trick.

My thanks to both of you.

Harry

Harry, glad it worked out.

Nothing like having a redefinition of logic after you got something working ...

Rather than MAX, I have to determine the MAX and look for a tie. That is, if two of date fields are the same (for the most recent date prior to the seed date), I have to enter the value of 'checked'.

So, this code will not work:

COMPUTE MAX_CODE/A10 =
IF DATE1 EQ MAX_DATE THEN PREV_U_CODE1 ELSE
IF DATE2 EQ MAX_DATE THEN PREV_U_CODE2 ELSE
IF DATE3 EQ MAX_DATE THEN PREV_U_CODE3 ELSE PREV_U_CODE4;

Is there an easy way to sort the values of the 4 'separate' date fields and then do LAST logic?

Other than hammer and pound, I am not seeing an easy way.

Any ideas?

Thanks

quote:

if two of date fields are the same (for the most recent date prior to the seed date),

- I need a drink!

How about:

COMPUTE T1/I1 = IF DATE1 EQ MAX_DATE THEN 1 ELSE 0;
COMPUTE T2/I1 = IF DATE2 EQ MAX_DATE THEN 1 ELSE 0;
COMPUTE T3/I1 = IF DATE3 EQ MAX_DATE THEN 1 ELSE 0;
COMPUTE T4/I1 = IF DATE4 EQ MAX_DATE THEN 1 ELSE 0;

COMPUTE CODE/A10 = IF (T1 + T2 + T3 + T4) GT 1 THEN 'CHECKED' ELSE '';

Francis,

If this works, I will buy you a drink!

Thanks,

Harry