topic Re: Difficulties using macro with variables and storing results in SAS Enterprise Guide

Difficulties using macro with variables and storing results

veronicasalmaso — Mon, 16 Oct 2023 13:18:24 GMT

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

Re: Difficulties using macro with variables and storing results

PaigeMiller — Mon, 16 Oct 2023 13:22:22 GMT

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.

Re: Difficulties using macro with variables and storing results

Astounding — Mon, 16 Oct 2023 14:05:52 GMT

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.

Re: Difficulties using macro with variables and storing results

Reeza — Mon, 16 Oct 2023 15:54:34 GMT

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.

Re: Difficulties using macro with variables and storing results

Quentin — Mon, 16 Oct 2023 17:21:22 GMT

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-hospital/m-p/884611

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.