Search code examples
sassas-macro

SAS program, How to do the calculation based on two tables without IML

I've been struggling with some matrix calculation in sas. I have two tables, which both contain Longitude and Latitude information

Tasks table

enter image description here

Engineers table:

enter image description here

My output goal is the distance between tasks and engineers

Distances:

enter image description here

Due to the additional cost for SAS/IML, I don't have it on premises. Does someone have any experience how to do that?

P.S. The code in R is easy as follows

   distances <- matrix(nrow = NROW(tasks), ncol = NROW(cses))
   for (i in 1:NROW(tasks)){
     for(j in 1:NROW(cses)){
       distances[i,j] <- distm (c(tasks$CUST_LNG_X[i], tasks$CUST_LAT_Y[i]), c(cses$LNG_X[j], cses$LAT_Y[j]), fun = distHaversine)

     }
   }
Solution

  • Maybe I'm missing something but a cross join should also work fine with the GEODIST function and then a transpose to make it wide. It doesn't matter how many points are in each file, though I suppose if it got big enough SQL may error out due to memory.

    proc sql;
create table want as
select task_id as col1, id as col2, geodist(a.x, a.y, b.x, b.y) as distance
from tasks as a , engineers as b;
quit;

proc transpose data=want out=want_wide ;
by col1;
id col2;
var distance;
run;