DATA Step, Macro, Functions and more

Matching with related dates

Reply
Occasional Contributor
Posts: 10

Matching with related dates

All, I have the following two data sets:

 

have1:

 

IDDate1
6774983128AUG2015:08:49:00.000000
6801112010SEP2015:11:03:00.000000

 

have2: 

IDDate2
6774983126AUG2015:08:49:00.000000
6774983127AUG2015:08:49:00.000000
67749831

29AUG2015:08:49:00.000000

 

I want the following dataset:

 

Want:

IDDate1Date2
6774983128AUG2015:08:49:00.00000027AUG2015:08:49:00.000000
6801112010SEP2015: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!

Super User
Posts: 11,343

Re: Matching with related dates

Pet peeve: Calling datetime values "dates".

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

Occasional Contributor
Posts: 10

Re: Matching with related dates

My apologies. They are both datetime20.

Super User
Posts: 5,518

Re: Matching with related dates

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;

 

Trusted Advisor
Posts: 1,022

Re: Matching with related dates

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.
Ask a Question
Discussion stats
  • 4 replies
  • 163 views
  • 0 likes
  • 4 in conversation