proc sql join in SAS that is closest to a date

Question

How can I do a one-to-many join between two datasets with proc sql in SAS to obtain the record in Dataset B is closest to a value in Dataset A?

Dataset A

#Patient     #Date of Dose
001                 2020-02-01

Dataset B

# Patient        # Lab Test         #Date of Test     # Value 
001            Test 1           2020-01-17      6
001            Test 1           2020-01-29      10

I want to do the join to select the second record in Dataset B, the record with a "Date of Test" that is the closest (less than or equal to) to the "Date of Dose" in the first dataset.

Answer 1

I want to do the join to select the [..] record in Dataset B [...] with a "Date of Test" that is the closest (less than or equal to) to the "Date of Dose" in the first dataset.

You could use outer appy - if sas supports that:

select a.*, b.*
from a
outer apply(
    select top 1 b.*
    from b 
    where b.patient = a.patient and b.date_of_test <= a.date_of_dose
    order by b.date_of_test desc
) b

Another solution is to join with a not exists condition:

select a.*, b.*
from a
left join b 
    on  b.patient = a.patient
    and b.date_of_test <= a.date_of_dose
    and not exists (
        select 1
        from b b1
        where 
            b1.patient = a.patient
            and b1.date_of_test <= a.date_of_dose
            and b1.date_of_test > b.date_of_test 
    )