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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.