BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
veronicasalmaso
Obsidian | Level 7

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_IDlatitudelongitude

1

10.1545.73
210.2545.83
310.3545.93
410.4546.03
510.5546.13

 

And this is the on with the hospitals:

hospital_idhospital_lathospital_lon
110.6546.23
210.7546.33
310.8546.43

 

 

I want to create a dataset that looks like this:

subject_IDhospital_IDdistance (km)distance (minutes)
11

-

-
12--
13--
21--
22--
23--

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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.

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Astounding
PROC Star

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
Super User
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
Super User

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.

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

SAS Enterprise Guide vs. SAS Studio

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1193 views
  • 6 likes
  • 5 in conversation