04-11-2015 08:26 PM
You have to be more specific with your question. Do you want to find, say, the ten zip codes from file2 that are closest to the zip codes identified in each of file1's records?
04-11-2015 11:48 PM
So if I understand right you want to assign a Zip code to the Senior Executive with minimal summed up distance to the Executives.
To determine this Zip code: Does it need to be one of the Zip codes of the Executives or should it be another Zip code "in the center" of all the Executives.
04-12-2015 12:17 AM
A GIS software is better at this than SAS, but it's doable in SAS.
ZIP codes represent area's though they're represented by the centroid latitude and longitude most of the time. You can create a distance table of the ZIP to ZIP code (40thousand rows*40 thousand Columns) or a table with ZIP1, ZIP2, Distance which would be 1.6Billion rows. You could simplify this by deleting distances over 1000 KM or so, or some other rule that makes business sense.
Then you can loop and assign an area for each executive, defining rules for overlaps. The rules may need to be different in rural vs urban areas.
There's probably a more efficient way of doing this - for example if it was Canada, I know all the Postal Codes starting with T belong to Alberta, and T0 is an rural area. I'm not sure if the ZIP code has such a structure. Also, there may be more efficient ways of programming this - hopefully someone smarter than me chimes in .
In a GIS software such as ArcGIS or QGIS this is as simple as selecting nearest neighbours within a specific distance and then making sure you have no overlaps - much easier
04-12-2015 01:07 PM
There is a geodist function SAS(R) 9.4 Functions and CALL Routines: Reference, Third Edition Remember it is physical direct distance line, NOT the distance by travelling hours.
How many senior executive you have 100 100? small enough the be a column representation?
04-13-2015 04:21 AM
Kumar, Now the problem is becoming interesting. You can cluster your zipcodes in the number you are needing by nearest distance and than map the executives on that or you have some fixed locations for those executives and than trying to map zip to them. It is all on the soft and hard specs en reqs with the goals behind those.
There must be a lot of publications on the more theoretical parts. There will be often not a best solutions but many times an acceptable one (time/effort change/situation)