Driving Distance and Time between residential addresses and health clinics using Google Maps

[ark123]

I am encountering difficulties in calculating the driving distance and time between residential addresses and health clinics using SAS and Google Maps. I have two separate datasets: one contains the lat and long coordinates of residential addresses, and the other contains the lat and long coordinates for health clinics. These datasets do not have a unique identifier to link all records together.

My goal is to compute the median distance and time between each residential address and its nearest health clinic. I reviewed similar request for help, but I have been unsuccessful. Two methods I tried were not recognized by SAS. I've provided the syntax for both of my attempts here. Any guidance or alternative approaches would be greatly appreciated!

/*ATTEMPT 1*/
data Res_addrs; 
	set Res_addrs;
	addr1 = catx(',',y,x); 
run;
data clinic_addr; 
	set clinic_addr;
	addr2 = catx(',',y,x); 
	rename USER_lastname = ClinicID;
run;

data AddressFile;
	set Res_addrs clinic_addr;
run;


*Keep ID and coordinates only;
data Addresses;
	set AddressFile;
	keep patientid ClinicID addr1 addr2;
run;

*Check;
proc contents data=Addresses;
run;


*Macro to connect Google Maps; 
data _null_;
call symputx('Observations',obs);
stop;
set Addresses nobs=obs;
run;
 

/** SAS doesn't seem to recognized below **/

%macro distance_time;
 
%do j=1 %to 5 /*&Observations*/;
data _null_;
nrec = &j;
set Addresses point=nrec;
%let ll1=addr1;
%let ll2=addr2;
stop;
run;
 
filename x url "https://www.google.com/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;

*Drive distance & time;
data temp;
infile z recfm=f lrecl=&filesize. eof=done;
input @ 'miles' +(-15) @ '"' distance :comma12. text $30.;
units    = scan(text,1,'"');
text     = scan(text,3,'"');
* convert times to seconds;
  select;
* combine days and hours;
   when (find(text,'d') ne 0) time = sum(86400*input(scan(text,1,' '),best.), 
                                        3600*input(scan(text,3,' '),best.));
* combine hours and minutes;
   when (find(text,'h') ne 0) time = sum(3600*input(scan(text,1,' '),best.), 
                                        60*input(scan(text,3,' '),best.));
* just minutes;
   otherwise                  time = 60*input(scan(text,1,' '),best.);
  end;
output; 
keep distance time;
stop;
done:
output;
run;
 
filename x clear;
filename z clear;
 
*Add an obs to the data set;
proc append base=distance_time data=temp;
run;
%end;
%mend;
 
*Run the macro;
%distance_time;
 
*Add vars from original dataset to distance_time;
data distance_time;
set distance_time;
set Addresses point=_n_;
straight_line = round(geodist(&ll1/&ll2,'DM'), 0.01);
run;
 
proc print data=distance_time noobs label;
var x y time distance straight_line zip city statecode;
format zip z5. time time6. ;
run;


/******************************/

/* ATTEMPT 2 */

*Rename;
data Res_addrs; 
	set Res_addrs;
	rename  y = PatientLatitude
			x = PatientLongitude ; 
run;
data clinic_addr; 
	set clinic_addr;
	rename  y = ClinicLatitude
			x = ClinicLongitude
			USER_lastname = ClinicID ;
run;


/** SAS doesn't seem to recognize below **/

*Step 1 - Macro;
%macro distance
  (patientID=
  ,patientLatitude=
  ,patientLongitude=
  ,clinicID=
  ,clinicLatitude=
  ,clinicLongitude=
  ,out= mapmacro ) ;

filename x url "https://www.google.com/maps/dir/?api=1&origin=&patientLatitude.,&patientLongitude.&destination=&clinicLatitude.,&clinicLongitude.&travelmode=driving";
filename z temp;

%local filesize ;
 
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;


*Step 2 - Driving distance ;
data __distance(keep=PatientID ClinicID Distance PatientLatitude PatientLongitude ClinicLatitude ClinicLongitude) ;
  length PatientID $8 ClinicID $8 PatientLatitude PatientLongitude ClinicLatitude ClinicLongitude $20;
  PatientID="&PatientID" ;
  ClinicID="&ClinicID" ;
  PatientLatitude="&PatientLatitude" ;
  PatientLongitude="&PatientLongitude" ;
  ClinicLatitude="&ClinicLatitude" ;
  ClinicLongitude="&ClinicLongitude" ;

  infile z recfm=f lrecl=&filesize. eof=done;
  input @ 'miles' +(-15) @ '"' Distance :comma12. text $30.;
  units    = scan(text,1,'"');
  text     = scan(text,3,'"');
  output; 

  put PatientID= ClinicID= Distance=;
  stop;
  done:
  output;
run;

proc append base=&out data=__distance ;
run ;

proc delete data=__distance ;
run ;

filename x clear ;
filename z clear ;

%mend distance ;


*Step 3 - all driving routes;
proc sql ;
  create table routes as
  select 
    ehdi_rucc3.ID as PatientID
   ,ehdi_rucc3.Latitude as PatientLatitude
   ,ehdi_rucc3.Longitude as PatientLongitude
   ,Aud_Clinic1.ID as ClinicID
   ,Aud_Clinic1.Latitude as ClinicLatitude
   ,Aud_Clinic1.Longitude as ClinicLongitude
  from ehdi_rucc3,Aud_Clinic1
 ;
quit ;


*Step 4 - run macro;
data _null_;
  set routes;
  call execute 
    (
    '%nrstr('
        ||  '%distance'
        ||    '('
        ||    ' patientID='         || trim(patientID)
        ||    ',patientLatitude='   || trim(patientLatitude)
        ||    ',patientLongitude='  || trim(patientLongitude)
        ||    ',ClinicID='        || trim(ClinicID)
        ||    ',ClinicLatitude='  || trim(ClinicLatitude)
        ||    ',ClinicLongitude=' || trim(ClinicLongitude)
        ||    ',out=mydistances'
        ||    ' )'
        || ')'          
    );
run;
 

I also attached a copy of the log for reference. 

[ballardw]

If this is supposed to assign values to a macro variable using the value of a variable in a data set it is wrong.

%do j=1 %to 5 /*&Observations*/;
data _null_;
nrec = &j;
set Addresses point=nrec;
%let ll1=addr1;
%let ll2=addr2;
stop;
run;

  The timing of execution of macro statements and the data step means that the %let executes in the compile phase of the data step.  So LL1 has the text value of addr1, not that of the variable.

Use CALL SYMPUTX to assign a value to macro variable in a data step.

%do j=1 %to 5 /*&Observations*/;
data _null_;
nrec = &j;
set Addresses point=nrec;
call symputx('ll1')= addr1; /* best is to actually use a PUT with format if the value is numeric to control the conversion to text that the macro variable with hold*/
Call symputx('ll2')=addr2;
stop;
run;

Your call to GEODIST is likely quite suspect as well

straight_line = round(geodist(&ll1/&ll2,'DM'), 0.01);

The Geodist function takes 4 numeric parameters of lat and long for start and end point. I don't see any way that "&ll1" id going to work even when defined properly and / is entirely too likely be interpreted as a division between two numeric values. Meaning that you are short 3 parameters.

[ark123]

The code comes from two other support posts. These were accepted solutions, so they worked for someone., which is why I tried them. But they do not work for me, which is why I asked for help here.

https://communities.sas.com/t5/SAS-Programming/Find-driving-distance-google-maps-usinga-list-of-10-h...

https://communities.sas.com/t5/SAS-Programming/Find-shortest-distance-between-one-LAT-LONG-and-list-...

[Tom]

Can you be more specific about what parts of that are not working?

Do you have a list of the pairs of FROM/TO locations you want to find the distances between?  Note this does not need to include all patients or all clinics.  Just all patient LOCATIONS and all clinic LOCATIONS.  Some patients might share a location. Some clinics might share a location. 

Do you have a macro that can calculate the distance between a FROM and TO location? Does it take in just one pair of FROM and TO locations?  If so does in aggregate the results into a common dataset?  Or does it take in a dataset that has multiple FROM/TO location pairs? 

Can you provide a pair of LAT/LONG values that you are trying to find the distance between?  Can you show working code that calculates the distance for that pair (without any macro variable or macro logic)?

So assuming you have successfully created a dataset with all FROM/TO pairs you can use a step like this to generate one macro call for each pair.   Let's call the dataset with the FROM/TO locations as PAIRS.  And use variable names  PatientLatitude,PatientLongitude and ClinicLatitude,ClinicLongitude as the variables with the LAT/LONG pairs.  And let's assume you have created a macro named DISTANCE() that takes as input those four values and appends the result to same common dataset.  Let's call that dataset DISTANCE.

Then the step to generate the macro calls might look like:

data _null_;
  set pairs;
  call execute(cats('nrstr(%distance)'
    ,'(',PatientLatitude,',',PatientLongitude
    ,',',ClinicLatitude ,',',ClinicLongitude
    ,')'
  ));
run;

[ark123]

Hi Tom, 

Thanks for your response. The code will not work because SAS isn't recognizing it correctly (the 'run' statement is black rather than the typical blue). I can select and run, but nothing happens. The issue starts here on 'attempt 1':

%macro distance_time;
 
%do j=1 %to 5 /*&Observations*/;
data _null_;
nrec = &j;
set Addresses point=nrec;
%let ll1=addr1;
%let ll2=addr2;
stop;
run;
 
filename x url "https://www.google.com/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;

The code will not work because SAS isn't recognizing it correctly (the 'run' statement is black rather than the typical blue). I can select and run, but nothing happens on 'attempt 2'. 

%macro distance
  (patientID=
  ,patientLatitude=
  ,patientLongitude=
  ,clinicID=
  ,clinicLatitude=
  ,clinicLongitude=
  ,out= mapmacro ) ;

filename x url "https://www.google.com/maps/dir/?api=1&origin=&patientLatitude.,&patientLongitude.&destination=&clinicLatitude.,&clinicLongitude.&travelmode=driving";
filename z temp;

%local filesize ;
 
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;

I should mention that I do not have experience with macros and have never calculated distance using SAS before. 

Where I am starting: I have two datasets. One contains the latitude and longitude of 180,000 residential addresses. The other contains the latitude and longitude of 192 health clinics.

Desired outcome: I would like to know the distance and travel time between each residential address and its' nearest health clinic.
(I do not have specific pairs that I am trying to find distances to/from).

(I have no distances calculated or macro written yet).

(I don't have any pairs to calculate distances between because I first need to know which health clinic is nearest the residence which would require a distance calculation to be run).

(I am starting from the beginning with just these two datasets).

Hope this helps. Let me know if there is anything else

[Tom]

The color in the editor might not make any difference.  The editor is NOT SAS.  It is just trying to guess how SAS will interpret your code and add colors based on those guesses.

The beginning of your first macro makes no sense.

%macro distance_time;
 
%do j=1 %to 5 /*&Observations*/;
data _null_;
nrec = &j;
set Addresses point=nrec;
%let ll1=addr1;
%let ll2=addr2;
stop;
run;
 
filename x url "https://www.google.com/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;

The macro processor works BEFORE the resulting generated code is forwarded to SAS to interpret and run.  So that first data step does nothing since you actually ran this code:

%do j=1 %to 5 /*&Observations*/;
%let ll1=addr1;
%let ll2=addr2;
data _null_;
nrec = &j;
set Addresses point=nrec;
stop;
run;

I suspect you wanted to do something like this instead. 

%do j=1 %to 5 /*&Observations*/;

data _null_;
  set Addresses firstobs=&j obs=&j;
  call symputx('ll1',addr1);
  call symputx('ll2',addr2);
run;

[sbxkoenk]

---

@ark123 wrote:

I reviewed similar request for help, but I have been unsuccessful.  

---

Have you tried this one? :
Find driving distance (google maps) using a list of 10 hospital addresses and individual zipcodes
https://communities.sas.com/t5/SAS-Programming/Find-driving-distance-google-maps-usinga-list-of-10-h...

Koen

[ark123]

Yes, I referenced this post. You'll notice the code is same in one of my posted attempts. These are the two support posts I referenced, but they did not work for me or I am missing something.

https://communities.sas.com/t5/SAS-Programming/Find-driving-distance-google-maps-usinga-list-of-10-h...

https://communities.sas.com/t5/SAS-Programming/Find-shortest-distance-between-one-LAT-LONG-and-list-...