SQL left join , how to how to use min or max in the key between

[bebess]

Dear all, 

any idea about how to resolve the following case : 

in table a i have the variable "Year" taking value from 2016 to 2022  and in table b Year variable takes value from value 2019 to  2020.

in that case , i would like to apply the following resuls:

a.2016=b.2019 ( in table a i would like to take value corresponding to min of year in table b which is 2019 , same case from 2017 to 2019 )

a.2020=b.2020 ( 2020 value from table a exist on table b )

a.2021=b.2020 ( from 2020 to 2021 from table a takes max value from table b which is 2020 )

i've tried the following code , but it doesn't work as expected :

for example that case correspond to the first case a.2016=b.2019 ( in table a i would like to take value corresponding to min of year in table b which is 2019 , same case from 2017 to 2019 )

proc sql;
create table want as
select a.*,b.var_want
from tableA as a
left join tableB as b
on a.YEAR=max(2019,min(b.YEAR,2020))
;
quit;

it gives an empty value for var_want and doesn't take value of 2019 in table b when year=2016 in table a

thanks for your help

[PGStats · Accepted solution]

Should be the other way around:

proc sql;
create table want as
select a.*, b.var_want
from tableA as a
left join tableB as b
on b.YEAR=max(2019, min(a.YEAR, 2020))
;
quit;