Programming the statistical procedures from SAS

Calculating All GEODIST Combos

Reply
Occasional Contributor
Posts: 12

Calculating All GEODIST Combos

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.

Super User
Posts: 18,569

Re: Calculating All GEODIST Combos

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. 

Occasional Contributor
Posts: 12

Re: Calculating All GEODIST Combos

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. Smiley Happy

Super User
Posts: 18,569

Re: Calculating All GEODIST Combos

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;
Ask a Question
Discussion stats
  • 3 replies
  • 307 views
  • 0 likes
  • 2 in conversation