topic Calculating All GEODIST Combos in Statistical Procedures

Calculating All GEODIST Combos

KyleM_Corrie — Tue, 08 Mar 2016 04:36:19 GMT

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.

Re: Calculating All GEODIST Combos

Reeza — Tue, 08 Mar 2016 06:24:36 GMT

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.

Re: Calculating All GEODIST Combos

KyleM_Corrie — Tue, 08 Mar 2016 06:29:50 GMT

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

Re: Calculating All GEODIST Combos

Reeza — Tue, 08 Mar 2016 15:28:52 GMT

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;