Calculating driving distances based on addresses

[devsas]

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;

[art297]

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.

[devsas]

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"
}

[art297]

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

[devsas]

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.

[art297]

What browser are you using? If your site has a special browser interface (e.g., one that requires you to log on each time you initially access it), you won't be able to use the url access method.

Art, CEO, AnalystFinder.com

[devsas]

I'm using google chrome as i mentioned earlier. im always signed in on google on chrome. Although default browser at work is IE, and its possible that SAS connects to google through IE. Although I think it has gotten too complicated and im not sure if we are on the same page. 

[art297]

It's not very complicated and, to the contrary, I think that we're both still on the same page. I think there is something unique at your site that isn't allowing access via the URL method. I am able to successfully run the code using your key and/or my key.

I would pose the question to your IT people and, if that doesn't help. to SAS Technical Support.

The code is working perfectly but, at your site, nothing is being returned from submitting the filename google.

That is what leads me to think that it has something to do with how internet access is configured at your site.

Art, CEO, AnalystFinder.com

[devsas]

Thanks Sir for trying so hard. I logged into google through IE, got the key and still the same result. The IT support here is clueless, not sure if i can contact SAS support for this. Generally we dont have any firewall etc. Can anyone else chip in to see what possibly could be the issue? Thanks once again everyone.

[art297]

Here is a simple test you can run to see if you're actually getting access to the web.

Does running the following end up creating a file called esug_achive on your work directory and contain a bunch of links to a bunch of pdf files?:

filename eSUG url "http://www.sas.com/offices/NA/canada/en/edmonton.html";
data eSUG_achive(keep=pdffile);
  length pdffile $200;
  infile eSUG length=len lrecl=32767;
  input line $varying32767. len;
  *all the file names end with .pdf;
  if find(line,".pdf") then do;
    *get the string ending with .pdf and enclosed by quotation marks;
    pdffile=scan(line,2,'"');
    output;
  end;
run;
filename eSUG clear;

Art, CEO, AnalystFinder.com

[devsas]

Yes, it does create the dataset called esug_archive which has many pdf links inside it.

[art297]

Then, so much for my initial hypothesis. Run the following and post the resulting file: work.output

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)=AIzaSyBsF0lfNZgJm0uuGsP3Hm0sSuFpGEA3m1o";
    data dist(drop=junk);
      length html $1000;
      infile google lrecl=1000;
      input;
      html=_infile_;
    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)

proc print data=output;
run;

Art, CEO, AnalystFinder.com

p.s. Does your input file happen to have another variable in it, specifically one called n ?

[devsas]

Ok, one last attempt and attached is the output file in text. Output dataset is still blank for distance variable.

[art297]

Post the resulting SAS dataset, not a txt version of it. The code I last posted doesn't try to compute distance, simply capture the results in a field called html.

Art, CEO, AnalystFinder.com

[devsas]

Here it is, but as i already mentioned earlier, there is nothing for html variable.

Ok, i tried and it doest let me attach SAS dataset on this thread. Im attaching excel evrsion of the dataset. Its the same.