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.
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;
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;
Have you tried the SQL solution in that paper? It's probably the easiest to implement if you're new to SAS.
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.