proc sql;

create table temp1 as
select x.*, y.P_ETHNCTYC
from dwh.ucb_pit as x left join cws.client_t as y
on x.FKCLIENT_T = y.IDENTIFIER;

quit;

/*Child Primary Ethnicity*/
/*find all children in UCB_FC2 file and identify those with Native American
  primary ethnicity - need to keep all children for later search for secondary
  Native American ethnicity*/
data pe_child_01 (keep = fkclient_t NAI_FLAG ICWA_ELGCD );
        set temp1;
         if P_ETHNCTYC in (820,821) then NAI_FLAG = 1;
                else NAI_FLAG = 2;
        run;

/*limit to one record per child*/
proc sort data = pe_child_01 out = pe_child nodupkey;
        by fkclient_t;
        run;


/*Child Secondary Ethnicity*/
/*Note: child and scp secondary ethnicities are on the same table. The
  estblsh_id links to either child ID and scp ID.
  Using the estblsh_cd (C or S) doesn't add anything*/
 proc sql;
        create table child_se_01 as select
        clscp_et.estblsh_id,
        clscp_et.estblsh_cd,
        clscp_et.ethnctyc,
        1 as SE_NAI_FLAG
        from cws.clscp_et
        where clscp_et.estblsh_id in(select fkclient_t from pe_child)
                and clscp_et.ethnctyc in(820,821)
        order by estblsh_id;
        quit;

proc sort data = child_se_01 out = se_child nodupkey;
        by estblsh_id;
        run;


/*bring child primary and secondary Native American ethnicity data together*/
proc sql;
        create table na_child_11 as select
        pe_child.*,
        se_child.se_nai_flag
        from pe_child left join se_child
        on pe_child.fkclient_t = se_child.estblsh_id
        order by pe_child.fkclient_t;
        quit;

/*set child_nai_flag to 1 if either primary or secondary Native American
  ethnicity*/
data na_child;
        set na_child_11;
        if nai_flag = 1 then child_na_flag = 1;
                else if se_nai_flag = 1 then child_na_flag = 1;
                else child_na_flag = 2;
        run;

/*Substitute Care Providers*/
/*SCP primary ethnicity*/
data pe_scp (keep = IDENTIFIER P_ETHNCTYC HISP_CD NAI_FLAG
                rename = (IDENTIFIER = FKSB_PVDRT));
        set CWS.SB_PVDRT;
        if P_ETHNCTYC in (820,821) then NAI_FLAG = 1;
                else if HISP_CD = "Y" then NAI_FLAG = 2;
                else if P_ETHNCTYC in( ., 7093) then NAI_FLAG = 99;
                else NAI_FLAG = 2;
run;

/*scp secondary ethnicity*/
/*note there are a few (9) SCPs where the estblsh_cd is "C". These don't match
  with the client table, this the estblsh_cd of "C" appears to be an error.*/
proc sql;
        create table scp_se_01 as select
        clscp_et.estblsh_id,
        clscp_et.estblsh_cd,
        clscp_et.ethnctyc,
        1 as SE_NAI_FLAG
        from cws.clscp_et
        where clscp_et.estblsh_id in(select fksb_pvdrt from pe_scp)
                and clscp_et.ethnctyc in(820,821)
        order by estblsh_id;
        quit;

/*limit to one secondary ethnnicity record per SCP*/
proc sort data = scp_se_01 out = se_scp nodupkey;
        by estblsh_id;
        run;


/*bring scp primary and secondary Native American ethnicity data together*/
proc sql;
        create table na_scp_01 as select
        pe_scp.*,
        se_scp.se_nai_flag,
        se_scp.estblsh_cd
        from pe_scp left join se_scp
        on pe_scp.fksb_pvdrt = se_scp.estblsh_id
        order by pe_scp.fksb_pvdrt;
        quit;

/*set scp_nai_flag to 1 if either primary or secondary Native American
  ethnicity*/
data na_scp_02;
        set na_scp_01;
        by fksb_pvdrt;
        if nai_flag = 1 then scp_na_flag = 1;
                else if se_nai_flag = 1 then scp_na_flag = 1;
                else scp_na_flag = nai_flag;
        run;

/*need to link to placement home table and then out-of-home placement table
  since UCB_PIT only includes the out of home placement identifier (ohmpl_id)
  and not the facility identifier (fkplc_hm_t)
  doing an inner join limits records to those with identified fkplc_hm_t
  and identified fksb_pvdrt, i.e., ones with useful data*/

proc sql;
        create table na_scp_04 as select
        na_scp_02.*,
        hm_scp_t.fkplc_hm_t
        from na_scp_02, cws.hm_scp_t
        where na_scp_02.fksb_pvdrt = hm_scp_t.fksb_pvdrt
        order by fkplc_hm_t, scp_na_flag;
        quit;

/*limit to one record per placement home, i.e., identifies that there is
  or isn't a Native American caregiver without identifying the caregiver*/
data na_scp_06 (keep = fkplc_hm_t scp_na_flag);
        set na_scp_04;
        by fkplc_hm_t scp_na_flag;
        if first.fkplc_hm_t;
        run;

/*link to out of home placement table*/
proc sql;
        create table na_scp as select
        na_scp_06.*,
        o_hm_plt.identifier as ohmpl_id
        from na_scp_06, cws.o_hm_plt
        where na_scp_06.fkplc_hm_t = o_hm_plt.fkplc_hm_t
        order by ohmpl_id, scp_na_flag;
        quit;

/*result is 2 files, na_child and na_scp that have flags indicating
  child and substitute care provider has any reported Native American ancestry*/


data test1;
set dwh.ucb_pit;

if ETHNIC not in (1,2,3,4,5) then ETHNIC = 99;

run;


/*add child Native American Status Data*/
proc sql;
        create table test2 as select
        test1.*,
        na_child.child_na_flag
        from test1 left join na_child
        on test1.fkclient_t = na_child.fkclient_t
        order by fkclient_t;
        quit;

/*add scp Native American Status Data*/
proc sql;
        create table test3 as select
        test2.*,
        na_scp.scp_na_flag
        from test2 left join na_scp
        on test2.ohmpl_id = na_scp.ohmpl_id
        order by fkclient_t;
        quit;

/*return to original program*/
data test11b;
set test3;

if ICWA_ELGCD = 'Y' then ICWA_FLAG = 1;
else ICWA_FLAG = 0;

if ETHNIC = 5 or CHILD_NA_FLAG = 1 then MR_FLAG = 1;
else MR_FLAG = 0;

run;

data point1;
set test11b;

if ICWA_FLAG = 1 or MR_FLAG = 1 then do;
  *if (PIT_SCPR in (3,4,6,7) or PIT_PLC = 2) then NA_CAT = 1;                 /* Relatives including NREFM*/
  if (PIT_SCPR in (3,4) or PIT_PLC = 2) then NA_CAT = 1;                      /* Relatives */
  else if SCP_NA_FLAG = 1 and PIT_SCPR in (1,2) and
          PIT_PLC not in (2,10,11,12,19) then NA_CAT = 2;                     /* Non Relatives, Indian SCPs */
  else if SCP_NA_FLAG = 2 and PIT_SCPR in (1,2) and
          PIT_PLC not in (2,10,11,12,19) then NA_CAT = 3;                     /* Non Relatives, Non Indian SCPs */
  else if SCP_NA_FLAG = 99 and PIT_SCPR in (1,2) and
          PIT_PLC not in (2,10,11,12,19) then NA_CAT = 4;                     /* Non Relatives, SCP Ethnicity Missing */
  else if PIT_PLC in(10,12) then NA_CAT = 5;                                  /* Group Homes, STRTP */
  else if PIT_PLC = 19 then NA_CAT = 6;                                       /* SILP */
  else if PIT_PLC in(11, 17,13,18,20, 29) then NA_CAT = 9;  /*modified*/      /*other runaways as other j magruder 2021.12.20*/
        /*note all modified to include/exclude fed 11, 12 - STRTPs and Shelter*/
end;

*if (ICWA_FLAG = 1 or MR_FLAG = 1) and LIMBO = '1' then NA_CAT = 9;        /* Other */
if NA_CAT = . then NA_CAT = 99;                                           /* Missing */


run;

data point1a;
set point1;

AGENCY = 4;

run;

data point2;
set point1 point1a;
run;

data point3a;
set point2;

where cnty_spfcd = '19';
COUNTY = 1900;

run;

data point3;
set point2 point3a;
run;

proc sql;
create table county as
select COUNTY as CNTY length=3, AGENCY, COURT_IND, AGE, GENDER_CD, ICWA_FLAG, MR_FLAG, NA_CAT,
       ETHNIC, TIME_IN, PIT_PLC, PIT_SCPR, REMREAS, PERIOD_DT, count(*) as COUNT length=4 format=comma12.
from point3
group by CNTY, AGENCY, ICWA_FLAG, MR_FLAG, NA_CAT, COURT_IND, AGE, GENDER_CD, ETHNIC, TIME_IN, PIT_PLC, PIT_SCPR, REMREAS, PERIOD_DT;
quit;


data point4;
set point2;

CNTY = 0;

run;

proc sql;
create table state as
select CNTY length=3, AGENCY, COURT_IND, AGE, GENDER_CD, ETHNIC, ICWA_FLAG, MR_FLAG, NA_CAT,
       TIME_IN, PIT_PLC, PIT_SCPR, REMREAS, PERIOD_DT, count(*) as COUNT length=4 format=comma12.
from point4
group by CNTY, AGENCY, ICWA_FLAG, MR_FLAG, NA_CAT, COURT_IND, AGE, GENDER_CD, ETHNIC, TIME_IN, PIT_PLC, PIT_SCPR, REMREAS, PERIOD_DT;
quit;

data pit_la1;
set county state;

if PIT_PLC = . then PIT_PLC = 99;

run;

data dvlp.ICWA;
set pit_la1;

/*
if cnty_spfcd = '19' and COUNTY = . then COUNTY = 1999;
else if COUNTY = . then COUNTY = 9800;
*/

run;

proc sort data = dvlp.ICWA;
by AGENCY CNTY PERIOD_DT;
run;

data test.ICWA;
set dvlp.ICWA;
run;

proc sort data = test.ICWA;
by AGENCY CNTY PERIOD_DT;
run;