Hi,
I need to find the closest prior event date ( from have 2) to index date( from have 1). so that the final dataset has ID, index date, and event date, one line per ID
I found some codes with Proc SQL but their codes didn't get me what I need. Thanks for your help.
data have1;
input ID index_dt;
datalines;
15269 01/16/10
18658 07/31/08
23227 06/18/10
data have2;
input ID event_dt;
datalines;
15269 01/15/10
15269 10/10/10
15269 09/02/10
15269 10/12/10
15269 04/28/10
15269 08/07/10
15269 05/13/10
15269 05/31/10
18658 06/13/08
18658 06/14/08
18658 06/19/08
18658 07/05/08
18658 07/14/08
18658 08/05/08
18658 03/02/09
23227 01/28/10
23227 03/30/10
23227 04/17/10
23227 04/18/10
Final dataset should look like;
ID index_dt event_dt
15269 01/16/10 01/15/10
18658 07/31/08 07/14/10
23227 06/18/10 04/14/10
Your test data will not work if ran. Hence this is untested:
proc sql; create table want as select a.*, max(b.event_dt) as event_dt from have1 a left join have2 b on a.id=b.id and b.event_dt <= a.index_dt group by id; quit;
Your test data will not work if ran. Hence this is untested:
proc sql; create table want as select a.*, max(b.event_dt) as event_dt from have1 a left join have2 b on a.id=b.id and b.event_dt <= a.index_dt group by id; quit;
Thanks! it worked
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.