DATA Step, Macro, Functions and more

PROC sql: how to join to nearest year

Reply
Contributor
Posts: 43

PROC sql: how to join to nearest year

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?

Esteemed Advisor
Posts: 5,625

Re: PROC sql: how to join to nearest year

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          .
PG
Ask a Question
Discussion stats
  • 1 reply
  • 127 views
  • 2 likes
  • 2 in conversation