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

bebess — Sat, 19 Sep 2020 20:28:08 GMT

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

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

PGStats — Sat, 19 Sep 2020 20:47:24 GMT

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;

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

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

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