topic Re: Trying to get driving distance using Google maps using coordinates in New SAS User

Trying to get driving distance using Google maps using coordinates

SASDatesKillMe — Tue, 24 Sep 2019 18:57:01 GMT

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);

Re: Trying to get driving distance using Google maps using coordinates

PaigeMiller — Tue, 24 Sep 2019 19:03:41 GMT

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.

Re: Trying to get driving distance using Google maps using coordinates

Reeza — Tue, 24 Sep 2019 19:34:22 GMT

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?

Re: Trying to get driving distance using Google maps using coordinates

SASDatesKillMe — Tue, 24 Sep 2019 19:46:50 GMT

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?

Re: Trying to get driving distance using Google maps using coordinates

PaigeMiller — Tue, 24 Sep 2019 19:47:23 GMT

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.

Re: Trying to get driving distance using Google maps using coordinates

SASDatesKillMe — Tue, 24 Sep 2019 19:50:10 GMT

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.

Re: Trying to get driving distance using Google maps using coordinates

SASDatesKillMe — Tue, 24 Sep 2019 19:50:34 GMT

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

                                                          ";

Re: Trying to get driving distance using Google maps using coordinates

PaigeMiller — Tue, 24 Sep 2019 19:55:49 GMT

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.

Re: Trying to get driving distance using Google maps using coordinates

ballardw — Tue, 24 Sep 2019 21:27:58 GMT

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 .

Re: Trying to get driving distance using Google maps using coordinates

SASDatesKillMe — Wed, 25 Sep 2019 12:06:09 GMT

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

Re: Trying to get driving distance using Google maps using coordinates

SASDatesKillMe — Wed, 25 Sep 2019 12:06:30 GMT

Good catch! I appreciate your help.