*****************************************************************************************;
** Copyright (c) 2007 Center for Social Services Research,                             **;
** University of California at Berkeley. All rights reserved.                          **;
**                                                                                     **;
** This program produces data for Dynamic 4E 1&2 Reports                               **;
**                                                                                     **;
** ICWA                                                                                **;
** These reports examine the point in time placement status of two overlapping groups  **:
** of children: Indian Child Welfare Act eligible children [4E(1)] and children with   **;
** primary or secondary (multi) ethnicity of American Indian [4E(2)]. Placement status **;
** takes placement type, child relationship to substitute care provider, and           **;
** substitute care provider ethnicity into account.                                    **;
**                                                                                     **;
** Programmer: S. Sean Lee                                                             **;
**                                                                                     **;
** 2019.02.25 revised to conform with CWS/CMS Release 8.4 modifications j magruder     **;
** 2022.01.11 revised to conform with addition of tribally approved hones place type   **;
** 2023.11.23 restructured to use UCB_PIT as base data source                          **;
**                                                                                     **;
*****************************************************************************************;

*libname dynamic "/ssa3/SAS/REPORT_DATA/&YEAR.&QTR/ICWA";
libname test "/ssa3/SAS/REPORT_DATA/&YEAR.&QTR.Test/ICWA";
libname dvlp "/ssa3/SAS/REPORT_DATA/&YEAR.&QTR.Dvlp/ICWA";

/*identify potential Native American population based on primary and secondary
  ethnicity for both child and substitute care providers*/
/*Note, in earlier versions of the program this functionality was in the macro, 
  as a result it was recalculated in every iteration of the macro*/

data ucb_fc2;
set dwh.ucb_fc2(drop=ETHNIC);

select(P_ETHNCTYC) ;
   when(.,0,6351,6352,6453) ETHNIC=99 ;              /* Missing */
   when(823,826) ETHNIC=1 ;                          /* Black */
   when(839,840,841,842,843,844) ETHNIC=2 ;          /* White */
   when(830,3162,3163,3164,3165) ETHNIC=3 ;          /* Hispanic */
   when(820,821) ETHNIC=5 ;                          /* Native Am */
   when(5922,5923,822,824,825,827,828,829,
        831,832,833,834,835,836,837,838) ETHNIC=4 ;  /* Asian */
    otherwise ETHNIC=99 ;                            /* Invalid Value */
end ;

if HISP_CD eq 'Y' then ETHNIC=3 ;                    /* Hispanic */

run;

/*Child*/
/*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 ucb_fc2;
	 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;
/*
proc tabulate data = na_child_11 missing format = comma10.0;
	class nai_flag se_nai_flag;
	table nai_flag all, se_nai_flag all;
	run;
*/
/*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;

/*
*Note - there is only a limited relationship between reported Native
 American primary ethnicity and ICWA status;
proc tabulate data = na_child missing format = comma10.0;
	class child_na_flag icwa_elgcd;
	table icwa_elgcd all, child_na_flag all;
	format child_na_flag yes_no.;
	run;
*/
/*
proc tabulate data = na_child missing format = comma10.0;
	class nai_flag se_nai_flag;
	table nai_flag all, se_nai_flag all;
	where 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;
/*
proc freq data = na_scp_02;
	table scp_na_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;
/*
proc print data = na_scp_04 (obs = 40);
	where scp_na_flag = 1;
	run;
*/
/*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;
/*
proc print data = na_scp_06 (obs = 40);
	*where fkplc_hm_t ne ' ' and scp_na_flag = 1;
	run;
proc freq data = na_scp_06;
	table scp_na_flag;
	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;

/*
proc print data = na_scp (obs = 40);
where scp_na_flag = 1;
	run;
*/
/*result is 2 files, na_child and na_scp that have flags indicating
  child and substitute care provider has any reported Native American ancestry*/ 


*%let syear = 2023;
*%let sqtr = 2;


/* macro is called at end of file... */

%macro COUNTY(START,STOP) ;
%do SYEAR = &START %to &STOP ;
%do SQTR = 1 %to 4 ;

/* DATA cut */

data test0;
	set dwh.ucb_pit;
	where period_dt = yyq(&SYEAR,&SQTR);
run;

data test1;
set test0;

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;



/*
proc tabulate data = test3 missing format = comma8.0;
	class ethnic child_na_flag scp_na_flag ICWA_ELGCD;
	table ethnic=' ' all, child_na_flag*n=' ' all*n=' '
		/box = "Primary Ethnicity";
	table scp_na_flag=' ' all, child_na_flag*n=' ' all*n=' '
		/box = "SCP Native American";
	table ICWA_ELGCD=' ' all, child_na_flag*n=' ' all*n=' '
		/box = "ICWA Status";
	format ethnic $ethnic.;
	format child_na_flag scp_na_flag  yes_no.;
	where agency = 1;
	run;
*/

/*return to original program*/
data test11b (rename = (pit_plc = fed));
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;

/*
proc tabulate data = test11b missing format = comma7.0;;
	class icwa_flag mr_flag;
	table icwa_flag all, mr_flag all;
	run;
*/



/**/

data point1;
set test11b;

if ICWA_FLAG = 1 or MR_FLAG = 1 then do;    
  *if (PIT_SCPR in (3,4,6,7) or FED = 2) then NA_CAT = 1;                 /* Relatives including NREFM*/
  if (PIT_SCPR in (3,4) or FED = 2) then NA_CAT = 1;                      /* Relatives */
  else if SCP_NA_FLAG = 1 and PIT_SCPR in (1,2) and
          FED 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
          FED 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
          FED not in (2,10,11,12,19) then NA_CAT = 4;                     /* Non Relatives, SCP Ethnicity Missing */
  else if FED in(10,12) then NA_CAT = 5;                              	  /* Group Homes, STRTP */
  else if FED = 19 then NA_CAT = 6;                                       /* SILP */
  else if FED 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 */


*if HISP_CD eq 'Y' then ETHNIC=3 ;         /* Hispanic */

/*
if GENDER_CD = 'M' then GENDER = 2;
else if GENDER_CD = 'F' then GENDER = 1;
else if GENDER_CD = 'I' then GENDER = 3;
else GENDER = 99;

*/

run;


/*testing routines*/
/*
proc tabulate data = point1 missing format = 6.0;
	class na_cat fed ethnic;
	*table fed all, na_cat all;
	table na_cat all, ethnic all;
	*table icwa_flag all, ethnic all;
	where icwa_flag = 1 and agency = 1; * and age < 22 and age ge 0;
	*where mr_flag = 1 and agency = 1; * and age < 22 and age ge 0;
	format fed newfed.;
	format ethnic $ethnic.;
	run;

proc tabulate data = point1 missing format = 6.0;
	class scp_na_flag na_cat;
	table na_cat all, scp_na_flag all;
	where icwa_flag = 1 and agency = 1;
	run;
	
proc tabulate data = point1 missing format = 6.0;
	class fed scp_na_flag;
	table fed all, scp_na_flag all;
	where na_cat = 99 and icwa_flag = 1 and agency = 1;
	run;


proc tabulate data = point1 missing format = 6.0;
	class pit_scpr scp_na_flag;
	table pit_scpr all, scp_na_flag all;
	where na_cat = 99 and icwa_flag = 1 and agency = 1;
	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 pit_cnty_&SYEAR.Q&SQTR as
select COUNTY as CNTY length=3, AGENCY, COURT_IND, AGE, GENDER_CD, ICWA_FLAG, MR_FLAG, NA_CAT, 
       ETHNIC, TIME_IN, FED as 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 pit_state_&SYEAR.Q&SQTR as
select CNTY length=3, AGENCY, COURT_IND, AGE, GENDER_CD, ETHNIC, ICWA_FLAG, MR_FLAG, NA_CAT, 
       TIME_IN, FED as 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;




%end ;
%end ;
%mend COUNTY ;
%COUNTY(1998,2025) ;

data dvlp.ICWA;
set pit_cnty_2025q1
    pit_cnty_2024q4 pit_cnty_2024q3 pit_cnty_2024q2 pit_cnty_2024q1
    pit_cnty_2023q4 pit_cnty_2023q3 pit_cnty_2023q2 pit_cnty_2023q1
    pit_cnty_2022q4 pit_cnty_2022q3 pit_cnty_2022q2 pit_cnty_2022q1
    pit_cnty_2021q4 pit_cnty_2021q3 pit_cnty_2021q2 pit_cnty_2021q1
    pit_cnty_2020q4 pit_cnty_2020q3 pit_cnty_2020q2 pit_cnty_2020q1
    pit_cnty_2019q4 pit_cnty_2019q3 pit_cnty_2019q2 pit_cnty_2019q1
    pit_cnty_2018q4 pit_cnty_2018q3 pit_cnty_2018q2 pit_cnty_2018q1
    pit_cnty_2017q4 pit_cnty_2017q3 pit_cnty_2017q2 pit_cnty_2017q1
    pit_cnty_2016q4 pit_cnty_2016q3 pit_cnty_2016q2 pit_cnty_2016q1
    pit_cnty_2015q4 pit_cnty_2015q3 pit_cnty_2015q2 pit_cnty_2015q1
    pit_cnty_2014q4 pit_cnty_2014q3 pit_cnty_2014q2 pit_cnty_2014q1
    pit_cnty_2013q4 pit_cnty_2013q3 pit_cnty_2013q2 pit_cnty_2013q1
    pit_cnty_2012q4 pit_cnty_2012q3 pit_cnty_2012q2 pit_cnty_2012q1
    pit_cnty_2011q4 pit_cnty_2011q3 pit_cnty_2011q2 pit_cnty_2011q1
    pit_cnty_2010q4 pit_cnty_2010q3 pit_cnty_2010q2 pit_cnty_2010q1
    pit_cnty_2009q4 pit_cnty_2009q3 pit_cnty_2009q2 pit_cnty_2009q1
    pit_cnty_2008q4 pit_cnty_2008q3 pit_cnty_2008q2 pit_cnty_2008q1
    pit_cnty_2007q4 pit_cnty_2007q3 pit_cnty_2007q2 pit_cnty_2007q1
    pit_cnty_2006q4 pit_cnty_2006q3 pit_cnty_2006q2 pit_cnty_2006q1
    pit_cnty_2005q4 pit_cnty_2005q3 pit_cnty_2005q2 pit_cnty_2005q1
    pit_cnty_2004q4 pit_cnty_2004q3 pit_cnty_2004q2 pit_cnty_2004q1
    pit_cnty_2003q4 pit_cnty_2003q3 pit_cnty_2003q2 pit_cnty_2003q1
    pit_cnty_2002q4 pit_cnty_2002q3 pit_cnty_2002q2 pit_cnty_2002q1
    pit_cnty_2001q4 pit_cnty_2001q3 pit_cnty_2001q2 pit_cnty_2001q1
    pit_cnty_2000q4 pit_cnty_2000q3 pit_cnty_2000q2 pit_cnty_2000q1
    pit_cnty_1999q4 pit_cnty_1999q3 pit_cnty_1999q2 pit_cnty_1999q1
    pit_cnty_1998q4 pit_cnty_1998q3 pit_cnty_1998q2 pit_cnty_1998q1

    pit_state_2025q1
    pit_state_2024q4 pit_state_2024q3 pit_state_2024q2 pit_state_2024q1
    pit_state_2023q4 pit_state_2023q3 pit_state_2023q2 pit_state_2023q1
    pit_state_2022q4 pit_state_2022q3 pit_state_2022q2 pit_state_2022q1
    pit_state_2021q4 pit_state_2021q3 pit_state_2021q2 pit_state_2021q1
    pit_state_2020q4 pit_state_2020q3 pit_state_2020q2 pit_state_2020q1
    pit_state_2019q4 pit_state_2019q3 pit_state_2019q2 pit_state_2019q1
    pit_state_2018q4 pit_state_2018q3 pit_state_2018q2 pit_state_2018q1
    pit_state_2017q4 pit_state_2017q3 pit_state_2017q2 pit_state_2017q1
    pit_state_2016q4 pit_state_2016q3 pit_state_2016q2 pit_state_2016q1
    pit_state_2015q4 pit_state_2015q3 pit_state_2015q2 pit_state_2015q1
    pit_state_2014q4 pit_state_2014q3 pit_state_2014q2 pit_state_2014q1
    pit_state_2013q4 pit_state_2013q3 pit_state_2013q2 pit_state_2013q1
    pit_state_2012q4 pit_state_2012q3 pit_state_2012q2 pit_state_2012q1
    pit_state_2011q4 pit_state_2011q3 pit_state_2011q2 pit_state_2011q1
    pit_state_2010q4 pit_state_2010q3 pit_state_2010q2 pit_state_2010q1
    pit_state_2009q4 pit_state_2009q3 pit_state_2009q2 pit_state_2009q1
    pit_state_2008q4 pit_state_2008q3 pit_state_2008q2 pit_state_2008q1
    pit_state_2007q4 pit_state_2007q3 pit_state_2007q2 pit_state_2007q1
    pit_state_2006q4 pit_state_2006q3 pit_state_2006q2 pit_state_2006q1
    pit_state_2005q4 pit_state_2005q3 pit_state_2005q2 pit_state_2005q1
    pit_state_2004q4 pit_state_2004q3 pit_state_2004q2 pit_state_2004q1
    pit_state_2003q4 pit_state_2003q3 pit_state_2003q2 pit_state_2003q1
    pit_state_2002q4 pit_state_2002q3 pit_state_2002q2 pit_state_2002q1
    pit_state_2001q4 pit_state_2001q3 pit_state_2001q2 pit_state_2001q1
    pit_state_2000q4 pit_state_2000q3 pit_state_2000q2 pit_state_2000q1
    pit_state_1999q4 pit_state_1999q3 pit_state_1999q2 pit_state_1999q1
    pit_state_1998q4 pit_state_1998q3 pit_state_1998q2 pit_state_1998q1;

if pit_plc = . then pit_plc = 99;

run;

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

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

proc sql;
create index INDX1 on dvlp.ICWA(AGENCY, ICWA_FLAG, MR_FLAG, CNTY, PERIOD_DT);
*create index INDX1 on test.ICWA(AGENCY, ICWA_FLAG, MR_FLAG, CNTY, PERIOD_DT);
quit;