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?
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 .
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.