qrp_report

****************************************************************************************************

*                                           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;