/* ucb_ref.sas */
/*using abuse and referral address address*/
/**************************************************************************
 * Copyright (c) 2019 by Center for Social Services Research,
 * University of California at Berkeley. All rights reserved.
 **************************************************************************/
/* Construct analysis table with essential attributes from the intake data.
 * CWS/CMS Derived (de-normalized) table
 * Programmer: M. Armijo.
 * Revision History:
 * 2004.01.20 Methodology change.
 * The original method used a inner-join between the REFERRAL_CLIENT
 * and CHILD_CLIENT to construct the universe of child-victims.  While
 * these were indeed child-victims, they could be incorrectly linked to
 * referrals, in which they were not a victim. The use of
 * ALLGTN_T.FKCLIENT_T as the universe of child-victims, where each is
 * linked to a referral (by FKREFERL_T), logically follows from the data
 * model, and eliminates those records where a child is incorrectly
 * appears in a referral as the victim.
 * 2004.07.29 Added REFERL_T.FKREFERL_T (Associated Referral).
 * 2006.05.04 CNTY_SPFCD uses values from ASGNM_T.CNTY_SPCD 
 * Bounded range ASGNM_T.START_DT <= REF_RCV_DT <= ASGNM_T.END_DT 
 * used to determine which assignment is appropriate for referral.
 * 2010.09.23 Added REFERL_T.RSP_AGY_CD (RESPONSIBLE_AGENCY_CODE).
 * 2012.05.18 Made correction to mapping of P_ETHNCTYC to derived var ETHNIC. 
 * 2016.04.04 J. Magruder. Modified to add FKCLIENT_0 (abuser); CLNTRELC (relationship between
 * child and abuser; and NON_PRT_CD (primarily to identify abuse in foster care).
 * 2017.11.22 Added ABUSE_STDT and ABUSE_ENDT
 * 2018.12.14 j magruder add zip, state, city of maltreatment and time-of-referral address
 * 2020.07.22 j magruder add assignment office and, for Los Angeles, office based
			on residence and abuse location Zip Code (also add Census race/ethnicity)
 * 2023.02.02 j magruder add mandated reporter variable per D Webster request
*/
 

%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="|----|+|---+=|-/\<>*";

%let S_YEAR=1998 ; /* Start-of-Data */



/*eliminate duplicates in the Los Angeles Zip Code lookup table*/
proc sort data = "/dss/SAS/SASDATA/LA_POP/la_office_zip_2020" out = la_zip_01 nodupkey;
	by la_zipcode county;
	run;




/* 1. Join ASSIGNMENT with REFERRAL */
proc sql ;
create table REFERL_T_0 as
select
A.IDENTIFIER as REF_ID,
A.REF_RCV_DT format mmddyy10.,
A.REFCLSR_DT format mmddyy10.,
A.FKADDRS_T,
A.FKREFERL_T,
A.CNTY_SPFCD as CNTY_SPFCD_0,
A.RSP_AGY_CD,
A.RFR_RSPC,
B.IDENTIFIER as ASG_ID,
B.CNTY_SPFCD as CNTY_SPFCD label="ASSIGNMENT_COUNTY",
B.START_DT as ASG_START_DT format mmddyy10.,
B.END_DT as ASG_END_DT format mmddyy10.,
B.END_TM as ASG_END_TM
from CWS.REFERL_T A left join CWS.ASGNM_T B
on A.IDENTIFIER=B.ESTBLSH_ID
   and ESTBLSH_CD='R'
   and ASGNMNT_CD='P'
   and year(REF_RCV_DT) ge &S_YEAR
   and REF_RCV_DT between B.START_DT and B.END_DT
order by  REF_ID, ASG_START_DT, ASG_END_DT, ASG_END_TM
;
quit ;

/*bring in information about referral disposition*/
proc sql;
	create table referl_t_01 as
	select
		referl_t_0.*,
		refr_clt.fkclient_t,
		refr_clt.dispstn_cd,
		refr_clt.dsp_rsnc
	from referl_t_0 left join cws.refr_clt
	on referl_t_0.ref_id = refr_clt.fkreferl_t
	order by  REF_ID, ASG_START_DT, ASG_END_DT, ASG_END_TM;
	quit;
	

/* 2. Where there are multiple ASSIGNMENTS, select only one (the last one, by date). */
data REFERL_T ;
set REFERL_T_01 ;
by REF_ID ASG_START_DT ASG_END_DT ASG_END_TM ;
/* convenient place for some recodes ... */
if CNTY_SPFCD eq '99' then CNTY_SPFCD='59' ;
if CNTY_SPFCD eq ' '  then CNTY_SPFCD='98' ;
if last.REF_ID then output ;
run ;

proc tabulate data = referl_t missing format = comma9.0 noseps;
	class dispstn_cd dsp_rsnc RFR_RSPC;
	table dsp_rsnc all, dispstn_cd all;
	table rfr_rspc all, dispstn_cd all;
	format dsp_rsnc rfr_rspc syscode.;
	where year(ref_rcv_dt) = 2018;
	run;

/*bring in allegation data*/
proc sql ;
create table PASS01 as
select
A.FKCLIENT_T,
A.IDENTIFIER as ALG_ID label="ALLEGATION_ID",
A.ALG_DSPC as ALG_DSPC length=3 label="DISPOSITION_TYPE" ,
A.ALG_TPC as ALG_TPC  length=3 label="ALLEGATION_TYPE",
A.DISPSN_DT as DISPSN_DT length=4 label="DISPOSITION_DATE" format mmddyy10.,
A.FKCLIENT_0 label="NAMES_AS_PERPETRATOR",
A.NON_PRT_CD label="NON_PROTECTING_PARENT_CODE",
A.FKREFERL_T,
A.ABUSE_STDT format mmddyy10. label="ABUSE START DATE",
A.ABUSE_ENDT format mmddyy10. label="ABUSE END DATE" format mmddyy10.,
B.REF_RCV_DT as REF_RCV_DT length=4 label="RECEIVED_DATE",
B.REFCLSR_DT as REFCLSR_DT length=4 label="CLOSURE_DATE",
B.FKADDRS_T as MALTX_FKADDRS_T label="FKADDRS_T" ,
B.FKREFERL_T as ASSOC_REF label="ASSOCIATED_REFERRAL",
B.RFR_RSPC,
B.CNTY_SPFCD label="ASSIGNMENT_COUNTY" ,
B.CNTY_SPFCD_0,
B.RSP_AGY_CD
from CWS.ALLGTN_T A , REFERL_T B
where A.FKREFERL_T=B.REF_ID
   and year(REF_RCV_DT) ge &S_YEAR
;

/* find relationship between abused and abuser - added 2016Q1 */
proc sql;
	create table rel_01 as
	select PASS01.fkreferl_t,
		PASS01.alg_id,
		PASS01.fkclient_t,
		PASS01.fkclient_0,
		cln_relt.clntrelc
	from PASS01, cws.cln_relt
	where PASS01.fkclient_t = cln_relt.fkclient_t
		and PASS01.fkclient_0 = cln_relt.fkclient_0
	order by fkreferl_t, alg_id;
	quit;

proc sql;
	create table rel_02 as
	select PASS01.fkreferl_t,
		PASS01.alg_id,
		PASS01.fkclient_t,
		PASS01.fkclient_0,
		cln_relt.clntrelc
	from PASS01, cws.cln_relt
	where PASS01.fkclient_t = cln_relt.fkclient_0
		and PASS01.fkclient_0 = cln_relt.fkclient_t
	order by fkreferl_t, alg_id;
	quit;

data rel_03;
	set rel_01 rel_02;
	run;

proc sort data = rel_03;
	by fkreferl_t alg_id;
	run;
/* Eliminate one relationship when 2 found - less that 0.1% in 2012-2015 test */
data rel_04;
	set rel_03;
	by fkreferl_t alg_id;
	if first.alg_id;
		run;

proc sql;
	create table PASS02 as
	select PASS01.*,
		rel_04.clntrelc length=4 label="CLIENT_RELATIONSHIP_TYPE"
	from PASS01 left join rel_04
		on PASS01.alg_id = rel_04.alg_id
	order by fkclient_t, fkreferl_t, fkclient_0;
	quit;
/* end of relationship module */


/*add address data for maltreatment location address*/
proc sql;
	create table pass04 as select
	pass02.*,
	addrs_t.zip_no as MALTX_ZIP_NO label "Maltreatment loc. Zip",
	addrs_t.state_c as MALTX_STATE_C label "Maltreatment loc. State",
	addrs_t.city_nm as MALTX_CITY_NM label "Maltreatment loc. City"
	from pass02 left join cws.addrs_t
	on pass02.maltx_fkaddrs_t = addrs_t.identifier
	order by fkclient_t, fkreferl_t, fkclient_0;
	quit;


/*add referral address data*/


/*get client address data for children with referrals in the test year
	based on referral received date.
	Note: when more than one address active on referral 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*/
proc sql;
	create table ref_addr_10 as select
	pass04.fkclient_t,
	pass04.fkreferl_t,
	pass04.ref_rcv_dt,
	cl_addrt.fkaddrs_t as REF_FKADDRS_T label "Residence at Referral Address ID",
	cl_addrt.eff_strtdt as ref_addrs_s_dt format mmddyy10.,
	cl_addrt.eff_end_dt as ref_addrs_e_dt format mmddyy10.
	from pass04 left join cws.cl_addrt
	on pass04.fkclient_t = cl_addrt.fkclient_t 
		and cl_addrt.eff_strtdt le ref_rcv_dt and
		(cl_addrt.eff_end_dt ge ref_rcv_dt or cl_addrt.eff_end_dt = .)
	order by fkclient_t, fkreferl_t, cl_addrt.eff_strtdt;
	quit;
/*take the address with the latest start date on or before the referral date*/
data ref_addr_12;
	set ref_addr_10;
	by fkclient_t fkreferl_t ref_addrs_s_dt;
	if last.fkreferl_t;
	run;


/*add address data for residence at time of referral*/
proc sql;
	create table ref_addr_16 as select
	ref_addr_12.*,
	addrs_t.zip_no as REF_ZIP_NO label "Residence at Referral Zip",
	addrs_t.state_c as REF_STATE_C label "Residence at Referral State",
	addrs_t.city_nm as REF_CITY_NM label "Residence at Referral City"
	from ref_addr_12 left join cws.addrs_t
	on ref_addr_12.ref_fkaddrs_t = addrs_t.identifier
	order by fkclient_t, fkreferl_t;
	quit;


proc sql;
	create table pass06 as select
		pass04.*,
		ref_addr_16.ref_fkaddrs_t,
		ref_addr_16.ref_zip_no,
		ref_addr_16.ref_state_c,
		ref_addr_16.ref_city_nm
	from pass04 left join ref_addr_16
	on pass04.fkclient_t = ref_addr_16.fkclient_t
		and pass04.fkreferl_t = ref_addr_16.fkreferl_t
	order by fkclient_t, fkreferl_t, fkclient_0;
	quit;

/*bring in referral office assignment data - this primarily relevant to
	finding which Los Angeles Office the referral may be attached to*/
/*Bring in office assignment data, but limit to Los Angeles regional offices*/
/*This could be modified to include other counties' regional offices*/
proc sql;
	create table pass08 as
		select pass06.*,
			ucb_office.type,
			ucb_office.office_s_dt,
			ucb_office.office_e_dt,
			ucb_office.cnty_spfcd as OFFICE_CNTY_SPFCD,
			ucb_office.cws_off_no,
			ucb_office.fkcws_offt
			from pass06 left join dwh.ucb_office
		on ucb_office.fkchld_clt = pass06.fkclient_t
			and ucb_office.type = "R"
			and (ucb_office.office_e_dt ge ref_rcv_dt or ucb_office.office_e_dt = .)
			and (ucb_office.office_s_dt le dispsn_dt or dispsn_dt = .)
			/*and cws_off_no in("S3253","S1277","S1280","S1254","S8234","S3239","S8236",
				"S5252","S1255","S8251","S4261","S7207","S2213","S5211","S6219","S2217",
				"S2218","S6260","S5212")*/
		order by fkclient_t, fkreferl_t, alg_id, ref_rcv_dt, office_s_dt;
		;
	quit;


/*take the last office assignment open for each allegation*/
/*determine office_county based on office assignment - doesn't always match county
	associated with the referral. For Los Angees assigns by office.*/
data pass10 (drop = x_cws_off_no x_fkcws_offt x_cnty 
		rename=(COUNTY=OFFICE_COUNTY));
	set pass08;
	by fkclient_t fkreferl_t alg_id ref_rcv_dt office_s_dt;
	if last.alg_id;
	   x_cws_off_no = cws_off_no;
	   x_fkcws_offt = fkcws_offt;
	   x_cnty = office_cnty_spfcd;
       
        %include "/dss/SAS/PROGRAM/CWS_CMS/SAS_MACRO/county.sas";
	label county = "Assignment Office";
    run;

  
proc freq data = pass10;
	table office_county rfr_rspc;
	where year(ref_rcv_dt) = 2019;
	run;


/* pass12.sas renamed from pass2.sas */
/* Add attributes from REFERRAL_CLIENT (REFR_CLT) [child level data] */

proc sql ;
create table PASS12 as
select
A.*,
B.DSP_RSNC as DSP_RSNC length=3 label="DISPOSITION_CLOSURE_REASON",
B.DISPSTN_CD as DISPSTN_CD,  /* DISPOSITION_CODE                         */
B.RCL_DISPDT as RCL_DISPDT length=4 label="Referral Disposition Date" format mmddyy10.
from PASS10 A, CWS.REFR_CLT B
where
A.FKCLIENT_T=B.FKCLIENT_T
and
A.FKREFERL_T=B.FKREFERL_T
;


/* pass14.sas table retitled from pass3.sas */
/* Add Collateral Client Rptr Relationship Type */
proc sql ;
create table PASS14 as
select
A.*,
B.COL_RELC length=3 label="REPORTER_RELATIONSHIP",
B.MNRPTR_IND /*add 2023.01.31 j magruder*/
from PASS12 A left join CWS.REPTR_T B
on A.FKREFERL_T=B.FKREFERL_T
;

/* Add demographic attributes */
/* The inner-join requires a presence in CLIENT_T to be selected. */
create table pass16(label="UCB_REFERRAL") as
select
A.*,
B.BIRTH_DT as BIRTH_DT length=4 format mmddyy10.,
B.P_ETHNCTYC as P_ETHNCTYC length=3 label="PRIMARY_ETHNICITY_TYPE",
case 
   when B.HISP_CD = "Y" then '3'                          /* Hispanic overrides P_ETHNCTYC values*/	
   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 */
   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", 
B.GENDER_CD,
B.HISP_CD  
from PASS14 A, CWS.CLIENT_T B
where A.FKCLIENT_T=B.IDENTIFIER
;

/*add Census race/ethnicity codes*/
proc sql;

create table pass20 as
select x.*, y.CENS_RC, y.HISP_CDX
from pass16 as x left join dwh.CENSUS_RC as y
on x.FKCLIENT_T = y.FKCLIENT_T;
quit;

/*add Los Angeles office based on client address and maltreatment location 
  Zip Codes*/

/*add Los Angeles office assignment for maltreatment location based on zip code*/
proc sql;
	create table pass25 as select
	pass20.*,
	laz.county as MALTX_COUNTY format=6.0 label="LA Office based on Mal tx location"
	/*from pass20 left join "/pool01/home/joemagruder/Los_Angeles/LA_office_zip_2020" as laz*/
	from pass20 left join la_zip_01 as laz
	/*from pass20 left join "/dss/SAS/SASDATA/LA_POP/la_office_zip_2020" as laz*/
	on pass20.maltx_zip_no = laz.la_zipcode
	order by fkclient_t, ref_rcv_dt, alg_id;
	quit;

/*add Los Angeles office assignment for residence location based on zip code*/
proc sql;
	create table pass30 as select
	pass25.*,
	laz.county as RES_COUNTY format=6.0 label="LA Office based on Residence"
	from pass25 left join la_zip_01 as laz
	/*from pass25 left join "/dss/SAS/SASDATA/LA_POP/la_office_zip_2020" as laz*/
	on pass25.ref_zip_no = laz.LA_zipcode
	order by alg_id;
	quit;

/*Using 3 sources - worker assignment, child residence, maltreatment location
in preference order - assign Los Angeles referrals to regional offices*/
/*for remainder of state use office county*/
/*if office county is missing using cnty_spfcd*100*/
/*there are duplicate rows on the la_office_zip_2020 table which create duplicate
records on the pass25 and pass30 files therefore need to deduplicate by alg_id
as file should have only one record per alg_id*/

data pass35 (drop = type office_e_dt office_s_dt);
set pass30;

by alg_id;
if first.alg_id;

if cnty_spfcd = "19" then do;
if office_cnty_spfcd = "19" and office_county ge 1901 and office_county le 1919
then COUNTY = office_county;
else if res_county ne . then COUNTY = res_county;
else if maltx_county ne . then COUNTY = maltx_county;
else COUNTY = 1999;
end;

else COUNTY = cnty_spfcd*100;

label COUNTY = "Most Likely Office";

/*class allegations as investigated or not investigated.
Investigated if allegation disposition of Substantiated,
Inconclusive, or Unfounded or if referral response type
of immediate, 3-day, 5-day, or 10-day. Using both indicators
provides data about allegations where the disposition pending*/

if alg_dspc in(45, 47, 46) or rfr_rspc in(1516, 1517, 1518, 1520)
then INVESTIGATE = 1; /* Investigated */
else INVESTIGATE = 2; /* Not Investigated */
label INVESTIGATE = "Investigated Referral";
 
run;

DATA DWH.UCB_REF;
set pass35;
run;