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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.