Calling PROC HTTP in a loop

[DanHouston]

I want to be able to call an API using PROC HTTP in a loop of a SAS dataset.

Basic idea is:

 - I have a SAS dataset with latitude and longitude

 - Loop over the latitude's and longitudes in the dataset and call proc HTTP to calculate distance and time

 - Return the distance and time to be stored in the dataset

I'm really struggling to even get this started in SAS. I've made it as far as creating a macro that I can call once, but no idea how to call it as part of a loop or use the results to store with the data.

%macro get_dist (stop1,stop2);
	filename out temp;
	
	 proc http
	   url="https://<HIDDEN>/NAServer/Route/solve"
		method=POST
		in = FORM("stops"="&stop1;&stop2"
				 "returnRoutes"="FALSE"
				 "returnDirections"="TRUE"
				 "outputLines"="esriNAOutputLineNone"
				 "directionsOutputType"="esriDOTSummaryOnly"
				 "f"="JSON")
		out=out;
	run;

	libname dph_dist JSON fileref=out;
	
	/*show me the distance data*/
	proc print data=dph_dist.DIRECTIONS_SUMMARY;
	run;
%mend;

%get_dist('-91.183,30.449','-81.508,28.69');

[Reeza · Accepted solution]

Someone did this last year using the Google API - if you search on here you should find the answer, including the looping part. For distance/time is that driving distance? Travelling distance? If it's distance as the crow flies, then you can do that within SAS.

You would just need to modify that one for ESRI rather than Google.

EDIT: the answer didn't use a loop as the Google API returns a distance matrix, which you're trying to do manually.

Instead of a loop you can pass the values to a macro repeatedly using CALL EXECUTE once you get the process worked out.

Assuming your code works (can't test it) this should get you started with one approach, there are others.

%macro get_dist (stop1,stop2, N);
	filename out temp;
	
	 proc http
	   url="https://<HIDDEN>/NAServer/Route/solve"
		method=POST
		in = FORM("stops"="&stop1;&stop2"
				 "returnRoutes"="FALSE"
				 "returnDirections"="TRUE"
				 "outputLines"="esriNAOutputLineNone"
				 "directionsOutputType"="esriDOTSummaryOnly"
				 "f"="JSON")
		out=out;
	run;

	libname dph_dist JSON fileref=out;
	
	/*show me the distance data*/
	proc print data=dph_dist.DIRECTIONS_SUMMARY;
	run;

        data _distance_&N;
           set dph_dist.directions_summary;
       run;
            
%mend;

*call macro for each line in data set;
data _null_;
set locationFile;
*check that str is created to match the macro call;
str = catt(
  '%get_dist(', 
                 stop1,
                   ", ", 
                  stop2, 
                   ", ", 
                  _N_,
                  ");"
              );
call execute(str);
run;

*combine results into one table, by combining all tables that start with _distance_;
data matrix_distance;
set _distance_:;
run;

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

---

@DanHouston wrote:

I want to be able to call an API using PROC HTTP in a loop of a SAS dataset.

Basic idea is:

 - I have a SAS dataset with latitude and longitude

 - Loop over the latitude's and longitudes in the dataset and call proc HTTP to calculate distance and time

 - Return the distance and time to be stored in the dataset

I'm really struggling to even get this started in SAS. I've made it as far as creating a macro that I can call once, but no idea how to call it as part of a loop or use the results to store with the data.

%macro get_dist (stop1,stop2);
	filename out temp;
	
	 proc http
	   url="https://<HIDDEN>/NAServer/Route/solve"
		method=POST
		in = FORM("stops"="&stop1;&stop2"
				 "returnRoutes"="FALSE"
				 "returnDirections"="TRUE"
				 "outputLines"="esriNAOutputLineNone"
				 "directionsOutputType"="esriDOTSummaryOnly"
				 "f"="JSON")
		out=out;
	run;

	libname dph_dist JSON fileref=out;
	
	/*show me the distance data*/
	proc print data=dph_dist.DIRECTIONS_SUMMARY;
	run;
%mend;

%get_dist('-91.183,30.449','-81.508,28.69');

---

[DanHouston]

Thank you Reeza! I  had seen several google examples, but not that did the looping portion. Your code is extremely helpful, I am working through it now.

[DanHouston]

I made a few adjustments, which I am sharing should anyone need/want them.

%macro get_dist (stop1, stop2, source, target);
	filename out temp;
	
	 proc http
	   url="https://<HIDDEN>/NAServer/Route/solve"
		method=POST
		in = FORM("stops"="&stop1;&stop2"
				 "returnRoutes"="FALSE"
				 "returnDirections"="TRUE"
				 "outputLines"="esriNAOutputLineNone"
				 "directionsOutputType"="esriDOTSummaryOnly"
				 "f"="JSON")
		out=out;
	run;

	libname dph_dist JSON fileref=out;

	data distance_new (drop=ordinal_directions ordinal_summary replace=YES);
		set dph_dist.directions_summary;
		length source $8 target $8;
		source=&source;
		target=&target;
	run;

	proc append base=distance data=distance_new;
	run;
	
%mend;

data _null_;
	set rdc_adc_distance;

	stop1 = catt("'",rdc_longitude,",",rdc_latitude,"'");
	stop2 = catt("'",adc_longitude,",",adc_latitude,"'");
	src=catt("'",source,"'");
	tgt = catt("'",target,"'");
	str = catt('%get_dist(',stop1,',',stop2,',',src,',',tgt,')');
	put str;
	call execute(str);
run;