DATA Step, Macro, Functions and more

Distance Calculation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Distance Calculation


SAS_M  SAS_F
myzip  facility_zip    
22180  33180
22181  33181
22182  33182
22183  33183
22184  .....
22185  numobs=270 zip codes
22186
22187
22188
22189
22190
.....
.....
.....
numobs=3000 zip codes

 

SAS_M and SAS_F are two SAS datasets each containing only a column of zip. SAS_M has 3000 rows and SAS_F has 270 rows.
ZIPCITYDISTANCE function works fine for a pair of zips. I need some sort of looping that can do the followings,

ZIPCITYDISTANCE(22180,33180) then
ZIPCITYDISTANCE(22180,33181) then
ZIPCITYDISTANCE(22180,33182) then
ZIPCITYDISTANCE(22180,33183) then
ZIPCITYDISTANCE(22181,33180) then
ZIPCITYDISTANCE(22181,33181) and soforth.
............................

As said the ZIPCITYDISTANCE function works fine when you only have a pair of zips. In my case I need the distance between
first zip from SAS_M to every row of zip in the SAS_F dataset and then the second zip from the SAS_M to every zip rows of
SAS_F dataset and soforth.

Thank you for your help.
* ZIPCITYDISTANCE function;
 d1 = zipcitydistance(12203,27513);


Accepted Solutions
Solution
‎03-14-2018 09:46 AM
Super User
Posts: 10,532

Re: Distance Calculation

Posted in reply to mauri0623

SQL should do it:

proc sql;
create table want as
select
  a.myzip,
  b.facility_zip,
  zipcitydistance(a.myzip,b.facility_zip) as distance
from sas_m a, sas_f b;
quit;

as it automatically creates a cartesian join.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎03-14-2018 09:46 AM
Super User
Posts: 10,532

Re: Distance Calculation

Posted in reply to mauri0623

SQL should do it:

proc sql;
create table want as
select
  a.myzip,
  b.facility_zip,
  zipcitydistance(a.myzip,b.facility_zip) as distance
from sas_m a, sas_f b;
quit;

as it automatically creates a cartesian join.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 9

Re: Distance Calculation

Posted in reply to KurtBremser

Thank you. I will try.

Super User
Super User
Posts: 9,813

Re: Distance Calculation

Posted in reply to mauri0623

If you need all rows of the second calculated against the rows from the first, them merge 1 to many:

data want;
  merge myzip (in=a) facility_zip (in=b;
  if a;
  diff=zipdistance(myzip,facility_zip);
run;

Note, not tested, post test data in the form of a datastep, and show required output.  We are not here to type this in for you!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 112 views
  • 2 likes
  • 3 in conversation