topic Re: Calculating driving distances based on addresses in SAS Programming

Calculating driving distances based on addresses

devsas — Fri, 10 Mar 2017 18:06:20 GMT

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!

Re: Calculating driving distances based on addresses

art297 — Fri, 10 Mar 2017 18:33:50 GMT

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

Art, CEO, AnalystFinder.com

Re: Calculating driving distances based on addresses

rogerjdeangelis — Fri, 10 Mar 2017 20:19:33 GMT

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.

Re: Calculating driving distances based on addresses

devsas — Fri, 10 Mar 2017 20:54:30 GMT

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.

Re: Calculating driving distances based on addresses

art297 — Fri, 10 Mar 2017 21:09:27 GMT

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

Re: Calculating driving distances based on addresses

devsas — Fri, 10 Mar 2017 21:15:38 GMT

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)

Re: Calculating driving distances based on addresses

ballardw — Fri, 10 Mar 2017 21:49:55 GMT

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.

Re: Calculating driving distances based on addresses

devsas — Fri, 10 Mar 2017 21:59:54 GMT

Log is attached

Re: Calculating driving distances based on addresses

ballardw — Fri, 10 Mar 2017 23:44:17 GMT

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.

Re: Calculating driving distances based on addresses

art297 — Sat, 11 Mar 2017 02:01:36 GMT

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-by-address-only-without-knowing-latitude-and-longitude/

Art, CEO, AnalystFinder.com

Re: Calculating driving distances based on addresses

art297 — Sat, 11 Mar 2017 03:33:59 GMT

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

Re: Calculating driving distances based on addresses

art297 — Tue, 14 Mar 2017 17:50:33 GMT

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

Re: Calculating driving distances based on addresses

devsas — Sun, 12 Mar 2017 01:21:18 GMT

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.

Re: Calculating driving distances based on addresses

devsas — Mon, 13 Mar 2017 14:46:16 GMT

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.

Re: Calculating driving distances based on addresses

art297 — Mon, 13 Mar 2017 14:55:47 GMT

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

Re: Calculating driving distances based on addresses

devsas — Mon, 13 Mar 2017 14:59:45 GMT

I'm in Maine currently and running from there on google chrome. Below is the code, including the key I got from the link you provided earlier. Not sure what else im missing here.

%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)=AIzaSyBsF0lfNZgJm0uuGsP3Hm0sSuFpGEA3m1o";
    data dist(drop=junk);
      infile google recfm=f 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;

Re: Calculating driving distances based on addresses

art297 — Mon, 13 Mar 2017 15:15:26 GMT

I just ran the macro, as you posted it, with the following code:

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;                      

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

proc print data=output;
run;

The results of the proc print on file OUTPUT is attached. Like I said, if your results were different, run the following in your browser, copy the resulting page's source code and post it here:

https://maps.googleapis.com/maps/api/distancematrix/json?origins=306 rodman road+auburn+ME&destinations=57 water st.+blue hill+me&key=AIzaSyBsF0lfNZgJm0uuGsP3Hm0sSuFpGEA3m1o

Art, CEO, AnalystFinder.com

p.s. You should cancel that key and NEVER post your key on the web. Too much chance that others will abuse it.

Re: Calculating driving distances based on addresses

devsas — Mon, 13 Mar 2017 15:19:47 GMT

Sorry I'm little lost now, not sure im following correctly. Anyways i ran the link on my browser and got the following source code.

{
   "destination_addresses" : [ "57 Water St, Blue Hill, ME 04614, USA" ],
   "origin_addresses" : [ "306 Rodman Rd, Auburn, ME 04210, USA" ],
   "rows" : [
      {
         "elements" : [
            {
               "distance" : {
                  "text" : "194 km",
                  "value" : 193703
               },
               "duration" : {
                  "text" : "2 hours 18 mins",
                  "value" : 8266
               },
               "status" : "OK"
            }
         ]
      }
   ],
   "status" : "OK"
}

Re: Calculating driving distances based on addresses

art297 — Mon, 13 Mar 2017 17:30:47 GMT

Then the macro should have run correctly. Post a copy of your log and I'll be glad to see if I can spot what might have gone wrong.

Art, CEO, AnalystFinder.com

Re: Calculating driving distances based on addresses

devsas — Mon, 13 Mar 2017 17:42:37 GMT

Here is the log file attached. Just keep in mind, i changed input dataset name to input1 and output to output1, rest its all same.