One LAST try! A colleage of mine suggested removing the recfm option. In the meantime, I refined the code a bit to get the best distance approximation Google offers. Try the following:
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; %road(input,output,startAddr,startCity,startSt,endAddr,endCity,endSt)
Let me/us know if that works,
Art, CEO, AnalystFinder.com
Thanks Sir. I used your code exactly and I got the results this time. But here is the catch-the example just has 4 start addresses and 4 end addresses. In the real data set, there are around 106 start addresses and 60 end addresses. When i ran the code on real data set, it only gave me the distances between start address and end address in the same row-nothing like what output data set gave from your example code. I'm attaching the final output dataset as excel for you to see what it gave me. The input dataset is same except the distance field. Ideally what it should have given is the distance from each start address to end address (as in your example output) and then i would have to aggregate picking the shortest one.
I really appreciate your help till now.
If you send me your two datasets, and the code you used (other than the macro), I'd be glad to take a look. You can send them to me, directly, at art@analystfinder.com
However, given 106 X 60 addresses (i.e., 6360 combinations), you would have to separate the data to run 1/3rd each day, as Google has a limit of 2500 per day unless you pay for a premium account.
Art, CEO, AnalystFinder.com
I just sent you an email. I believe premium account may be worth it.
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
Thanks again! We have storm here, so not able to work. I will follow up with your email and this extra information tomorrow morning hopefully.
Fortunately, the storm appears to have missed Toronto 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.