Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- exist based on row calculation

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 04-22-2020 10:36 PM
(377 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Try the GEODIST() function.

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.