BookmarkSubscribeRSS Feed
Fluorite | Level 6



I'm trying to come up with a way to calculate all distances between particular locations and run a tally of other locations that fall within particular ranges.


For example, take this data set:


147 40.00376 -105.27621
193 40.58591 -105.00766
211 39.63461 -104.80849
245 40.39708 -104.69702
365 39.74437 -104.98742
384 40.55901 -105.09548
422 39.74773 -104.84025
449 39.58765 -104.86672
578 39.55761 -104.88142
584 40.57984 -105.05559

I have ten separate stores and their locations.


What I want to do is tally all the stores within three- and five-mile radiuses of each store.


For example, 

DISTANCE=GEODIST(40.00376,-105.27621,40.58591,-105.00766, 'M');


This value would give the distance, in miles, between stores 147 and 193. I want to repeat this process for stores 147 vs 211, 147 vs 245, etc. If this distance were less than or equal to 3 miles, I'd like to code a variable, MILE_3, as 1, otherwise zero. If this distance were less than or equal to 5 miles, I'd like to code a variable, MILE_5, as 1, otherwise 0. Then, I'd like a final tally record that resembled something like the following (where TOT_3 and TOT_5 are made up values for the number of stores in the data set within 3 and 5 miles of store 147).



---------- ------------- ----------------- --------- ---------

147        40.00376  -105.27621     1            2

193 ... etc


If possible, once store 147 is finished, I'd like store 193 to begin at the top and compare itself with store 147 so that its TOT_3 and TOT_5 values are accurate for itself.


I'm just trying to find some way to do this, so if anyone far more clever than I has any suggestions, I'd  love to try them. So far, the similar GEODIST topics haven't provided me the results I was after.


Thanks much.

Super User

Do you just need counts or a list of the actual stores. 

It's inefficient but a starting point is a cross join in SQL. 

Fluorite | Level 6

Hi, Reeza.


Efficiency isn't a primary concern.


Just a count is what I'm after, but a list of stores would be cool as well.


Right now I'm messing with a DO loop idea. I've created a separate data set with just the latitude and longitude values, and I'm trying to do it that way. Just trying to come up with something. 🙂

Super User

Here's the calculate all portion. You should be able to get the other metrics from this data set relatively easily.


proc sql;
create table mixed as
select a.*, as store2, geodist(a.latitude,  a.longitude, b.latitude, b.longitude, 'M') as dist 
from TEMP as a
cross join TEMP as b
and  ne
order by;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 2 in conversation