Glad to hear that the macro is working properly. What you didn't do, but must, is separate the file into the two groups you want to compare, and run the proc sql code to obtain the Cartestian product (i.e., all pairs between the two datasets).
You also have to edit the raw data, as there are 3 or 4 records that include both an address and a PO Box # in the address field. I removed the PO Box part from each record and Google calculated distance for all pairs. Interestingly, one of your records only had a PO Box # for the address and Google didn't have any problem with it.
Like I said, cancel your Google key, get a new one, and NEVER share it on the web. Also, since your data requires 6,634 calculations, you'll either have to break it down into three sets of data to run over three days, or get a premium Google key.
Here is the code I ran:
data people;
infile cards dlm=',' dsd;
format startaddr $40.;
input startAddr startCity $ startSt $;
cards;
"306 Rodman Road","Auburn","ME"
"155 Center Street","Auburn","ME"
"28 Arsenal Street", "Augusta", "ME"
"9 Green Street", "Augusta", "ME"
;
data hospitals;
infile cards dlm=',' dsd;
format endaddr $40.;
input endAddr endCity $endSt $;
cards;
"57 Water St.", "Blue Hill", "ME"
"25 Hospital Drive", "Bridgton", "ME"
"163 Van Buren Rd. Suite 1" ,"Caribou" ,"ME"
"287 Main St. Suite 301", "Lewiston", "ME"
;
proc sql;
create table input as
select *
from people
,hospitals
;
quit;
%macro road(input,output,startAddr,startCity,startSt,endAddr,endCity,endSt);
/* Check if input data set exists; otherwise, throw exception */
%if %sysfunc(exist(&input))ˆ=1 %then %do;
data _null_;
file print;
put #3 @10 "Data set &input. does not exist";
run;
%abort;
%end;
/* Check if user specified output dataset name; otherwise, create default */
%if %length(&output) gt 1 %then %let outData=&output;
%else %let outData = &input._dist;
/* Replace all inter-word spaces with plus signs */
data tmp;
set &input;
addr1 = tranwrd(left(trim(&startAddr))," ","+")||","||
tranwrd(left(trim(&startCity))," ","+")||","||
left(trim(&startSt));
addr2 = tranwrd(left(trim(&endAddr))," ","+")||","||
tranwrd(left(trim(&endCity))," ","+")||","||
left(trim(&endSt));
recnum = _n_;
run;
data _NULL_;
if 0 then set tmp nobs=numobs;
call symputx("nObs",numobs); stop;
run;
%do i=1 %to &nObs;
/* Place starting and ending locations into macro variables */
data _null_;
set tmp(where=(recnum=&i));
call symput("addr1",trim(left(addr1)));
call symput("addr2",trim(left(addr2)));
run;
/* Determine road distance*/
options noquotelenmax;
filename google url "https://maps.googleapis.com/maps/api/distancematrix/json?origins=&addr2.%nrstr(&destinations=)&addr1.%nrstr(&key)=AIzaSyBsF0lfNZgJm0uuGsP3Hm0sSuFpGEA3m1o";
data dist(drop=_:);
infile google lrecl=1000;
format distance 8.3;
length _junk _distance _distance_long $20;
input @ '"distance"' _junk @;
input @ '"text" : "' _distance @;
input @ '"value" : ' _distance_long;
distance=input(compress(_distance_long,,'kd'),12.3);
run;
data dist;
merge tmp(where=(recnum=&i)) dist;
run;
/* Append to output dataset */
%if &i=1 %then %do;
data &outData;
set dist(drop=recnum addr:);
run;
%end;
%else %do;
proc append base=&outData data=dist(drop=recnum addr:) force;
run;
%end;
%end;
/* Delete the temporary dataset */
proc delete data=work.tmp;
run;
%mend;
proc import datafile="c:\art\input2a.xlsx"
out=have replace dbms=xlsx;
getnames=yes;
sheet='Sheet1';
run;
data people;
set have (keep=Main_Site_Name Sub_Site_Name endAddr endCity endSt
rename=(endAddr=startAddr endCity=startCity endSt=startSt)
where=(not missing(startAddr)));
run;
data hospitals;
set have (keep=Service_Location startAddr startCity startSt
rename=(startAddr=endAddr startCity=endCity startSt=endSt)
where=(not missing(endAddr)));
run;
proc sql;
create table input as
select *
from people
,hospitals
order by Sub_Site_Name, Service_Location
;
quit;
%road(input,output,startAddr,startCity,startSt,endAddr,endCity,endSt)
proc sort data=output;
by sub_site_name distance;
run;
data closest;
set output;
by sub_site_name;
if first.sub_site_name;
run;
I'd say your problem is now solved and you should mark it so.
Art, CEO, AnalystFinder.com
Click image to register for webinar
