BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
avepo
Fluorite | Level 6

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

Thanks! it worked

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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