topic PROC sql: how to join to nearest year in SAS Programming

PROC sql: how to join to nearest year

mrdlau — Fri, 04 May 2018 21:02:52 GMT

I'm trying to left join table2 to table 1, but it needs to join by the closest match, instead of exact.

For example,

Table 1 has a column year with values 1990, 2000, 2010, 2020.

Table 2 has column year with values 1991, 2002,2015,2030.

1990 should join with 1991, 2000 to 2002, 2010 to 2015 and 2020 to 2030.

Table 1 should match the years in table 2 that is closest and not higher than Table1.

how would I do this?

Re: PROC sql: how to join to nearest year

PGStats — Sat, 05 May 2018 03:32:18 GMT

This might work for you:

data a;
do y = 1990, 2000, 2010, 2020, 2025, 2040; output; end;
run;

data b;
do y = 1991, 2002, 2015, 2030; output; end;
run;

proc sql;
select 
    a.y, 
    b.y as closest_y
from
    a left join
    b on b.y >= a.y
group by a.y
having min(b.y) = b.y;
quit;

                                       y  closest_y
                                -------------------
                                    1990       1991
                                    2000       2002
                                    2010       2015
                                    2020       2030
                                    2025       2030
                                    2040          .