SAS (PROC SQL) Merge/Join to dates prior and only keeping the nearest entry

Question

Suppose I have the following two datasets in SAS:

Have_1 - contains monthly data per ID:

ID   Month 
1     1
1     2
1     3
1     4
1     5
1     6
1     7
1     8
1     9
1     10

Have_2 - A list of months where a certain event occurs:

ID   Month_Event 
1     4
1     9

I am trying to merge the Month_Event from Have_2 onto Have_1 in a way where the Month_Event will be populated for all entries of ID where Month is less than or equal to Month_Event, i.e.

Want:

ID   Month   Month_Event
1     1      4
1     2      4
1     3      4
1     4      4
1     5      9
1     6      9
1     7      9
1     8      9
1     9      9
1     10     .

I've tried the following:

proc sql;
create table want as
select a.*, b.Month_Event
from have_1 as a
left join have_2 as b
on a.ID = b.ID and a.Month <= b.Month_Event;
quit;

This almost gives the required output, with the only issue that the output does not only provide the Month_Event for the nearest, instead this provides:

ID   Month   Month_Event
1     1      4
1     1      9
1     2      4
1     2      9
1     3      4
1     3      9
1     4      4
1     4      9
1     5      9
1     6      9
1     7      9
1     8      9
1     9      9
1     10     .

How can I adjust my code accordingly to give the required output?

Answer 1

Take the minimum.

First let's convert your listings into actual SAS datasets.

data have;
  input ID   Month ;
cards;
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
;
data have2;
  input ID   Month_Event ;
cards;
1 4
1 9
;

Now join the two and take the minimum month_event that qualifies

proc sql;
create table want as
  select a.id,a.month,min(b.month_event) as month_event
  from have a left join have2 b
    on a.id = b.id 
      and a.month <= b.month_event
  group by a.id, a.month
;
quit;

Result

                      month_
Obs    ID    Month     event

  1     1       1        4
  2     1       2        4
  3     1       3        4
  4     1       4        4
  5     1       5        9
  6     1       6        9
  7     1       7        9
  8     1       8        9
  9     1       9        9
 10     1      10        .