01-04-2017 02:02 PM
All, I have the following two data sets:
I want the following dataset:
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?
01-04-2017 03:34 PM
Assuming both data sets are in order by both ID and date:
set have2 (rename=(date2=date1) in=in2) have1;
by id date1;
if first.id then date2=.;
if in2 then date2=date1;
01-04-2017 03:42 PM
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;