## Calculating driving distances based on addresses

Solved
Frequent Contributor
Posts: 121

# Calculating driving distances based on addresses

Thanks so much!

Accepted Solutions
Solution
‎03-15-2017 06:37 PM
PROC Star
Posts: 8,165

## Re: Calculating driving distances based on addresses

Glad to hear that the macro is working properly. What you didn't do, but must, is separate the file into the two groups you want to compare, and run the proc sql code to obtain the Cartestian product (i.e., all pairs between the two datasets).

You also have to edit the raw data, as there are 3 or 4 records that include both an address and a PO Box # in the address field. I removed the PO Box part from each record and Google calculated distance for all pairs. Interestingly, one of your records only had a PO Box # for the address and Google didn't have any problem with it.

Like I said, cancel your Google key, get a new one, and NEVER share it on the web. Also, since your data requires 6,634 calculations, you'll either have to break it down into three sets of data to run over three days, or get a premium Google key.

Here is the code I ran:

```data people;
infile cards dlm=',' dsd;
input startAddr startCity \$ startSt \$;
cards;
"155 Center Street","Auburn","ME"
"28 Arsenal Street", "Augusta", "ME"
"9 Green Street", "Augusta", "ME"
;
data hospitals;
infile cards dlm=',' dsd;
cards;
"57 Water St.", "Blue Hill", "ME"
"25 Hospital Drive", "Bridgton", "ME"
"163 Van Buren Rd. Suite 1" ,"Caribou" ,"ME"
"287 Main St. Suite 301", "Lewiston", "ME"
;

proc sql;
create table input as
select *
from people
,hospitals
;
quit;

/* Check if input data set exists; otherwise, throw exception */
%if %sysfunc(exist(&input))ˆ=1 %then %do;
data _null_;
file print;
put #3 @10 "Data set &input. does not exist";
run;
%abort;
%end;
/* Check if user specified output dataset name; otherwise, create default */
%if %length(&output) gt 1 %then %let outData=&output;
%else %let outData = &input._dist;
/* Replace all inter-word spaces with plus signs */
data tmp;
set &input;
tranwrd(left(trim(&startCity))," ","+")||","||
left(trim(&startSt));
tranwrd(left(trim(&endCity))," ","+")||","||
left(trim(&endSt));
recnum = _n_;
run;
data _NULL_;
if 0 then set tmp nobs=numobs;
call symputx("nObs",numobs); stop;
run;
%do i=1 %to &nObs;
/* Place starting and ending locations into macro variables */
data _null_;
set tmp(where=(recnum=&i));
run;
options noquotelenmax;
data dist(drop=_:);
format distance 8.3;
length _junk _distance _distance_long \$20;
input @ '"distance"' _junk @;
input @ '"text" : "' _distance @;
input @ '"value" : ' _distance_long;
distance=input(compress(_distance_long,,'kd'),12.3);
run;
data dist;
merge tmp(where=(recnum=&i)) dist;
run;
/* Append to output dataset */
%if &i=1 %then %do;
data &outData;
run;
%end;
%else %do;
proc append base=&outData data=dist(drop=recnum addr:) force;
run;
%end;
%end;
/*   Delete the temporary dataset */
proc delete data=work.tmp;
run;
%mend;

proc import datafile="c:\art\input2a.xlsx"
out=have replace dbms=xlsx;
getnames=yes;
sheet='Sheet1';
run;

data people;
set have (keep=Main_Site_Name Sub_Site_Name endAddr endCity endSt
run;

data hospitals;
set have (keep=Service_Location startAddr startCity startSt
run;

proc sql;
create table input as
select *
from people
,hospitals
order by Sub_Site_Name, Service_Location
;
quit;

proc sort data=output;
by sub_site_name distance;
run;

data closest;
set output;
by sub_site_name;
if first.sub_site_name;
run;
```

I'd say your problem is now solved and you should mark it so.

Art, CEO, AnalystFinder.com

All Replies
PROC Star
Posts: 8,165

## Re: Calculating driving distances based on addresses

You can automate the manual process with the following macro: http://www.lexjansen.com/wuss/2013/100_Paper.pdf

Art, CEO, AnalystFinder.com

Valued Guide
Posts: 505

## Re: Calculating driving distances based on addresses

``````Computing trip distance on surface roads

WPS does not support 'DOSUBL'.

I could not get the code in the article to work. I suspect

inspired by
https://goo.gl/FxwkXR

HAVE
====

from=Colchester,VT   to=Brattleboro,VT
from Colchester,VT   to=Rutland,VT
from=Colchester,VT   to=Burlington,VT

WANT
====

from=Colchester,VT   to=Brattleboro,VT   DIST=153 Miles
from Colchester,VT   to=Rutland,VT       DIST=74.0 Miles
from=Colchester,VT   to=Burlington,VT    DIST=8.3 Miles

WORKING CODE

from='Colchester,VT';
do too='Brattleboro,VT','Rutland,VT','Burlington,VT';
dosubl
dist=scan(substr(_infile_,index(_infile_,"miles")-10,10),-1,'"');
end;

FULL SOLUTION
=============

%symdel too from / nowarn; * just in case;
data _null_;
from='Colchester,VT';
do too='Brattleboro,VT','Rutland,VT','Burlington,VT';
call symputx('from',from);
call symputx('too',too);
rc=dosubl('
data dist;
input;
idxmyl=index(_infile_,"miles");
if idxmyl>0 then do;
dist=scan(substr(_infile_,idxmyl-10,10),-1,"22"x);
put "from=&from" @32 "to=&too" @ 55 dist= "Miles";
stop;
end;
run;quit;
');
end;
run;quit;

from=Colchester,VT   to=Brattleboro,VT   DIST=153 Miles
from Colchester,VT   to=Rutland,VT       DIST=74.0 Miles
from=Colchester,VT   to=Burlington,VT    DIST=8.3 Miles

This may not work in the future, however just analyze the google html and adjust the code.As a side note google like SAS does not always honor legacy, GMAIL no longer supports a fixed font?a command line scripting language makes it much more difficult for SAS and others to drop key functionality.

``````
Frequent Contributor
Posts: 121

## Re: Calculating driving distances based on addresses

Thanks both for trying. Yes, that's true I tried the macro in the article and its not working. Let me post the example data and then it might be more clearer.

a  306 Rodman Road Auburn ME f 57 Water St. Blue Hill ME
b 155 Center Street Auburn ME g 25 Hospital Drive Bridgton ME
c 28 Arsenal Street Augusta ME h 163 Van Buren Rd., Suite 1 Caribou ME
d 9 Green Street Augusta ME i 287 Main St., Suite 301 Lewiston ME

Basically, I want to run each startid address with each endID address to find the closest place. So, in terms of final dataset, im looking for is

startid  endid   distance

a          h           5 mi

b          g           4 mi

c          h            10 mi

d          i              2 mi

What the above final set tells us is that of all the endID's,  h is closest to a, g is closest to b in terms of driving distance and so on.

PROC Star
Posts: 8,165

## Re: Calculating driving distances based on addresses

Post the code you ran. I manually ran a couple of your examples and a is a lot closer to i than it is to h.

Regardless, rather than having pairs of addresses, don't you actually have two sets of address, one for people and another for hospitals and, what you want, is the closest hospital for each person.

Art, CEO, AnalystFinder.com

Frequent Contributor
Posts: 121

## Re: Calculating driving distances based on addresses

I ran the exact code as mentioned in the article and the log had many errors. The distances i gave were just for example, so you are right in that. You can consider startid as people and endid as hospitals.

``````%macro road(input,output,startAddr,startCity,startSt,endAddr,endCity,endSt);
/* Check if input data set exists; otherwise, throw exception */
%if %sysfunc(exist(&input))ˆ=1 %then %do;
data _null_;
file print;
put #3 @10 "Data set &input. does not exist";
run;
%abort;
%end;
/* Check if user specified output dataset name; otherwise, create default */
%if &outputˆ="" %then %let outData=&output;
%else %let outData = &input._dist;
/* Replace all inter-word spaces with plus signs */
data tmp; set &input;
tranwrd(left(trim(&startCity))," ","+")||","||
left(trim(&startSt));
tranwrd(left(trim(&endCity))," ","+")||","||
left(trim(&endSt));
n = _n_;
run;
data _NULL_;
if 0 then set tmp nobs=n;
call symputx("nObs",n); stop;
run;
%do i=1 %to &nObs;
/* Place starting and ending locations into macro variables */
data _null_; set tmp(where=(n=&i));
run;
options noquotelenmax;
data dist(drop=html);
input @ ’<div class="altroute-rcol altroute-info"> <span>’ @;
input html \$50.;
if _n_ = 1;
run;
data dist; merge tmp(where=(n=&i)) dist; run;
/* Append to output dataset */
%if &i=1 %then %do;
data &outData; set dist(drop=n addr:); run;
%end;
%else %do;
proc append base=&outData data=dist(drop=n addr:) force; run;
5
%end;
%end;
/* Delete the temporary dataset */
proc datasets library=work noprint;
delete tmp;
quit;
%mend;

Super User
Posts: 13,583

## Re: Calculating driving distances based on addresses

Turn Options MPRINT; on

Run the code

Post the log as an attached TEXT file.

You may want to make sure your example data is only few lines.

Or post the first few errors.

Frequent Contributor
Posts: 121

Log is attached

Super User
Posts: 13,583

## Re: Calculating driving distances based on addresses

I suspect your issue is that the code was touched by something other that the SAS editor at some time:

Your post for lines like this:

input @<div class="altroute-rcol altroute-info"> <span>

shows a curly single quote not the '

And the log

’<div class="altroute-rcol altroute-info"> <span>’

note the quotes are not the ' also.

So SAS is not treating the result as quoted string.

PROC Star
Posts: 8,165

## Re: Calculating driving distances based on addresses

[ Edited ]

In addition to the probel that @ballardw mentioned, Google apparently now requires one to obtain their API in order to run such code programmatically. Do a web search for google maps api

Conversely, you CAN apparently use PROC GEOCODE to obtain street address level co-ordinates. Take a look at:

Art, CEO, AnalystFinder.com

PROC Star
Posts: 8,165

## Re: Calculating driving distances based on addresses

I looked into the Google Maps Distance Matrix API. You can get a key for free that allows you to do up to 2,500 searchs per day. You can get the key at: https://developers.google.com/maps/documentation/distance-matrix/

Any more than 2500 per day would cost 50 cents for each 1,000 lookups up to 100,000 lookups per day.

You would have to change a small part of the macro's code, including the filename specs and the data step that reads the results.

Unfortunately SAS UE doesn't provide internet access for me to test that code, so I simply did such a search using my web browser, and copied and pasted the json file into a data step. Here is what it looked like:

```/*https://maps.googleapis.com/maps/api/distancematrix/json?origins=Rodman Road+Auburn+ME&destinations=9+Green+Street,Augusta,ME&key)=xxxxx*/

data dist (drop=junk);
infile cards;
length junk distance \$20;
input @ '"distance"' junk;
input @ '"text" : ' distance;
cards;
{
"destination_addresses" : [ "9 Green St, Augusta, ME 04330, USA" ],
"origin_addresses" : [ "Rodman Rd, Auburn, ME 04210, USA" ],
"rows" : [
{
"elements" : [
{
"distance" : {
"text" : "62.3 km",
"value" : 62312
},
"duration" : {
"text" : "40 mins",
"value" : 2372
},
"status" : "OK"
}
]
}
],
"status" : "OK"
}
;
run;
```

Which produced the result: distance=62.3

HTH,

Art, CEO, AnalystFinder.com

PROC Star
Posts: 8,165

## Re: Calculating driving distances based on addresses

[ Edited ]

I revised the macro and got it to run:

```data people;
infile cards dlm=',' dsd;
input startAddr startCity \$ startSt \$;
cards;
"155 Center Street","Auburn","ME"
"28 Arsenal Street", "Augusta", "ME"
"9 Green Street", "Augusta", "ME"
;
data hospitals;
infile cards dlm=',' dsd;
cards;
"57 Water St.", "Blue Hill", "ME"
"25 Hospital Drive", "Bridgton", "ME"
"163 Van Buren Rd. Suite 1" ,"Caribou" ,"ME"
"287 Main St. Suite 301", "Lewiston", "ME"
;

proc sql;
create table input as
select *
from people
,hospitals
;
quit;

/* Check if input data set exists; otherwise, throw exception */
%if %sysfunc(exist(&input))ˆ=1 %then %do;
data _null_;
file print;
put #3 @10 "Data set &input. does not exist";
run;
%abort;
%end;
/* Check if user specified output dataset name; otherwise, create default */
%if %length(&output) gt 1 %then %let outData=&output;
%else %let outData = &input._dist;
/* Replace all inter-word spaces with plus signs */
data tmp;
set &input;
tranwrd(left(trim(&startCity))," ","+")||","||
left(trim(&startSt));
tranwrd(left(trim(&endCity))," ","+")||","||
left(trim(&endSt));
n = _n_;
run;
data _NULL_;
if 0 then set tmp nobs=n;
call symputx("nObs",n); stop;
run;
%do i=1 %to &nObs;
/* Place starting and ending locations into macro variables */
data _null_;
set tmp(where=(n=&i));
run;
options noquotelenmax;
data dist(drop=junk);
length junk distance \$20;
input @ '"distance"' junk @;
input @ '"text" : "' distance;
run;
data dist;
merge tmp(where=(n=&i)) dist;
run;
/* Append to output dataset */
%if &i=1 %then %do;
data &outData;
run;
%end;
%else %do;
proc append base=&outData data=dist(drop=n addr:) force;
run;
%end;
%end;
/*   Delete the temporary dataset */
proc delete data=work.tmp;
run;
%mend;

```

Art, CEO, AnalystFinder.com

Frequent Contributor
Posts: 121

## Re: Calculating driving distances based on addresses

Thanks Sir and everyone else who chipped in. Unfortunately I dont have access to SAS till Monday, so will try all this and let you guys know.

Frequent Contributor
Posts: 121

## Re: Calculating driving distances based on addresses

I ran your macro, and the distances are still blank. Are you sure you got it right? I ran exactly your macro first and didnt change anything.

PROC Star
Posts: 8,165

## Re: Calculating driving distances based on addresses

Hopefully you changed the key to the one you got from GoogleDistanceMatrix.

If so, there could be a difference bewteen running it from Canada versus wherever you are.

I first ran the link directly in my browser, then copied the returned source code to a txt file and wrote the code based on that file.

If you do the same, and post the text file here, I'm sure any difference will be easy to find.

Art, CEO, AnalystFinder.com

☑ This topic is solved.