Just for your simple example code like below should do.
Data NewData;
INFILE DATALINES DSD;
input id $ name $ addy ~$25. city $ state $ zip $ addy_want ~$25. year $;
DATALINES;
01,ABCSTORE,123 MAIN HIGHWAY 75,MOBILE,AL,36619,123 MAIN HIGHWAY,2011
01,ABCSTORE,123 MAIN HIGHWAY 75,MOBILE,AL,36619,123 MAIN HIGHWAY,2012
01,ABCSTORE,123 MAIN STREET,MOBILE,AL,36619,123 MAIN STREET,2013
01,ABCSTORE,123 MAIN ST,MOBILE,AL,36619,123 MAIN STREET,2014
01,ABCSTORE,123 MAIN,MOBILE,AL,36619,123 MAIN,2015
;
data standardize;
set NewData;
addy_stdz=compbl(upcase(addy));
addy_stdz=prxchange('s/^(.*?)\d*$/$1/i',1,strip(addy_stdz));
addy_stdz=prxchange('s/^(.*?)\.? *st$/$1 STREET/i',1,strip(addy_stdz));
addy_stdz=prxchange('s/^(.*?)\.? *ave$/$1 AVENUE/i',1,strip(addy_stdz));
run;
Address cleansing gets quickly very involved and you need to find the balance between effort and quality you need.
If this is a one-off with a not to high data volume then eventually some programmatical standardization and then some manual checks with further amendments are quickest.
If it's a one-off with a bigger data volume then consider using some specialized 3rd party provider for address cleansing (besides of rule based approaches such providers also use address databases against which they can verify addresses).
If it's something you will need to do regularly for high data volumes then it's may-be worth to consider the purchase of specialized software (like SAS DataQuality/DataFlux) or to contract with a 3rd party provider.
... View more