How to extract visit using date of incident?

[shrutisd0]

I am trying to create a new variable called "stroke visit" using the information below. I have the date of stroke and visit numbers available. The stroke visit would be closest to the date of stroke (preferably before the latest visit) e.g. for ID 2 the visitnum should be "3".

id stroke dateofstrk   visitdate   visitnum

1    0               .          11/1/2022     1

1    0               .          12/1/2022     2

1    0               .          1/1/2023       3

2    1     12/25/2022  11/1/2022      1

2    1     12/25/2022  12/1/2022      2

2    1     12/25/2022  1/1/2023        3

3    1     11/13/2022  11/1/2022      1

3    1     11/13/2022  12/1/2022      2

3    1     11/13/2022  11/1/2023      3

[PGStats · Accepted solution]

The date of the first visit occuring at or after the date of stroke can be obtained as:

data have;
input id stroke (dateOfStrk visitDate) (:mmddyy.) visitNum;
format dateofStrk visitDate yymmdd10.;
datalines;
1    0               .          11/1/2022     1
1    0               .          12/1/2022     2
1    0               .          1/1/2023       3
2    1     12/25/2022  11/1/2022      1
2    1     12/25/2022  12/1/2022      2
2    1     12/25/2022  1/1/2023        3
3    1     11/13/2022  11/1/2022      1
3    1     11/13/2022  12/1/2022      2
3    1     11/13/2022  11/1/2023      3
;

proc sql;
/* create table want as */
select 
    a.*, b.strkVisit format=yymmdd10.
from 
    have as a left join
    (select id, min(visitDate) as strkVisit 
    from have where stroke and visitDate>=dateOfStrk 
    group by id) as b
    on a.id=b.id
    order by id, visitNum;
quit;