Closest match

[torvyle]

Hello,

Say that I have a dataset1 with ID and variable 1, like this:

ID	Var1
A	5.1
B	6.1
C	7.1
D	8.1
F	9.1
G	10.1

I also have dataset2, which is a reference table like this:

Var2	Var3
5	20
6	30
7	40
8	50
9	60

How do I obtain a dataset that has ID, Var1, and Var3 such that Var3 corresponds to Var1's closest match in Var2 of the reference table. The resulting dataset would look like this:

ID	Var1	Var3
A	5.1	20
B	6.1	30
C	7.1	40
D	8.1	50
F	9.1	60
G	10.1	60

I came across the following article but I'm not sure how it can be adapted to my problem: http://www.lexjansen.com/pharmasug/2003/CodersCorner/cc001.pdf

Thank you.

[gamotte · Accepted solution]

Hello,

proc sql;
	CREATE TABLE want0 AS
	SELECT a.ID, a.VAR1, b.VAR2, b.VAR3, abs(b.VAR2-a.VAR1) AS DIFF, min(CALCULATED DIFF) AS MIN_DIFF
	FROM have1 a, have2 b
	GROUP BY a.ID
	HAVING CALCULATED DIFF=CALCULATED MIN_DIFF;
quit;

[Reeza]

Have you tried the SQL solution in that paper? It's probably the easiest to implement if you're new to SAS.