Hello. I have a dataset with patient addresses and another dataset with doctor zip codes. I'd like to figure out the driving distance between each patient and the closest doctor. I previously used this code to figure out the closest straight-line distance:
PROC SQL;
CREATE TABLE CBTI.PatientProviderDistance AS
SELECT A.ID, A.PatientZIP, A.Y, A.X, A.PatientCity, A.PatientState, B.LAT, B.LONG, B.ProviderCity, B.ProviderState, GEODIST(A.Y, A.X, B.LAT, B.LONG, 'M') AS Distance
FROM CBTI.GeoCodedPatientCity A, CBTI.GeocodedProviderList B
GROUP BY A.ID
HAVING CALCULATED DISTANCE EQ MIN(CALCULATED DISTANCE)
AND NOT MISSING(CALCULATED DISTANCE);
QUIT;
But now I need to figure out the shortest driving distance for each patient. I could use the resulting dataset from the code above and just figure out the driving distance for each patient and the doctor within the shortest straight-line distance. That may not exactly be the doctor with the closest drive, but it's very likely to be the same doctor. Here's some code I modified based on what I found online. I concatenated the patients' and closest doctors' coordinates as addr1 and addr2. The problem is I can't get the macro to run. I get an error message saying "Bad request. Use the debug option for more info."
Here's the code:
%macro ShortDrDist(j=);
*Create loop to access each observation;
%do i=1 %to &j;
*Assign macro variables needed;
data _null_;
set test;
if _n_=&i then do;
call symput('addr1',addr1);
call symput('addr2',addr2);
call symput('Subjid',id);
end;
run;
*Find Road distances;
filename gmaps url
"https://www.google.com/maps/dir/&addr1/&addr2";
data drdistX&i (drop=sitecode idrivdist rdstart);
infile gmaps recfm=f lrecl=1000 end=eof;
input sitecode $1000.;
id=&subjid;
if find(sitecode,'miles');
idrivdist=find(sitecode,'miles');
rdstart=idrivdist-1;
drivdist=input(compress(scan(substr(sitecode,1,rdstart),1,' "','bc'),','),best12.);
run;
*Create dataset retaining only the shortest distance;
proc sql;
create table drdist&i as
select id,
min(drivdist) as drivdist
from drdistx&i
group by id;
quit;
proc print data=drdist&1;
run;
filename gmaps clear;
%end;
%mend;
%ShortDrDist(j=10);
The first error is that your text string is too long or may have unbalanced quotes
NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks. MPRINT(SHORTDRDIST): filename gmaps url "https://www.google.com/maps/dir/28.5383,-81.3792 /33.7490,-84.3880 ";
Probably not a legal URL ... however to fix this, try this (note the change to using CALL SYMPUTX)
data _null_;
set test;
if _n_=&i then do;
call symputx('addr1',addr1);
call symputx('addr2',addr2);
call symputx('Subjid',id);
end;
run;
See if that makes any difference.
The problem is I can't get the macro to run. I get an error message saying "Bad request. Use the debug option for more info."
Use this command before you run the macro, and then run the macro again
options mprint;
Then show us the full LOG by clicking on the {i} icon and pasting the log into the window that appears. Do not skip this step.
Thanks for your reply. I ran that option and reran the macro. I do see an expanded log now. I see that the URL is now looking strange. It seems to include hard returns. Here's what it looks like with fake coordinates:
MPRINT(SHORTDRDIST): *Find Road distances; NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks. MPRINT(SHORTDRDIST): filename gmaps url "https://www.google.com/maps/dir/28.5383,-81.3792 /33.7490,-84.3880 ";
Am I right to assume the URL including the hard returns is causing the issue? Any suggestions for how to avoid this?
The {i} icon is in the icon bar above where you type your reply. Please include the ENTIRE log, not just the ERRORs, by using the {i} icon. Thanks.
Thanks for pointing that out. Here's the entire log (with the fake coordinates):
674 options mprint; 675 676 %macro ShortDrDist(j=); 677 *Create loop to access each observation; 678 %do i=1 %to &j; 679 680 *Assign macro variables needed; 681 data _null_; 682 set test; 683 if _n_=&i then do; 684 call symput('addr1',addr1); 685 call symput('addr2',addr2); 686 call symput('Subjid',id); 687 end; 688 run; 689 690 *Find Road distances; 691 filename gmaps url 692 "https://www.google.com/maps/dir/&addr1/&addr2"; 693 694 data drdistX&i (drop=sitecode idrivdist rdstart); 695 infile gmaps recfm=f lrecl=1000 end=eof; 696 input sitecode $1000.; 697 id=&subjid; 698 if find(sitecode,'miles'); 699 idrivdist=find(sitecode,'miles'); 700 rdstart=idrivdist-1; 701 drivdist=input(compress(scan(substr(sitecode,1,rdstart),1,' "','bc'),','),best12.); 702 run; 703 704 *Create dataset retaining only the shortest distance; 705 proc sql; 706 create table drdist&i as 707 select id, 708 min(drivdist) as drivdist 709 from drdistx&i 710 group by id; 711 quit; 712 proc print data=drdist&1; 713 run; 714 filename gmaps clear; 715 %end; 716 %mend; 717 %ShortDrDist(j=1); MPRINT(SHORTDRDIST): *Create loop to access each observation; MPRINT(SHORTDRDIST): *Assign macro variables needed; MPRINT(SHORTDRDIST): data _null_; MPRINT(SHORTDRDIST): set test; MPRINT(SHORTDRDIST): if _n_=1 then do; MPRINT(SHORTDRDIST): call symput('addr1',addr1); MPRINT(SHORTDRDIST): call symput('addr2',addr2); MPRINT(SHORTDRDIST): call symput('Subjid',id); MPRINT(SHORTDRDIST): end; MPRINT(SHORTDRDIST): run; NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column). 3:170 NOTE: There were 697 observations read from the data set WORK.TEST. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(SHORTDRDIST): *Find Road distances; NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks. MPRINT(SHORTDRDIST): filename gmaps url "https://www.google.com/maps/dir/28.5383,-81.3792 /33.7490,-84.3880 "; MPRINT(SHORTDRDIST): data drdistX1 (drop=sitecode idrivdist rdstart); MPRINT(SHORTDRDIST): infile gmaps recfm=f lrecl=1000 end=eof; MPRINT(SHORTDRDIST): input sitecode $1000.; MPRINT(SHORTDRDIST): id= 169; MPRINT(SHORTDRDIST): if find(sitecode,'miles'); MPRINT(SHORTDRDIST): idrivdist=find(sitecode,'miles'); MPRINT(SHORTDRDIST): rdstart=idrivdist-1; MPRINT(SHORTDRDIST): drivdist=input(compress(scan(substr(sitecode,1,rdstart),1,' "','bc'),','),best12.); MPRINT(SHORTDRDIST): run; ERROR: Bad request. Use the debug option for more info. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.DRDISTX1 may be incomplete. When this step was stopped there were 0 observations and 2 variables. WARNING: Data set WORK.DRDISTX1 was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.07 seconds cpu time 0.00 seconds MPRINT(SHORTDRDIST): *Create dataset retaining only the shortest distance; MPRINT(SHORTDRDIST): proc sql; MPRINT(SHORTDRDIST): create table drdist1 as select id, min(drivdist) as drivdist from drdistx1 group by id; NOTE: Table WORK.DRDIST1 created, with 0 rows and 2 columns. MPRINT(SHORTDRDIST): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: Line generated by the invoked macro "SHORTDRDIST". 7 proc print data=drdist&1 - 22 200 7 ! ; run; filename gmaps clear; ERROR: File WORK.DRDIST.DATA does not exist. MPRINT(SHORTDRDIST): proc print data=drdist&1 run; ERROR 22-322: Syntax error, expecting one of the following: ;, (, BLANKLINE, CONTENTS, DATA, DOUBLE, GRANDTOTAL_LABEL, GRANDTOT_LABEL, GRAND_LABEL, GTOTAL_LABEL, GTOT_LABEL, HEADING, LABEL, N, NOOBS, NOSUMLABEL, OBS, ROUND, ROWS, SPLIT, STYLE, SUMLABEL, UNIFORM, WIDTH. ERROR 200-322: The symbol is not recognized and will be ignored. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE PRINT used (Total process time): real time 0.01 seconds cpu time 0.00 seconds MPRINT(SHORTDRDIST): filename gmaps clear; NOTE: Fileref GMAPS has been deassigned.
The first error is that your text string is too long or may have unbalanced quotes
NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks. MPRINT(SHORTDRDIST): filename gmaps url "https://www.google.com/maps/dir/28.5383,-81.3792 /33.7490,-84.3880 ";
Probably not a legal URL ... however to fix this, try this (note the change to using CALL SYMPUTX)
data _null_;
set test;
if _n_=&i then do;
call symputx('addr1',addr1);
call symputx('addr2',addr2);
call symputx('Subjid',id);
end;
run;
See if that makes any difference.
Thanks very much. This fixed it. Have a great day.
You also have an issue with the macro variable in this bit at the end of your macro:
proc print data=drdist&1;
The digit 1 by itself is not an acceptable macro variable name. From context I suspect that you intended to use an &i .
Good catch! I appreciate your help.
Yes, I got it to run when I manually entered the coordinates for one patient. The issue seems to be the URL. Here's log info I posted in response to another reply:
MPRINT(SHORTDRDIST): *Find Road distances; NOTE: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks. MPRINT(SHORTDRDIST): filename gmaps url "https://www.google.com/maps/dir/28.5383,-81.3792 /33.7490,-84.3880 ";
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.