BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASDatesKillMe
Obsidian | Level 7

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);
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller

View solution in original post

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
SASDatesKillMe
Obsidian | Level 7

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?

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
SASDatesKillMe
Obsidian | Level 7

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.

 

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
SASDatesKillMe
Obsidian | Level 7

Thanks very much. This fixed it. Have a great day.

ballardw
Super User

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 .

SASDatesKillMe
Obsidian | Level 7

Good catch! I appreciate your help.

Reeza
Super User
Did you get this to run, not as a macro, but as just base code?

Are we debugging a macro or the access to Google and extracting the data?
SASDatesKillMe
Obsidian | Level 7

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

                                                          ";

SAS Innovate 2025: Register Now

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!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 10 replies
  • 1317 views
  • 0 likes
  • 4 in conversation