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.
