DATA Step, Macro, Functions and more

Compare distance/driving time between two cities

Reply
Occasional Contributor
Posts: 5

Compare distance/driving time between two cities

Hello. I have code below for calculating the shortest distance between a patient's zip code and their nearest specialty medical provider. The problem is that while I have patient zip codes, I only have city and state for a long list of medical providers. I'd be grateful for help with finding the shortest distance between a patient's zip code and the city of their nearest medical provider. I also figure that this code is probably not ideal, as I have city/state for over 400 doctors. Any suggestions for how to do this more efficiently than this?

 

data PatientZipDoctorCityState;
set PatientZipDoctorCityState;
if not missing(PatientZip) then PatientZipCode=put(PatientZip,$z5.);
Doctor1 =put(00000,$z5.);
Doctor2	=put(00000,$z5.);
Doctor3	=put(00000,$z5.);
Doctor4	=put(00000,$z5.);
Doctor5	=put(00000,$z5.);
Doctor6	=put(00000,$z5.);
Doctor7	=put(00000,$z5.);
Doctor8 =put(00000,$z5.);
Doctor9	=put(00000,$z5.);

if not missing(PatientZipCode) then distance=zipcitydistance(PatientZipCode,Doctor1);
array calc[9] Doctor1-Doctor9;
do i=1 to 9;
	if not missing(PatientZip) 
		then distance=min(distance, zipcitydistance(PatientZip,calc(i))) ;
end;
drop Doctor1-Doctor9 i;
run;
PROC Star
Posts: 8,115

Re: Compare distance/driving time between two cities

Posted in reply to SASDatesKillMe

Depending upon what (if anything) you want to output when you can't calculate distance, proc sql may be easier code to maintain.

 

e.g., the following will only output a record if there is a non-missing minimum distance:

data PatientZip;
  do zip=15101,15100,15116,15123,15102,15122,15123,15102,15110,15112,15116,15120;
    Patient+1;
    output;
  end;
run;

data DoctorCityState;
  input doctor zip;
  cards;
1 15101
2 15102
3 15104
4 15106
5 15108
6 15210
7 15212
8 15216
9 15220
;

proc sql;
  create table PatientZipDoctorCityState as
    select a.patient, a.zip,
           zipcitydistance(a.Zip,b.zip) as distance
      from PatientZip a, DoctorCityState b
        group by patient
          having calculated distance eq min(calculated distance)
                 and not missing(calculated distance)
  ;
quit;

Of course it can also be done using an array, but I wouldn't do it the way you did. Absolutely no reason to keep all of the doctor ids/names and zips for each patient. However, I won't suggest a modification if you decide to go with the sql method instead.

 

Art, CEO, AnalystFinder.com

 

 

 

 

Occasional Contributor
Posts: 5

Re: Compare distance/driving time between two cities

This provides exactly what I need, which is the distance between the patient's zip code and the nearest doctor. But do you know of a way to do this with city and state data instead of zip codes? I only have city and state for each doctor, but I have zip code for each patient.

Super User
Posts: 22,874

Re: Compare distance/driving time between two cities

Posted in reply to SASDatesKillMe

For each city, either take a representative ZIP that works for you or find all zips allocated to that city and use an average of the latitude/longitude instead. You're going to have make assumptions or some rule, so just make it consistent and if you have GIS skills and/or are working with a small area make a map so you understand what those assumptions may mean. 

 

If you have lat/long for zips you can use GEODIST() instead. 

 

SAS has several files with the lat/long for cities as well as the lat/long for zip codes. 

They're in the SASMAPS and GFSK libraries these days. 

 

PROC Star
Posts: 8,115

Re: Compare distance/driving time between two cities

Posted in reply to SASDatesKillMe

Not sure how you would select a centralized zip for a large city. Here is a start, at least, for getting zip from city and state:

/* create DoctorCityState for testing purposes; */
data need(drop=wantzip);
  input doctor wantzip;
  city=scan(zipcity(wantzip),1,',');
  state=zipstate(wantzip);
  cards;
1 15101
2 15102
3 15104
4 15106
5 15108
6 15210
7 15212
8 15216
9 15220
;

proc sql;
  create table DoctorCityState as
    select a.doctor,a.city,a.state,b.zip
      from DoctorCityState a,
           sashelp.zipcode b
        group by a.doctor,b.city,b.state
          having upcase(a.city) eq upcase(b.city)
                 and upcase(a.state) eq upcase(b.statecode)
                 and b.zip eq min(b.zip)
  ;
quit;
/* END: create DoctorCityState for testing purposes; */

Art, CEO, AnalystFinder.com

 

Super User
Posts: 13,084

Re: Compare distance/driving time between two cities

Posted in reply to SASDatesKillMe

See if you have access to SAS/GIS with a road map set. This is really a GIS problem as a short distance may equal a long drive time. I can show you some 15 mile drives that are much longer to drive than 30 or 40 miles of freeway.

 

Alternatively this might be a linear programming program if you have a database of from/to coordinates with travel time/distance information. If you have, or can find such information, that could be a SAS/OR problem.

Super User
Posts: 22,874

Re: Compare distance/driving time between two cities

Posted in reply to SASDatesKillMe

Do you have the street address of the clinic, if not the ZIP? If so, SAS has a geocoding option that will geocode addresses. 

 

If you search LexJansen.com there's some papers on using Google to get driving distances instead of distances 'as the crow flies'. 

 

Ask a Question
Discussion stats
  • 6 replies
  • 103 views
  • 0 likes
  • 4 in conversation