At the suggestion of others, I'll restate my initial problem. I have two tables of location data, one origins and one destinations. One of the columns in both of these tables is lat_longs containing latitude and longitudes in the form for example: 40.77657419,-73.87393838 40.76251880,-73.98145280 40.77657419,-73.87393838 ... I want to use these lat-longs in a proc http call to a Google Distance Matrix API that has the url format: url="https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=lat1,long1|lat2,long2|...|latn,longn&destinations=lat1,long1|lat2,long2|...|latn,longn&key=<API_KEY>" The individual lat-longs already are combined with a the comma between them as desired, but we need to combine the separate lat-longs with a '|' character and then reference them in the url. Additionally, the syntax for Google's url use the "&" character which I now see needs to be masked during macro compilation. Building on PaigeMiller's reply, I think this code is sufficient for the problem. proc sql;
select distinct Lat_long into :origins separated by '|' from Location_Data1;
quit;
proc sql;
select distinct Lat_long into :destinations separated by '|' from Location_Data2;
quit;
filename resp temp;
proc http
url="https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial/%nrstr(&)origins=&origins%nrstr(&)destinations=&destinations%nrstr(&)key=<API_KEY>"
out=resp;
run;
libname posts JSON fileref=resp;
title "Raw values from the JSON response";
proc datasets lib=posts; quit;
run; This creates tables Elements_Distance and Elements_Duration in the Posts library each with column "value" that contains the distances/durations.
... View more