New Contributor
Posts: 4

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

[ Edited ]

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 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;

Super User
Posts: 21,917

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

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.

New Contributor
Posts: 4

Thanks again.
Greg
Super User
Posts: 21,917

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

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

Valued Guide
Posts: 505

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

``````/* 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

WANT
===

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

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

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

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

and click [GET KEY}

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

WANT
===

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

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;

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: 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      !  =
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      ! 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();

+  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

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

and click [GET KEY}

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
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;

``````
Valued Guide
Posts: 505

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

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.

Super User
Posts: 10,387

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

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

Discussion stats
• 6 replies
• 142 views
• 0 likes
• 4 in conversation