## exist based on row calculation

Hello Community,

 CITI LATI LONGTI want1 AAA 128.12 37.65 0 BBB 127.11 36.54 1.500 CCC 127.22 36.22 1.689 DDD 127.00 36.01 1.985

want1

sqrt((128.12-127.11)^2 + (37.65-36.54)^2)=1.500

sqrt((128.12-127.22)^2 + (37.65-36.22)^2)=1.689

sqrt((128.12-127.00)^2 + (37.65-36.01)^2)=1.985

want2

sqrt((127.11-127.22)^2 + (36.54-36.22)^2)=0.333

sqrt((127.11-127.00)^2 + (36.54-36.01)^2)=0.541

4 REPLIES 4

## Re: exist based on row calculation

Do you actually have more than 4 rows of data and want some sort of general solution? If so then you need to describe the general rules involved.

## Re: exist based on row calculation

Try the GEODIST() function.

https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n1korpfg2e18lon1nwpow9qijdxe.htm...

There's also PROC DISTANCE with Euclidean distance that will give you a distance matrix, which is likely what you want overall.

Hello Community,

 CITI LATI LONGTI want1 AAA 128.12 37.65 0 BBB 127.11 36.54 1.500 CCC 127.22 36.22 1.689 DDD 127.00 36.01 1.985

want1

sqrt((128.12-127.11)^2 + (37.65-36.54)^2)=1.500

sqrt((128.12-127.22)^2 + (37.65-36.22)^2)=1.689

sqrt((128.12-127.00)^2 + (37.65-36.01)^2)=1.985

want2

sqrt((127.11-127.22)^2 + (36.54-36.22)^2)=0.333

sqrt((127.11-127.00)^2 + (36.54-36.01)^2)=0.541

## Re: exist based on row calculation

GEODIST() doesn't like your parameters for some reason.

``````proc sql;
create table want as
select t1.ID as StartLocation, t2.ID as EndLocation,
t1.lat as lat1, t1.long as long1,
t2.lat as lat2, t2.long as long2,
sqrt((t1.lat-t2.lat)**2 + (t1.long-t2.long)**2) as distance
from have as t1, have as t2
where t1.id>=t2.id
order by 1, 2;
quit;
``````

## Re: exist based on row calculation

I agree with Reeza that you should use the GEODIST function if your coordinates are latitudes and longitudes (as the variable names suggest). It looks like you interchanged latitudes and longitudes, though: Latitudes must be between -90 (South Pole) and 90 (North Pole). After this correction all your locations would be in South Korea. Does this sound plausible? If so, you can use the code provided by Mike Zdeb in Using Proc Distance to Calculate Geodesic Distance (the title refers to the OP's question in that old thread) to compute the desired distances. Omit the last argument 'm' in the GEODIST function call if you want distances in kilometres rather than miles.

The DISTANCE procedure does not support geodesic distance, so the results you would obtain (with PROC DISTANCE options method=euclid nostd and VAR statement var interval(longti lati)), i.e., the "distances" 1.50073, 1.68964, etc., would be questionable.

Discussion stats
• 4 replies
• 378 views
• 0 likes
• 4 in conversation