topic Re: Selecting closest value from two variables in SAS Programming

Selecting closest value from two variables

dwarden3 — Wed, 06 Jan 2021 18:36:46 GMT

Thank you in advance.

Table A:

ID X Y

1 -90 35

2 -90 37

3 -90 39

Table B:

Area X Y

A -90 33

B -91 36

C -90 38

How can I query Table B to select the area that is closest (smallest difference between both x and y) for each observation in Table A such that I end up with:

ID X Y Area

Table A is about 240k observations. There are about 18k observations(areas) in table B.

Edit: for the final table, I want the X/Y coords from table A.

Re: Selecting closest value from two variables

PaigeMiller — Wed, 06 Jan 2021 18:54:06 GMT

Closest? How do you define that in this case? Euclidean distance? Sum of the differences? Sum of the absolute value of the differences? Or some other measure?

Can you give an example or three?

Re: Selecting closest value from two variables

gamotte — Wed, 06 Jan 2021 18:56:48 GMT

Hello,

Depending of your definition of distance, you can modify the macro below :

%macro distance(x1, y1, x2, y2);

    sqrt((&x2-&x1)**2+(&y2-&y1)**2)

%mend;

proc sql noprint;
CREATE TABLE want AS
SELECT a.*, b.Area, %distance(a.X,a.Y, b.X, b.Y) AS DIST
FROM a, b
GROUP BY ID
HAVING %distance(a.X,a.Y, b.X, b.Y)=min(%distance(a.X,a.Y, b.X, b.Y))
;
quit;

Note that the number of observations will increase if there are several observation for the minimal distance.

Re: Selecting closest value from two variables

ballardw — Wed, 06 Jan 2021 18:57:45 GMT

Is there any other information that would allow at least grouping?

The meaning of X? If that is a longitude in degrees and the range of values is large enough then 179.9 and -179.9 are "closer" than 179.9 and 175 and we need to know that to appropriately calculate distance.

Re: Selecting closest value from two variables

dwarden3 — Wed, 06 Jan 2021 18:57:55 GMT

Euclidean distance would work. These are X/Y coordinates.

Re: Selecting closest value from two variables

ballardw — Wed, 06 Jan 2021 20:03:29 GMT

@dwarden3 wrote:

Euclidean distance would work. These are X/Y coordinates.

Are these geocoodinates? As in latitude and longitude?

I ask because there is a specific function, Geodist, to calculate geodetic distance or the length of a curve on the earths surface and simple Euclidean distance may not be precise enough if dealing with a large range of coordinates.

Re: Selecting closest value from two variables

dwarden3 — Wed, 06 Jan 2021 21:36:59 GMT

Yes, these are geocoordinates (Long/Lat).

Re: Selecting closest value from two variables

ballardw — Wed, 06 Jan 2021 23:01:41 GMT

@dwarden3 wrote:
Yes, these are geocoordinates (Long/Lat).

Then you could replace the %Distance in @gamotte's solution with Geodist (lat1,long1, lat2,long2). The latitude does need to come before the longitude in the parameter order but the pair position doesn't.

The default unit reported as the distance would be Kilometers. If you want the distance in miles then Geodist (lat1,long1, lat2,long2,'M')

Re: Selecting closest value from two variables

dwarden3 — Thu, 07 Jan 2021 19:59:31 GMT

This works. Sorry for the late reply. I have to break the dataset up to the county level for it to run, but the output is exactly what I need. Thank you @ballardw and @gamotte