Find driving distance (google maps) usinga list of 10 hospital addresses and individual zipcodes

[stats_auca]

Hello. I am fairly new to using SAS and I am trying to figure out a way to find the closest hospital for a large number of individuals using their zip code. I am only interested in 10 hospitals and I have exact addresses (and geocoded coordinates) for each hospital as well as  zip codes (with coordinates) for each individual. I then want to create a column of the driving distances in miles using Google maps to the nearest hospital for each individual. Below is a sample of my data (anonymized).

data WORK.HOSPITAL_CLINICS;
infile datalines dsd truncover;
input Name:$33. Street:$28. City:$10. State:$2. Zip:BEST12. Geocoded_Latitude:BEST12. Geocoded_Longitude:BEST12.;
format Zip BEST12. Geocoded_Latitude BEST12. Geocoded_Longitude BEST12.;
datalines;

Hospital name 123 Avenue City ST 55555 XX.XXXXXXXX -XX.XXXXXXXX
Hospital name 123 Avenue City ST 55555 XX.XXXXXXXX -XX.XXXXXXXX

Hospital name 123 Avenue City ST 55555 XX.XXXXXXXX -XX.XXXXXXXX

Hospital name 123 Avenue City ST 55555 XX.XXXXXXXX -XX.XXXXXXXX

Hospital name 123 Avenue City ST 55555 XX.XXXXXXXX -XX.XXXXXXXX

data WORK.ZIP_GEOCODE(label='Geocoded 12Jul2023');
infile datalines dsd truncover;
input Y:32. X:32. MSA:32. AREACODE:32. M_OBS:32. _MATCHED_:$16. ID:BEST12. Zip:32.;
format ID BEST12.;
label Y="Geocoded Latitude (DEGREES)" X="Geocoded Longitude (DEGREES)" MSA="Metro Statistical Area code by common pop-pre 2003; no MSA for rural" AREACODE="Single Area Code for ZIP Code.";
datalines;
XX.XXXXXX -XX.XXXXXX X 555 55555 ZIP XX 55555
XX.XXXXXX -XX.XXXXXX X 555 55555 ZIP XX 55555
XX.XXXXXX -XX.XXXXXX X 555 55555 ZIP XX 55555
XX.XXXXXX -XX.XXXXXX X 555 55555 ZIP XX 55555
XX.XXXXXX -XX.XXXXXX X 555 55555 ZIP XX 55555

[raavichouhan]

To find the driving distance using Google Maps between a list of 10 hospital addresses and individual zip codes, you can follow these steps using SAS:

Load the hospital and zip code data: Ensure that the hospital and zip code data are stored in SAS datasets named HOSPITAL_CLINICS and ZIP_GEOCODE, respectively. If the datasets are not already created, use the provided data and create the datasets accordingly.

Perform a Cartesian join: Combine the hospital and zip code datasets using a Cartesian join. This will create a dataset with all possible combinations of hospital and zip code pairs.

Calculate driving distance: Utilize the PROC GEOCODE procedure in SAS to calculate the driving distance between each hospital and zip code pair. This procedure allows you to specify the starting and ending coordinates (latitude and longitude) for each location. Use the GEOMAP statement to define the Google Maps service for driving directions.

Extract driving distances: After calculating the driving distances, extract the distances from the output dataset and create a new column in your desired format (miles).

I am sharing a code snippet as an example to demonstrate the process:

SAS Code

/* Step 2: Perform a Cartesian join */
proc sql;
    create table WORK.CARTESIAN_JOIN as
    select *
    from WORK.HOSPITAL_CLINICS
    cross join WORK.ZIP_GEOCODE;
quit;

/* Step 3: Calculate driving distance */
proc geocode data=WORK.CARTESIAN_JOIN out=WORK.DRIVING_DISTANCES;
    geocode lat=_GEOM_LAT_ lon=_GEOM_LON_ / service=GOOGLEMAPS;
    geomap mode=DRIVING;
run;

/* Step 4: Extract driving distances */
data WORK.DISTANCES;
    set WORK.DRIVING_DISTANCES;
    Driving_Distance_Miles = _DISTANCE_;
    drop _:;
run;

Make sure to adjust the dataset names and variables according to your actual data structure. This code will produce a dataset named DISTANCES with the driving distances in miles for each hospital and zip code pair.

Note: Ensure that you have proper API access and authorization to use the Google Maps service for geocoding and driving directions.

[stats_auca]

Thank you so much for the assistance. Step 1 worked perfectly but I am having difficulty troubleshooting Step 2. Both 'geocode' and 'geomap' are displayed in red:

/* Step 3: Calculate driving distance */
proc geocode data=WORK.CARTESIAN_JOIN out=WORK.DRIVING_DISTANCES;
    geocode lat=_GEOM_LAT_ lon=_GEOM_LON_ / service=GOOGLEMAPS;
    geomap mode=DRIVING;
run;

and I am getting the following syntax error:

15   proc geocode data=WORK.CARTESIAN_JOIN out=WORK.DRIVING_DISTANCES;
16       geocode = _GEOM_LAT_ lon=_GEOM_LON_ / service=GOOGLEMAPS;
         -------
         180
17       geomap = DRIVING;
         ------
         180
ERROR 180-322: Statement is not valid or it is used out of proper order.

18   run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DRIVING_DISTANCES may be incomplete.  When this step was stopped
         there were 0 observations and 0 variables.
WARNING: Data set WORK.DRIVING_DISTANCES was not replaced because this step was stopped.
NOTE: PROCEDURE GEOCODE used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


19   proc geocode data=WORK.CARTESIAN_JOIN out=WORK.DRIVING_DISTANCES;
20       geocode lat=_GEOM_LAT_ lon=_GEOM_LON_ / service=GOOGLEMAPS;
                 ---
                 22
                 76
21       geomap mode=DRIVING;
         ------
         180
ERROR 22-322: Syntax error, expecting one of the following: ;, ADDRESSCITYVAR,
              ADDRESSCOUNTRYVAR, ADDRESSPLUS4VAR, ADDRESSSTATEVAR, ADDRESSVAR, ADDRESSZIPVAR,
              ATTRIBUTEVAR, BEGINRANGEVAR, CITY, CUSTOM, DATA, DIRECT, ENDRANGEVAR, FIPS,
              LOOKUP, LOOKUPCITY, LOOKUPCITYVAR, LOOKUPCOUNTRYVAR, LOOKUPKEYVAR,
              LOOKUPLATVAR, LOOKUPLONGVAR, LOOKUPPLUS4VAR, LOOKUPSTATEVAR, LOOKUPSTREET,
              LOOKUPVAR, LOOKUPZIPVAR, LOOKUP_CITY, METHOD, NOCITY, NOSTIMER, NOZIP, OUT,
              PLUS4, RANGE, RANGEDATA, RANGEDECIMAL, RANGEKEYVAR, STATE, STREET, TYPE, ZIP.
ERROR 76-322: Syntax error, statement will be ignored.
ERROR 180-322: Statement is not valid or it is used out of proper order.
22   run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DRIVING_DISTANCES may be incomplete.  When this step was stopped
         there were 0 observations and 0 variables.
WARNING: Data set WORK.DRIVING_DISTANCES was not replaced because this step was stopped.
NOTE: PROCEDURE GEOCODE used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds

[SASKiwi]

I suggest you refer to the GEOCODE documentation for the correct syntax. This link is for SAS 9.4.

[stats_auca]

I have not successfully located a way to find driving distances for GPS coordinates with the Geocode Documentation.

[raavichouhan]

You can find correct syntax here- https://documentation.sas.com/doc/en/sasstudiocdc/3.8/pgmsascdc/grmapref/p087i29802bnfgn1qn9isdqzde0...

[stats_auca]

The link takes me to the geocode procedure, which, unless I am missing something, there is no syntax provided to find Google Map driving distances using GPS coordinates.

[SASKiwi]

@stats_auca - Here is a link to a Community post that has code calling the Google driving distance service with latitude and longitude. I agree with you that the SAS and Viya documentation provide no details of such functionality. The GEOCODE procedure code provided by @raavichouhan does not match the documentation.

[Quentin]

---

@SASKiwi wrote:

 The GEOCODE procedure code provided by @raavichouhan does not match the documentation 

I'm not making an accusation, but from the reading the text of @raavichouhan 's answer, and then looking at the code, I would suspect the answer may have come from ChatGPT (or similar).  The PROC GEOCODE looks like a reasonable-ish guess (prediction) of what the code to answer this question might look like:

proc geocode data=WORK.CARTESIAN_JOIN out=WORK.DRIVING_DISTANCES;
    geocode lat=_GEOM_LAT_ lon=_GEOM_LON_ / service=GOOGLEMAPS;
    geomap mode=DRIVING;
run;

It *looks* like SAS code, but it's not.  From my limited playing, chatGPT isn't that good at generating SAS code.  My guess is it's better at other more common languages (e.g. Python)  because there was much more python code on the internet to "learn" from. But even for Python, it feels to me like chatGPT is at the stage of being useful to programmers with experience in a language, I'm not sure it's good enough to be helpful to people learning a language.

[stats_auca]

@SASKiwithank you! I spent a lot of time looking through the community post you had linked in your post. The syntax looks promising and I was able to create a dataset to match the community post:

id addr1 addr2

However, starting with the below syntax, I don't understand exactly where the adjustments were made to the syntax in the solution post:

*Place number of observations in a macro variable;

I can share syntax if that would be helpful. Any advice or guidance would be greatly appreciated.

[SASKiwi]

@stats_auca - I didn't look very closely at the code except to understand that it could give you the answer you want. Please post the SAS log of your version of the code including the bit you are stuck on.

[stats_auca]

@SASKiwi  Below is the code from the post you linked. The solution to the post is below but since this is the first I am using a Macro, I don't understand where to make the changes in the syntax as suggested in the solution. Any advice would be very much appreciated! (I didn't include my log since it is already such a long post but I can if that would be helpful).

*Convert latitude and longitude into character variables and then concatenate them into a single variable;
data data.HospIDLatLong;
set data.HospID_geocoded;

yc=input(y,$15.); drop y; rename yc=y;
xc=input(x,$15.); drop x; rename xc=x;
latc=input(lat,$15.); drop lat; rename latc=lat;
longc=input(long,$15.); drop long; rename longc=long;

addr1=catx(',',y,x);
addr2=catx(',',lat,long);
run;

*Create version of the dataset with only ID and coordinates;
data data.CBTI;
set data.HospIDLatLong;
keep id addr1 addr2;
run;

*Check the dataset;
proc print data=data.CBTI;
run;

*Verify coordinate variables are character;
proc contents data=data.CBTI;
run;
*Place number of observations in a macro variable;
data _null_;
call symputx('Observations',obs);
stop;
set data.CBTI nobs=obs;
run;
 
*Delete any data set named DISTANCE_TIME that might exist in the WORK library;
proc datasets lib=work nolist;
delete distance_time;
quit;
 
*Create a macro that contains a loop to access Google Maps multiple times;
* create a macro that contains a loop to access Google Maps multiple time;
%macro distance_time;
 
* delete any data set named DISTANCE_TIME that might exist in the WORK library;
proc datasets lib=work nolist;
delete distance_time;
quit;
 
%do j=1 %to 5/*&Observations*/;
data _null_;
nrec = &j;
set data.CBTI point=nrec;
%let ll1=addr1;
%let ll2=addr2;
stop;
run;
 
* lat/long of centroid of zip 12203 hard-coded as part of the URL;
filename x url "https://www.google.com/maps/dir/&ll1/&ll2/?force=lite";
filename z temp;
 
* same technique used in the example with a pair of lat/long coodinates;
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 time as a numeric variable;
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 observation to the data set DISTANCE_TIME;
proc append base=distance_time data=temp;
run;
%end;
%mend;
 
* use the macro;
%distance_time;
 
*
add variables from original data set to new data set distance_time
use geodist function to calculate straight line distance
;
data distance_time;
set distance_time;
set data.CBTI 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;

Posted Solution:

You can use

options mlogic mprint symbolgen;

to get additional debug info in the log for macros.

In your macro there is the following data step :

data _null_;
nrec = &j;
set CBTI point=nrec;
%let ll1=addr1;
%let ll2=addr2;
stop;
run;

Here, you symply create two macrovariables ll1 and ll2 which resolve respectively to addr1, addr2, so the instruction :

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

will become

filename x url "https://www.google.com/maps/dir/addr1/addr2/?force=lite";

 Is that really what you want ? If you want to use the values of dataset columns addr1 and addr2, use instead :

call symputx('ll1',addr1,'g');

Note that i used the 'g' parameter to ensure that the resulting macrovariable is global.

Indeed, the error you obtain in the log comes from the fact that you use macrovariables ll1 and ll2 outside the

macro that defined them.

[Quentin]

From my quick read, the suggestion is to change:

data _null_;
  nrec = &j;
  set data.CBTI point=nrec;
  %let ll1=addr1;
  %let ll2=addr2;
  stop;
run;

to:

data _null_;
  nrec = &j;
  set data.CBTI point=nrec;
  call symputx('ll1',addr1,'g');
  call symputx('ll2',addr2,'g');
  stop;
run;

But as always with macro, since this is complex code, you may be better of starting with no macro code.  The macro is basically to loop through the list of 10 hospitals.  I would focus first on hard-coding an example that works for one hospital, and debugging that.  Once you have working code for one hospital, then the next step could be to make it a macro which iterates through a list (or maybe you might not even need a macro).  So basically, you want to separate the core problem of integrating SAS with google-maps to get the output you want, from the side-problem of running the solution repeatedly.  

That will also make it easier for people to help you.  Because if you post the input data you have for one hospital, along with the code to generate the driving distance for the one hospital, people can run it and try it themselves and help you get that working.  Then later, if you post working code for one hospital, other people can help turn it into a macro.

[Quentin]

As an example of starting with a hard-coded approach, if I take your code and update the URL to a have hardcoded lat and long, it looks like it's roughly working (I say roughly, because I'm not convinced the time calculation is correct). So I would start with getting something like this cleaned up and working like you want, for one trip from a zipcode to a hospital.

filename x url "https://www.google.com/maps/dir/?api=1%nrstr(&)origin=42.37,-71.11%nrstr(&)destination=35.79,-78.78%nrstr(&)travelmode=driving";
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 time as a numeric variable;
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;
put distance= time= ;
stop;
done:
output;
run;

As an alternative to this approach, which is scraping a TON of HTML, you might try using the google maps API to get this data.  https://developers.google.com/maps/documentation/distance-matrix/overview.  It's a bit more to set up, but it would return friendlier JSON.  Chris Hemedinger wrote a blog post about using a similar google API at https://blogs.sas.com/content/sasdummy/2017/04/14/using-sas-to-access-google-analytics-apis/.

[stats_auca]

Thank you so much! The hard-coded approach works perfectly. I removed the time calculation because I am only interested in driving distance and I agree it did not appear to be correct. When I then attempted to update the Macro, I noticed that the macro URL is not recognizing the origin or destination lat/long (see below). I am also wondering if my data is set up correctly. My goal is is to find the closest hospital to each individual  lat/longitude (roughly~10,000) and 10 hospital lat/longitudes. I also tried creating a separate dataset for each hospital with each hospital listed as addr2 but unfortunately that was not working either.

My data:

Obs   ID      addr1    addr2

   1       23      X,-X       

   2      28      X,-X       

  3       54     X,-X       

  4      Hosp#1          X,-X

  5      Hosp#2         X,-X

Hard-coded approach that is working:

filename x url "https://www.google.com/maps/dir/?api=1%nrstr(&)origin=25.79585457901094,-80.2799268941304%nrstr(&)destination=26.07420546642483,-80.15058074645368%nrstr(&)travelmode=driving";
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;

* driving distance as a numeric variable;
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,'"');
output; 
keep distance;
put distance=;
stop;
done:
output;
run;

Macro that is not working (URL does not seem to be correct but I am unsure how to correct it):

*Place number of observations in a macro variable;
data _null_;
call symputx('Observations',obs);
stop;
set data.CBTI nobs=obs;
run;
 
*Delete any data set named DISTANCE_TIME that might exist in the WORK library;
proc datasets lib=work nolist;
delete distance_time;
quit;
 
*Create a macro that contains a loop to access Google Maps multiple times;
%macro distance_time;
 
* delete any data set named DISTANCE_TIME that might exist in the WORK library;
proc datasets lib=work nolist;
delete distance_time;
quit;
 
%do j=1 %to 5/*&Observations*/;
data _null_;
  nrec = &j;
  set data.CBTI point=nrec;
  call symputx('ll1',addr1,'g');
  call symputx('ll2',addr2,'g');
  stop;
run;
 
* URL;
filename x url "https://www.google.com/maps/dir/?api=1%nrstr(&)origin=&111%nrstr(&)destination=&112%nrstr(&)travelmode=driving";
filename z temp;
 
* same technique used in the example with a pair of lat/long coordinates;
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;

* driving distance only as a numeric variable;
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,'"');
output; 
keep distance;
put distance=;
stop;
done:
output;
run;
 
filename x clear;
filename z clear;
 
* add an observation to the data set DISTANCE_TIME;
proc append base=distance_time data=temp;
run;
%end;
%mend;
 
* use the macro;
%distance_time;

 I also need to add the column "ID" to the data set distance_time but I have not been able to successfully add it.

Log:

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds



NOTE: The infile X is:

      Filename=https://www.google.com/maps/dir/?api=1&origin=&111&destination=&112&travelmode=
      driving,
      Local Host Name=X,
      Local Host IP addr=X,
      Service Hostname Name=X,
      Service IP addr=X,
      Service Name=X,
      Recfm=Fixed

NOTE: The file Z is:

      Filename=C:\Users\Local\Temp\SAS Temporary
      Files\_X,
      RECFM=F,LRECL=1,File Size (bytes)=0,
      Last Modified=19Jul2023:01:01:29,
      Create Time=19Jul2023:01:01:29

NOTE: 67544 records were read from the infile X.
NOTE: 67544 records were written to the file Z.
NOTE: DATA statement used (Total process time):
      real time           1.41 seconds
      cpu time            0.06 seconds



NOTE: The infile Z is:

      Filename=C:\Users\Local\Temp\SAS Temporary
      Files\_X,
      RECFM=F,LRECL=58755,File Size (bytes)=58755,
      Last Modified=19Jul2023:01:01:29,
      Create Time=19Jul2023:01:01:29

NOTE: 1 record was read from the infile Z.
NOTE: SAS went to a new line when INPUT @'CHARACTER_STRING' scanned past the end of a line.
NOTE: The data set WORK.TEMP has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.04 seconds


NOTE: Fileref X has been deassigned.
NOTE: Fileref Z has been deassigned.

NOTE: Appending WORK.TEMP to WORK.DISTANCE_TIME.
NOTE: BASE data set does not exist. DATA file is being copied to BASE file.
NOTE: There were 1 observations read from the data set WORK.TEMP.
NOTE: The data set WORK.DISTANCE_TIME has 1 observations and 1 variables.
NOTE: PROCEDURE APPEND used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds



NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds