Flag the closeset prior date

avepo — Thu, 31 May 2018 12:23:50 GMT

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

Re: Flag the closeset prior date

RW9 — Thu, 31 May 2018 12:37:40 GMT

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;

Re: Flag the closeset prior date

avepo — Thu, 31 May 2018 12:52:38 GMT

Thanks! it worked

topic Re: Flag the closeset prior date in SAS Programming

Flag the closeset prior date

Re: Flag the closeset prior date

Re: Flag the closeset prior date