/* census_rc.sas */
/* census conforming ethnic classification */
/* Based entirely on work by Joe Magruder.
 * Programmer: M. Armijo
 * Revision History: 
 * 2011.04.18 Basically a re-organization of Joe's program to conform to my
 * coding standard. 
 */

options SYNTAXCHECK ;

/* construct a table with needed columns only */
proc sql ;
create table E1 as
select
 IDENTIFIER as FKCLIENT_T
,P_ETHNCTYC length=4
,HISP_CD
from CWS.CLIENT_T
;

/* Using OMB/Census definitions, assign standard ethnic codes as race codes.
 * This means that Hispanic ethnicities are marked as missing race.
 * The Hispanic code is modified to include people where Hispanic Code is not
 * "Y" but a primary ethnicity is one of the Hispanic ethnicity codes;
 */


data E2 ;
length default=4 ;
set E1 ;
HISP_CDN=0 ;
select(P_ETHNCTYC) ;
   when(.,0,830,3162,3163,3164,3165,6351,6352,6453)                           P_RACE=0 ; /* Missing or Hispanic code */
   when(823,826)                                                              P_RACE=1 ; /* Black */
   when(839,840,841,842,843,844)                                              P_RACE=2 ; /* White */
   when(820,821)                                                              P_RACE=5 ; /* Native Am */
   when(5922,5923,822,824,825,827,828,829,831,832,833,834,835,836,837,838)    P_RACE=4 ; /* Asian */
   otherwise                                                                  P_RACE=9 ; /* Invalid Value */
end ;
if HISP_CD = "Y" then HISP_CDN = 1;
if P_ETHNCTYC in(830,3162,3163,3164,3165) then HISP_CDN = 1 ;
run ;

proc freq data = E2;
table P_RACE;
run;

/* Get data from client/substitute care provider ethnicity table. 
 * this table has secondary ethnicities for clients (and all ethnicity data
 * for substitute care providers). A client may have more than one secondary
 * ethnicity, i.e., more than one row;
 */

proc sql ;
create table E3 as
select
 E2.*
,CLSCP_ET.ETHNCTYC as SEC_ETHNIC length=4 
from E2 left join CWS.CLSCP_ET
on E2.FKCLIENT_T = CLSCP_ET.ESTBLSH_ID
order by FKCLIENT_T;
quit;

/* Assign standard ethnicity codes to secondary race codes as before. */

data E4 ;
length default=4 ;
set E3 ;
select(SEC_ETHNIC) ;
   when(.,0,830,3162,3163,3164,3165,6351,6352,6453)                          S_RACE=0 ; /* Missing or Hispanic code */
   when(823,826)                                                             S_RACE=1 ; /* Black */
   when(839,840,841,842,843,844)                                             S_RACE=2 ; /* White */
   when(820,821)                                                             S_RACE=5 ; /* Native Am */
   when(5922,5923,822,824,825,827,828,829,831,832,833,834,835,836,837,838)   S_RACE=4 ; /* Asian */ 
   otherwise                                                                 S_RACE=9 ; /* Invalid Value */
end ;
run ;

proc freq data = E4;
table S_RACE;
run;

proc freq data = E4;
table SEC_ETHNIC;
run;

/* Identify each race and hispanic status with a separate record for each
 * child/race combination.  Use this data to identify missing and mixed race
 * children as well as single race children and Hispanic status.
 * This creates three sets of tables: a base table with one row per child, a race
 * table with one or more rows for each child for whom a specific race is identified, 
 * and a hispanic ethnicity table with one or more rows for each child for whom Hispanic
 * ethnicity is identified whether by Hispanic Code, Primary Ethncity or Secondary Ethnicity.
 * These tables are later reduced to one row per person/race before being merged 
 * into a single file.
 */

data
E4_BASE (keep=FKCLIENT_T)
E4_BL (keep=FKCLIENT_T RACE_BL)
E4_WH (keep=FKCLIENT_T RACE_WH)
E4_AS (keep=FKCLIENT_T RACE_AS)
E4_NA (keep=FKCLIENT_T RACE_NA)
E4_HI (keep=FKCLIENT_T HISP_CDX)
;
set E4 ;
by FKCLIENT_T ;
if first.FKCLIENT_T then output E4_BASE ;
if P_RACE eq 1 or S_RACE eq 1 then do;
  RACE_BL = 1;
  output E4_BL;
end;
if P_RACE eq 2 or S_RACE eq 2 then do;
  RACE_WH = 1; 
  output E4_WH;
end;
if P_RACE = 4 or S_RACE = 4 then do;
  RACE_AS = 1; 
  output E4_AS;
end;
if P_RACE eq 5 or S_RACE eq 5 then do;
  RACE_NA = 1; 
  output E4_NA;
end;
if SEC_ETHNIC in(830,3162,3163,3164,3165) or HISP_CDN eq 1 then do;
  HISP_CDX = 1; 
  output E4_HI;
end;
run;

/* Eliminate any duplicate records (multiple secondary races of the same class
 * e.g., two Asian secondary races).
 */
proc sql ;
create table E4U_BL as
select distinct *
from E4_BL ;

create table E4U_WH as
select distinct *
from E4_WH ;

create table E4U_AS as
select distinct *
from E4_AS ;

create table E4U_NA as
select distinct *
from E4_NA ;

create table E4U_HI as
select distinct *
from E4_HI ;

data E5 ;
merge E4_BASE E4U_BL E4U_WH E4U_AS E4U_NA E4U_HI ;
  by FKCLIENT_T ;
run ;

data E6 ;
set E5 ;
if HISP_CDX eq . then HISP_CDX = 0;
if RACE_BL eq . then RACE_BL = 0;
if RACE_WH eq . then RACE_WH = 0;
if RACE_AS eq . then RACE_AS = 0;
if RACE_NA eq . then RACE_NA = 0;

if RACE_BL eq 1 then CENS_RC = 1 ;
if RACE_WH eq 1 then CENS_RC = 2 ;
if RACE_AS eq 1 then CENS_RC = 4 ;
if RACE_NA eq 1 then CENS_RC = 5 ;
if sum(RACE_BL, RACE_WH, RACE_AS, RACE_NA)  > 1 then CENS_RC = 8 ;
if sum(RACE_BL, RACE_WH, RACE_AS, RACE_NA) eq 0 then CENS_RC = 9 ;
run ;

proc sql ;
create table DWH.CENSUS_RC as
select
 FKCLIENT_T
,CENS_RC  length=3 label="Census Race"
,HISP_CDX length=3 label="Hispanic Status"
from E6
;