Difficulties using macro with variables and storing results

[veronicasalmaso]

Hello SAS Experts!

I'm having some trouble using MACRO, I can't figure it out.

I have a dataset with the following variables: subject_ID, latitude, longitude

and another dataset with: hospital_id, hospital_lat, hospital_lon.

Let's pretend this is my dataset with the subjects:

subject_ID	latitude	longitude
1	10.15	45.73
2	10.25	45.83
3	10.35	45.93
4	10.45	46.03
5	10.55	46.13

And this is the on with the hospitals:

hospital_id	hospital_lat	hospital_lon
1	10.65	46.23
2	10.75	46.33
3	10.85	46.43

I want to create a dataset that looks like this:

subject_ID	hospital_ID	distance (km)	distance (minutes)
1	1	-	-
1	2	-	-
1	3	-	-
2	1	-	-
2	2	-	-
2	3	-	-

To do that, I tried using a macro.

%MACRO distance(lat1, lon1);

filename filejsn "P:\Folder\macro.json" lrecl = 32000;

proc http
method="POST"
url="https://api.openrouteservice.org/v2/directions/driving-car/json"
ct="application/json"
oauth_bearer="5b3ce3597851110001cf6248da7564cd409644daae4f2eafdc8f3662"
in="{""coordinates"": [[&lat1,&lon1],[10.65, 46.23]] }"
out = filejsn;
run;

libname mac JSON fileref= filejsn;

data dist; set mac.routes_summary; 

%MEND distance;

%distance(lat1 = 10.15, lon1 = 45.73);

The output data dist gives me the distance between the first subject and the first hospital, exactly what I'm looking for!

How can I make sure to get the results without changing manually the coordinates everytime?

Thank you a lot for your collaboration!
I'm using SAS Enterprise Guide, 9.4M6 release

[PaigeMiller]

How can I make sure to get the results without changing manually the coordinates everytime?

Since your latitude/longitude coordinates are in a SAS data set, you could use CALL EXECUTE to call the macro using values in the data set. There are examples at the link of extracting data from a database that can be used in macro call.

[Astounding]

This looks like an all combinations join, where this would do the trick:

proc sql noprint;
create table want as select * from subjects, hospitals;
quit;

No macro language needed.  You just need to begin with the two SAS data sets you describe as the inputs.

[Reeza · Accepted solution]

1. Cross join table to get all location information in one table.
2. Redesign macro to take both locations as parameters (not just &lat1, &lon1) and the ID (hospital and subject) as well.
3. Modify macro to append results to a master table, including the IDs
4. Use CALL EXECUTE to call the macro for each line in table 1.
5. Ensure that the output is dropped before each run to avoid duplicate results.
6. If your datasets are large, you can add some filters using the flying distance before you call the API for the driving distance using geodist() function.

[Quentin]

I worked through a very similar exercise in this thread:

https://communities.sas.com/t5/SAS-Programming/Find-driving-distance-google-maps-usinga-list-of-10-h...

It basically implements the approach that @Reeza listed.  Since you've already got a version of the simple macro working, you should be able to use that post as an example of one way to extend it to work for multiple patients and multiple hospitals.