/* ucb_case_svc_comp_test_2022Q4rev2_jm.sas */ 


/* This is the UCB derived file used for case-service-component related analyses.

 * Note that ownership county is not available in this file.
 * Ownership county and optionally office number can be determined for a given 
 * point-in-time from table UCB_ASSIGNMENT_CASE.
 * Programmer: M. Armijo
 * Revision History: 
 * 2008.02.12 Starting with ucb_case.sas, a substantial simplification and re-write
 * was done.
 * 2010.09.21 M. Armijo
 * Previous to R6.4, this table excluded mental health, KinGAP and non-CWD foster care cases, 
 * determined by intervention reason (INTV_RNT.INTVRSNC).
 * Starting with R6.4, all cases are included. A new attribute, CASE_T.RSP_AGY_CD,
 * has been added and can be used to report by responsible agency.
 * 2012.05.18 Made correction to mapping of P_ETHNCTYC to derived var ETHNIC. 
 *2013.08.12 j magruder;
 *2013.09.10 j magruder;
 *2013.12.02 j magruder modified to:
    exclude KinGAP and Mental Health cases as they are only in CWS/CMS for
     convenience of Los Angeles and are not part of the CWS population, 
    add census race/ethnicity from dwh.census_rc, 
    add case start and end dates and rename as case_st_dt, case_e_dt, and
    add case closure reason type;
 *2014.02.24 j magruder modified to include starting and ending county;
 *2014.03.04 j magruder modified to include case counter;
 *2022.09.27 j magruder add ICWA and tribal membership status;
 *2023.04.21 j magruder added voluntary status at start and end of each 
    case service component spell; renumbered working tables

 */

/* Add FKCHLD_CLT to the case-service-component level.
 * Restrictng to child welfare supervised.
 */

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

/* %include "/pool01/home/joemagruder/1basic_start.sas";*/

proc sql _method ;
create table UCB_CASE0 as
select distinct 
   CS_SVCMT.IDENTIFIER as CS_ID
	/*Eliminates duplicate rows with the same information */
  ,CS_SVCMT.FKCASE_T
  ,CS_SVCMT.EFFECTV_DT as CSC_S_DT format mmddyy10. label="SERVICE_COMPONENT_START_DATE"
        /*rename case service component start date*/
  ,CS_SVCMT.END_DT as CSC_E_DT format mmddyy10.  label="SERVICE_COMPONENT_END_DATE"
        /*rename case service component end date*/
  ,put(CS_SVCMT.SRV_CMPC,LCODE.) as SRV_CMPT length=2 label="SERVICE_COMPONENT_TYPE"
  ,CASE_T.FKCHLD_CLT
  ,CASE_T.RSP_AGY_CD
  ,case_t.start_dt as CASE_S_DT format mmddyy10. LABEL = "CASE_START_DATE" /*add case start date*/
  ,case_t.end_dt as CASE_E_DT format mmddyy10.  LABEL = "CASE_END_DATE" /*add case end date*/
  ,case_t.CLS_RSNC /*add case closure reason type*/
   ,case
    when case_t.end_dt = . then '  '
        else case_t.cnty_spfcd
        end as CASE_E_CNTY LABEL = "COUNTY_ASSIGNMENT_AT_CASE_END"/*add last county - only for closed cases*/
from CWS.CASE_T, CWS.CS_SVCMT 
where
CS_SVCMT.FKCASE_T=CASE_T.IDENTIFIER
    and rsp_agy_cd not in ("K" "M") /*Excludes KinGAP (“K”) and Mental Health(“M”)agencies*/
/*order by fkchld_clt, fkcase_t*/ /*add sort*/
;
/*although it would appear that a left join might identify cases without service programs,
  with the exception of cases with rsp_agy_cds of "K" and "M" (LA KinGAP and Mental Health) there 
  is only one case in CWS/CMS without at least one service program and that is from 1994/1995.*/


/* Adding person-level attributes from CLIENT_T. */
/* Code as Missing: 6351=Unable to determine, 6352=Declines to State, 6453=Other Race Unknown */
/* Code as Black: 823=Black, 826=Ethiopian */
/* Code as White: 839=White, 840=White-Armenian, 841=White-Central American, 842=White-European, 843=White-Middle Eastern, 844=White-Romanian */
/* Code as Hispanic: 830=Hispanic, 3162=Caribbean, 3163=Central American, 3164=Mexican, 3165=South American, or those coded as Hispanic using Hispanic Origin indicator (hisp_cd) */
/* Code as Asian: 822=Asian Indian, 824=Cambodian, 825=Chinese, 827=Filipino, 828=Guamanian, 829=Hawaiian, 831=Japanese, 832=Korean, 833=Laotian, 
                  834= Other Asian/Pacific Islander, 835=Hmong, 836=Polynesian, 837=Samoan, 838=Vietnamese, 5922=Other Asian, 5923=Other Pacific Islander */
/* Code as Native American: 820=Alaskan Native, 821=American Indian */

create table UCB_CASE2 as
select
   UCB_CASE0.*,
   B.BIRTH_DT,
   B.GENDER_CD,
   B.P_ETHNCTYC,
   B.HISP_CD,
/* UCB ethnic groups */
   case 
   when B.P_ETHNCTYC in(0,6351,6352,6453,.) then '0'       /* Missing */
   when B.P_ETHNCTYC in(823,826) then '1'                 /* Black */
   when B.P_ETHNCTYC in(839,840,841,842,843,844) then '2' /* White */
   when B.P_ETHNCTYC in(830,3162,3163,3164,3165) then '3' /* Hispanic - 3162 (Caribbean) coded as Hispanic based on assumption that the majority 
                                                             of those in mainland US are from Puerto Rico or Cuba */
   when B.P_ETHNCTYC in(822,824,825,827,828,829,
      831,832,833,834,835,836,837,838,5922,5923) then '4' /* Asian */
   when B.P_ETHNCTYC in(820,821) then '5'                 /* Native Am */
   else '9'                                               /* UNKNOWN VALUE */
   end as ETHNIC length=1 label="ETHNIC_CLASS"
from UCB_CASE0 A left join CWS.CLIENT_T B
on UCB_CASE0.FKCHLD_CLT=CLIENT_T.IDENTIFIER
;

/* where GENDER_CD  not in('F','M'), recode to 'U'. */
update UCB_CASE2
set GENDER_CD='U'
where GENDER_CD not in('F','M')    /* F = Female, M = Male  As of 2019, the corrected CWS/CMS label for this variable is Sex at Birth.  
                                      The Sex at Birth variable also includes a value for I = Intersex.*/ 
;

/* HISP_CD over-rides P_ETHNCTYC */
update UCB_CASE2 
set ETHNIC='3' /* Hispanic*/
where HISP_CD eq 'Y'
;

/*create order count for csc status 2023.04.21 j magruder*/
update UCB_CASE2
set csc_e_dt = '31DEC3000'D
where csc_e_dt = .
;

proc sort data = ucb_case2;
	by fkchld_clt fkcase_t csc_s_dt csc_e_dt;
	run;

data ucb_case4 ;
	set ucb_case2;
	by fkchld_clt fkcase_t csc_s_dt csc_e_dt;
	retain csc_order;
	if first.fkchld_clt then csc_order = 1;
	else csc_order = csc_order+1;
	if csc_e_dt = '31DEC3000'D then csc_e_dt = .;
	run;

*add census race/ethnicity codes;
proc sql;
    create table ucb_case6 as
    select ucb_case4.*,
        census_rc.cens_rc,
        census_rc.hisp_cdx
    from ucb_case4 left join dwh.census_rc
        on ucb_case4.fkchld_clt = census_rc.fkclient_t
    order by fkchld_clt, csc_order;
    quit;

*add incoming ICPC intervention reason flag;
proc sql;
    create table ucb_case7 as
        select a.*,
            /*intv_rnt.cnty_spfcd as icpc_cnty_spfcd,*/
            /*intv_rnt.intvrsnc as icpc_intvrsnc,*/
            case
                when intv_rnt.intvrsnc = 1226 then 1 /* 1226 = Incoming ICPC request */
                    else 0
                    end as icpc_in /*add flag for incoming ICPC cases*/
        from ucb_case6 as a left join cws.intv_rnt
            on a.fkcase_t = intv_rnt.fkcase_t
                and intvrsnc = 1226
        order by fkchld_clt, csc_order;
    quit;

*add ICWA status from child client table (using code from UCB_FC programs
    	2022.08.30 j magruder;
proc sql;
	create table ucb_case8 as select
	ucb_case7.*,
	chld_clt.icwa_elgcd
	from ucb_case7 left join cws.chld_clt
	on ucb_case7.fkchld_clt = chld_clt.fkclient_t
	order by  fkchld_clt, csc_order;
	quit;


/* Add known tribal membership status. 2022.09.27 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*/

proc sort data = ucb_case8 out = case_cl  (keep = fkchld_clt) nodupkey;
	by fkchld_clt;
	run; 

/* 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 fkchld_clt from case_cl)
	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 ucb_case10 as select
	ucb_case8.*,
	tribe_05.indn_stc,
	tribe_05.idn_trbcx label "Modified Indian Tribe ID"
	from ucb_case8 left join tribe_05
	on ucb_case8.fkchld_clt = tribe_05.fkclient_t
	order by fkchld_clt, csc_order;
	quit;

/*add voluntary status at start and end of case service component
	2023.04.21 j magruder*/
data CSVOL_VIEW (rename = (start_dt = vol_s_dt end_dt = vol_e_dt))/view=CSVOL_VIEW ;
set CWS.CSVOL_ST ;
if END_DT = . then END_DT='31dec3000'd ;
format start_dt end_dt mmddyy10.;

run ;

/* Retrieve VOLUNTARY_IND for the point-in-time 2023.04.21 j magruder*/
/*find voluntary status within case service component*/
/*this will give more than one status for some case service components*/
/*note > and < rather than ge and le to deal with status change on day of CSC change - looks 
  at start status on day after change and end status on day before change. Organized so that
  if status not determined until later is CSC that status is shown as first status*/
proc sql;
create table vol_01 as
select
   A.*
  ,B.VLNTRY_IND as csc_vol_ind ,
  b.vol_s_dt format mmddyy10.,
  b.vol_e_dt format mmddyy10.
from ucb_case10 A left join csvol_view B
on A.FKCASE_T=B.FKCASE_T
	and (b.vol_s_dt < a.csc_e_dt or a.csc_e_dt = . or 
			(b.vol_s_dt le a.csc_e_dt and a.csc_s_dt = a.csc_e_dt))
		and (b.vol_e_dt > csc_s_dt or b.vol_e_dt = . or
			(b.vol_e_dt ge a.csc_s_dt and a.csc_s_dt = a.csc_e_dt))
	order by fkcase_t, csc_order, vol_s_dt, vol_e_dt;
quit;

/*
proc print data = vol_s_01a (obs = 70);
	var fkcase_t case_s_dt case_e_dt csc_s_dt s_vol_s_dt s_vol_e_dt csc_e_dt srv_cmpt csc_vol_f_ind csc_order;
	where year(case_s_dt) > 2019;
	run;
*/
/*first vol status and last vol status*/
data vol_s_03 (rename = (csc_vol_ind = csc_vol_s_ind 
		vol_s_dt = s_vol_s_dt vol_e_dt = s_vol_e_dt))
	vol_e_03 (rename = (csc_vol_ind = csc_vol_e_ind 
		vol_s_dt = e_vol_s_dt vol_e_dt = e_vol_e_dt));
		set vol_01;
	by fkcase_t csc_order vol_s_dt vol_e_dt;
	if first.csc_order then output vol_s_03;
	if last.csc_order then output vol_e_03;
	run;

/*merged files with 1 row per CSC order*/
data ucb_case12;
	merge vol_s_03 vol_e_03;
	by fkcase_t csc_order;
	label
		csc_vol_s_ind = "First CSC vol status"
		csc_vol_e_ind = "Last CSC vol status"
		s_vol_s_dt = "csc start vol status s dt"
  		s_vol_e_dt = "csc start vol status e dt"
		e_vol_s_dt = "csc end vol status s dt"
  		e_vol_e_dt = "csc end vol status e dt";
	format s_vol_s_dt s_vol_e_dt e_vol_s_dt e_vol_e_dt mmddyy10.;
	run;




*add identification of county at case opening and case closure from assignment
 table-note that not all cases have data on the assignment table;

/*
proc contents data = ucb_case12 varnum; run;
*/
proc sort data = dwh.ucb_assignment_case out = test_ac;
    by fkcase_t start_dt;
    run;

*assignment county at case start;
data case_cty_st (keep = fkcase_t cnty_spfcd
        rename=(cnty_spfcd = case_s_cnty_at));
    set test_ac;
    by fkcase_t start_dt;
    if first.fkcase_t;
    run;

*assignment county at case end (or current county if case still open -
 data only retained later in program if case closed);
data case_cty_et (keep = fkcase_t cnty_spfcd
        rename=(cnty_spfcd = case_e_cnty_at));
    set test_ac;
    by fkcase_t start_dt;
    if last.fkcase_t;
    run;

data case_cty;
    merge case_cty_st case_cty_et;
    by fkcase_t;
    run;

*add county at case opening and case closing from assignment table to base file;
proc sql;
    create table ucb_case14 as
        select ucb_case12.*,
            b.case_s_cnty_at,
            b.case_e_cnty_at
        from ucb_case12 (rename=(case_e_cnty = case_e_cnty_x)) 
            left join case_cty as b
        on ucb_case12.fkcase_t = b.fkcase_t
        order by fkchld_clt, fkcase_t, csc_s_dt, srv_cmpt;
    quit;

*proc contents data = ucb_case14 varnum; run;

*if no assignment table data or if assignment is county 99, use case_e_cnty from case table;
*otherwise, use start and end county from assignment table,
 but only show end county if case closed;
*drop incoming ICPC cases;
data ucb_case16;
    set ucb_case14;
    if icpc_in = 1 then delete;
    if case_s_cnty_at = '  ' then CASE_S_CNTY = case_e_cnty_x;
        else CASE_S_CNTY = case_s_cnty_at;    
    if case_e_cnty_at in('  ', '99') then CASE_E_CNTY = case_e_cnty_x;
        else if case_e_dt ne . then CASE_E_CNTY = case_e_cnty_at;
    if case_e_dt = . then case_e_dt = '01JAN3000'D;
    label case_e_cnty = "County at Case Closure";
    label case_s_cnty = "County at Case Opening";
    /*removed to correct sort at count_02 below*/
    *if case_e_dt = '01JAN3000'D then case_e_dt = .;
    *if s_vol_e_dt = '31DEC3000'D then s_vol_e_dt = .;
    *if e_vol_e_dt = '31DEC3000'D then e_vol_e_dt = .;

    run;
/*
proc print data = ucb_case16 (obs = 4);
	where indn_stc ne .;
    run;
*/
*assign case counter;
*by sorting on child, case start date and case end date the file includes concurrent
 cases and, when there are two cases starting on the same date, the deleting of the
 record of the case that closed first;
proc sort data = ucb_case16 out = count_01
    (keep = fkchld_clt fkcase_t case_s_dt case_e_dt
        case_s_cnty case_e_cnty) nodupkey; 
	   /*Note: case_s_cnty_at, case_e_cnty_at, & case_e_cnty_x are excluded from the keep command */
    by fkchld_clt case_s_dt case_e_dt;
    run;

*if two cases opened on the same date, take the case that closed last.
 if both cases opened and closed on the same date, the case retained would be random.
*only affects about 2450 case pairs out of 1.5 million;
data count_02;
    set count_01;
    by fkchld_clt case_s_dt case_e_dt;
    if last.case_s_dt;
    run;

data count_03;
    set count_02;
    by fkchld_clt case_s_dt case_e_dt;
    retain fkchld_clt_x CASE_CT;
    if first.fkchld_clt then CASE_CT = 1;
        else CASE_CT = CASE_CT+1;
    fkchld_clt_x = fkchld_clt;
    label CASE_CT = "Case Counter";
    run;

*merge count with ucb_case16 file. By doing left join from count_03 the cases
 that were deleted in count_02 because they had the same start date are not included;
proc sql;
    create table ucb_case18 as
        select ucb_case16.*,
            count_03.case_ct
        from count_03 left join ucb_case16 (drop = case_e_cnty_at case_e_cnty_x case_s_cnty_at)
        on ucb_case16.fkcase_t = count_03.fkcase_t
    order by fkchld_clt, csc_order;
    quit;

proc contents data = ucb_case18; run;

data DWH.UCB_CASE_SVC_COMP (drop = icpc_in);
    set ucb_case18 ;
    where icpc_in ne 1;
    if case_e_dt = '01JAN3000'D then case_e_dt = .;
    if s_vol_e_dt = '31DEC3000'D then s_vol_e_dt = .;
    if e_vol_e_dt = '31DEC3000'D then e_vol_e_dt = .;


run ;