Issues with Canadian locations using DRIVING DISTANCE AND DRIVE TIME, with SAS and Google Map

[grparent]

Hello,

I am trying to get the driving distance and time between two points in Canada using SAS and Google Maps. The code below works for US coordinate pairs but not Canadian. The Canadian coordinates work when put in the Google Maps directions menu. I tried a variety of Canadian coordinate pairs and none of them work.

Has anyone had this issue? Know of a solution?

Thanks.

Greg 

ps. I'm using SAS 9.2

/*USA cocordinates - this works */
%let ll1=%str(42.691560,-73.827840);
%let ll2=%str(35.805410,-78.797679);

/* Canadian Coordinates - does not work? */
/*%let ll1=%str(46.284774,-63.178973);*/
/*%let ll2=%str(46.292494,-63.172751);*/

filename x url "https://www.google.ca/maps/dir/&ll1/&ll2/?force=lite";
filename z temp;

data _null_;
infile x recfm=f lrecl=1 end=eof;
file z recfm=f lrecl=1;
input @1 x $char1.;
put @1 x $char1.;
if eof;
call symputx('filesize',_n_);
run;

data _null_;
infile z recfm=f lrecl=&filesize. eof=done;
input @ 'miles' +(-15) @ '"' distance :comma12. text $30.;
units = scan(text,1,'"');
time = scan(text,3,'"');
file print;
put "DRIVING DISTANCE BETWEEN &ll1 AND &ll2 : "
distance units" (TIME: " time ")";
stop;
done:
file print;
put "CANNOT FIND THE DRIVING DISTANCE BETWEEN &ll1 AND &ll2 : " /
"TRY ANOTHER PAIR OF COORDINATES";
stop;
run;

%put x;
%put z;

filename x clear;
filename z clear;

[Reeza]

1. Are you using the latest version of this code from SAScommunity page? It's gone through some changes over time.

2. Google has changed their API and licensing since this was originally written and you are likely violating the license agreement. Depending on what you're doing that may or may not be an issue.

[grparent]

Thanks for the fast reply. I will check for the latest version of the code off the SASCommunity page and read up on Google's license agreement.
Thanks again.
Greg

[Reeza]

Also, this code didn't work for either US or Canadian points for me, using SAS 9.4 M3 in Canada.

[rogerjdeangelis]

/* T1003050 SAS/WPS/R: Surface road distances, times, bicycle, walk, avoid tolls and trafffic using google maps in one dtatstep

I believe this works for Canadian distinces, however the default may be kilometers?

%let pgm=utl_get_driving_distance;

SAS/WPS/R: Surface road distances, times, bicycle, walk, avoid tolls and trafffic using google maps in one datastep

HAVE
====

Obs               FRO                               TOO

 1     306+Rodman+Road,Auburn,ME    163+Van+Buren+Rd.+Suite+1,Caribou,ME
 2     306+Rodman+Road,Auburn,ME    57+Water+St.,Blue+Hil,ME
 3     306+Rodman+Road,Auburn,ME    25+Hospital+Drive,Bridgton,ME
 4     306+Rodman+Road,Auburn,ME    287+Main+St.+Suite+301,Lewiston,ME


WANT
===

Up to 40 obs WORK.RESADD total obs=5
                                                                       Distance(Miles)  Time(Hrs)
Obs               FRO                               TOO                      DSTMYL     TYMHRS

 1     306+Rodman+Road,Auburn,ME    163+Van+Buren+Rd.+Suite+1,Caribou,ME    284.072    4.41694
 2     306+Rodman+Road,Auburn,ME    57+Water+St.,Blue+Hil,ME                120.361    2.27944
 3     306+Rodman+Road,Auburn,ME    25+Hospital+Drive,Bridgton,ME            31.812    0.77944
 4     306+Rodman+Road,Auburn,ME    287+Main+St.+Suite+301,Lewiston,ME       3.155     0.15833

DOCUMENTATION
=============

R package
https://cran.r-project.org/web/packages/gmapsdistance/gmapsdistance.pdf

The algorithm below may be more of an academic solution but it demonstrates
some advanced routines.

1. You need to declare common variables for R and the parent dataset (layout dataset=reslay).
   This is because  deferred dataset must ne congruent with the R data frame.
   This is done are parent compuile time.

     routine "if _n_=0 then "data reslay; length fro too timec distancec $200;run;quit;";

2.  Pass data from parent to R using macro variables

     call symputx('fro',fro);
     call symputx('too',too);

3.  Call R and compute mileage and time in meters and seconds. The quoting is tricky

     results <- gmapsdistance(origin = c(&fro.), destination = c(&too.), mode = "driving");

4   Dataset reslay has been compiled and we are now at execution time.
    Dataset res(from R) is DEFERED so that the SAS compiler did not see it at compile or during
    reading reslay.

    set reslay res(rename=(v1=fro v2=too v3=timec v4=distancec)) open=defer end=dne;


Some interesting options in the R package
=========================================

Lat Lon
results = gmapsdistance(origin = "38.1621328+24.0029257",destination = "37.9908372+23.7383394",
mode = "walking")

mode = "bicycling",
dep_date = "2017-08-16",
dep_time = "20:40:00")

mode = "driving",
departure = 1514742000,
traffic_model = "pessimistic",

mode = "driving",
avoid = "tolls",


R Package
=========

https://cran.r-project.org/web/packages/gmapsdistance/gmapsdistance.pdf

To get a key go to

https://developers.google.com/maps/documentation/distance-matrix/get-api-key#key

and click [GET KEY}

I key looks like 'wfweopfgoasdsadfhuiasdfgfsdfksf'

Some interesting options in the R package

Lat Lon
results = gmapsdistance(origin = "38.1621328+24.0029257",destination = "37.9908372+23.7383394",
mode = "walking")

mode = "bicycling",
dep_date = "2017-08-16",
dep_time = "20:40:00")

mode = "driving",
departure = 1514742000,
traffic_model = "pessimistic",

mode = "driving",
avoid = "tolls",

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

HAVE
====

Obs               FRO                               TOO

 1     306+Rodman+Road,Auburn,ME    163+Van+Buren+Rd.+Suite+1,Caribou,ME
 2     306+Rodman+Road,Auburn,ME    57+Water+St.,Blue+Hil,ME
 3     306+Rodman+Road,Auburn,ME    25+Hospital+Drive,Bridgton,ME
 4     306+Rodman+Road,Auburn,ME    287+Main+St.+Suite+301,Lewiston,ME


WANT
===

Up to 40 obs WORK.RESADD total obs=5
                                                                       Distance(Miles)  Time(Hrs)
Obs               FRO                               TOO                      DSTMYL     TYMHRS

 1     306+Rodman+Road,Auburn,ME    163+Van+Buren+Rd.+Suite+1,Caribou,ME    284.072    4.41694
 2     306+Rodman+Road,Auburn,ME    57+Water+St.,Blue+Hil,ME                120.361    2.27944
 3     306+Rodman+Road,Auburn,ME    25+Hospital+Drive,Bridgton,ME            31.812    0.77944
 4     306+Rodman+Road,Auburn,ME    287+Main+St.+Suite+301,Lewiston,ME       3.155     0.15833


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

* this will work without an API key;

proc datasets lib=work;
delete res:;
run;quit;
%symdel too fro;

data resadd(drop=rc timec distancec where=(fro ne ''));

  if _n_=0 then do;
      %let rc=%sysfunc(dosubl('
        data reslay; length fro too timec distancec $200;run;quit;
     '));
  end;

  fro=repeat('"306+Rodman+Road,Auburn,ME",',2)!!'"306+Rodman+Road,Auburn,ME"';
  too=catx(','
     ,'"163+Van+Buren+Rd.+Suite+1,Caribou,ME"'
     ,'"57+Water+St.,Blue+Hil,ME"'
     ,'"25+Hospital+Drive,Bridgton,ME"'
     ,'"287+Main+St.+Suite+301,Lewiston,ME"');

      call symputx('fro',fro);
      call symputx('too',too);

      rc=dosubl('
         %utl_submit_wps64(''
         libname sd1 "d:/sd1";
         options set=R_HOME "C:/Program Files/R/R-3.3.2";
         libname wrk "%sysfunc(pathname(work))";
         proc r;
         submit;
         source("c:/Program Files/R/R-3.3.2/etc/Rprofile.site",echo=T);
         library(gmapsdistance);
         set.api.key("AIzaSyBXdO7whgDyi8EjCDT0E20QRnLyEoJ21dA");
         get.api.key();
         results <- gmapsdistance(origin = c(&fro.), destination = c(&too.), mode = "driving");
         res<-as.data.frame(cbind(c(&fro.),c(&too),t(results$Time)[2:5],t(results$Distance)[2:5]));
         endsubmit;
         import r=res data=wrk.res;
         run;quit;
      '')');

        do until (dne);
           set reslay res(rename=(v1=fro v2=too v3=timec v4=distancec)) open=defer end=dne;
           * convert to hours and miles;
           DstMyl=input(Distancec,best.)*0.000621371;
           TymHrs=input(Timec,best.)/3600;
           output;
        end;
        stop;
run;quit;


1                                        The WPS System        10:49 Monday, March 13, 2017

NOTE: (c) Copyright World Programming Limited 2002-2016.  All rights reserved.
NOTE: World Programming System 3.02 (03.02.02.00.015680)
      Licensed to Roger DeAngelis, for express only
NOTE: This session is executing on the X64_WIN7PRO platform and is running in 64 bit mode

NOTE: This session is executing in WPS EXPRESS edition mode and is limited to processing only
      100 records from any input dataset or file.

NOTE: AUTOEXEC processing beginning; file is c:\oto\Tut_Otowps.sas
NOTE: Format num2mis is already in the catalog and will be overwritten
NOTE: Format $chr2mis is already in the catalog and will be overwritten
NOTE: Procedure format step took :
      real time : 0.046
      cpu time  : 0.000



NOTE: The data step took :
      real time : 0.015
      cpu time  : 0.015


NOTE: AUTOEXEC processing completed

1         libname sd1 "d:/sd1";
NOTE: Library sd1 assigned as follows:
      Engine:        SAS7BDAT
      Physical Name: d:\sd1

2         options set=R_HOME "C:/Program Files/R/R-3.3.2";
3         libname wrk "e:\saswork\wrk\_TD6072_BEAST_";
NOTE: Library wrk assigned as follows:
      Engine:        SAS7BDAT
      Physical Name: e:\saswork\wrk\_TD6072_BEAST_

4         proc r;
5         submit;
6         source("c:/Program Files/R/R-3.3.2/etc/Rprofile.site",echo=T);
7         library(gmapsdistance);
8         set.api.key("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
9         get.api.key();
10        results <- gmapsdistance(origin = c("306+Rodman+Road,Auburn,ME","306+Rodman+Road,Auburn,
ME","306+Rodman+Road,Auburn,ME","306+Rodman+Road,Auburn,ME"), destination
10      !  =
11         c("163+Van+Buren+Rd.+Suite+1,Caribou,ME","57+Water+St.,Blue+Hil,ME","25+Hospital+Drive,
Bridgton,ME","287+Main+St.+Suite+301,Lewiston,ME"), mode = "driving");
12        res<-as.data.frame(cbind(c("306+Rodman+Road,Auburn,ME","306+Rodman+Road,Auburn,ME","306+
Rodman+Road,Auburn,ME","306+Rodman+Road,Auburn,ME"),c("163+Van+Buren+Rd.+
12      ! Suite+1,Caribou,ME","57+Water+St.,Blue+Hil,ME","25+Hospital+Drive,Bridgton,ME","287+
13        uite+301,Lewiston,ME"),t(results$Time)[2:5],t(results$Distance)[2:5]));
14        endsubmit;
NOTE: Using R version 3.3.2 (2016-10-31) from C:/Program Files/R/R-3.3.2


NOTE: Submitting statements to R:

> source("c:/Program Files/R/R-3.3.2/etc/Rprofile.site",echo=T);
> .libPaths(c(.libPaths(), "d:/3.3.2", "d:/3.3.2_usr"))
> options(help_type = "html")
> library(gmapsdistance);
> set.api.key("xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx");
> get.api.key();
> results <- gmapsdistance(origin = c("306+Rodman+Road,Auburn,ME","306+Rodman+Road,Auburn,ME","306
+Rodman+Road,Auburn,ME","306+Rodman+Road,Auburn,ME"), destination =

+  c("163+Van+Buren+Rd.+Suite+1,Caribou,ME","57+Water+St.,Blue+Hil,ME","25+Hospital+Drive,Bridgton
,ME","287+Main+St.+Suite+301,Lewiston,ME"), mode = "driving");

There were 12 warnings (use warnings() to see them)

2                                                                             The WPS System


> res<-as.data.frame(cbind(c("306+Rodman+Road,Auburn,ME","306+Rodman+Road,Auburn,ME","306+Rodman+R
oad,Auburn,ME","306+Rodman+Road,Auburn,ME"),c("163+Van+Buren+Rd.+Suite+1,
Caribou,ME","57+Water+St.,Blue+Hil,ME","25+Hospital+Drive,Bridgton,ME","287+Main+St.+S
+ uite+301,Lewiston,ME"),t(results$Time)[2:5],t(results$Distance)[2:5]));



NOTE: Processing of R statements complete

15        import r=res data=wrk.res;
NOTE: Creating data set 'WRK.res' from R data frame 'res'
NOTE: Data set "WRK.res" has 4 observation(s) and 4 variable(s)

16        run;
NOTE: Procedure r step took :
      real time : 3.920
      cpu time  : 0.015


17        quit;

NOTE: Submitted statements took :
      real time : 3.998
      cpu time  : 0.046





https://cran.r-project.org/web/packages/gmapsdistance/gmapsdistance.pdf

To get a key go to

https://developers.google.com/maps/documentation/distance-matrix/get-api-key#key

and click [GET KEY}

I key looks like 'wfweopfgoasdsadfhuiasdfgfsdfksf'

Some interesting options in the R package

Lat Lon
results = gmapsdistance(origin = "38.1621328+24.0029257",destination = "37.9908372+23.7383394",
mode = "walking")

mode = "bicycling",
dep_date = "2017-08-16",
dep_time = "20:40:00")

mode = "driving",
departure = 1514742000,
traffic_model = "pessimistic",

mode = "driving",
avoid = "tolls",


SOAPBOX ON
google API is almost useless?
A tiny number of requests (25 million) would cost the rediculous $25,000 dollars.
Less then 25 cents of storage and 1 minute of CPU?
SOAPBOX OFF


%macro utl_submit_R(pgmx)/des="Semi colon separated set of R commands";
  * write the program to a temporary file;
  filename r_pgm temp lrecl=32766 recfm=v;
  data _null_;
    file r_pgm;
    pgm=&pgmx;
    put pgm;
  run;
  %let __loc=%sysfunc(pathname(r_pgm));
  * pipe file through R;
  filename rut pipe "c:\Progra~1\R\R-3.3.2\bin\i386\R --quiet --no-save < &__loc";
  *filename rut pipe "C:\R-2.15.3\bin\i386\R --quiet --no-save < &__loc";
  data _null_;
    file print;
    infile rut;
    input;
    put _infile_;
  run;
  filename rut clear;
  filename r_pgm clear;
%mend utl_submit_r;

[rogerjdeangelis]

As a side note;

  I posted a map digitizer a week or so ago(maybe SAS-L).  If you map has a mileage bar, you can digitize the endpoints of the bar and then digitize any two ponts on the map. SAS could then calculate the straightline mileage.

[Ksharp]

If you already have Longtitude and Latitude data, Try DISTANCE() function.