DATA Step, Macro, Functions and more

Flag the closeset prior date

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Flag the closeset prior date

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

 

 


Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 9,416

Re: Flag the closeset prior date

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;

View solution in original post


All Replies
Solution
3 weeks ago
Super User
Super User
Posts: 9,416

Re: Flag the closeset prior date

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;
Occasional Contributor
Posts: 9

Re: Flag the closeset prior date

Thanks! it worked

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 58 views
  • 0 likes
  • 2 in conversation