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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.