topic Re: exist based on row calculation in SAS Procedures

exist based on row calculation

scad — Thu, 23 Apr 2020 02:36:22 GMT

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

ballardw — Thu, 23 Apr 2020 03:08:19 GMT

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

Reeza — Thu, 23 Apr 2020 04:05:34 GMT

Try the GEODIST() function.

https://documentation.sas.com/?docsetId=lefunctionsref&docsetTarget=n1korpfg2e18lon1nwpow9qijdxe.htm&docsetVersion=9.4&locale=en#p04exx349ronntn148aiejhbn1fc

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

@scad wrote:

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

Reeza — Thu, 23 Apr 2020 04:20:38 GMT

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

FreelanceReinh — Thu, 23 Apr 2020 10:35:32 GMT

Hello @scad,

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.