@SASKiwi Below is the code from the post you linked. The solution to the post is below but since this is the first I am using a Macro, I don't understand where to make the changes in the syntax as suggested in the solution. Any advice would be very much appreciated! (I didn't include my log since it is already such a long post but I can if that would be helpful). *Convert latitude and longitude into character variables and then concatenate them into a single variable;
data data.HospIDLatLong;
set data.HospID_geocoded;
yc=input(y,$15.); drop y; rename yc=y;
xc=input(x,$15.); drop x; rename xc=x;
latc=input(lat,$15.); drop lat; rename latc=lat;
longc=input(long,$15.); drop long; rename longc=long;
addr1=catx(',',y,x);
addr2=catx(',',lat,long);
run;
*Create version of the dataset with only ID and coordinates;
data data.CBTI;
set data.HospIDLatLong;
keep id addr1 addr2;
run;
*Check the dataset;
proc print data=data.CBTI;
run;
*Verify coordinate variables are character;
proc contents data=data.CBTI;
run;
*Place number of observations in a macro variable;
data _null_;
call symputx('Observations',obs);
stop;
set data.CBTI nobs=obs;
run;
*Delete any data set named DISTANCE_TIME that might exist in the WORK library;
proc datasets lib=work nolist;
delete distance_time;
quit;
*Create a macro that contains a loop to access Google Maps multiple times;
* create a macro that contains a loop to access Google Maps multiple time;
%macro distance_time;
* delete any data set named DISTANCE_TIME that might exist in the WORK library;
proc datasets lib=work nolist;
delete distance_time;
quit;
%do j=1 %to 5/*&Observations*/;
data _null_;
nrec = &j;
set data.CBTI point=nrec;
%let ll1=addr1;
%let ll2=addr2;
stop;
run;
* lat/long of centroid of zip 12203 hard-coded as part of the URL;
filename x url "https://www.google.com/maps/dir/&ll1/&ll2/?force=lite";
filename z temp;
* same technique used in the example with a pair of lat/long coodinates;
data _null_;
infile x recfm=f lrecl=1 end=eof;
file z recfm=f lrecl=1;
input @1 x $char1.;
put @1 x $char1.;
if eof;
call symputx('filesize',_n_);
run;
* drive time as a numeric variable;
data temp;
infile z recfm=f lrecl=&filesize. eof=done;
input @ 'miles' +(-15) @ '"' distance :comma12. text $30.;
units = scan(text,1,'"');
text = scan(text,3,'"');
* convert times to seconds;
select;
* combine days and hours;
when (find(text,'d') ne 0) time = sum(86400*input(scan(text,1,' '),best.),
3600*input(scan(text,3,' '),best.));
* combine hours and minutes;
when (find(text,'h') ne 0) time = sum(3600*input(scan(text,1,' '),best.),
60*input(scan(text,3,' '),best.));
* just minutes;
otherwise time = 60*input(scan(text,1,' '),best.);
end;
output;
keep distance time;
stop;
done:
output;
run;
filename x clear;
filename z clear;
* add an observation to the data set DISTANCE_TIME;
proc append base=distance_time data=temp;
run;
%end;
%mend;
* use the macro;
%distance_time;
*
add variables from original data set to new data set distance_time
use geodist function to calculate straight line distance
;
data distance_time;
set distance_time;
set data.CBTI point=_n_;
straight_line = round(geodist(&ll1/&ll2,'DM'), 0.01);
run;
proc print data=distance_time noobs label;
var x y time distance straight_line zip city statecode;
format zip z5. time time6. ;
run; Posted Solution: You can use options mlogic mprint symbolgen; to get additional debug info in the log for macros. In your macro there is the following data step : data _null_;
nrec = &j;
set CBTI point=nrec;
%let ll1=addr1;
%let ll2=addr2;
stop;
run; Here, you symply create two macrovariables ll1 and ll2 which resolve respectively to addr1, addr2, so the instruction : filename x url "https://www.google.com/maps/dir/&ll1/&ll2/?force=lite"; will become filename x url "https://www.google.com/maps/dir/addr1/addr2/?force=lite"; Is that really what you want ? If you want to use the values of dataset columns addr1 and addr2, use instead : call symputx('ll1',addr1,'g'); Note that i used the 'g' parameter to ensure that the resulting macrovariable is global. Indeed, the error you obtain in the log comes from the fact that you use macrovariables ll1 and ll2 outside the macro that defined them.
... View more
