DATA Step, Macro, Functions and more

Calculating driving distances based on addresses

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 87
Accepted Solution

Calculating driving distances based on addresses

Good Afternoon everyone! I was wondering if SAS can help in finding driving distances in miles between two complete addresses (not just zip codes). I know one could do that based on zip codes only, but this is not helpful here as there are multiple addresses within the same zip code. So, I have a file with addresses and I need to check each address with list of addresses in another file and match the address from first file with that address in second file which is closest in distance. Manually, it will be calculating distance using each address from the first file with all addresses in second file by google maps. 

Thanks so much!

 

 

 


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

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;
  format startaddr $40.;
  input startAddr startCity $ startSt $;
  cards;
"306 Rodman Road","Auburn","ME"
"155 Center Street","Auburn","ME"
"28 Arsenal Street", "Augusta", "ME"
"9 Green Street", "Augusta", "ME"
;
data hospitals;
  infile cards dlm=',' dsd;
  format endaddr $40.;
  input endAddr endCity $endSt $;
  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;                      

%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 %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;
    addr1 = tranwrd(left(trim(&startAddr))," ","+")||","||
     tranwrd(left(trim(&startCity))," ","+")||","||
      left(trim(&startSt));
    addr2 = tranwrd(left(trim(&endAddr))," ","+")||","||
     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));
      call symput("addr1",trim(left(addr1)));
      call symput("addr2",trim(left(addr2)));
    run;
    /* Determine road distance*/
    options noquotelenmax;
    filename google url "https://maps.googleapis.com/maps/api/distancematrix/json?origins=&addr2.%nrstr(&destinations=)&addr1.%nrstr(&key)=AIzaSyBsF0lfNZgJm0uuGsP3Hm0sSuFpGEA3m1o";
    data dist(drop=_:);
      infile google lrecl=1000;
      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;
        set dist(drop=recnum addr:);
      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
            rename=(endAddr=startAddr endCity=startCity endSt=startSt)
            where=(not missing(startAddr)));
run;

data hospitals;
  set have (keep=Service_Location startAddr startCity startSt
            rename=(startAddr=endAddr  startCity=endCity startSt=endSt)
            where=(not missing(endAddr)));
run;

proc sql;                  
  create table input as    
    select *           
      from people               
           ,hospitals
        order by Sub_Site_Name, Service_Location
  ;              
quit; 
                     
%road(input,output,startAddr,startCity,startSt,endAddr,endCity,endSt)

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

 

 

View solution in original post


All Replies
PROC Star
Posts: 7,366

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
google changed it 'html' code.

inspired by
https://goo.gl/FxwkXR
https://communities.sas.com/t5/Base-SAS-Programming/Calculating-driving-distances-based-on-addresses/m-p/340043

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
         filename google url "http://maps.google.com/maps?daddr=&too.%nrstr(&saddr)=&from";
         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('
         filename google url "http://maps.google.com/maps?daddr=&too.%nrstr(&saddr)=&from";
         data dist;
             infile google recfm=f lrecl=10000;
             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: 87

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.

StartID startAddr startCity StartSt EndID endaddr EndCity endst
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: 7,366

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: 87

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;
addr1 = tranwrd(left(trim(&startAddr))," ","+")||","||
tranwrd(left(trim(&startCity))," ","+")||","||
left(trim(&startSt));
addr2 = tranwrd(left(trim(&endAddr))," ","+")||","||
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));
call symput("addr1",trim(left(addr1)));
call symput("addr2",trim(left(addr2)));
run;
/* Determine road distance*/
options noquotelenmax;
filename google url "http://maps.google.com/maps?daddr=&addr2.%nrstr(&saddr)=&addr1";
data dist(drop=html);
infile google recfm=f lrecl=10000;
input @ ’<div class="altroute-rcol altroute-info"> <span>’ @;
input html $50.;
if _n_ = 1;
roaddistance = input(scan(html,1," "),comma12.);
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;

% road(input,output,startAddr,startCity,startSt,endAddr,endCity,endSt)
Super User
Posts: 10,550

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: 87

Re: Calculating driving distances based on addresses

Log is attached

Attachment
Super User
Posts: 10,550

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: 7,366

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:

http://blogs.sas.com/content/sgf/2015/11/03/sas-to-the-rescue-claiming-your-location-on-google-map-b...

 

Art, CEO, AnalystFinder.com

 

PROC Star
Posts: 7,366

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: 7,366

Re: Calculating driving distances based on addresses

[ Edited ]

I revised the macro and got it to run:

 

data people;
  infile cards dlm=',' dsd;
  format startaddr $40.;
  input startAddr startCity $ startSt $;
  cards;
"306 Rodman Road","Auburn","ME"
"155 Center Street","Auburn","ME"
"28 Arsenal Street", "Augusta", "ME"
"9 Green Street", "Augusta", "ME"
;
data hospitals;
  infile cards dlm=',' dsd;
  format endaddr $40.;
  input endAddr endCity $endSt $;
  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;                      

%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 %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;
    addr1 = tranwrd(left(trim(&startAddr))," ","+")||","||
     tranwrd(left(trim(&startCity))," ","+")||","||
      left(trim(&startSt));
    addr2 = tranwrd(left(trim(&endAddr))," ","+")||","||
     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));
      call symput("addr1",trim(left(addr1)));
      call symput("addr2",trim(left(addr2)));
    run;
    /* Determine road distance*/
    options noquotelenmax;
    filename google url "https://maps.googleapis.com/maps/api/distancematrix/json?origins=&addr2.%nrstr(&destinations=)&addr1.%nrstr(&key)=xxxxxxyxxxxtxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
    data dist(drop=junk);
      infile google lrecl=1000;
      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;
        set dist(drop=n addr:);
      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;

%road(input,output,startAddr,startCity,startSt,endAddr,endCity,endSt)

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 87

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: 87

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: 7,366

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 36 replies
  • 275 views
  • 5 likes
  • 4 in conversation