Matching with related dates

[SThomas]

All, I have the following two data sets:

have1:

ID	Date1
67749831	28AUG2015:08:49:00.000000
68011120	10SEP2015:11:03:00.000000

have2: 

ID	Date2
67749831	26AUG2015:08:49:00.000000
67749831	27AUG2015:08:49:00.000000
67749831	29AUG2015:08:49:00.000000

I want the following dataset:

Want:

ID	Date1	Date2
67749831	28AUG2015:08:49:00.000000	27AUG2015:08:49:00.000000
68011120	10SEP2015:11:03:00.000000

The idea is to merge the two files based on the exact same ID, but I also want the Date2 to be the latest date that is still before Date1. There might be many Instances of a Date2 that come before Date1 (I want the most recent one), one instance, or no instances.

Does anyone have any ideas? 

Thanks!

[ballardw]

Pet peeve: Calling datetime values "dates".

So are your datetime variables SAS datetime values (have a format like datetime20.) or are they character?

[SThomas]

My apologies. They are both datetime20.

[Astounding]

Assuming both data sets are in order by both ID and date:

data want;

set have2 (rename=(date2=date1) in=in2) have1;

by id date1;

if first.id then date2=.;

retain date2;

if in2 then date2=date1;

else output;

run;

[mkeintz]

Assuming both have1 and have2 are sorted by id/date, then this interleaving operation will work:

data want;
  set have2 (in=in2 rename=(date2=date1))
      have1 (in=in1);
  by id date1;

  date2=ifn(in1 and lag(in2) and first.id=0,lag(date1),.);
  if in1;
  format date1 date2 datetime25.6;
run;

Notes:

1.  In the SET statement HAVE2 must precede HAVE1  (for when dates are exactly tied)
2. The IFN function tests whether the record-in-hand is from have1, and is immediately preceded by a HAVE2 record for the same id.  If true then the prior date1 value is really a date2 var that has an earlier date.
3. The IFN ALWAYS updates the lag queues, even if the lagged value is not returned by IFN.