## Closest match

Solved
Occasional Contributor
Posts: 10

# Closest match

[ Edited ]

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.

Accepted Solutions
Solution
‎06-09-2017 11:45 AM
Super Contributor
Posts: 340

## Re: Closest match

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;``````

All Replies
Solution
‎06-09-2017 11:45 AM
Super Contributor
Posts: 340

## Re: Closest match

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;``````
Super User
Posts: 23,739

## Re: Closest match

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

☑ This topic is solved.