DATA Step, Macro, Functions and more

find addresses within 30 miles radius

Reply
Frequent Contributor
Posts: 77

find addresses within 30 miles radius

Suppose I have a address dataset(addr_lst) which has 100,000 US addresses and the following fields: idx, address, city, state, zip, x, y.

I want to find the addresses within 30 miles radius of each address.

Here is my sql to do this:

proc sql;
create table addr_30m as
select a.idx,
b.idx as idx2,
geodist(a.y, a.x, b.y, b.x,'M') as dist
from addr_lst a, addr_lst b
where geodist(a.y, a.x, b.y, b.x, 'M')<=30
; quit;

It is very slow because it uses Cartesian product. Is there any better way to speed up the computation?
Trusted Advisor
Posts: 2,113

Re: find addresses within 30 miles radius

your less than or equal to sign chopped of most of your SQL and question.
Regular Contributor
Posts: 241

Re: find addresses within 30 miles radius

...

> It is very slow because it uses Cartesian product. Is

> there any better way to speed up the computation?

...

See Howard Schreier's excellent NESUG 16 (2003) paper.
Frequent Contributor
Posts: 77

Re: find addresses within 30 miles radius

"See Howard Schreier's excellent NESUG 16 (2003) paper. "

Thanks, Chung expert.
Ask a Question
Discussion stats
  • 3 replies
  • 268 views
  • 0 likes
  • 3 in conversation