Glad the macro works for you.
I made a small change to the macro when I was testing step 3. I realized that in your example data you have a LOT of precision for latitude and longitude, more than SAS can maintain in a numeric variable, so I made these variable character.
The updated macro:
%macro distance
(patientID=
,patientLatitude=
,patientLongitude=
,hospitalID=
,hospitalLatitude=
,hospitalLongitude=
,out= /*name of output dataset to append to*/
) ;
filename x url "https://www.google.com/maps/dir/?api=1%nrstr(&)origin=&patientLatitude%2C&patientLongitude%nrstr(&)destination=&hospitalLatitude%2C&hospitalLongitude%nrstr(&)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;
* driving distance as a numeric variable;
data __distance(keep=PatientID HospitalID Distance PatientLatitude PatientLongitude HospitalLatitude HospitalLongitude) ;
length PatientID $8 HospitalID $8 PatientLatitude PatientLongitude HospitalLatitude HospitalLongitude $20;
PatientID="&PatientID" ;
HospitalID="&HospitalID" ;
PatientLatitude="&PatientLatitude" ;
PatientLongitude="&PatientLongitude" ;
HospitalLatitude="&HospitalLatitude" ;
HospitalLongitude="&hospitalLongitude" ;
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= HospitalID= 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 ;
The first part of step 3 is to create the data. Suppose you start with a dataset of hospitals with the latitude and longitude of each hospital, and a dataset of patients:
data Hospitals ;
input ID $1 Latitude :$20. Longitude:$20. ;
cards ;
A 26.07420546642483 -80.15058074645368
B 26.08 -80.15
C 26.09 -80.15
;
run ;
data Patients ;
input ID $1 Latitude :$20. Longitude:$20. ;
cards ;
1 25.79585457901094 -80.2799268941304
2 25.71 -80.27
3 25.72 -80.27
;
run ;
The goal is to create a dataset of trips, which has all possible trips (Patient 1 to Hospital A, Patient 1 to Hospital B, etc.). You can use PROC SQL to make a dataset which is the cartesian product of patients and hospitals. So 3 patients * 3 hospitals=9 trips:
proc sql ;
create table trips as
select
Patients.ID as PatientID
,Patients.Latitude as PatientLatitude
,Patients.Longitude as PatientLongitude
,Hospitals.ID as HospitalID
,Hospitals.Latitude as HospitalLatitude
,Hospitals.Longitude as HospitalLongitude
from Patients,Hospitals
;
quit ;
So that data looks like:
Patient Hospital
ID PatientLatitude PatientLongitude ID HospitalLatitude HospitalLongitude
1 25.79585457901094 -80.2799268941304 A 26.07420546642483 -80.15058074645368
1 25.79585457901094 -80.2799268941304 B 26.08 -80.15
1 25.79585457901094 -80.2799268941304 C 26.09 -80.15
2 25.71 -80.27 A 26.07420546642483 -80.15058074645368
2 25.71 -80.27 B 26.08 -80.15
2 25.71 -80.27 C 26.09 -80.15
3 25.72 -80.27 A 26.07420546642483 -80.15058074645368
3 25.72 -80.27 B 26.08 -80.15
3 25.72 -80.27 C 26.09 -80.15
Then you can use that dataset to call the macro. Basically you want to call the %DISTANCE macro nine times, and each time you call it, you pass in the data for one trip. One way to do that is with CALL EXECUTE. With a DATA step, you can read the TRIPS data, and use CALL EXECUTE to generate a macro call from each record. The code is a bit ugly, and CALL EXECUTE is an advanced macro topic. But it's a handy method:
data _null_;
set trips;
call execute
(
'%nrstr('
|| '%distance'
|| '('
|| ' patientID=' || trim(patientID)
|| ',patientLatitude=' || trim(patientLatitude)
|| ',patientLongitude=' || trim(patientLongitude)
|| ',HospitalID=' || trim(HospitalID)
|| ',HospitalLatitude=' || trim(HospitalLatitude)
|| ',HospitalLongitude=' || trim(HospitalLongitude)
|| ',out=mydistances'
|| ' )'
|| ')'
);
run;
When that step runs, it will generate nine macro calls. The same as if you had typed nine macro calls yourself. If you look in the log, it will show the macro calls, e.g.:
NOTE: CALL EXECUTE generated line.
1 + %distance(
patientID=1,patientLatitude=25.79585457901094,patientLongitude=-80.2799268941304,HospitalID=A,Hospital
Latitude=26.07420546642483,HospitalLongitude=-80.15058074645368,out=mydistances )
After the macro calls have executed, work.mydistances will have nine records:
Patient Hospital
ID ID PatientLatitude PatientLongitude HospitalLatitude HospitalLongitude Distance
1 A 25.79585457901094 -80.2799268941304 26.07420546642483 -80.15058074645368 26.1
1 B 25.79585457901094 -80.2799268941304 26.08 -80.15 28.6
1 C 25.79585457901094 -80.2799268941304 26.09 -80.15 27.6
2 A 25.71 -80.27 26.07420546642483 -80.15058074645368 30.5
2 B 25.71 -80.27 26.08 -80.15 33.0
2 C 25.71 -80.27 26.09 -80.15 31.9
3 A 25.72 -80.27 26.07420546642483 -80.15058074645368 29.5
3 B 25.72 -80.27 26.08 -80.15 32.1
3 C 25.72 -80.27 26.09 -80.15 31.0
That has all possible trips. To select the shortest trip for each patient, you could sort by Patient and Distance, then select the first record for each patient.
As a side note, each time you call the macro, it will append to work.mydistances. So if you want to start fresh, you should delete it before calling the macro. You could put this code before the data _null_ step that runs CALL EXECUTE:
%if %sysfunc(exist(mydistances)) %then %do ;
proc delete data=mydistances ;
run ;
%end ;
BASUG is hosting
free webinars Next up:
Mark Keintz presenting History Carried Forward, Future Carried Back: Mixing Time Series of Differing Frequencies on May 8. Register now at the Boston Area SAS Users Group event page:
https://www.basug.org/events.
Click image to register for webinar
