**************************************************************************************************** * PROGRAM OVERVIEW **************************************************************************************************** * * PROGRAM: addstatetozip3.sas * Created (mm/dd/yyyy): 08/09/2017 * *-------------------------------------------------------------------------------------------------- * PURPOSE: The macro adds state based on 3 Digit Zip Code. * Program inputs: * - 3 Digit Zip * * Program outputs: * - 3 Digit Zip/State * * PARAMETERS: * * Programming Notes: * * *-------------------------------------------------------------------------------------------------- * CONTACT INFO: * Sentinel Coordinating Center * info@sentinelsystem.org * ***************************************************************************************************; %macro addstatetozip3(data=); %put =====> MACRO CALLED: addstatetozip3; proc sql noprint; create table _tempzip as select distinct statecode, substr(zip,1,3) as zip3 from infolder.&zipfile.; quit; /* recode the following 3 digit zips: 1) 063 = CT 2) 205 = DC 3) 739 = OK 4) 834 = ID 5) 969 = GU These 3 digit zip codes correspond to multiple states as of 1/1/2017. If new discordant states arise, a warning will be written to the log. The decision was made to assign to 1 state */ data _tempzip; set _tempzip; if zip3 = '063' then statecode = 'CT'; if zip3 = '205' then statecode = 'DC'; if zip3 = '739' then statecode = 'OK'; if zip3 = '834' then statecode = 'ID'; if zip3 = '969' then statecode = 'GU'; run; proc sql noprint; create table ziplookup as select distinct zip3, statecode from _tempzip; quit; %put WARNING: Due to 3 zip codes mapping to multiple states, the following 3 digit zip codes have been recoded: 1) 063 = CT, 2) 205 = DC, 3) 739 = OK, 4) 834 = ID, 5) 969 = GU; /*Check to ensure no additional states need to be reclassified*/ proc sql noprint; create table _recodedstates as select zip3, count(zip3) as zip_count from _tempzip group by zip3 having zip_count > 1 and zip3 not in ('063', '205', '739', '834', '969'); quit; %ISDATA(dataset=_recodedstates); %if %eval(&nobs. > 1) %then %do; %put WARNING: Additional 3 digit zip codes map to multiple states. Further investigation recommended. See output dataset for these zip and state codes; data output.discrepant_states; set _recodedstates; run; %end; proc sql noprint undo_policy=none; create table &data. as select x.* , y.statecode from &data. as x left join ziplookup as y on x.zip3 = y.zip3; quit; data &data. (drop = statecode); set &data.; if zip3 not in ("Missing", "Invalid") and missing(zip3) = 0 then zip3 = strip(zip3)||"/"||strip(statecode);; run; %put =====> END MACRO: addstatetozip3; %mend addstatetozip3;