Example 2: US Counties

libname MAPSCSTM "C:\Data\mapscstm";
libname VALIB "C:\SAS\VA\Lev1\SASApp\Data\valib";

%let REGION_LABEL=US Counties; /* The label for the custom region */
%let REGION_PREFIX=CS; /* unique ISO 2-Letter Code - make sure it doesn't conflict with any code! */
%let REGION_ISO=000; /* unique ISO Code - make sure it doesn't conflict with any other code! */
%let REGION_DATASET=MAPSCSTM.US_COUNTIES1; /* Name of the polygon data set to be created - be sure 
   to use suffix "1" */
%let REGION_SHP_FILE=C:\shp\US\cb_2015_us_county_500k.shp; /* From  
   https://www.census.gov/geo/maps-data/data/cbf/cbf_counties.html */
%let REGION_SHP_ID_COL=GEOID; /* the column representing the unique ID of a polygon */
%let REGION_SHP_NAME_COL=NAME; /* the column representing the name of the polygon */
%let REGION_SHP_DENSITY = 3; /* Value between 1 (low) - 10 (high) describing the detail level/density. */

/* Utility macro to stop processing in case of any validation errors */
%let ERROR_COUNT=0; /* 0 indicates no error */
%let ERROR_MESSAGE=;

%MACRO ErrorTest;
   %GLOBAL ERROR_COUNT;

   %IF &ERROR_COUNT gt 0 %THEN
      %DO;
         %PUT ERROR: &ERROR_MESSAGE;
         %abort cancel;
         %let ERROR_COUNT=0; /* reset */
      %END;
%MEND;

/* [BACKUP] Create a backup of the original lookup tables if required */
%macro backupLookupTable(dsn);
   %if %sysfunc(exist(&dsn._original)) %then
      %do;
         %put INFO: Backup table &dsn found.;
      %end;
   %else
      %do;
         data &dsn._original;
            set &dsn;
         run;
      %end;
%mend backupLookupTable;

%backupLookupTable(valib.attrlookup);
%backupLookupTable(valib.centlookup);

/* [VALIDATION STEP] Check for unique 2-letter ISO code */
proc sql noprint;
   /* count and create error code if greater than zero */
   select count(ID) as count,
      case 
         when calculated count > 0 then "2-letter ISO code &REGION_PREFIX. is not unique in ATTRLOOKUP. Please specify a different code."
         else "" 
      end
   into :ERROR_COUNT, :ERROR_MESSAGE
      from valib.attrlookup_original
         where upcase(ID) eq upcase("&REGION_PREFIX.");
quit;

%ErrorTest;

/* [VALIDATION STEP] Check for unique ISO code */
proc sql noprint;
   /* count and create error code if greater than zero */
   select count(ISO) as count,
      case 
         when calculated count > 0 then "ISO code &REGION_ISO. is not unique in ATTRLOOKUP. Please specify a different ISO code." 
         else "" 
      end
   into :ERROR_COUNT, :ERROR_MESSAGE
      from valib.attrlookup_original
         where ISO eq "&REGION_ISO.";
quit;

%ErrorTest;

/* Remove any previous entries if required */
proc sql;
   delete from valib.attrlookup where ID like "&REGION_PREFIX.%";
   delete from valib.centlookup where ID like "&REGION_PREFIX.%";
quit;

/* Step 1: Import the SHP file into SAS */
PROC MAPIMPORT DATAFILE="&REGION_SHP_FILE."
   OUT=&REGION_DATASET.;
   ID &REGION_SHP_ID_COL.;
RUN;

/* Add the DENSITY column to the polygon data set used to reduce the polygon density in the visualization */
proc greduce data=&REGION_DATASET. out=&REGION_DATASET.;
   id &REGION_SHP_ID_COL.;
run;

/* Determine the column type of REGION_SHP_ID_COL */
data _null_;
   set &REGION_DATASET.(obs=1);
   call symputx('REGION_SHP_ID_COL_TYPE', vtype(&REGION_SHP_ID_COL.));
run;

/* Step 2: Region lookup can either be via IDLABEL (IDNAME) or ID. In case we lookup by IDLABEL let's generate a 
   unique name. The following steps need to be adjusted depending on your source data.

   As there are multiple counties across the US with the same name – the code snippet below is adjusted to 
   include the county ID. This combination of name and id makes the IDNAME unique across the regions.
*/
proc sql;
   create table work.idname_fmt as
   select distinct
      "idname" as FMTNAME,
      "&REGION_SHP_ID_COL_TYPE." as TYPE,
      &REGION_SHP_ID_COL. as START,
      trim(left(&REGION_SHP_NAME_COL.)) || " (" || trim(left(&REGION_SHP_ID_COL.)) || ")" as LABEL
   from   &REGION_DATASET.;
quit;


/* [VALIDATION STEP] Check for duplicate names in the IDNAME lookup data set. With duplicate entries here the idname based lookup would not work.
If you have names listed here, revisit the previous step to make sure each LABEL is unique, for example, by merging other parent level details */
proc sql noprint;
   create table work.nonunique_names 
      as select distinct LABEL, count(LABEL) as count
         from work.idname_fmt 
            group by LABEL 
               having calculated count > 1   
                  order by count desc;

   /* count and create error code if greater than zero */
   select count(*) as count,
      case 
         when calculated count > 0 then compress(put(calculated count,8.)) 
         || " duplicate names have been found. Please check the idname_fmt table for duplicates."
         || " A list of duplicate names are in WORK.NONUNIQUE_NAMES."
         else "" 
      end
   into :ERROR_COUNT, :ERROR_MESSAGE
      from work.nonunique_names;
quit;

%ErrorTest;

proc format cntlin=work.idname_fmt;
run;

/* [VALIDATION STEP] Check for required columns in the input polygon data set. */
data _null_;
   dsid = open("&REGION_DATASET.");
   array colNames(5) $50 _temporary_ ("&REGION_SHP_ID_COL.", "X", "Y", "SEGMENT", "DENSITY");

   do i = lbound(colNames) to hbound(colNames);
      check = varnum(dsid, colNames(i));

      if check eq 0 then
         do;
            call symputx('ERROR_COUNT', 1);
            call symputx('ERROR_MESSAGE',"Column " || compress(colNames(i)) || " is required in &REGION_DATASET.");
            stop;
         end;
   end;
run;

%ErrorTest;

/* [VALIDATION STEP] Check for conflicting columns in the input polygon data set. */
data _null_;
   array colNames(7) $50 _temporary_ ("IDNAME", "LONG", "LAT", "RESOLUTION", "LAKE", "ISOALPHA2", "AdminType");

   do i = lbound(colNames) to hbound(colNames);
      if colNames(i) eq "&REGION_SHP_ID_COL." then
         do;
            call symputx('ERROR_COUNT', 1);
            call symputx('ERROR_MESSAGE',"Column name for REGION_SHP_ID_COL conflicts with predefined column " || compress(colNames(i)) || ".");
            stop;
         end;
   end;
run;

%ErrorTest;

/* Step 3: Create the final polygon data set with the required set of columns. We are going to create a new
shorter ID column here as the original ID columns are often long and unnecessarily complex. Note, we also apply 
the previously created idname format to the IDNAME column. 
*/
data &REGION_DATASET.;
   set &REGION_DATASET.(keep=&REGION_SHP_ID_COL. X Y SEGMENT DENSITY);

   /* create new (shorter) ID */
   length _TMP_ $60. _SID_ 8.;

   if (_n_ eq 1) then
      do;
         _SID_ = 1;
         _TMP_ = &REGION_SHP_ID_COL.;
      end;

   if _TMP_ ne &REGION_SHP_ID_COL. then
      _SID_ = _SID_ + 1;
   _TMP_ = &REGION_SHP_ID_COL.;
   retain _TMP_ _SID_;
   drop _TMP_ _SID_;
   _RID_ = compress("&REGION_PREFIX.-" || put(_SID_,8.));
   IDNAME = put(&REGION_SHP_ID_COL., idname.);
   LONG = X;
   LAT = Y;
   ISO = "&REGION_ISO.";
   RESOLUTION = 1;
   LAKE = 0;
   ISOALPHA2 = "&REGION_PREFIX.";
   AdminType = "regions";
   where density<&REGION_SHP_DENSITY.;
   keep _RID_ SEGMENT IDNAME LONG LAT X Y ISO DENSITY RESOLUTION LAKE ISOALPHA2 AdminType;
   rename _RID_=ID;
run;

/* Step 4: Add a custom region (country) to ATTRLOOKUP - a new entry will appear in the country selection list */
proc sql;
   insert into valib.attrlookup
      values ( 
         "&REGION_LABEL.",         /* IDLABEL=State/Province Label */
         "&REGION_PREFIX.",        /* ID=SAS Map ID Value */
         "&REGION_LABEL.",         /* IDNAME=State/Province Name */
         "",                       /* ID1NAME=Country Name */
         "",                       /* ID2NAME */
         "&REGION_ISO.",           /* ISO=Country ISO Numeric Code */
         "&REGION_LABEL.",         /* ISONAME */
         "&REGION_LABEL.",         /* KEY */
         "",                       /* ID1=Country ISO 2-Letter Code */
         "",                       /* ID2 */
         "",                       /* ID3 */
         "",                       /* ID3NAME */
         0                         /* LEVEL (0=country level, 1=state level) */
         );
quit;

/* Register each single region in the lookup data set */
proc sql;
   insert into valib.attrlookup
      select distinct 
         IDNAME,                             /* IDLABEL=State/Province Label */
         ID,                                 /* ID=SAS Map ID Value */
         IDNAME,                             /* IDNAME=State/Province Name */

         "&REGION_LABEL.",                   /* ID1NAME=Country Name */
         "",                                 /* ID2NAME */
         "&REGION_ISO.",                     /* ISO=Country ISO Numeric Code */
         "&REGION_LABEL.",                   /* ISONAME */
         trim(IDNAME) || "|&REGION_LABEL.",  /* KEY */

         "&REGION_PREFIX.",                  /* ID1=Country ISO 2-Letter Code */
         "",                                 /* ID2 */
         "",                                 /* ID3 */
         "",                                 /* ID3NAME */
         1                                   /* LEVEL (1=state level) */
   from &REGION_DATASET.;
quit;

/* Register custom region in CENTLOOKUP */
proc sql;
   /* Add custom region */
   insert into valib.centlookup
      select distinct
         "&REGION_DATASET." as mapname,
         "&REGION_PREFIX." as ID,
         avg(x) as x,
         avg(y) as y
      from &REGION_DATASET.;

   /* Add custom provinces */
   insert into valib.centlookup
      select distinct
         "&REGION_DATASET." as mapname,
         ID as ID,
         avg(x) as x,
         avg(y) as y
      from &REGION_DATASET.
         group by id;
quit;

/* Step 5: (optional) Creates a validation data set which can be used to validate the newly added regions in SAS Visual Analytics */
proc sql;
   create table &REGION_DATASET._VALIDATE as 
      select distinct ID as ID, 
         IDNAME as NAME 
      from &REGION_DATASET.;
   create table &REGION_DATASET._VALIDATE as 
      select *, 
         round(ranuni(1) * 10000) as measure1, 
         round(ranuni(1) * 100000) as measure2 format=dollar20.0 
      from &REGION_DATASET._VALIDATE
         group by ID, NAME 
            order by ID, NAME;
quit;