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
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.
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.
I worked through a very similar exercise in this thread:
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.