topic Re: Matching with related dates in SAS Programming

Matching with related dates

SThomas — Wed, 04 Jan 2017 19:02:01 GMT

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!

Re: Matching with related dates

ballardw — Wed, 04 Jan 2017 20:21:13 GMT

Pet peeve: Calling datetime values "dates".

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

Re: Matching with related dates

Astounding — Wed, 04 Jan 2017 20:34:43 GMT

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;

Re: Matching with related dates

SThomas — Wed, 04 Jan 2017 20:39:19 GMT

My apologies. They are both datetime20.

Re: Matching with related dates

mkeintz — Wed, 04 Jan 2017 20:42:25 GMT

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:

In the SET statement HAVE2 must precede HAVE1 (for when dates are exactly tied)
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.
The IFN ALWAYS updates the lag queues, even if the lagged value is not returned by IFN.