*Version UCB_FC_2024Q3_jm.SAS;
*****look at oh_plc_athc,;
*2018.06.18 start j magruder;
*2018.10.08 final draft j magruder;
*2020.06.17 revised for state identifiers j magruder;
*2020.11.17 revised to include hospitalizations of >30 days and initial runaways
		in foster care population;
*2021.02.01 Title IV-E eligibility added j magruder;
*2022.01.11 Tribal Specified and Approved Homes predominant placement types added; 
*2022.02.14 correcting error in OH_CNTY that should refer to county where placement is 
	     located, not county responsible for the child j magruder
*2022.02.14 In situations of duplicate placements with same dates changed priority order
		to give runaways lower priority than known placement and court ordered lower 
		priority than known placement (affects 20 placements post 1/1/1998) j magruder;
*2022.06.23 Add ICWA status j magruder;
*2022.06.27 correct facility type change methodology j magruder;
*2022.07.19 add post-RFA predominant facility type j magruder;
*2022.09.26 add most certain tribal membership status and tribe identifier j magruder;
*2022.09.29 remove placement episode records with no placements from UCB_FC2 j magruder;
*2023.01.16 correct placement end for runaways when overlapping episodes are present j magruder;
*2023.10.06 added placement authority at start and end of each placement j magruder;
*2023.11.02 changed predominant placement type calculation to require more than half
        time in placement to quality as predominant - problem of identifying one of 
		two equal length short placement types as predominant j magruder; 
*2024.04.15 convert court specified placements with kin to kin placements j magruder;
*2024.07.01 recoded ethnic as numeric, not character j magruder
        added variable for geographic relationship between sup agency and placement j magruder;
*2024.12.26 add additional IV-E state ID codes j magruder;

/*
Changes from original version of UCB_FC
(1) non-foster care placements added and identified
(2) episodes with only non-foster care placements identified
(3) incoming ICPC episodes added and identified
(4) incoming ICPC determination episode rather than placement based
(5) transitional housing indicator added
(6) starting and ending legal authority for placement added
(7) county responsible at beginning of each placement added
(8) group home Rate Classification Level added
(9) county responsible for payment, and summary responsible county added at placement level
(10) placement state location, both based on state ID and based on Zip Code for 
	regular placements, adoptive placements, and non-foster care placements
(11) UCB_PLCMNT replaced with PMT_ORDER which puts all placements in order across episodes
(12) add sort on episode ID when multiple episodes with same start and end dates to give
     consistent order over multiple program runs
(13) delete duplicate placements when start and end dates are the same
(14) Add ICWA status and tribal membership status
 

/**************************************************************/

%macro age(date,birth);
  floor ((intck('month',&birth,&date)
  - (day(&date) < day(&birth))) / 12); 
%mend age;

%macro moage(date,birth);
  floor ((intck('month',&birth,&date)
  - (day(&date) < day(&birth)))/12 ); 
%mend moage;

OPTIONS FORMCHAR="|----|+|---+=|-/\<>*";

/*

Stephanie would add:

TRANSITIONAL_HOUSING_PLACEMENT (PLC_HM_T.TRNHSG_FAC)
TRANSITIONAL_HOUSING_PLACEMENT_P (PLC_HM_T.TRNHSG_IND)

In the past Sean has been asked to pass these along to Chapin Hall for the CalYouth study 
so it might be wise to have them on the file.

Some other items that might be useful are:
LEGAL_AUTH_FOR_PLACEMENT_TYPE (LG_AUTHT.PLC_ATHC) [problem with first and/or last and/or predominant authority]
REMOVAL_TIME (PLC_EPST.REMOVAL_TM)

*/



/*CHILD ID MODULE*/
/*find FKCLIENT_T for all children in one or more out of home placements - only foster
	care placements used at this point*/
data child_id_01_fc (keep = fkplc_epst rename=(fkplc_epst = FKCLIENT_T));
	set cws.o_hm_plt;
	where start_dt le &cutoff. and start_dt ne .;
	label fkplc_epst = "Client ID";
	run;

/*find children in non-foster care placements*/
data child_id_01_nfc (keep = fkplc_epst rename=(fkplc_epst = FKCLIENT_T));;
	set cws.nfc_plct;
	where  start_dt le &cutoff. and start_dt ne .;
	run;

/*merge files*/
data child_id_01;
	set child_id_01_fc child_id_01_nfc;
	run;

/*reduce to one record per client*/
/*file includes children in non-foster care, mental health, KinGAP, etc.*/
proc sort data = child_id_01 out = child_id_02 nodupkey;
	by fkclient_t;
	run;

/*get date of birth for child */
proc sql;
	create table child_id_03 as select
	child_id_02.fkclient_t,
	client_t.birth_dt format mmddyy10.,
	client_t.est_dob_cd
	from child_id_02, cws.client_t
	where child_id_02.fkclient_t = client_t.identifier
	order by fkclient_t;
	quit;

/*
proc print data = child_id_03 (obs = 40);
	where est_dob_cd ne "N";
	run;
*/

/*find all placement episodes for these children, but just for purpose of excluding children
	where the responsible agency is out of state, mental health, KinGAP etc. 
	Result is one record per child per episode*/ 
proc sql;
	create table child_episode_01 as select
		child_id_03.fkclient_t,
		child_id_03.birth_dt,
		plc_epst.third_id as PLCEP_ID label = "PLC_EPST.THIRD_ID" ,
		plc_epst.REMOVAL_DT as PE_S_DT length=4 format=mmddyy10. label="PLC_EPST.REMOVAL_DT",
		plc_epst.PLEPS_ENDT as PE_E_DT length=4 format=mmddyy10. label="PLC_EPST.PLEPS_ENDT"
	from cws.plc_epst, child_id_03
	where plc_epst.fkclient_t = child_id_03.fkclient_t
		and AGY_RSPC not in (0035, 0036, 5604, 5605, 6133, 6134)
		 /* 0035, 5604 = out of state agency, 
			0036, 5605 = Private Adoption Agency,
			6133 = mental health,
			6134 = KinGAP*/
		and plc_epst.REMOVAL_DT le &cutoff.
		/*and plc_epst.REMOVAL_DT ge child_id_03.birth_dt*/
		and (plc_epst.pleps_endt= . 
			or plc_epst.PLEPS_ENDT - plc_epst.REMOVAL_DT not in (0, 1))
	order by fkclient_t, third_id;
;quit;

/*Use case intervention reason 1226 (Incoming ICPC Request) to identify placement episodes to
	exclude because of a concurrent case with this intervention reason. Analysis found that
	these cases are very likely to not have California court hearings (a sign child is responsibility
	of California, not another state) while placements with an incoming ICPC authority for placement
	but no Incoming ICPC Request case intervention reason were likely to have California court hearings.*/
	
/* Find all intervention reasons of "Incoming ICPC Request" (reason 1226) */
data icpc_in_01;
set CWS.INTV_RNT;
where intvrsnc = 1226;
run;

/* Find the cases associated with that intervention reason */
proc sql;
create table icpc_in_02 as
select icpc_in_01.*,
y.fkchld_clt as fkclient_t,
y.start_dt as case_s_dt format mmddyy10.,
y.end_dt as case_e_dt format mmddyy10.
from icpc_in_01 as x left join CWS.CASE_T as y
on x.FKCASE_T = y.IDENTIFIER
order by FKCLIENT_T, START_DT, FKCASE_T;
quit;

/* Find placements where case and placement are concurrent;*/
proc sql;
create table icpc_in_03 as
select x.FKCLIENT_T,
       x.FKCASE_T,
       x.CASE_S_DT,
       x.CASE_E_DT,
	   x.lst_upd_ts,
       y.pe_S_DT,
       y.pe_E_DT,
       y.plcep_ID,
       1 as ICPC_IN length = 3 label = "Out-of_state ICPC"
       format=6.0
from icpc_in_02 as x, child_episode_01 as y
where x.FKCLIENT_T = y.FKCLIENT_T and 
     (CASE_S_DT le pe_E_DT or pe_E_DT = .) and 
     (CASE_E_DT ge pe_S_DT or CASE_E_DT = .)
order by plcep_id, case_s_dt;
quit;
/*
proc print data = icpc_in_03 (obs = 40);
	var fkclient_t pe_s_dt pe_e_dt case_s_dt case_e_dt fkcase_t;
	run;
*/

data ICPC_IN (keep = PLCEP_ID ICPC_IN);
	set icpc_in_03;
	by plcep_id case_s_dt;
	if first.plcep_id;
	run;

proc sql;
	create table child_episode_02 as select
	child_episode_01.*,
	ICPC_IN.ICPC_IN
	from child_episode_01 left join ICPC_IN
	on child_episode_01.plcep_id = ICPC_IN.plcep_id
	order by fkclient_t, pe_s_dt;
	quit;

/*add marker for episodes starting on same date*/
data conc_episode_01;
	set child_episode_02;
	by fkclient_t pe_s_dt;
	if first.pe_s_dt = 0 or last.pe_s_dt = 0
		then conc_pe_s_dt = 1;
		else conc_pe_s_dt = 0;
	if pe_e_dt = . then tpe_e_dt = '01JAN3000'D;
		else tpe_e_dt = pe_e_dt;
	format tpe_e_dt mmddyy10.;
	label conc_pe_s_dt = "Shared pe_s_dt for same child";
	run;

/*create pseudo episode id to group multiple episodes starting on the same date
	as a single episode to avoid double count of entries and exits*/ 
	/*add plcep_id 2021.05.24 to give consistent results over multiple runs when child has more
	than one episode starting and ending on the same dates*/

proc sort data = conc_episode_01;
	by fkclient_t pe_s_dt descending tpe_e_dt plcep_id;
	run;

data child_episode_03;
	set conc_episode_01;
	retain rfkclient_t rpe_s_dt rev_plcep_id;
	by fkclient_t pe_s_dt descending tpe_e_dt plcep_id;
	if first.pe_s_dt then rev_plcep_id = plcep_id;
	rfkclient_t = fkclient_t;
	rpe_s_dt = pe_s_dt; 
	format rpe_s_dt mmddyy10.;
	run;

proc sort data = child_episode_03 (drop = rfkclient_t rpe_s_dt);
	by fkclient_t rev_plcep_id plcep_id;
	run;


/*	Find if the episode only includes non-foster care placements. This is done by 
	identifying all episodes that include at least one foster care placement. The
	remaining episodes are those that include ony non-foster care placements.*/
	/*note - this is done at the revised episode level so that if any of the 
	episodes starting on the same date have foster care placements, all same-date 
	episodes are treated as having a foster care placement*/
proc sql;
	create table child_episode_ohp_01 as select
		child_episode_03.fkclient_t,
		child_episode_03.rev_plcep_id,
		child_episode_03.plcep_id,
		o_hm_plt.identifier as OHMPL_ID,
       0 as NFC_ONLY length = 3 label = "NFC Only Episode"
       format=6.0
	from child_episode_03, cws.o_hm_plt
	where child_episode_03.plcep_id = o_hm_plt.fkplc_eps0
	order by fkclient_t, rev_plcep_id, plcep_id;
	quit;

/*create one record per episode with at least one foster care placement*/

data child_episode_ohp_02;
	set child_episode_ohp_01;
	by fkclient_t rev_plcep_id plcep_id;
	if first.rev_plcep_id;
	run;

proc sql;
	create table child_episode_04 as select 
		child_episode_03.*,
		child_episode_ohp_02.NFC_ONLY
	from child_episode_03 left join child_episode_ohp_02
	on child_episode_03.rev_plcep_id = child_episode_ohp_02.rev_plcep_id
	order by fkclient_t, rev_plcep_id, plcep_id;
	quit;

/*	Table child_episode has one record per child per episode excluding those episodes where the agency
	is out of state, mental health, KinGAP, or private adoption. If the child is an out-of-state
	ICPC child based on case intervention reason the ICPC_IN flag is set to "1." If the only 
	placements in the episode are in non-foster care placements, the NFC_ONLY flage is set to "1."
	Note about 3600 (0.3%) of all episodes have a concurrent start date with another episode.*/
 	
data child_episode_06 (keep = fkclient_t rev_plcep_id plcep_id birth_dt ICPC_IN conc_pe_s_dt NFC_ONLY);
	set child_episode_04;
	by fkclient_t rev_plcep_id plcep_id;
	if ICPC_IN = . then ICPC_IN = 0;
	if NFC_ONLY = . then NFC_ONLY = 1;
	run;

/*	Table child_id has one record per child who has had an open placement episode*/

data child_id (keep = FKCLIENT_T birth_dt);
	set child_episode_06;
	by fkclient_t rev_plcep_id plcep_id;
	if first.fkclient_t;
	run;

/*
data ucb_fc.child_episode_06;
	set child_episode_06;
	run;

data ucb_fc.child_id;
	set child_id;
	run;
*/


/*CHILD CHARACTERISTICS MODULE*/

/*get demographic data from client table*/
proc sql;
create table child_char_01 as
select 
y.FKCLIENT_T,
x.P_ETHNCTYC,
x.HISP_CD,
x.GENDER_CD,
x.p_lang_tpc,
x.s_lang_tc,
x.BIRTH_DT format=mmddyy10.
from CWS.CLIENT_T as x, child_id as y 
where x.IDENTIFIER = y.FKCLIENT_T
order by y.FKCLIENT_T; 
quit;

data child_char_03;
set child_char_01;

/* Assign Ethnicity Class */
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 */


*format ADOPT_FLAG $1.;
format ETHNIC 2.;
*informat ADOPT_FLAG $1.;
informat ETHNIC 2.;

label
ETHNIC ="ETHNIC_CLASS";
*ADOPT_FLAG = "ADOPTIVE_PLCMNT_FLAG";

run;

/* Add ICWA status from Child Client table. 2022.06.23 j magruder*/ 
/* This is the variable used for existing ICWA reports. It only partially
   matches with court hearing data on determination of ICWA status.
   In addition, it gives the most recent ICWA status, which may not have
   been the known ICWA status at the time of the placement.*/ 

proc sql;
	create table child_char_05 as select
	child_char_03.*,
	chld_clt.icwa_elgcd
	from child_char_03 left join cws.chld_clt
	on child_char_03.fkclient_t = chld_clt.fkclient_t
	order by fkclient_t;
	quit;

/* Add known tribal membership status. 2022.09.26 j magruder*/
/* As with the ICWA data it gives the most recent tribal membership
   status, which man not have been known at the time of placement*/

/* get tribal membership status data - one child usually has more then one
   record*/
proc sql;
	create table tribe_01 as select
	tr_mbvrt.fkclient_t,
	tr_mbvrt.status_dt as tr_st_dt format mmddyy10.
		label  "Indian Tribe Enrollment Status Date",
	tr_mbvrt.indn_stc,
	tr_mbvrt.third_id as tr_st_id,
	tr_mbvrt.fktr_mbvrt,
	tr_mbvrt.fktrb_orgt,
	tr_mbvrt.idn_trbc,
	case tr_mbvrt.indn_stc /*place tribal status in priority order*/		
		when 1212 then  1 /* Member*/
		when 1211 then  2 /* Eligible*/
		when 1214 then  3 /* Pending Verification*/
		when 1210 then  4 /* claims membership*/
		when 1213 then  5 /* Not Eligible*/
		when 6532 then  6 /* No Response After 60 Days*/
		else 9 /* no data*/
		end as indn_stc_ord label "Priority Order Tribal Enrollment Status",
	datepart(tr_mbvrt.lst_upd_ts) as tr_st_ts_dt format mmddyy10.,
	case
		when status_dt ne . then status_dt
		else datepart(tr_mbvrt.lst_upd_ts)
		end as tr_st_dt_m format mmddyy10. label "Modified Tribal Status Date"
		
	from cws.tr_mbvrt
	where tr_mbvrt.fkclient_t in(select fkclient_t from child_char_05)
	order by fkclient_t, indn_stc_ord, idn_trbc, tr_st_dt_m;
	quit;

/*eliminate duplicate records showing same status and same tribe
  if multiple modified dates (status date or, if missing, last update date) take first date*/
data tribe_03;
	set tribe_01;
	by fkclient_t indn_stc_ord idn_trbc tr_st_dt_m ;
	if first.idn_trbc;
	run;

proc sort data = tribe_03;
	by fkclient_t indn_stc_ord tr_st_dt_m;
	run;

/*take one record per child giving priority to tribal member, etc.*/
/*if multiple tribes with same status give mixed code for tribe ID*/
data tribe_05;
	set tribe_03;
	by fkclient_t indn_stc_ord tr_st_dt_m;
	if last.indn_stc_ord = 0 then IDN_TRBCx = 3192;
		else IDN_TRBCx = idn_trbc;
	if first.fkclient_t; *then take = 1;
	run;
/*
proc freq data = tribe_05;
	table idn_trbcx;
	format idn_trbcx syscode.;
	run;
*/
/*
proc tabulate data = tribe_05 missing format = comma7.0 noseps;
	class idn_trbcx indn_stc_ord indn_stc;
	table idn_trbcx all, 
		indn_stc*(N*(f=comma7.0) colpctn='%'*(f=5.1)) 
			all="Total"*(N*(f=comma8.0) )
			/box = "Tribe";
	format idn_trbcx syscode.;
	format indn_stc syscode.;
	run;
*/

proc sql;
	create table child_char_07 as select
	child_char_05.*,
	tribe_05.indn_stc,
	tribe_05.idn_trbcx label "Modified Indian Tribe ID"
	from child_char_05 left join tribe_05
	on child_char_05.fkclient_t = tribe_05.fkclient_t
	order by fkclient_t;
	quit;


/* Add Census Ethnic information */

proc sql;
	create table child_char as
	select x.*, y.CENS_RC, y.HISP_CDX
	from child_char_07 as x left join dwh.CENSUS_RC as y
	on x.FKCLIENT_T = y.FKCLIENT_T
	order by fkclient_t;
quit;

/*

proc tabulate data = child_char missing format = comma7.0 noseps;
	options pageno = 1;
	class ethnic p_lang_tpc s_lang_tc;
	table p_lang_tpc=' ' all, ethnic*N=' ' all*N=' '/box = "Primary Language";
	table s_lang_tc=' ' all, ethnic*N=' ' all*N=' '/box = "Secondary Language";
	format p_lang_tpc s_lang_tc syscode.;
	format ethnic $ethnic.;
	where year(birth_dt) ge 2000;
	title3 "Children born on or after 1/1/2000 ";
	run;

proc print data = child_char (obs = 40);
var fkclient_t birth_dt gender_cd ethnic p_ethnctyc hisp_cd hisp_cdx cens_rc;
*where birth_dt = .;
format p_ethnctyc syscode.;
format ethnic $ethnic.;
run;

data test;
	set child_char;
	birth_yr = year(birth_dt);
	run;

proc tabulate data = test missing format = comma7.0 noseps;
	options pageno = 1;
	class ethnic birth_yr cens_rc hisp_cdx;
	table birth_yr=' ', 
		ethnic*(N*(f=comma6.0) pctn<ethnic all>='%'*(f=5.1)) 
			all="Total"*(N*(f=comma7.0))
			/rts = 8;
	table birth_yr=' ', 
		cens_rc*(N*(f=comma6.0) pctn<cens_rc all>='%'*(f=5.1)) 
			all="Total"*(N*(f=comma7.0))
			/rts = 8;
	table birth_yr=' ', 
		hisp_cdx*(N*(f=comma6.0) pctn<hisp_cdx all>='%'*(f=5.1)) 
			all="Total"*(N*(f=comma7.0))
			/rts = 8;
	where birth_yr ge 1981;
	format ethnic $ethnic.;
	format cens_rc ethnic.;
	format hisp_cdx yes_no.;
	title3 "Placement Birth cohorts by Ethnicity";
run;
*/



/*EPISODE MODULE*/ 

/*get placement episode data for included children
	excludes out of state agencies, KinGAP, private adoption agencies, mental health*/
/*note: some of these episodes (ca 3600 or 0.3% share a start date with another
	episode for the same child. In the original UCB_FC one of these episodes would 
	be dropped, resulting in all the placements assigned to that episode being
	dropped*/
/*	In this revision, when more than one placement episode start on the same date, the
	episode with the last end date is retained. The ID is known as rev_plcep_id.*/

data child_episode_07;
	set child_episode_06;
	by fkclient_t rev_plcep_id plcep_id;
	if first.rev_plcep_id;
	run;


proc sql _method ;
	create table EPISODE_01 as
	select
		ce7.fkclient_t,
		ce7.rev_plcep_id label = "Master Episode",
		ce7.birth_dt,
		ce7.ICPC_IN,
		ce7.nfc_only,
		plc_epst.REMOVAL_DT as PE_S_DT length=4 format=mmddyy10. label="PLC_EPST.REMOVAL_DT",
		plc_epst.REMOVAL_TM as PE_S_TM label="PLC_EPST.REOMOVAL_TM",
		plc_epst.PLEPS_ENDT as PE_E_DT length=4 format=mmddyy10. label="PLC_EPST.PLEPS_ENDT",
		plc_epst.RMV_RSNC length=4 ,
		plc_epst.AGY_RSPC length=4,
		plc_epst.TERM_RS_CD,
		plc_epst.TERM_TY_C length=4,
		case 
			when plc_epst.gvr_entc = 0 then '  '
			else put(plc_epst.GVR_ENTC-1067, Z2.) 
			end as CNTY_REM format=$2. informat=$2. label="PLC_EPST.GVR_ENTC",
		plc_epst.RMVCR1_ID,
		plc_epst.RMV_FRM1C,
		plc_epst.RMVCR2_ID,
		plc_epst.RMV_FRM2C
	from child_episode_07 as ce7 left join cws.plc_epst
	on plc_epst.third_id = ce7.rev_plcep_id
	order by ce7.fkclient_t, rev_plcep_id;
quit;

/* 	Add legal authority for placement as reported on Placement Episode Legal Authority table*/
proc sql;
	create table lg_auth_02 as select
	episode_01.fkclient_t,
	episode_01.rev_plcep_id,
	lg_autht.plc_athc,
	lg_autht.effctv_dt as lg_auth_s_dt format mmddyy10.
	from episode_01 left join cws.lg_autht
	on episode_01.rev_plcep_id = lg_autht.fkplc_eps0
	order by fkclient_t, rev_plcep_id, effctv_dt;
	quit; 

/*find first and last legal authorities for placement. In spite of 20% of episodes having
	multiple legal authority records, the first and last only differ in 2.3% of all episodes.
	One problem is that about 3.2% of episodes only have an authority for placement that has
	an effective date that is after the episode end. */
data lg_auth_04a (keep = fkclient_t rev_plcep_id plc_athc lg_auth_s_dt
			rename= (plc_athc = FIRST_PLC_ATHC lg_auth_s_dt = FIRST_LG_AUTH_S_DT)) 
		lg_auth_04b (keep = fkclient_t rev_plcep_id plc_athc lg_auth_s_dt
			rename = (plc_athc = LAST_PLC_ATHC lg_auth_s_dt = LAST_LG_AUTH_S_DT));
	set lg_auth_02;
	by fkclient_t rev_plcep_id lg_auth_s_dt;
	if first.rev_plcep_id then output lg_auth_04a;
	if last.rev_plcep_id then output lg_auth_04b;
	run;

data lg_auth_04 (keep = fkclient_t rev_plcep_id first_plc_athc first_lg_auth_s_dt 
		last_plc_athc last_lg_auth_s_dt);
	merge lg_auth_04a lg_auth_04b;
	by fkclient_t rev_plcep_id;
		label first_plc_athc = "Episode First Legal Auth. for Placement";
		label first_lg_auth_s_dt = "Episode First Legal Auth. Start Date";
		label last_plc_athc = "Episode Last Legal Auth. for Placement";
		label last_lg_auth_s_dt = "Episode Level Last Legal Auth. Start Date";
	run;

data episode_02;
	merge episode_01 lg_auth_04;
	by fkclient_t rev_plcep_id;
        if pe_e_dt gt &DODEX. then pe_e_dt = .; 
	if pe_e_dt = . then tpe_e_dt = &DODEX.;
		else tpe_e_dt = pe_e_dt;
	format tpe_e_dt mmddyy10.;
	run;


/*add 3 Spell Counters: All spells, welfare-only spells, probation-only spells.*/
	/*when 2 episodes start on the same date, defer to episode ending on later
	date as earlier episode - this makes the first episode the longer episode - gives
	a more realistic view of episode length*/

proc sort data = episode_02;
	by fkclient_t pe_s_dt descending tpe_e_dt;
	run;

/*modified to only count episodes with at least one foster care placement,
	i.e., to exclude non-foster care only (NFC_ONLY) episodes 2019.04.23 j magruder*/

data episode_04;
	set episode_02;
	by fkclient_t pe_s_dt descending tpe_e_dt;
	retain fkclient_t SPELL a34_spellc pr_spellc;
	if first.fkclient_t then do;
		SPELL = 0;
		A34_SPELLc = 0;
		PR_SPELLc = 0;
		end;
	if NFC_ONLY ne 1 then SPELL = spell+1;
	if agy_rspc = 34 and NFC_ONLY ne 1 then do;
		A34_SPELLC = a34_spellc +1;
		A34_SPELL = a34_spellc;
		PR_SPELL = 0;
		end;
	if agy_rspc in(33, 5603) and NFC_ONLY ne 1 then do;
		pr_spellc = pr_spellc+1;
		PR_SPELL = pr_spellc;
		A34_SPELL = 0;
		end;
	label spell = "Episode Counter";
	label a34_spell = "Welfare Episode Counter";
	label pr_spell = "Probation Episode Counter";
	run;

data episode_06 (drop = a34_spellc pr_spellc tpe_e_dt);
	set episode_04;
	by fkclient_t pe_s_dt descending tpe_e_dt;
	run;



/*add child address data at time of removal*/

/*get client address data for children with based on episode start date.
	Note: when more than one address active on episode date, the address with the
	latest start date is selected as it appears that addresses of previously known 
	clients are updated at the time of referral*/
/*There is some risk that the child's address entered in CWS/CMS will be the placement 
	address. Indeed 2.1% of	all first placements have a Zip Code match and an address 
	start date that is the same as the first placement start date. These may be problematic, 
	but address start dates matching placement start dates are not unusual - 16.4% of all
	first placements have an address start date that equals the placement start date 
	and 12.6% of first placements have a Zip Code that equals the apparent removal Zip Code.
	i.e, about 13% of placements that share a start date with the removal address start
	date also share a Zip Code*/
	/*10 days addd to address end date to allow use of address ending just before placement
	start when that is the most recent address available*/

proc sql;
	create table rem_addr_10 as select
	episode_06.fkclient_t,
	episode_06.rev_plcep_id,
	episode_06.pe_s_dt,
	episode_06.pe_e_dt,
	cl_addrt.fkaddrs_t as REM_FKADDRS_T label "Address at Removal Date",
	cl_addrt.eff_strtdt as rem_addrs_s_dt format mmddyy10.,
	cl_addrt.eff_end_dt as rem_addrs_e_dt format mmddyy10.
	from episode_06 left join cws.cl_addrt
	on episode_06.fkclient_t = cl_addrt.fkclient_t 
		and cl_addrt.eff_strtdt le pe_s_dt and
		(cl_addrt.eff_end_dt+10 ge pe_s_dt or cl_addrt.eff_end_dt = .)
	order by fkclient_t, rev_plcep_id, cl_addrt.eff_strtdt;
	quit;

/*take the address with the latest start date on or before the removal date*/
data rem_addr_12;
	set rem_addr_10;
	by fkclient_t rev_plcep_id rem_addrs_s_dt;
	if last.rev_plcep_id;
	run;


/*add address data for residence at time of removal*/
proc sql;
	create table rem_addr_16 as select
	rem_addr_12.*,
	addrs_t.zip_no as REM_ZIP_NO label "ZipCode at Removal Date",
	addrs_t.state_c as REM_STATE_C label "State at Removal Date",
	addrs_t.city_nm as REM_CITY_NM label "City at Removal Date"
	from rem_addr_12 left join cws.addrs_t
	on rem_addr_12.rem_fkaddrs_t = addrs_t.identifier
	order by fkclient_t, rev_plcep_id;
	quit;  

/*add address data back into the episode file*/
proc sql;
	create table episode_07 as select
	episode_06.*,
	rem_addr_16.rem_fkaddrs_t,
	rem_addr_16.rem_zip_no,
	rem_addr_16.rem_state_c,
	rem_addr_16.rem_city_nm /*,
	rem_addr_16.rem_addrs_s_dt,
	rem_addr_16.rem_addrs_e_dt*/
	from episode_06 left join rem_addr_16
	on episode_06.rev_plcep_id = rem_addr_16.rev_plcep_id
	order by fkclient_t, rev_plcep_id;
	quit;

/*create record for each original episode to allow search for individual placements*/
proc sql;
	create table episode_08 as select
	ep_07.fkclient_t,
	child_episode_06.plcep_id,
	ep_07.rev_plcep_id,
	ep_07.pe_s_dt,
	ep_07.pe_e_dt,
	ep_07.cnty_rem
	from episode_07 as ep_07 left join child_episode_06
	on episode_07.rev_plcep_id = child_episode_06.rev_plcep_id
	order by fkclient_t, rev_plcep_id;
	quit;




/*end of EPISODE MODULE*/

/*Note: This episode table does not include the predominate placement type within the episode
	(to date) as this can only be calculated after the placement data have been organized
	which happens in the next module. This is reason file is "episode_08" rather than
	"epsode"*/

/*

proc freq data = episode_07;
	table rmv_frm1c rmv_frm2c;
	where year(pe_s_dt) = 2019;
	format rmv_frm1c rmv_frm2c syscode.;
	run;

proc tabulate data = episode_07 missing format = comma6.0;
	options pageno = 1;
	class rmv_frm1c rmv_frm2c;
	table rmv_frm1c=' ' all, all*N=' ' rmv_frm2c*N=' '
		/box = "Caretaker 1 Type";
	where year(pe_s_dt) = 2019;
	format rmv_frm1c rmv_frm2c syscode.;
	label rmv_frm2c = "Caretaker 2 Type";
	title3 "Person(s) Child Removed From";
	run;
*/


/*PLACEMENT MODULE*/

/*find all standard foster care placements associated with each episode*/
/*note that the match is with plcep_id, not rev_plcep_id as placements can appear in
  any of the concurrent episodes*/
proc sql;
	create table ohp_02 as select
	ep.fkclient_t,
	ep.rev_plcep_id,
	ep.plcep_id,
	ep.pe_s_dt,
	ep.pe_e_dt,
	ep.cnty_rem,
	O_HM_PLT.IDENTIFIER as OHMPL_ID label="O_HM_PLT.IDENTIFIER" ,
	O_HM_PLT.SCP_RLTC length=3 format=4.,
	O_HM_PLT.START_DT as OH_S_DT length=4 format=mmddyy10. label="O_HM_PLT.START_DT",
	O_HM_PLT.END_DT as OH_E_DT length=4 format=mmddyy10. label="O_HM_PLT.END_DT",
	O_HM_PLT.FKPLC_HM_T,
	O_HM_PLT.PLCG_RNC length=4,
	O_HM_PLT.TRBSPH_CD, /*added 2022.02..14 j magruder*/
	O_HM_PLT.TDCNSL_IND /*added 2022.02..14 j magruder*/
	from episode_08 as ep, cws.o_hm_plt
	where ep.plcep_id = O_HM_PLT.fkplc_eps0
		and o_hm_plt.start_dt le &cutoff.
	order by fkclient_t, pe_s_dt, start_dt;
	quit;

/*add facility characteristic data*/
proc sql;
	create table ohp_04 as select
		ohp_02.*,
		PLC_HM_T.PLC_FCLC as FAC_TYPE, /* length=6, */
		PLC_HM_T.OPRTD_BYID,
		case when plc_hm_t.gvr_entc > 1067 
			and plc_hm_t.gvr_entc < 1126 then
		(put(PLC_HM_T.GVR_ENTC-1067, Z2.)) end as OH_CNTY 
			label = "Placement Level Placement County",/*added 2022.02.14 j magruder*/
		PLC_HM_T.F_STATE_C as OH_STATE_CD,
		PLC_HM_T.ZIP_NO as OH_ZIP_NO,/*added 2020.06.17 j magruder*/
		zipstate(plc_hm_t.zip_no) as OH_ZIP_STATE label = "State from ZipCode",
			/*added 2020.06.17 j magruder*/
		plc_hm_t.trnhsg_fac /*added 2018.08.03 j magruder*/
	FROM ohp_02 left join CWS.PLC_HM_T
	on ohp_02.FKPLC_HM_T=PLC_HM_T.IDENTIFIER
	order by fkclient_t, pe_s_dt, oh_s_dt;
	quit;

/*add CWS/CMS placement counter*/
proc sql;
	create table ohp_06 as select
	ohp_04.*,
	plc_cnt.plc_cnt_no
	from ohp_04 left join cws.plc_cnt
	on ohp_04.ohmpl_id = plc_cnt.estblsh_id
	order by fkclient_t, pe_s_dt, oh_s_dt;
	quit;

/* Add Adoptive Placements */
data adplc_t;
   set CWS.ADPLC_T ;

   /*delete record if no agreement signed date, no removal date, and no final date -
     these seem to be records started because child in home, but no actual adoption
     activity yet added 2018.08.02 j magruder*/
   if adagsgn_dt = . and rmv_dt = . and final_dt = . then delete;
   if adagsgn_dt > &cutoff. then delete;
   *** Assign placement facility type - 2222 is not in CWS/CMS;
   FAC_TYPE=2222;
   *ADOPT_FLAG = 'Y';

   *** There is no way to tell if the adoptive placement home is with a guardian or not,
       so the two options are Relative/NonGuardian (1639) and NonRelative NonGuardian (1637);
   if ADPREL_IND='Y' then SCP_RLTC=1639;
   else SCP_RLTC=1637;

   *** We will need to add the following placement change reason codes to the format
      5279 = Death of adoptive parent(s)
      5280 = Abuse or neglect of child
      5281 = Inability to meet childs needs
      5282 = Childs behavior or care needs
      5283 = Factors not directly related to the child

   ;
   if ADPLCREM_C=5278 then PLCG_RNC=1430;      ** Death of Child;
   else if ADPLCREM_C=5284 then PLCG_RNC=1442; ** Other;
   *else if adplcrem_c = 5282 then plcg_rnc = 5417; ** Child's behavior;
   else PLCG_RNC=ADPLCREM_C;


   *** Assign variables to the appropriate O_HM_PLT structure;
   OHMPL_ID=IDENTIFIER;
   OH_S_DT=ADAGSGN_DT;
   /*if no agreement signed date but a first date in the home, set oh_s_dt to
   	that first date - only affects 5 records 2018.08.02 j magruder*/
   if oh_s_dt = . then oh_s_dt = first_dt;
  
   if RMV_DT ne . and FINAL_DT = . then OH_E_DT = RMV_DT;
   else OH_E_DT=FINAL_DT;
   format oh_s_dt oh_e_dt mmddyy10.;
   keep FKADHMST_T OHMPL_ID OH_S_DT OH_E_DT SCP_RLTC PLCG_RNC FAC_TYPE /*ADOPT_FLAG*/ fkadchcl_t;
run;

/*get fkclient_t for all adoptive children (added 8/2/18) j magruder*/
proc sql;	
	create table adplc_t_01 as select
          a.*,
          b.fkchld_clt as fkclient_t
   from adplc_t as a left join CWS.adchcl_t as b
   on a.fkadchcl_t = b.identifier;
quit;

/*get CWS/CMS placement counter for adoptive placements*/
proc sql;
   create table adplc_t_02 as
   select a.*,
          b.PLC_CNT_NO length=3
   from adplc_t_01 as a left join CWS.PLC_CNT as b
   on a.OHMPL_ID=b.ESTBLSH_ID;
quit;

/*

proc print data = adplc_t_02 (obs = 60);
	*where oh_s_dt = . and oh_e_dt ne .;
	run;
*/

proc sql;
   create table adopt1 as
   select a.*,
                  b.FKHM_SCP_T,
                  b.FKHM_SCP_0,
                  b.FKHM_SCP_1
   from adplc_t_02 as a left join CWS.ADHMST_T as b
   on a.FKADHMST_T=b.IDENTIFIER
   ;
quit;

*** The file did not gain any records (no duplication of records) from joining the
    adoption placement and adoption home study tables.  ***;

proc sql;

   *** It is possible to link the adoption home study table to the Placement home
       substitute care provider table six ways:  FKHM_SCP_T=FKSB_PVDRT (1st adopt parent),
       FKHM_SCP_0=FKPLC_HM_T (1st adopt parent), FKHM_SCP_1=THIRD_ID (1st adopt parent),
       FKHM_SCP_2=FKSB_PVDRT (2nd adopt parent), FKHM_SCP_3=FKPLC_HM_T (2nd adopt parent),
       FKHM_SCP_4=THIRD_ID (2nd adopt parent).  But, we only need the placement home of the
       primary provider - so I only use the first join.;

   create table adopt2a as
   select a.*,
                  b.FKPLC_HM_T,
                  b.START_DT,
                  b.END_DT
   from adopt1 as a left join CWS.HM_SCP_T as b
   on a.FKHM_SCP_T=b.FKSB_PVDRT and   
      a.FKHM_SCP_0=b.FKPLC_HM_T and   
      a.FKHM_SCP_1=b.THIRD_ID
 ;
quit;

*** There are instances where the adoption home study table links to multiple placement
    home sub care provider records.  The code below selects the placement home that was open
    most recently PRIOR to the start of the adoption home placement. ***;

data adopt2b;
   set adopt2a;

   *** The order variable is set to give priority to placements open on or before
       the start of the adoption placement;
   if START_DT gt OH_S_DT then order=2;
   else if (START_DT=. or OH_S_DT=.) then order=3;
   else order=1;

   *** The difference variable is used to organize the various placements - the absolute
       value is used for cases where there are more than one placement that occurs after
       the start of the adoption placement - in those cases we want the first one opened.;
   diff=abs(OH_S_DT-START_DT);
   drop FKADHMST_T fkadchcl_t FKHM_SCP_T START_DT END_DT;
run;

*** Sort by ID, ORDER, then DIFF to maintain the proper hierarchy ***;
proc sort data=adopt2b; by OHMPL_ID order diff; run;

*** Unduplicate by selecting the last ID - the one that has the newest placement home
    substitute care provider start date after the placement start date. ***;

data adopt_ohmpl;
   set adopt2b;
   by OHMPL_ID ;
   if last.OHMPL_ID ;

   *** The variable FKPLC_HM_T is the link to the placement home table ***;

   drop diff order;

   if OH_S_DT = . and OH_E_DT = . then delete;

run;

proc sort data = adopt_ohmpl;
by FKCLIENT_T OH_S_DT PLC_CNT_NO;
run;


/*	Place adoptive placement in placement episode. The first two steps require
	that either the adoptive placement at least starts within an episode
	(adopt_ohmpl_01) or, if not, at least ends within an episode
	(adopt_ohmpl_04). About 97.7% of adoptive placements fit in one of these 2
	groups and are included. Another 0.7% of children in adoptive placements
	have episodes in CWS/CMS, but the episodes are not at the same time as
	the adoptive placement or are only 1 day long and thus excluded from UCB_FC*/
	/*Some placements in the first group fall into 2 episodes (ca 0.3%). 
	Taking the last episode is someshat arbitrary and often leaves a 
	placement in its own episode*/ 
	/*This process excludes adoptions with no associated episode. These probably 
	would be adoptions of children not from the foster care/CWS system*/
proc sql;
create table adopt_ohmpl_01a as
select 
	x.fkclient_t,
	x.rev_plcep_id,
	x.pe_s_dt,
	x.pe_e_dt,
	x.cnty_rem,
       /*y.ADOPT_FLAG,*/
       y.FAC_TYPE,
       y.FKPLC_HM_T,
       y.OHMPL_ID,
       y.OH_E_DT,
       y.OH_S_DT,
       y.PLCG_RNC,
       y.PLC_CNT_NO,
       y.SCP_RLTC      
from episode_07 as x, adopt_ohmpl as y
where x.FKCLIENT_T = y.FKCLIENT_T and
      x.PE_S_DT <= y.OH_S_DT and
     (y.OH_S_DT <= x.PE_E_DT or x.PE_E_DT = .)
	 /*find episode that adoptive placement starts within*/
order by fkclient_t, ohmpl_id, pe_s_dt, oh_s_dt;

/*pick last episode when placement falls in 2 episodes*/
data adopt_ohmpl_01;
	set adopt_ohmpl_01a;
	by fkclient_t ohmpl_id pe_s_dt oh_s_dt;
	if last.ohmpl_id;
	run;

/*find adoptive placements not in adopt_ohmpl_01*/
proc sql;
create table adopt_ohmpl_04a as
select *
from adopt_ohmpl
where OHMPL_ID not in (select OHMPL_ID from adopt_ohmpl_01);
quit;

proc sql;
create table adopt_ohmpl_04b as
select  
	x.fkclient_t,
	x.rev_plcep_id,
	x.pe_s_dt,
	x.pe_e_dt,
	x.cnty_rem,
       /*y.ADOPT_FLAG,*/
       y.FAC_TYPE,
       y.FKPLC_HM_T,
       y.OHMPL_ID,
       y.OH_E_DT,
       y.OH_S_DT,
       y.PLCG_RNC,
       y.PLC_CNT_NO,
       y.SCP_RLTC
from episode_07 as x, adopt_ohmpl_04a as y
where x.FKCLIENT_T = y.FKCLIENT_T and
      x.PE_S_DT <= y.OH_E_DT and
     (y.OH_E_DT <= x.PE_E_DT or x.PE_E_DT = .)
	 /*find episode that adoptive placement ends within*/
	order by fkclient_t, ohmpl_id, pe_s_dt, pe_e_dt;

quit;

/*pick last episode when placement falls in 2 episodes*/
data adopt_ohmpl_04;
	set adopt_ohmpl_04b;
	by fkclient_t ohmpl_id pe_s_dt oh_s_dt;
	if last.ohmpl_id;
	run;


/*merge adoptive placement files together - revised 2020.06.17 j magruder*/

data adopt_ohmpl_06;
	set adopt_ohmpl_01 adopt_ohmpl_04;
	run;

/*add facility characteristic data to adoption records 2020.06.17 j magruder*/
proc sql;
	create table adopt_ohmpl_08 as select
		adopt_ohmpl_06.*,
		case when plc_hm_t.gvr_entc > 1067 
			and plc_hm_t.gvr_entc < 1126 then
		(put(PLC_HM_T.GVR_ENTC-1067, Z2.)) end as OH_CNTY 
			label = "Placement Level Placement County",/*added 2022.02.14 j magruder*/
		PLC_HM_T.F_STATE_C as OH_STATE_CD,/*added 2020.06.17 j magruder*/
		PLC_HM_T.ZIP_NO as OH_ZIP_NO,/*added 2020.06.17 j magruder*/
		zipstate(plc_hm_t.zip_no) as oh_zip_state/*added 2020.06.17 j magruder*/
	FROM adopt_ohmpl_06 left join CWS.PLC_HM_T
	on adopt_ohmpl_06.FKPLC_HM_T=PLC_HM_T.IDENTIFIER
	order by fkclient_t;
	quit;

/*merge adoptive placements into general placement file*/
data ohp_08;
set ohp_06 adopt_ohmpl_08;
run;

proc sort data = ohp_08;
	by fkclient_t pe_s_dt oh_s_dt;
	run;


/*add non-foster care placements*/
/*need to modify facility types - assign a code for non-foster care*/
/*at this point all missing facility types reclasses as [nfc] other*/
/*the non-foster care placement identifier is recoded as placement identifier (ohmpl_id)
	for sorting later in program*/
proc sql;
	create table nfc_02 as select
		ep.fkclient_t,
		ep.rev_plcep_id,
		ep.plcep_id,
		ep.pe_s_dt,
		ep.pe_e_dt,
		ep.cnty_rem,
		nfc_plct.identifier as ohmpl_id,
		nfc_plct.start_dt as oh_s_dt format mmddyy10.,
		nfc_plct.end_dt as oh_e_dt format mmddyy10.,
		nfc_plct.nfc_fclc as fac_type,
		nfc_plct.nfc_trmc,
		case when nfc_plct.gvr_entc > 1067 
			and nfc_plct.gvr_entc < 1126 then
		(put(NFC_PLCT.GVR_ENTC-1067, Z2.)) end as OH_CNTY 
			label = "Placement Level Placement County",/*added 2022.02.14 j magruder*/
		nfc_plct.F_STATE_C as OH_STATE_CD,/*added 2020.06.17 j magruder*/
		nfc_plct.F_ZIP_NO as OH_ZIP_NO,/*added 2020.06.17 j magruder*/
		zipstate(nfc_plct.F_ZIP_NO) as OH_ZIP_STATE/*added 2020.06.17 j magruder*/
	from episode_08 as ep, cws.nfc_plct
	where ep.plcep_id = nfc_plct.fkplc_eps0
		and (nfc_plct.end_dt - nfc_plct.start_dt > 0 or nfc_plct.end_dt = .)
		and nfc_plct.start_dt le &cutoff. and nfc_plct.start_dt ne .
	order by fkclient_t, pe_s_dt, start_dt;
	quit;
/*
proc print data = nfc_02 (obs = 40); 
	var plcep_id oh_s_dt oh_e_dt fac_type ohmpl_id;
	format fac_type syscode.;
run;

proc freq data = nfc_02 ;
	table fac_type;
	run;

*/
 
/*recode to match stardard out of home placement codes for placement end reasons*/
data nfc_04 (drop = nfc_trmc);
	set nfc_02;
	if fac_type = . then fac_type = 6371; 			 /*other*/
	if nfc_trmc = 5501 then plcg_rnc = 1437;           /*Committed to State Hospital*/
		else if nfc_trmc = 5502 then plcg_rnc = 1431; /*Ran Away*/
		else if nfc_trmc = 5503 then plcg_rnc = 5423; 
				/*Placed in FC coded as move to lower level of care*/
		else if nfc_trmc = 5504 then plcg_rnc = 1442; /*Other*/ 
		else if nfc_trmc = 6312 then plcg_rnc = 6311; /*Adjudged 601/602*/
		else if nfc_trmc = 6534 then plcg_rnc = 6527; /*Tribal Customary Adoption*/
		else if nfc_trmc = 6807 then plcg_rnc = 6799; /*NMD Moved to Unapproved Pmt*/
		else if nfc_trmc = 6808 then plcg_rnc = 1436; /*Incarcerated*/
		else if nfc_trmc = 6809 then plcg_rnc = 6802; /*Moved to [Another] NFC Pmt*/
		else if nfc_trmc = 6810 then plcg_rnc = 6796; /*Moved to SILP*/
		else if nfc_trmc = 6811 then plcg_rnc = 6797; /*Moved to THPP/THP+FC*/
	NFC = 1;
	label NFC = "Non-Foster Care Placement";
	/*but children in runaway status at start of episode and children in hospital for more than
		30 days who have other foster care placements are included in the AFCARS population and
		thus these placements need to be included in the AFCARS file j magruder 2020.11.17*/
	if fac_type in (7164, 7181) then NFC = .; /*child ran away before (7164) or after (7181) placement*/
	if fac_type in (6363, 6366,6367)
		and (oh_e_dt - oh_s_dt > 30 
			or (oh_e_dt = . and &cutoff. - oh_s_dt > 30)) then NFC = .;
			/*child hospitalized more than 30 days*/
	run;

data ohp_09;
	set ohp_08 nfc_04;
	if oh_e_dt = . then toh_e_dt = &DODEX.;
		else toh_e_dt = oh_e_dt;
	run;

proc sort data = ohp_09;
	by fkclient_t oh_s_dt toh_e_dt;
	run;

/*
proc contents data = ohp_09 varnum;
	run;

proc print data = ohp_09 (obs = 40);
	var fkclient_t pe_s_dt pe_e_dt oh_s_dt oh_e_dt fac_type nfc plcg_rnc plc_cnt_no;
	run;

proc freq data = ohp_09;
	table fac_type;
	format fac_type syscode.;
	run;
*/

/*eliminate duplicate placements (situations where two records have the same 
		placement start and end dates, giving priority to 1) adoptive home
		and 2) relative home types 3) placements in a placement episode
		jmagruder 2021.05.25*/
data priority_05;
	set ohp_09;
	by fkclient_t oh_s_dt toh_e_dt;
	if fac_type = 2222 then priority_flag = 1; 
		else if fac_type = 1421 then priority_flag = 2;
		else if fac_type = 7181 then priority_flag = 9; 
		/*added 2022.02.15 j magruder to give runaways lower priority than known placement*/
		else if fac_type = 1419 then priority_flag = 8; 
		/*added 2022.02.15 j magruder to give court ordered lower priority than known placement*/
		else if (oh_s_dt le pe_e_dt or pe_e_dt = .)
			and (toh_e_dt = . or toh_e_dt ge pe_s_dt) then priority_flag = 3;
		else priority_flag = 7;
	run;

proc sort data = priority_05;
	by fkclient_t oh_s_dt toh_e_dt priority_flag;
	run;

/*
proc print data = priority_05 (obs = 200);
	var fkclient_t pe_s_dt pe_e_dt plcep_id oh_s_dt oh_e_dt fkplc_hm_t fac_type priority_flag;
	*where adflag = 9;
	where fkclient_t = "2SmBJIH197";
	run;
*/	

data ohp_10;
	set priority_05;
	by fkclient_t oh_s_dt toh_e_dt priority_flag;
	if first.toh_e_dt;
	run;



/* Correct for changes in facility type from cws.pfachist file as modified 
   by data from table created by Cory Benton of CDSS modified table is dwh.ucb_pfachist. */

/*j magruder 2018.04.10*/
/*limit to changes occurring before the cutoff (end of extract quarter) date*/
/*data ucb_pfachist;
	set dwh.ucb_pfachist;
	if fty_s_dt le &cutoff.; 
	run;*/
	/*reference to cutoff date removed as changes between cutoff and extract
	 dates affect historic placement types - reporting historic placements as having
	 type entered between cutoff and extract dates j magruder 2022.06.27*/

proc sort data = dwh.ucb_pfachist out = pfachist_modi;
	by fkplc_hm_t fty_s_dt;
	run;

/* create table including only those placement facilities with multiple placement 
   types over time (cws.pfachist file includes records for almost all placements */

data pfachist_modi_01;
 	set pfachist_modi;
	by fkplc_hm_t fty_s_dt;
	if first.fkplc_hm_t = 0 or last.fkplc_hm_t = 0; /*i.e., multiple records for 1 facility*/
	run;

/* add multiple placement type data to ohp_14 file */
proc sql;
	create table ohp_15_2 as select
	ohp_10.*,
	pm.plc_fclc as plc_fclc_m,
	pm.fty_s_dt,
	pm.fty_e_dt
	from ohp_10 left join pfachist_modi_01 as pm
	on ohp_10.fkplc_hm_t = pm.fkplc_hm_t
	order by ohmpl_id, fty_s_dt;
	quit;

/* separate placements where facility has had more than one status (ohp_02_2a) from those 
   where the facility has had only one status (ohp_02_2b) for processing efficiency */

data ohp_15_2a ohp_15_2b;
	set ohp_15_2;
	by ohmpl_id fty_s_dt;
	if fty_s_dt = . then do; /*i.e., only single facility type record, thus no change needed*/
		oh_s_dt_m = oh_s_dt;
		oh_e_dt_m = oh_e_dt;
		format oh_s_dt_m oh_e_dt_m mmddyy10.;
		output ohp_15_2b;/*set file aside*/
		end;
	else do;
		/* for those placements where the faciity had multiple possible status over time,
		   identify the status/statuses in effect during the specific pacement */
	if (fty_s_dt le oh_e_dt or oh_e_dt = .)
		and (fty_e_dt > oh_s_dt or fty_e_dt = .)
		then flag = 1;
		output ohp_15_2a; /*placement facilities with more than one status - not
				necessarily during this particular placement. Flag = 1 means
				during this particluar placement*/
		end;
	run;


/* although at this time there are no placements where at least one row doesn't have the status
   flag set to 1, it might be  possible. So, do this step */

proc sort data= ohp_15_2a;
	by ohmpl_id descending flag;
	run;

/* retain record where no row has status flag set to 1 - ca 17 records - i.e., no status in
	effect during placement*/
data error_01;
	set ohp_15_2a;
	by ohmpl_id descending flag;
	if first.ohmpl_id and flag ne 1;
		oh_s_dt_m = oh_s_dt;
		oh_e_dt_m = oh_e_dt;
		format oh_s_dt_m oh_e_dt_m mmddyy10.;
	run;
/*proc contents data = error_01 varnum; run;*/
/*proc print data = error_01 (obs = 20);
		run;
	*/

/* flag = 1 means facility had more than one status, not necessarily during this placement */
proc sort data = ohp_15_2a out = ohp_15_4;
	by ohmpl_id fty_s_dt;
	where flag = 1;
	run;

/* if facility type was the same during all of the placement, just retain the modified facility type and 
   don't change oh_s_dt and oh_e_dt - the effect is to reset the facility type from what it is now to what it was
   during the placement If the facility type changed during the placement then create new oh_s_dt and oh_e_dt so 
   that placement has multiple records, one for each placement type */

data ohp_15_4a;
	set ohp_15_4;
	by ohmpl_id fty_s_dt;
	if first.ohmpl_id =1 and last.ohmpl_id = 1 then do;
		oh_s_dt_m = oh_s_dt;
		oh_e_dt_m = oh_e_dt;
		end;
	else if first.ohmpl_id = 1 and last.ohmpl_id = 0 then do;
		oh_s_dt_m = oh_s_dt;
		oh_e_dt_m = fty_e_dt;
		end;
	else if first.ohmpl_id = 0 and last.ohmpl_id = 0 then do;
		oh_s_dt_m = fty_s_dt;
		oh_e_dt_m = fty_e_dt;
		end; 
	else if first.ohmpl_id = 0 and last.ohmpl_id = 1 then do;
		oh_s_dt_m = fty_s_dt;
		oh_e_dt_m = oh_e_dt;
		end; 
	format oh_s_dt_m oh_e_dt_m mmddyy10.;
run;

/*reset facility type unless it is adoptive home*/
data ohp_15_5;
	set ohp_15_2b error_01 ohp_15_4a;
	if fac_type ne 2222 /*added 2021.04.01 j magruder to retain pre-adopt placement*/
		and plc_fclc_m ne . then fac_type_n = plc_fclc_m;
		else fac_type_n = fac_type;
	
run;

proc sort data = ohp_15_5;
	by fkclient_t oh_s_dt oh_s_dt_m;
	run;

data ohp_17 (drop = oh_s_dt oh_e_dt plc_fclc_m fty_s_dt fty_e_dt flag fac_type_n toh_e_dt
              rename = (oh_s_dt_m = OH_S_DT oh_e_dt_m = OH_E_DT));
	set ohp_15_5;
	if fac_type ne 2222 /*added 2021.04.01 j magruder to retain pre-adopt placement*/
		and plc_fclc_m ne . then fac_type = plc_fclc_m;
	label oh_s_dt_m = "O_HM_PLT.START_DT";
	label oh_e_dt_m = "O_HM_PLT.END_DT";
	informat oh_s_dt_m oh_e_dt_m DATE9.;
	run;

/*If court specified placement and caretaker is kin then change facility type
  to kin. Tkhis is becuse courts often order placement in kin home pending Resource
  Family Home approval. j magruder 2024.04.15*/

data ohp_18;
	set ohp_17;
	if fac_type = 1419 and scp_rltc in(1638,1639,6990,6991) then fac_type = 1421;
	run;

/*
proc freq data = ohp_20;
	table fac_type;
	format fac_type syscode.;
	run;
*/
/*end of facility multi-type sub-module*/


/*add group home, etc. facility names*/
/* Get names of group homes */

proc sql;

create table gh_01 as
select x.FKCLIENT_T,
       x.OHMPL_ID,
       x.FKPLC_HM_T,
       x.OPRTD_BYID,
       x.OH_S_DT,
       y.FACLTY_NM as GROUP_HM_NM label = "Group Home Name"
from ohp_04 as x, cws.FACLTY_NM as y
where x.FKPLC_HM_T = y.FKPLC_HM_T
order by x.FKCLIENT_T;

/* Add names of group home parent organizations */

create table gh_02 as
select x.FKCLIENT_T,
       x.OHMPL_ID,
       x.FKPLC_HM_T,
       x.OPRTD_BYID,
       x.OH_S_DT,
       x.GROUP_HM_NM,
       y.GRHM_ORGNM as GH_ORG label = "Group Home Parent Org."
from gh_01 as x left join cws.GRHM_OGT as y
on x.OPRTD_BYID = y.IDENTIFIER
order by x.FKCLIENT_T;

/* Get names of Foster Family Agencies */

create table ffa_01 as
select x.FKCLIENT_T,
       x.OHMPL_ID,
       x.FKPLC_HM_T,
       x.OPRTD_BYID,
       x.OH_S_DT,
       y.FACLTY_NM as FFA_NAME label "Foster Family Agency Name"
from ohp_04 as x, cws.FACLTY_NM as y
where x.OPRTD_BYID = y.FKPLC_HM_T
order by x.FKCLIENT_T;

quit;

/* Add group home and FFA name data back into file */

proc sql;

create table ohp_19 as
select x.*,
       y.GROUP_HM_NM,
       y.GH_ORG
from ohp_18 as x left join gh_02 as y
on x.OHMPL_ID = y.OHMPL_ID
    and x.oh_s_dt = y.oh_s_dt
order by x.FKCLIENT_T, x.OH_S_DT;

create table ohp_20 as
select x.*,
       y.FFA_NAME
from ohp_19 as x left join ffa_01 as y
on x.OHMPL_ID = y.OHMPL_ID
    and x.oh_s_dt = y.oh_s_dt
order by x.FKCLIENT_T, x.OH_S_DT;

quit;


/*add group home rate classification data - note this feature was only added to CWS/CMS as group home
  rate structure was being phased out 2018.05.22 j magruder*/
proc sql;
	create table rcl_01 as select
	ohp_20.fkclient_t,
	ohp_20.ohmpl_id,
	rcl.ghm_rclc,
	rcl.start_dt as rcl_s_dt format mmddyy10.,
	rcl.end_dt as rcl_e_dt format mmddyy10.
	from ohp_20, cws.ghm_rclt as rcl
	where ohp_20.fkplc_hm_t = rcl.fkplc_hm_t
		and (rcl.start_dt le ohp_20.oh_e_dt or ohp_20.oh_e_dt = .)
		and (rcl.end_dt = . or rcl.end_dt ge ohp_20.oh_s_dt)
	order by fkclient_t, ohmpl_id, rcl_s_dt;
	quit;

/*	Although unlikely, the rate classification level for a facility could have changed during a placement. 
	This takes the last rate classification (i.e., the one in effect at the end of the placement)*/
data rcl_02;
	set rcl_01;
	by fkclient_t ohmpl_id rcl_s_dt;
	if last.ohmpl_id;
	run;
 
/*add rate classification field to UCB_FC file*/
proc sql;
	create table ohp_26 as select
	ohp_20.*,
	rcl_02.ghm_rclc
	from ohp_20 left join rcl_02
	on ohp_20.ohmpl_id = rcl_02.ohmpl_id
	order by fkclient_t, oh_s_dt;
	quit;


/* Transform Resource Family Approval homes to separate kin and non kin homes.
   RFH - Relative homes become 6914.1, RFH - Non-Relative homes become 6914.2
   RFH FFA - Relative homes become 6915.1, RFH FFA - Non-Relative homes become 6915.2
   modified 2016Q4 to accommodate implementation of RFH home facility types */ 

data ohp_28;
set ohp_26;
format fac_type 6.1;
if fac_type = 6914 and scp_rltc in(1638, 1639, 6990, 6991) then fac_type = 6914.1;        /* RFA Relative */
else if fac_type = 6914 then fac_type = 6914.2;                                           /* RFA Non-Relative */
else if fac_type = 6915 and scp_rltc in(1638, 1639, 6990, 6991) then fac_type = 6915.1;   /* RFA FFA Relative */
else if fac_type = 6915 then fac_type = 6915.2;                                           /* RFA FFA Non-Relative */
rename

PLC_CNT_NO = PLCMNT;
run;

/*  At request of CDSS, reclass facility type to show placements in RFH and STRTP facilities
    as placements in the preexisting facility type as noted. This is a temporary measure
    pending modification to CWS/CMS to allow reporting of historic facility type for facilities
    converting to RFH or STRTP, e.g., foster home to RFH or group home to STRTP
    j magruder 2017.08.07 */
/*	This keeps fac_type as field name for file with all types and plc_fclc as file name for
	collaplsed types*/

data ohp_32;
set ohp_28;

if fac_type = 6914.1 then PLC_FCLC = 1421;      /*RFA_Rel as Kin*/
else if fac_type = 6914.2 then PLC_FCLC = 1416; /*RFA-NR as Foster*/
else if fac_type = 6915.1 then PLC_FCLC = 1421; /*RFA_FFA_Rel as Kin*/
else if fac_type = 6915.2 then PLC_FCLC = 2200; /*RFA_FFA-RN as FFA Home*/
else if fac_type = 6916 then PLC_FCLC = 1417;   /*STRTP as Group Home*/
else PLC_FCLC = fac_type;
label PLC_FCLC = "pre RFA Placement Facility Type";
label FAC_TYPE = "post RFA Placement Facility Type";
run;

/*note - Authority for placement was at this location and has been relocated after ohp_50 so
  that runaway authority for placement is calcualted as well. Otherwise, the results are the same
  j magruder 2023.10.8*/







/*Assign placements to office (thus county) based on office assignment during first 10 days of
	placement - lag because offices not always assigned on placement start date, also child may
	come into care and then be transferred to "correct" county*/
proc sql;
	create table assign_81 as
		select ohp_32.fkclient_t,
			ohp_32.ohmpl_id,
			ohp_32.oh_s_dt,
			ohp_32.oh_e_dt,
			ohp_32.cnty_rem,
			ucb_office.*
			from ohp_32 left join dwh.ucb_office
		on ucb_office.fkchld_clt = ohp_32.fkclient_t
			and office_s_dt le (oh_s_dt+10)
			and (office_e_dt = . or office_e_dt ge oh_s_dt)
		order by ohmpl_id, oh_s_dt, office_s_dt;
		;
	quit;

data assign_81a;
	set assign_81;
	by ohmpl_id oh_s_dt office_s_dt;
	if last.oh_s_dt;
	run;

/* Not all records will have office assignment data - especially probation records from 
   before about 2010. Separate those that do (assign_82a) from those that don't (assign_82b) */

data assign_82a assign_82b (drop = fkchld_clt cnty_spfcd office_s_dt office_e_dt type);
	set assign_81a;
	if office_s_dt ne . then do;
		source = 1;
		output assign_82a;
		end;
		else output assign_82b;
	run;

/* For those with no office data, see if the State ID county data identifies a county with 
   start and end dates */

proc sql;
	create table assign_82b1 as
		select st_id.*,
			assign_82b.*
			from assign_82b left join dwh.st_id
		on st_id.fkchld_clt = assign_82b.fkclient_t
			and (st_id.office_s_dt le (oh_s_dt+10))
			and (st_id.office_e_dt = . or st_id.office_e_dt ge oh_s_dt)
		order by ohmpl_id, oh_s_dt, office_s_dt;
		;
	quit;

data assign_82b2;
	set assign_82b1;
	by ohmpl_id oh_s_dt office_s_dt;
	if last.oh_s_dt;
	run;

data assign_82b3;
	set assign_82b2;
	if office_s_dt ne . then source = 2;
	run;

/* Put two files back together - i.e., add state ID county derrived identifiers to those records in 
   entries_02 that are lacking cnty_spfcd, office_s_dt and office_e_dt data */

data assign_83;
	set assign_82a assign_82b3;
	if source = . then source = 3;
	run;

proc sort data = assign_83;
	by ohmpl_id oh_s_dt office_s_dt;
	run;

/*  Note: some placement IDs (OHMPL_ID) have two consecutive placements - 2 placement start dates*/
/*	add office assignment data to each placement record, if no office assignment data,
	use county of removal*/	
proc sql;
	create table ohp_34 as select
	ohp_32.*,
	assign_83.cnty_spfcd as cnty_spfcdx,
	assign_83.office_s_dt,
	assign_83.office_e_dt,
	case 
		when assign_83.cnty_spfcd= '  ' then ohp_32.cnty_rem
			else assign_83.cnty_spfcd
		end as cnty_spfcd
	/*correcting error in OH_CNTY that should refer to county where placement is 
	  located, not county responsible for the child 2022.02.14 j magruder*/
	from ohp_32 left join assign_83
	on ohp_32.ohmpl_id = assign_83.ohmpl_id
		and ohp_32.oh_s_dt = assign_83.oh_s_dt
	order by fkclient_t, oh_s_dt;
	quit;


/*find county identified in State ID Number*/

data stid_01 (keep = fkclient_t start_dt end_dt stid_cnty identifier
		rename = (start_dt = stid_s_dt end_dt = stid_e_dt identifier = fkst_id_t));
	set cws.st_id_t;
	format start_dt end_dt mmddyy10.;
	format STID_CNTY $2.;
	informat STID_CNTY $2.;
	STID_CNTY = put(GVR_ENTC-1067, Z2.);
	label stid_cnty = "State ID County";
	run;

proc sql;
	create table stid_02 as select
	ohp_34.fkclient_t,
	ohp_34.pe_s_dt,
	ohp_34.pe_e_dt,
	ohp_34.oh_s_dt,
	ohp_34.oh_e_dt,
	ohp_34.ohmpl_id,
	stid_01.stid_s_dt,
	stid_01.stid_e_dt,
	stid_01.stid_cnty,
	stid_01.fkst_id_t
	from ohp_34 left join stid_01
	on ohp_34.fkclient_t = stid_01.fkclient_t
		and (oh_s_dt le stid_e_dt or stid_e_dt = .)
		and (stid_s_dt le oh_e_dt or oh_e_dt = .)
	order by fkclient_t, ohmpl_id, stid_s_dt;
	quit;


*IVE eligibility;
	*identify those placements where aid code was Federal IVE at any time duing the placement;
	*additional aid codes 49 (CWS/CMS 2184) and 03 (CWS/CMS 6149) added j magruder 20251226;
proc sql;
create table staid_01 as
select a.fkclient_t,
	  a.ohmpl_id,
	  a.oh_s_dt,
	  a.oh_e_dt,
	  a.stid_s_dt,
       b.AID_TPC,
       b.START_DT as AID_S_dt format mmddyy10.,
       b.END_DT as AID_E_dt format mmddyy10.,
         case
           when b.AID_TPC in(44, 2184, 6149) then 1
           else 0
       end as IVE
from STID_02 as a left join cws.STAIDC_T as b
on a.fkst_id_t  = b.FKST_ID_T and
   (b.START_DT < (OH_E_DT) or oh_e_dt = .)
 	and ( b.END_DT   = . or
   		b.END_DT   ge OH_S_DT )
	and b.aid_tpc in(44, 2184, 6149) 
order by FKCLIENT_T, ohmpl_id, stid_s_dt, aid_s_dt
	  ; 
quit;
/*
proc print data = staid (obs = 45);
*where IVE = 1;
var fkclient_t ohmpl_id oh_s_dt oh_e_dt aid_s_dt aid_e_dt ive aid_tpc;
	run;
*/
data staid_03;
	set staid_01;
	by fkclient_t ohmpl_id stid_s_dt aid_s_dt;
	if IVE = 1;
	run;

proc sql;
	create table staid_04 as select
	stid_02.*,
	staid_03.IVE,
	staid_03.aid_s_dt,
	staid_03.aid_e_dt
	from stid_02 as a left join staid_03 as b
	on a.fkclient_t = b.fkclient_t
		and a.ohmpl_id = b.ohmpl_id
		and a.stid_s_dt = b.stid_s_dt
	order by fkclient_t, ohmpl_id, stid_s_dt, aid_s_dt;
	quit;


/*  limit to one State ID record per placement - take the first one in
	effect during the placement - somewhat problematic as two counties are
	often represented in the overlapping State IDs*/
/* 	probably overstates Title IV-E eligibility somewhat since IV-E eligibility
	at any point in placement shows the placement as being IV-E eligible.*/ 
data stid_03 (keep = fkclient_t ohmpl_id stid_cnty ive);
	set staid_04;
	by fkclient_t ohmpl_id stid_s_dt;
	if first.ohmpl_id;
	if ive = . then ive = 0;
	run;

proc sql;
	create table ohp_36 as select
	ohp_34.*,
	stid_03.stid_cnty,
	stid_03.ive
	from ohp_34 left join stid_03
	on ohp_34.fkclient_t = stid_03.fkclient_t
		and ohp_34.ohmpl_id = stid_03.ohmpl_id
	order by fkclient_t, oh_s_dt;
	quit;

/*Add payment county data j magruder 2019.03.12*/
	/*To compensate for lack of case agency and State ID data in earlier placement
	records, especially Probation records*/
proc sql;
	create table ohp_38 as select
	ohp_36.*,
	plpayc_t.start_dt as pay_s_dt format mmddyy10.,
	plpayc_t.end_dt as pay_e_dt format mmddyy10.,
	plpayc_t.gvr_entc as pay_gvr_entc,
	put(plpayc_t.gvr_entc-1067,Z2.) as PAY_CNTY length=2 
	from ohp_36 left join cws.plpayc_t
	on ohp_36.ohmpl_id = plpayc_t.FKO_HM_PLT
	order by fkclient_t, oh_s_dt, ohmpl_id, start_dt;
	quit;

/*eliminate duplicates*/
data ohp_40 (drop = pay_s_dt pay_e_dt pay_gvr_entc);
	set ohp_38;
	by fkclient_t oh_s_dt ohmpl_id pay_s_dt;
	if last.ohmpl_id;
	if cnty_spfcd > "00" and cnty_spfcd < "59" then RSP_CNTY = cnty_spfcd ;
		else if pay_cnty > "00" and pay_cnty < "59" then RSP_CNTY = pay_cnty;
		else if stid_cnty > "00" and stid_cnty < "59" then RSP_CNTY = stid_cnty;
		else if cnty_rem > "00" and cnty_rem < "59" then RSP_CNTY = cnty_rem;
		else RSP_CNTY = "99";
	label pay_cnty = "County funding placement";
	label RSP_CNTY = "Most likely responsible county";
	run;

/*add country data for those placements with a foreign address
	2020.06.18 j magruder*/
proc sql;
	create table for_ad_01 as select
	ohp_40.fkplc_hm_t,
	frg_adrt.cntry_c as OH_CNTRY_C
	from ohp_40, cws.frg_adrt
	where ohp_40.fkplc_hm_t = frg_adrt.prmy_adrid
		and frg_adrt.cntry_c not in(0, 563)
	order by fkplc_hm_t;
quit;

data for_ad_03;
	set for_ad_01;
	by fkplc_hm_t;
	if first.fkplc_hm_t;
	run;

/*add foreign country placement location to base file*/
proc sql;
	create table ohp_42 as select
	ohp_40.*,
	for_ad_03.OH_CNTRY_C
	from ohp_40 left join for_ad_03
	on ohp_40.fkplc_hm_t = for_ad_03.fkplc_hm_t
	order by fkclient_t, oh_s_dt, ohmpl_id;
	quit;

/*repeat process for non-foster care placements
	non-foster care placements are identified by the ohmpl_id
	as of 2020Q1 extract this adds 7 country IDs*/
proc sql;
	create table for_ad_11 as select
	ohp_40.ohmpl_id,
	frg_adrt.cntry_c as OH_CNTRY_C
	from ohp_40, cws.frg_adrt
	where ohp_40.ohmpl_id = frg_adrt.prmy_adrid
		and frg_adrt.cntry_c not in(0, 563)
	order by ohmpl_id;
quit;

data for_ad_33;
	set for_ad_11;
	by ohmpl_id;
	if first.ohmpl_id;
	run;

/*add foreign country placement location to base file*/
/*this is done in two steps to retain country identified in adoption process*/
/*modified 2021.10.30 j magruder*/
proc sql;
	create table ohp_44 as select
	ohp_42.*,
	coalesce(ohp_42.oh_cntry_c, for_ad_33.oh_cntry_c) as oh_cntry_cx
	from ohp_42 left join for_ad_33
	on ohp_42.ohmpl_id = for_ad_33.ohmpl_id
	order by fkclient_t, pe_s_dt, oh_s_dt;
	quit;

data ohp_46 (drop = oh_cntry_c rename=(oh_cntry_cx = OH_CNTRY_C));;
	set ohp_44;
	by fkclient_t pe_s_dt oh_s_dt;
	/*create end placement date as extract date when placement still open to allow 
	  correct sort order for following runaway data*/
	if oh_e_dt = . then toh_e_dt = &DODEX.;
		else toh_e_dt = oh_e_dt;
	format toh_e_dt mmddyy10.;
	label OH_CNTRY_Cx = "Out of Home Placement Country";
	run;

/*runaways - create record for time child on runaway status*/
/*this matches the post Release 8.11 CWS/CMS data which includes runaways as a
  separate non-foster care placement type*/
/*added 2021.10.30 j magruder*/
  
/*deal with each episode start date separately*/
proc sort data = ohp_46;
	by fkclient_t pe_s_dt descending oh_s_dt descending toh_e_dt;
	run;
/*
proc print data = ohp_46 (obs = 40);
	var fkclient_t plcep_id pe_s_dt pe_e_dt oh_s_dt oh_e_dt plcg_rnc toh_e_dt plc_fclc;
	by fkclient_t pe_s_dt;
	where oh_e_dt = .;
	where fkclient_t = "002hF9o668";
	run;
*/
/*create new runaway record when placement ends in runaway and next placement doesn't
  start on the same date or when episode ends before another placement
  Done by descending placement start date so that prior record shows when child
  returned to care, if child returned in same episode.*/
data awol_01(drop = plc_fclc oh_s_dt oh_e_dt scp_rltc fac_type ffa_name fkplc_hm_t ghm_rclc group_hm_nm
		nfc ohmpl_id oh_cnty oh_plc_athc oh_zip_no oh_state_cd oh_zip_state oh_cntry_c oprtd_byid
		plcg_rnc plcmnt plc_fclc trnhsg_fac pay_cnty ive gh_org);
	set ohp_46;
	by fkclient_t pe_s_dt descending oh_s_dt descending toh_e_dt;
	retain roh_s_dt roh_e_dt rfkclient_t rpe_s_dt rpe_e_dt;
	if plcg_rnc = 1431 then do;
		if rpe_s_dt = pe_s_dt and roh_s_dt - toh_e_dt > 0 then do; 
			/*runaway followed by return to care in same episode*/
			nplc_fclc = 7181;
			noh_s_dt = oh_e_dt;
			noh_e_dt = roh_s_dt;
			toh_e_dt = roh_s_dt;
			nfac_type = 7181;
			type = 1;
			output awol_01;
		end;
		else if rpe_s_dt ne pe_s_dt and (pe_e_dt - oh_e_dt > 0 or pe_e_dt = .) then do; 
			/*runaway last episode event - using information from last placement in episode*/
			nplc_fclc = 7181;
			noh_s_dt = oh_e_dt;
			noh_e_dt = pe_e_dt;
			if pe_e_dt = . then toh_e_dt = &DODEX.; /*toh_e_dt only used for sorting*/
				else toh_e_dt = pe_e_dt;
			nfac_type = 7181;
			type = 2;
			output awol_01;
		end;
	end;
	rfkclient_t = fkclient_t;
	roh_s_dt = oh_s_dt;
	roh_e_dt = oh_e_dt;
	rpe_e_dt = pe_e_dt;
	rpe_s_dt = pe_s_dt;
	format roh_s_dt noh_s_dt noh_e_dt mmddyy10.;
	run;

proc sort data = awol_01 out = awol_03 
		(rename = (nplc_fclc = plc_fclc noh_s_dt = oh_s_dt noh_e_dt = oh_e_dt nfac_type = fac_type));;
	by fkclient_t noh_s_dt toh_e_dt;
	run;

proc print data = awol_03 (obs = 40);
	var fkclient_t pe_s_dt pe_e_dt oh_s_dt oh_e_dt toh_e_dt plc_fclc type;
	*where year(oh_s_dt) =2019;
	*where type = 2 and pe_e_dt = .;
	*where fkclient_t = "00Pw2B6JVG";
	where fkclient_t = "002hF9o668";
	run;


data ohp_48 (drop = roh_s_dt roh_e_dt rfkclient_t rpe_s_dt rpe_e_dt); * type);
	set ohp_46 awol_03;
	run;

/*there is a rare situation where placement episodes overlap and the above
  calculation of the runaway (7181) episode is incorrect because it uses
  the episode end rather than the earlier date the next placement starts
  this corrects for that issue. j magruder 2023.01.16*/

proc sort data = ohp_48;
	by fkclient_t descending oh_s_dt descending toh_e_dt;
	run;


data ohp_50;
	set ohp_48;
	by fkclient_t descending oh_s_dt descending toh_e_dt;
	retain rfkclient_t roh_s_dt;
	/*reset runaway placement end to start of next placement*/
	if fkclient_t = rfkclient_t 
		and roh_s_dt < oh_e_dt
		and plc_fclc = 7181
		then do;
			oh_e_dt = roh_s_dt;
			toh_e_dt = roh_s_dt; 
			end;
	rfkclient_t = fkclient_t;
	roh_s_dt = oh_s_dt;
	if oh_s_dt = . then delete; 
	/*2023.10.06 jmagruder moved from end of program to correct counter - deletes 3 adopt pmt records
	  that otherwise would be deleted at end of program with no change to termination type code*/
	run;
 
proc sort data = ohp_50 (drop=rfkclient_t roh_s_dt) out = ohp_52;
	by fkclient_t oh_s_dt toh_e_dt;
	run;


data ohp_54;
	set ohp_52;
	by fkclient_t oh_s_dt toh_e_dt;
	retain PMT_ORDER;
	if first.fkclient_t then PMT_ORDER = 1;
	else PMT_ORDER = PMT_ORDER+1;
	run;


/*Legal authority at placement level rather than episode level*/
/*Find legal authority for placement as reported on Placement Episode Legal Authority table*/
/*2023.10.06 j magruder*/
proc sql;
	create table lg_auth_04 as select
	lg_auth_00.fkclient_t,
	lg_auth_00.plcep_id,
	lg_auth_00.ohmpl_id,
	lg_auth_00.oh_s_dt,
	lg_auth_00.oh_e_dt,
	lg_auth_00.pmt_order,
	lg_autht.plc_athc,
	lg_autht.effctv_dt as oh_lg_auth_s_dt format mmddyy10.
	from ohp_54 as lg_auth_00 left join cws.lg_autht
	on lg_auth_00.plcep_id = lg_autht.fkplc_eps0
		and (effctv_dt le oh_e_dt or oh_e_dt = .)
	order by fkclient_t, pmt_order, effctv_dt desc;
	quit; 

proc print data = lg_auth_04 (obs = 30);
	run;

data lg_auth_06;
	set lg_auth_04;
	by fkclient_t pmt_order descending oh_lg_auth_s_dt;
	retain rlg_auth_s_dt;
	if first.pmt_order then oh_lg_auth_e_dt = .;
		else oh_lg_auth_e_dt = rlg_auth_s_dt;
	rlg_auth_s_dt = oh_lg_auth_s_dt;
	format oh_lg_auth_e_dt rlg_auth_s_dt mmddyy10.;
	run;

proc sort data = lg_auth_06;
	by fkclient_t pmt_order oh_lg_auth_s_dt;
	run;

proc print data = lg_auth_06 (Obs = 140);
	var plcep_id pmt_order oh_s_dt oh_e_dt oh_lg_auth_s_dt oh_lg_auth_e_dt;
	run;

data lg_auth_08;
	set lg_auth_06;
	by fkclient_t pmt_order oh_lg_auth_s_dt;
	/*delete authorities ending before placement start*/
	if oh_lg_auth_e_dt ne . and oh_lg_auth_e_dt < oh_s_dt then delete;
	/*delete authorities starting after placement end - redundant to lg_auth_02 proc sql*/
	if oh_lg_auth_s_dt > oh_e_dt and oh_e_dt ne . then delete;
	run;

proc print data = lg_auth_08 (Obs = 140);
	var plcep_id pmt_order oh_s_dt oh_e_dt oh_lg_auth_s_dt oh_lg_auth_e_dt;
	run;

/*  If there were multiple placement authorities during a placement, eliminate one
	or more authorities established on the last day of the placement. These appear to
	be associated with the next placement and not the ending placement. There is a problem
	in that there is not authority ending date on the legal authority table so it is not possible
	to determine which of two autorities established on the same day continued.
	Individual placements may have multiple start dates if placement type changed. This may be
	accompanied by a change in authority if change was from, e.g., foster to guardianship.
	Note "first.oh_s_dt = 0" means that at least one authority preceded that authority.*/

data lg_auth_09;
	set lg_auth_08;
	by fkclient_t pmt_order oh_lg_auth_s_dt;
	if oh_e_dt = oh_lg_auth_s_dt
		and first.pmt_order = 0
		then delete;
	run;	


proc print data = lg_auth_09 (Obs = 140);
	var plcep_id pmt_order oh_s_dt oh_e_dt oh_lg_auth_s_dt oh_lg_auth_e_dt;
	run;


data lg_auth_10_start (keep = fkclient_t pmt_order plcep_id oh_s_dt ohmpl_id plc_athc
		rename = (plc_athc = OH_S_PLC_ATHC))
	lg_auth_10_end(keep = fkclient_t pmt_order plcep_id oh_s_dt ohmpl_id plc_athc
		rename = (plc_athc = OH_E_PLC_ATHC));
	set lg_auth_09;
	by fkclient_t pmt_order oh_lg_auth_s_dt;
	if first.pmt_order then output lg_auth_10_start;
	if last.pmt_order then output lg_auth_10_end;
	run;

data lg_auth_12;
	merge lg_auth_10_start lg_auth_10_end;
	by fkclient_t pmt_order;
	if oh_s_plc_athc = . and oh_e_plc_athc = . then no_data = 1;
		else no_data = 9;
	if oh_s_plc_athc = oh_e_plc_athc  then nochange = 1;
		else nochange = 9;
	label oh_s_plc_athc = "Legal Authority at Placement Start";
	label oh_e_plc_athc = "Legal Authority at Placement End";
	run;

proc sql;
	create table ohp_56 as select
	ohp_54.*,
	lg_auth_12.oh_s_plc_athc,
	lg_auth_12.oh_e_plc_athc
	from ohp_54 left join lg_auth_12 on
	ohp_54.fkclient_t = lg_auth_12.fkclient_t and
	ohp_54.pmt_order = lg_auth_12.pmt_order
	order by ohp_54.fkclient_t, ohp_54.pmt_order;
	quit;


/*geographic relationship between supervising county and county of physical presence
  adjacent county based on Census Data File:
  //Census.gov/Library/References/County Adjacency File
  https://www.census.gov/programs-surveys/geography/library/reference/county-adjacency-file.html
  values:
	1 = "In County"
	2 = "Adjacent County"
	3 = "Non_adjacent County"
	4 = "Out of State"
	9 = "Missing";
  2024.07.01 j magruder*/
data ohp (drop = oh_cntyn rsp_cntyn);
	set ohp_56;
	/*delete oh_cnty when preponderance of evidence (out-of-state ZIP Code and
	  identified other state) says child not placed in California*/
	if oh_zip_state ne "CA" and oh_state_cd not in(0,1828) then oh_cnty = ' ';
	oh_cntyn = input(oh_cnty, 3.);
	rsp_cntyn  = input(rsp_cnty, 3.);
	if plc_fclc in(7164, 7181) then OH_LOC = 9; /*missing if in runaway status*/
	else if oh_state_cd ne 1828 and
		oh_state_cd ge 1823 and oh_state_cd le 1880 then OH_LOC = 4; /*placed out of state*/
	else if oh_cntyn = rsp_cntyn then OH_LOC = 1; /*placed in supervising county*/
	else if oh_cntyn > 58 or oh_cntyn = . or rsp_cntyn > 58 or rsp_cntyn = . then OH_LOC = 9;
	else if rsp_cntyn = 1 then do;
		if oh_cntyn in (1, 7, 38, 39, 41, 43, 50) then OH_LOC = 2; /*placed in adjacent county*/
		else OH_LOC = 3; /*placed in non-adjacent county*/
		end;
	else if rsp_cntyn = 2 then do;
		if oh_cntyn in (2,3,5,9,26,55) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 3 then do;
		if oh_cntyn in (2,3,5,9,34,39) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 4 then do;
		if oh_cntyn in (4, 6, 11, 32, 51, 52,58) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 5 then do;
		if oh_cntyn in (2,3,5,39,50,55) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 6 then do;
		if oh_cntyn in (4,6,11,17,51,57) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 7 then do;
		if oh_cntyn in (1,7,21,34,38,39,48,49) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 8 then do;
		if oh_cntyn in (8,12,47) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 9 then do;
		if oh_cntyn in (2,3,9,31,34) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 10 then do;
		if oh_cntyn in (10,14,16,20,24,26,27,35,54) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 11 then do;
		if oh_cntyn in (4,6,11,17,23,52) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 12 then do;
		if oh_cntyn in (8,12,23,47,53) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 13 then do;
		if oh_cntyn in (13,33,37) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 14 then do;
		if oh_cntyn in (10,14,15,26,36,54) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 15 then do;
		if oh_cntyn in (14,15,16,36,40,42,54,56) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 16 then do;
		if oh_cntyn in (10,15,16,27,40,54) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 17 then do;
		if oh_cntyn in (6,11,17,23,28,49,57) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 18 then do;
		if oh_cntyn in (18,25,32,45,46) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 19 then do;
		if oh_cntyn in (15,19,30,36,56) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 20 then do;
		if oh_cntyn in (10,20,22,24,26,55) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 21 then do;
		if oh_cntyn in (7,21,38,48,49) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 22 then do;
		if oh_cntyn in (20,22,24,50,55) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 23 then do;
		if oh_cntyn in (11,12,17,23,49,52,53) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 24 then do;
		if oh_cntyn in (10,20,22,24,35,43,50) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 25 then do;
		if oh_cntyn in (18,25,45,47) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 26 then do;
		if oh_cntyn in (2,10,14,20,26,55) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 27 then do;
		if oh_cntyn in (10,16,27,35,40,44) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 28 then do;
		if oh_cntyn in (17,28,48,49,57) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 29 then do;
		if oh_cntyn in (29,31,46,58) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 30 then do;
		if oh_cntyn in (19,30,33,36,37) then OH_LOC = 2;
		else  OH_LOC = 3;
		end;
	else if rsp_cntyn = 31 then do;
		if oh_cntyn in (9,29,31,34,51,58) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 32 then do;
		if oh_cntyn in (4,18,2,45,46,52,58) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 33 then do;
		if oh_cntyn in (13,30,33,36,37) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 34 then do;
		if oh_cntyn in (3,7,9,31,34,39,48,51,57) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 35 then do;
		if oh_cntyn in (10,24,27,35,43,44) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 36 then do;
		if oh_cntyn in (14,15,19,30,33,36) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 37 then do;
		if oh_cntyn in (13,	30,	33,	37) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 38 then do;
		if oh_cntyn in (1,7,21,38,41) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 39 then do;
		if oh_cntyn in (1,3,5,7,34,39,50) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 40 then do;
		if oh_cntyn in (15,16,27,40,42) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 41 then do;
		if oh_cntyn in (1,38,41,43,44) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 42 then do;
		if oh_cntyn in (15,40,42,56) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 43 then do;
		if oh_cntyn in (1,24,35,41,43,44,50) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 44 then do;
		if oh_cntyn in (27,35,41,43,44) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 45 then do;
		if oh_cntyn in (18,25,32,45,47,52,53) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 46 then do;
		if oh_cntyn in (18,29,32,46,58) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 47 then do;
		if oh_cntyn in (8,12,25,45,47,53) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 48 then do;
		if oh_cntyn in (7,21,28,34,48,49,57) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 49 then do;
		if oh_cntyn in (7,17,21,23,28,48,49) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 50 then do;
		if oh_cntyn in (1,5,22,24,39,43,50,55) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 51 then do;
		if oh_cntyn in (4,6,31,34,51,57,58) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 52 then do;
		if oh_cntyn in (4,11,23,32,45,52,53) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 53 then do;
		if oh_cntyn in (12,23,45,47,52,53) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 54 then do;
		if oh_cntyn in (10,14,15,16,54) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 55 then do;
		if oh_cntyn in (2,5,20,22,26,50,55) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 56 then do;
		if oh_cntyn in (15,19,42,56) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 57 then do;
		if oh_cntyn in (6,17,28,34,48,51,57) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	else if rsp_cntyn = 58 then do;
		if oh_cntyn in (4,29,31,32,46,51,58) then OH_LOC = 2;
		else OH_LOC = 3;
		end;
	label OH_LOC = "Placement Location";
run;

proc contents data = ohp varnum;
	run;
/*
proc print data = ohp (obs = 80);
	var fkclient_t pe_s_dt pe_e_dt oh_s_dt oh_e_dt plcg_rnc plc_fclc;
	by fkclient_t pe_s_dt;
	*where fkclient_t in( "0jmcGWuC3m");
	where plc_fclc = 7181;
	run;
*/

/*end of PLACEMENT MODULE*/


/*Predominate Placement Type Module*/
/*determine the predominate placement type for each placement episode aka spell*/

/* PREDOM_FT.sas */
/* Purpose: Derive new episode (spell)-level  attribute: PREDOM_FT.
 * This is the major facility-type class for each spell.
 * Spell is defined as the compound key FKCLIENT_T + SPELL.
 * 1. Reassign facility type to 7 new types.
 * 2. For each spell, count the number of days spent in each type.
 * 3. If the number of days spent in any type exceeds 50% of the spell length,
 *    that type is the Predominant Facility Type.
 * 4. If days spent in any single type does not exceed 50% of the spell length,
 *    then a new value for PREDOM_FT is assigned "MIXED".
 * Programmer: M. Armijo
 * Project: CWS/CMS Performance Indicators
 * Revision History:
 * 2002.01.18 M. Armijo
 * Corrected error in algorithm, which could result in the incorrect assignment of PREDOM_FT.
 */

/* Modified 2016Q4 to accommodate implementation of RFA home and STRPT facility types */


data predom_01 ;
set ohp ;

keep
FT
rev_plcep_id        /* Master Placement-Episode-ID */
OH_S_DT         /* OHMPL-Start-Date */
OH_E_DT         /* OHMPL-End-Date */
;

/* If OHMPL-End-Date is missing, use the date of the extract. */
if OH_E_DT eq . or oh_e_dt > &cutoff. then OH_E_DT= &DODEX ;

if PLC_FCLC = 2222 then FT = 1;                  /* Pre_Adopt */
else if PLC_FCLC in (1421,1422) then FT = 2;     /* Kin */
/* else if  SCP_RLTC in (1639) then FT = 2; */   /* Kin-rel-non-guardian */
else if PLC_FCLC in (1415,1416) then FT = 3;     /* Foster */
else if PLC_FCLC in (1414,2200) then FT = 4;     /* FFA */
else if PLC_FCLC = 1419 then FT = 9;             /* Court Specified Home */
else if PLC_FCLC in (7208) then FT = 9.2;        /* Tribally Approved Homes  2022.01.11 j magruder*/
else if PLC_FCLC = 1417 then FT = 10;            /* Group */
else if PLC_FCLC in (1418,7027) then FT = 11;    /* Shelter */
else if PLC_FCLC in ('1420', '6362', '6363', 
       '6364', '6365', '6366', '6367', '6368', 
       '6369', '6370', '6371') then FT = 13;     /* Non-FC added 2021.04.12 j magruder*/
else if PLC_FCLC = 5411 then FT = 14;            /* Guardian */
else if PLC_FCLC = 6716 then FT = 19;            /* SILP */

/* else if PLC_FCLC = 6914.1 then FT = 5; */     /* RFA - Non Relative */
/* else if PLC_FCLC = 6914.2 then FT = 6; */     /* RFA - Relative */
/* else if PLC_FCLC = 6915.1 then FT = 7; */     /* RFA FFA - Non Relative */
/* else if PLC_FCLC = 6915.2 then FT = 8; */     /* RFA FFA - Relative */
/* else if PLC_FCLC = 6916 then FT = 12;  */     /* STRTP */

else if PLC_FCLC not in (.,0) then FT = 29;      /* Other */
else if PLC_FCLC in (.,0) then FT = 99;          /* Missing */

run ;

/* Sum the days spent in each facilitye type (FT) */
proc sql ;
create table FT_SUMM as
select
FT ,
sum(OH_E_DT - OH_S_DT) as DAYS ,
rev_plcep_id
from predom_01
group by rev_plcep_id, FT
;

/* Compute total days for each spell by summing days in each placement*/
create table TOTAL as
select
rev_plcep_id,
sum(OH_E_DT - OH_S_DT) as DAYS
from predom_01
group by rev_plcep_id
;

/* Compute the proportion of spell duration spent in each type */
create table PTIME as
select
FT_SUMM.DAYS/TOTAL.DAYS as PTIME,
FT,
TOTAL.rev_plcep_id
from FT_SUMM, TOTAL
where FT_SUMM.rev_plcep_id = TOTAL.rev_plcep_id
order by rev_plcep_id, PTIME desc
;

/* Assign PREDOM_FT if any, otherwise assign value "MIXED" */
data PREDOM_FT ;
set PTIME ;
by rev_plcep_id ;
if first.rev_plcep_id then do ;
   select ;
      when(PTIME eq .) PREDOM_FT=99 ;
      when(PTIME > 0.5) PREDOM_FT=FT ;  
	  /*changed from ge to >. This conforms program to methodology. It
	    eliminates problem of one of two (usually very short) equal length placement
	    types being randomly selected as predominant placement. 2023.11.02 j magruder*/
      otherwise PREDOM_FT=30 ;                     /* MIXED */
   end;
end;
else delete ;
label predom_ft = "Predominant Facility Type";
run ;


/*predominant post-RFA placement type*/
/*added 2022.07.19 j magruder - CDSS data request*/

data rfa_predom_01 ;
set ohp ;

keep
rFT
rev_plcep_id        /* Master Placement-Episode-ID */
OH_S_DT         /* OHMPL-Start-Date */
OH_E_DT         /* OHMPL-End-Date */
;

/* If OHMPL-End-Date is missing, use the date of the extract. */
if OH_E_DT eq . or oh_e_dt > &cutoff. then OH_E_DT= &DODEX ;

if FAC_TYPE = 2222 then rFT = 1;                  /* Pre_Adopt */
else if FAC_TYPE in (1421,1422) then rFT = 2;     /* Kin */
/* else if  SCP_RLTC in (1639) then rFT = 2; */   /* Kin-rel-non-guardian */
else if FAC_TYPE in (1415,1416) then rFT = 3;     /* Foster */
else if FAC_TYPE in (1414,2200) then rFT = 4;     /* FFA */
else if FAC_TYPE = 1419 then rFT = 9;             /* Court Specified Home */
else if FAC_TYPE in (7208) then rFT = 9.2;        /* Tribally Approved Homes  2022.01.11 j magruder*/
else if FAC_TYPE = 1417 then rFT = 10;            /* Group */
else if FAC_TYPE in (1418,7027) then rFT = 11;    /* Shelter */
else if FAC_TYPE in ('1420', '6362', '6363', 
       '6364', '6365', '6366', '6367', '6368', 
       '6369', '6370', '6371') then rFT = 13;     /* Non-FC added 2021.04.12 j magruder*/
else if FAC_TYPE = 5411 then rFT = 14;            /* Guardian */
else if FAC_TYPE = 6716 then rFT = 19;            /* SILP */

else if FAC_TYPE = 6914.1 then rFT = 5;     /* RFA - Non Relative */
else if FAC_TYPE = 6914.2 then rFT = 6;      /* RFA - Relative */
else if FAC_TYPE = 6915.1 then rFT = 7;      /* RFA FFA - Non Relative */
else if FAC_TYPE = 6915.2 then rFT = 8;      /* RFA FFA - Relative */
else if FAC_TYPE = 6916 then rFT = 12;       /* STRTP */

else if FAC_TYPE not in (.,0) then rFT = 29;      /* Other */
else if FAC_TYPE in (.,0) then rFT = 99;          /* Missing */

run ;

/* Sum the days spent in each facilitye type (FT) */
proc sql ;
create table rfa_FT_SUMM as
select
rFT ,
sum(OH_E_DT - OH_S_DT) as DAYS ,
rev_plcep_id
from rfa_predom_01
group by rev_plcep_id, rFT
;

/* Compute total days for each spell by summing days in each placement*/
create table rfa_TOTAL as
select
rev_plcep_id,
sum(OH_E_DT - OH_S_DT) as DAYS
from rfa_predom_01
group by rev_plcep_id
;

/* Compute the proportion of spell duration spent in each type */
create table rfa_PTIME as
select
rfa_FT_SUMM.DAYS/rfa_TOTAL.DAYS as PTIME,
rFT,
rfa_TOTAL.rev_plcep_id
from rfa_FT_SUMM, rfa_TOTAL
where rfa_FT_SUMM.rev_plcep_id = rfa_TOTAL.rev_plcep_id
order by rev_plcep_id, PTIME desc
;

/* Assign rfa_PREDOM_FT if any, otherwise assign value "MIXED" */
data rfa_PREDOM_FT ;
set rfa_PTIME ;
by rev_plcep_id ;
if first.rev_plcep_id then do ;
   select ;
      when(PTIME eq .) RFA_PREDOM_FT=99 ;
      when(PTIME > 0.5) RFA_PREDOM_FT=rFT ;  
	  /*changed from ge to >. This conforms program to methodology. It
	    eliminates problem of one of two (usually very short) equal length placement
	    types being randomly selected as predominant placement. 2023.11.02 j magruder*/
      otherwise RFA_PREDOM_FT=30 ;                     /* MIXED */
   end;
end;
else delete ;
label rfa_predom_ft = "Post-RFA Predominant Facility Type";
run ;


/*modify episode table created in EPISODE MODULE to include predominate placement type*/
proc sql;
	create table episode_08 as select
	episode_07.*,
	predom_ft.predom_ft
	from episode_07 left join predom_ft
	on episode_07.rev_plcep_id = predom_ft.rev_plcep_id
	order by fkclient_t, rev_plcep_id;
	quit;


/*further modify episode table created in EPISODE MODULE to include rfa predominate placement type*/
proc sql;
	create table episode_10 as select
	episode_08.*,
	rfa_predom_ft.rfa_predom_ft
	from episode_08 left join rfa_predom_ft
	on episode_08.rev_plcep_id = rfa_predom_ft.rev_plcep_id
	order by fkclient_t, rev_plcep_id;
	quit;




/*  Adjust predominate facility type in two ways:
	if missing predominate facility type missing because NFC only, class as missing
	if episode NFC only but adoptive placement (ca 13 placements) reclass episode as not NFC only*/
data episode;
	set episode_10;
	if predom_ft = . then predom_ft = 99;
	if rfa_predom_ft = . then rfa_predom_ft = 99;
	if predom_ft = 1 then NFC_ONLY = 0;
	run;

/* End PREDOMINATE PLACEMENT TYPE MODULE*/


/*FINAL FILE MODULE*/
/*create single UCB_FC file from files from 3 modules*/

/*first create client-episode file from merge client and episode files*/
proc sql;
	create table client_episode_01 as select
	child_char.*,
	episode.*
	from child_char left join episode
	on child_char.fkclient_t = episode.fkclient_t
	order by fkclient_t, rev_plcep_id;

/* next add placement level data to table*/
/* each field taken from ohp is named because some fields in ohc are already in
	episode_01*/
	create table UCB_FC_NEW_x as select
	client_episode_01.*,
	ohp.fac_type,
	ohp.ffa_name,
	ohp.fkplc_hm_t,
	ohp.ghm_rclc,
	ohp.gh_org,
	ohp.group_hm_nm,
	ohp.nfc,
	ohp.ohmpl_id,
	ohp.oh_cnty,
	ohp.oh_e_dt,
	/*ohp.oh_plc_athc,*/ /*replaced by oh_e_plc_athc which includes runaways 2023.10.08 j magruder*/
	ohp.oh_s_dt,
	ohp.oh_zip_no,
	ohp.oh_state_cd,
	ohp.oh_zip_state,
	ohp.oh_cntry_c,
	ohp.oprtd_byid,
	ohp.plcg_rnc,
	ohp.plcmnt,
	ohp.plc_fclc,
	ohp.scp_rltc,
	ohp.trnhsg_fac,
	ohp.trbsph_cd,
	ohp.tdcnsl_ind,
	ohp.stid_cnty,
	ohp.pay_cnty,
	ohp.rsp_cnty,
	ohp.oh_s_plc_athc,
	ohp.oh_e_plc_athc,
	ohp.oh_loc,
	ohp.pmt_order,	
	ohp.ive
	from client_episode_01 left join ohp
	on client_episode_01.rev_plcep_id = ohp.rev_plcep_id
	order by fkclient_t, pmt_order ;
	quit;
*proc contents data = client_episode_01 varnum; run;

/*remove records that contain no placements - empty epidoses*/
data UCB_FC_NEW (rename = (rev_plcep_id = PLCEP_ID));
	set UCB_FC_NEW_x;
	*if oh_e_dt = . then oh_e_dt = '01JAN3000'D;
	if oh_s_dt = . then delete; 
		/*j magruder 20220929 - to remove records of episodes that contain no placements*/
	if ive = . then ive = 0;
	run;
*proc contents data = ucb_fc_new varnum; run;

data dwh.ucb_fc2;
	set ucb_fc_new;
	run;

/*create file with same criteria as current UCB_FC*/
data dwh.ucb_fc ;
	set dwh.ucb_fc2;
	if nfc = 1 or icpc_in = 1 or nfc_only = 1 or oh_s_dt = . then delete;
	run;