Flag the closeset prior date

[avepo]

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

[RW9 · Accepted solution]

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;

[avepo]

Thanks! it worked