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!
Pet peeve: Calling datetime values "dates".
So are your datetime variables SAS datetime values (have a format like datetime20.) or are they character?
My apologies. They are both datetime20.
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;
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:
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.