/* ucb_office.sas */

/* This program creates a file with one row for each county and office assignment
 * for each child based on case and referral. When the child is assigned to more
 * than one office during the day, the file shows the office assignment at the end
 * of the day. The program only considers primary assignments. 
 * Originally developed for Los Angeles office assignment project
 * Programmer: J. Magruder.
 * Revision History:
 * 2016.08.15 first revision
 * 2016.11.09 Adapted for production use by M. Armijo.
 . 2019.03.04 error in ST_ID table creation corrected j magruder
 * (cosmetic changes)
 */

%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))) ); 
%mend moage;

/* find case assignment county and office */

data ASGNM_T;
   set cws.ASGNM_T;

   if END_DT = . then tEND_DT = '31DEC3000'd;
   else tEND_DT = END_DT;
   format tEND_DT mmddyy10.;
	*where year(start_dt) ge 2011; /*date limit only for test purposes*/
run;


/* CASE */
/* find office assignment for cases */
proc sql;
   create table CASE_ASSIGN_01 as
   select x.FKCHLD_CLT,
          x.IDENTIFIER as case_id,
		  x.start_dt as case_s_dt format mmddyy10.,
		  case
		  	when x.end_dt = . then '31DEC3000'd
			else x.end_dt
			end as case_e_dt format mmddyy10.,
          y.START_DT  length=4 format mmddyy10.,
          y.tEND_DT,
          y.END_TM,
          y.CNTY_SPFCD,
		  y.FKCASE_LDT
   from cws.CASE_T as x, ASGNM_T as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'C'
   order by FKCHLD_CLT, START_DT, tEND_DT, END_TM;
/* place assignment within caseload */
   create table case_assign_02 as
   select case_assign_01.*,
            case_ldt.fkasg_unit
        from case_assign_01 left join cws.case_ldt
        on case_assign_01.fkcase_ldt = case_ldt.identifier
        order by FKCHLD_CLT, START_DT, tEND_DT, END_TM;

    /* place caseload within office */
    create table case_assign_03 as
        select case_assign_02.*,
            asg_unit.fkcws_offt
        from case_assign_02 left join cws.asg_unit
        on case_assign_02.fkasg_unit = asg_unit.identifier
        order by FKCHLD_CLT, START_DT, tEND_DT, END_TM;
quit;

/* find start and end date for each office assignment.
 * Note start and end dates/times are for specific worker assignment thus
 * a caseload and then office can have multiple start and end times
 */ 

/* set office start date to first caseload assignment date in that office assignment period. */ 
proc sort data = case_assign_03;
	by fkchld_clt case_id /*fkcws_offt*/ start_dt tend_dt end_tm;
	run;
 
data case_assign_03a;
	set case_assign_03;
	by fkchld_clt case_id /*fkcws_offt*/ start_dt tend_dt end_tm;
	retain xfkchld_clt xcase_id xfkcws_offt office_s_dt;
	if (case_id ne xcase_id)
		or (case_id = xcase_id and fkcws_offt ne xfkcws_offt) then
			office_s_dt = start_dt;
	xfkchld_clt = fkchld_clt;
	xfkcws_offt = fkcws_offt;
	xcase_id = case_id;
	format office_s_dt mmddyy10.;

	run;

/* set office end date to last caseload assignment date in that office assignment period.
 * requires descending sort to have the last caseload assignment in an office assignment
 * first on the list.
 */
proc sort data = case_assign_03A (drop = xfkchld_clt xfkcws_offt xcase_id);
	by fkchld_clt case_id /*fkcws_offt*/ descending start_dt descending tend_dt descending end_tm;
	run;

data case_assign_03b;
	set case_assign_03a;
	by fkchld_clt case_id /*fkcws_offt*/ descending start_dt descending tend_dt descending end_tm;
	retain xfkchld_clt xcase_id xfkcws_offt office_e_dt;
	if (case_id ne xcase_id)
		or (case_id = xcase_id and fkcws_offt ne xfkcws_offt) then 
			office_e_dt = tend_dt;
	xfkchld_clt = fkchld_clt;
	xfkcws_offt = fkcws_offt;
	xcase_id = case_id;
	format office_e_dt mmddyy10.;
	run;

/* take one record per office - sort puts records in temporal order again
 * note data step drops caseload assignment level data as irrelevant
 * note - if two office assignments start on the same date this takes the
 * one with the last end date.
 */
proc sort data = case_assign_03b;
	by fkchld_clt case_s_dt case_id office_s_dt office_e_dt start_dt;
	run;

data case_assign_03c (keep = fkchld_clt case_id case_s_dt case_e_dt fkcws_offt office_s_dt office_e_dt);
	set case_assign_03b;
	by fkchld_clt case_s_dt case_id office_s_dt office_e_dt start_dt;
	if last.office_s_dt;
	run;

proc sort data = case_assign_03c;
	by fkchld_clt case_s_dt office_s_dt;
	run;

/* add office number and name */
proc sql;
    create table case_assign_04 as
        select case_assign_03c.*,
			cws_offt.cnty_spfcd,
            cws_offt.cws_off_no,
			"C" as type
        from case_assign_03c left join cws.cws_offt
        on case_assign_03c.fkcws_offt = cws_offt.identifier
        order by FKCHLD_CLT, case_s_dt, office_s_dt, office_e_dt;
    quit;


/* REFERRAL */
/* find office assignment for referrals, some of which will overlap with cases */
proc sql;

create table ref_assign_01 as
select x.IDENTIFIER as fkreferl_t,
		x.ref_rcv_dt as ref_s_dt format mmddyy10.,
		  case
		  	when x.REFCLSR_DT = . then '31DEC3000'd
			else x.REFCLSR_DT
			end as ref_e_dt format mmddyy10.,
      	y.START_DT length=4 format mmddyy10., 
		y.tEND_DT,
		y.END_TM, 
		y.CNTY_SPFCD,
	   	y.FKCASE_LDT
from CWS.REFERL_T as x, ASGNM_T as y
where x.IDENTIFIER = y.ESTBLSH_ID and
      y.ASGNMNT_CD = 'P' and
      y.ESTBLSH_CD = 'R' /*and
	  year(start_dt) ge 2011*/
order by x.identifier, ref_s_dt, START_DT, tEND_DT, END_TM;

/* identify clients in referrals
 * looking for child clients, but output will include non-child clients
 * note this step not part of case process (above) because case is by
 * child while referral may include more than one child
 */

create table ref_assign_01a as
        select ref_assign_01.*,
            refr_clt.fkclient_t as fkchld_clt
        from ref_assign_01, cws.refr_clt
        where ref_assign_01.fkreferl_t = refr_clt.fkreferl_t
        order by fkclient_t, ref_s_dt, START_DT, tEND_DT, END_TM;

/* add in caseload, assignment unit and cws office 
 * for Los Angeles office assignment project j magruder 4/18/16
 * place assignment within caseload
 */
    
create table ref_assign_02 as
        select ref_assign_01a.*,
            case_ldt.fkasg_unit
        from ref_assign_01a left join cws.case_ldt
        on ref_assign_01a.fkcase_ldt = case_ldt.identifier
        order by fkchld_clt, ref_s_dt, START_DT, tEND_DT, END_TM;

    /* place caseload within office */
    create table ref_assign_03 as
        select ref_assign_02.*,
            asg_unit.fkcws_offt
        from ref_assign_02 left join cws.asg_unit
        on ref_assign_02.fkasg_unit = asg_unit.identifier
        order by fkchld_clt, ref_s_dt, START_DT, tEND_DT, END_TM;
quit;


/* find start and end date for each office assignment.
 * Note start and end dates/times are for specific worker assignment thus
 * a caseload and then office can have multiple start and end times.
 */

/* set office start date to first caseload assignment date in that office assignment period. */ 
proc sort data = ref_assign_03;
	by fkchld_clt fkreferl_t /*fkcws_offt*/ start_dt tend_dt end_tm;
	run;
 
data ref_assign_03a;
	set ref_assign_03;
	by fkchld_clt fkreferl_t /*fkcws_offt*/ start_dt tend_dt end_tm;
	retain xfkchld_clt xfkreferl_t xfkcws_offt office_s_dt;
	if (fkreferl_t ne xfkreferl_t)
		or (fkreferl_t = xfkreferl_t and fkcws_offt ne xfkcws_offt) then
			office_s_dt = start_dt;
	xfkchld_clt = fkchld_clt;
	xfkcws_offt = fkcws_offt;
	xfkreferl_t = fkreferl_t;
	format office_s_dt mmddyy10.;

	run;
/* set office end date to last caseload assignment date in that office assignment period.
 * requires descending sort to have the last caseload assignment in an office assignment
 * first on the list 
 */
proc sort data = ref_assign_03A (drop = xfkchld_clt xfkcws_offt xfkreferl_t);
	by fkchld_clt fkreferl_t /*fkcws_offt*/ descending start_dt descending tend_dt descending end_tm;
	run;

data ref_assign_03b;
	set ref_assign_03a;
	by fkchld_clt fkreferl_t /*fkcws_offt*/ descending start_dt descending tend_dt descending end_tm;
	retain xfkchld_clt xfkreferl_t xfkcws_offt office_e_dt office_e_tm;
	if (fkreferl_t ne xfkreferl_t)
		or (fkreferl_t = xfkreferl_t and fkcws_offt ne xfkcws_offt) then do; 
			office_e_dt = tend_dt;
			office_e_tm = end_tm;
			end;
	xfkchld_clt = fkchld_clt;
	xfkcws_offt = fkcws_offt;
	xfkreferl_t = fkreferl_t;
	format office_e_dt mmddyy10.;
	format office_e_tm time8.;
	run;


/* Take one record per office - sort puts records in temporal order again.
 * note data step drops caseload assignment level data as irrelevant
 * last start date is used so that if the assignment started in one office and 
 * was transferred to another on the same date, the last office on the date
 * is used
 */

proc sort data = ref_assign_03b;
	by fkchld_clt ref_s_dt fkreferl_t office_s_dt office_e_dt office_e_tm start_dt;
	run;

data ref_assign_03c (keep = fkchld_clt fkreferl_t ref_s_dt ref_e_dt fkcws_offt office_s_dt 
		office_e_dt office_e_tm);
	set ref_assign_03b;
	by fkchld_clt ref_s_dt fkreferl_t office_s_dt office_e_dt office_e_tm start_dt;
	if last.office_s_dt;
	run;

proc sort data = ref_assign_03c;
	by fkchld_clt office_s_dt office_e_dt office_e_tm;
	run;

/* further identify office */
proc sql;
    create table ref_assign_04 as
        select ref_assign_03c.*,
			cws_offt.cnty_spfcd,
            cws_offt.cws_off_no,
			"R" as type
        from ref_assign_03c left join cws.cws_offt
        on ref_assign_03c.fkcws_offt = cws_offt.identifier
        order by fkchld_clt, office_s_dt, office_e_dt, office_e_tm;
    quit;

/* CASE and REFERRAL Merge */
/* merge case and office assignment data together */
/* delete Los Angeles KinGAP and Probation Office assignments as they otherwise 
   override CWS referral assignments for children receiving KinGAP benefits or
   in Probation placements*/

data merged_01;
set ref_assign_04 case_assign_04;
if cws_off_no in("S9072" /* , "S0299" */) then delete; /*Los Angeles KinGAP and Probation*/
run;

proc sort data = merged_01 (keep = fkchld_clt type fkcws_offt cws_off_no cnty_spfcd
		office_s_dt office_e_dt office_e_tm);
	by fkchld_clt office_s_dt office_e_dt office_e_tm;
	run;

/*	At this point the list includes all assignments for case and referral for each
  	child. However many of these assignments overlap, primarily because of multiple
  	referrals open at the same time. Generally, but not always, these referrals are
  	open in the same county to the same office. To limit reporting to one office at 
  	any given time (except for situations where office assignment changes on a given
	date) the following corrections are made:
	1) if 2 assignments start on the same date, the one that lasts the longest is
		retained (about 6% of records);
	2) if 1 assignment is contained in another (e.g., 2/1/12 to 2/4/12 within
		1/25/12 to 2/14/12) the one within the other is deleted (another 6%);
	3) if an assignment starts before the previous easignment is ended (e.g., 3/1/13
 		to 4/2/13 followed by 3/15/13 to 4/15/13) the first assignment is ended
		on the date the second starts.
*/

	
/* 2 or more assignments starting on the same date - retain one lasting the longest */
data merged_02;
	set merged_01;
	by fkchld_clt office_s_dt office_e_dt office_e_tm;
	if not last.office_s_dt then delete;
	run;

/* Delete one or more assignments within another assignment.
 * This deletes multiple assignments within another assignment
 * e.g., if b is within a and c is within a, both b and c are
 * deleted, apparently because pointer moves to the next record
 * after the delete statement without resetting xfkchld_clt, etc.
 */

data merged_03;
	set merged_02;
	by fkchld_clt office_s_dt office_e_dt office_e_tm;
	retain xfkchld_clt xoffice_s_dt xoffice_e_dt;
	if fkchld_clt = xfkchld_clt 
		and xoffice_s_dt < office_s_dt
		and xoffice_e_dt > office_e_dt
		then delete;
	xfkchld_clt = fkchld_clt;
	xoffice_s_dt = office_s_dt;
	xoffice_e_dt = office_e_dt;
	format xoffice_s_dt xoffice_e_dt mmddyy10.;
	run;

proc sort data = merged_03 (drop =  xoffice_s_dt xoffice_e_dt xfkchld_clt);
	by fkchld_clt descending office_s_dt descending office_e_dt descending office_e_tm;
	run;

/*assignments overlap - replace previous assignment end date with new assignment 
  start date when another assignment starts before previous assignment ends.
  This requires working through file in descending order.*/
/*show assignment at the end of the day by changing end date to day before
  start date when previous assignment starts on same day previous assignment ends*/
data merged_04;
	set merged_03;
	by fkchld_clt descending office_s_dt descending office_e_dt descending office_e_tm;
	retain xoffice_s_dt xoffice_e_dt xfkchld_clt;
	if xfkchld_clt = fkchld_clt
		and xoffice_s_dt <= office_e_dt
		then noffice_e_dt = xoffice_s_dt-1;
		else noffice_e_dt = office_e_dt;
	xfkchld_clt = fkchld_clt;
	xoffice_s_dt = office_s_dt;
	xoffice_e_dt = office_e_dt;
	format xoffice_s_dt xoffice_e_dt noffice_e_dt mmddyy10.;
	label office_s_dt = "Office Assignment Start Date";
	label noffice_e_dt = "Office Assignment End Date";
	label type = "Referral or Case"; 
	run;

proc sort data = merged_04 (drop = xoffice_s_dt xoffice_e_dt xfkchld_clt office_e_dt
		rename=(noffice_e_dt = office_e_dt));
	by fkchld_clt office_s_dt office_e_dt office_e_tm;
	run;

/* State ID. Create cleaned file of county assignments using State IDs */
/* corrected to include all records, not just those with missing end dates 2019.03.04 j magruder*/
proc sql;
	create table xstid_01 as
		select
			B.FKCLIENT_T as fkchld_clt,
			put((gvr_entc-1067), Z2.) as cnty_spfcd,
			B.START_DT as office_s_dt length=4 format mmddyy10.,
			case 
				when b.end_dt = . then '01DEC3000'D
				else b.end_dt
				end as office_e_dt length=4 format mmddyy10.
		from cws.st_id_t as B
		/*where end_dt = .*/
	order by fkclient_t, start_dt, office_e_dt;
	quit;

proc sort data = xstid_01;
	by fkchld_clt office_s_dt office_e_dt;
	run;

/* delete one or more State IDs within another State ID.
 * This deletes multiple State IDs within another State ID
 * e.g., if b is within a and c is within a, both b and c are
 * deleted, apparently because pointer moves to the next record
 * after the delete statement without resetting xfkchld_clt, etc.
 */
data xstid_02;
	set xstid_01;
	by fkchld_clt office_s_dt office_e_dt;
	retain xfkchld_clt xoffice_s_dt xoffice_e_dt;
	if fkchld_clt = xfkchld_clt 
		and xoffice_s_dt < office_s_dt
		and xoffice_e_dt > office_e_dt
		then delete;
	xfkchld_clt = fkchld_clt;
	xoffice_s_dt = office_s_dt;
	xoffice_e_dt = office_e_dt;
	format xoffice_s_dt xoffice_e_dt mmddyy10.;
	run;

proc sort data = xstid_02 (drop =  xoffice_s_dt xoffice_e_dt xfkchld_clt);
	by fkchld_clt descending office_s_dt descending office_e_dt;
	run;

/* State ID overlap - replace previous State ID end date with new State ID 
 * start date when another State ID starts before previous State ID ends.
 * This requires working through file in descending order.
 * show State ID at the end of the day by changing end date to day before
 * start date when previous assignment starts on same day previous assignment ends
 */
data xstid_04;
	set xstid_02;
	by fkchld_clt descending office_s_dt descending office_e_dt;
	retain xoffice_s_dt xoffice_e_dt xfkchld_clt;
	if xfkchld_clt = fkchld_clt
		and xoffice_s_dt <= office_e_dt
		then noffice_e_dt = xoffice_s_dt-1;
		else noffice_e_dt = office_e_dt;
	xfkchld_clt = fkchld_clt;
	xoffice_s_dt = office_s_dt;
	xoffice_e_dt = office_e_dt;
	format xoffice_s_dt xoffice_e_dt noffice_e_dt mmddyy10.;
	label office_s_dt = "office Assignment Start Date";
	label noffice_e_dt = "office Assignment End Date";
	type = "S";
	run;

proc sort data = xstid_04 out = dwh.ST_ID (drop = office_e_dt xoffice_s_dt xoffice_e_dt xfkchld_clt
		rename= (noffice_e_dt = office_e_dt));
	by fkchld_clt office_s_dt noffice_e_dt;
	run;

/* Find State ID county assignment for those children with no office assignmant. */

proc sql;
	create table textx_01 as
		select
			st_id.*
		from dwh.st_id
		where fkchld_clt not in(select fkchld_clt from merged_04)
		order by fkchld_clt, office_s_dt;
	quit;

/* Add state ID identification of county to Office-based file */

data merged_05 (keep = fkchld_clt type office_s_dt office_e_dt  
	cnty_spfcd fkcws_offt cws_off_nm cws_off_no);
	set merged_04 textx_01;
	if office_s_dt > office_e_dt then delete;
	run;

/* Save production file (final form) */

proc sql ;
create table dwh.UCB_OFFICE as
select
fkchld_clt,
FKCWS_OFFT,
office_s_dt as office_s_dt length=4 format=MMDDYY10. ,
CNTY_SPFCD,
CWS_OFF_NO,
type,
office_e_dt as office_e_dt length=4 format=MMDDYY10.
from merged_05
order by fkchld_clt, office_s_dt, office_e_dt;
quit;