BookmarkSubscribeRSS Feed
KyleM_Corrie
Fluorite | Level 6

Hello,

 

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:

 

DATA TEMP;
INPUT STORE LATITUDE LONGITUDE;
DATALINES;
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
;
RUN;

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).

 

STORE LATITUDE LONGITUDE TOT_3 TOT_5

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

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.

3 REPLIES 3
Reeza
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. 

KyleM_Corrie
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. 🙂

Reeza
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.*,b.store as store2, geodist(a.latitude,  a.longitude, b.latitude, b.longitude, 'M') as dist 
from TEMP as a
cross join TEMP as b
and a.store  ne b.store
order by a.store;
quit;

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
  • 2810 views
  • 0 likes
  • 2 in conversation