05-30-2014 03:21 PM
I am preparing a de-identified version of a dataset. I am required to achieve a minimum of 3 records in each reported geographic area. My original data has zip codes in it, and I'd like to progressively redact digits to reduce the specificity of the zip code data enough to meet the sample size requirements.
For example, if the data look like this:
|Zip Code||Sample Size|
I would need to group the first four codes together and the last two codes together to get a minimum of 3 observations in each cell. I would do this by stripping off the last digit:
|Zip Code||Sample Size|
Does anyone have advice on an efficient way to loop through the data to do this? I'm interested in efficiency because my dataset is very large and takes a LONG time to run!
05-30-2014 04:31 PM
What will you do if you have something like this:
If you truncate to 9010x you have two groups of 3 with the same value.
And do you have any zip+4 codes such as 90100-1234?
Have you previously built a data set with the summary counts for zipcode? Subset the data for those less than 3 and I'd try making a format from that data to see the results.
Also if the resulting code is supposed to imply geographically continguous areas you may have some issues due to some organizations receiving enough mail that the PO will assign them their own Zip code.
05-30-2014 05:30 PM
In the first case, I think I'd "merge" with the (a) geographically more proximal of the two, or (b) the one with the smaller sample size (90100).
In the second case, the two groups of 3 with code 9010X would ultimately be collapsed into a single group with a size of 6, since none of those individual zips can stand alone.
I don't have any zip+4 codes - they are all just straight zips. I used Proc SQL to build the summary dataset, and could definitely start by sub-setting to just those zips that are going to be a problem. I am a little concerned about the geographic contiguity issue, but I don't really know how to best deal with it. I'm sure there's some way to write a script that can preferentially merge proximal areas, but I don't know how to do that.
05-30-2014 06:54 PM
The zipcode contiguity issue may be solved using Zipcitydistance function to give you a distance between likely candidates and then pick the closest.
Or possible look at the clustering procedures using that distance OR bring in the latitude and longitude from the sashelp.zipcode data set and use the x y for clustering.