I am trying to count respondents of a survey.. However the count has to be by address. Is there any kind of code that I can write to count address... Since addresses are so messy and start with charcters I am so lost on how to do it.
Right now I have it sorted by surveycd and house number but I need to count the address.
example of the data
Survey Number HouseNumber Street Address ZipCode
1 227 sue lane 254664
1 221 marsh lane 255486
3 2222 people street 25564
The house number is sorted but I can't count because I don't know how to count the street name.
SELECT count(*) FROM
(SELECT DISTINCT zipcode, StreetAddress, HouseNumber FROM mydata);
This assumes that the StreetAddress variable is typed and spelled consistently. "sue lane" and "sue ln" would be considered different streets by the SQL code. If your data are large, you may want to invest in a program to regularize the StreetAddress field (MelissaData.com has some). You could do some of that in the data step with the perl expressions, but it is messy to address all the possibilities. Just dropping the "lane" or "street" is not sufficient; in Durham, NC, we have "Chapel Hill Rd", "Chapel Hill St", and "Chapel Hill Blvd" within the same ZIP code.
Street names are unique within ZIP code, so you need that for the uniqueness.