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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.