*****************************************************************************************;
** Copyright (c) 2007 Center for Social Services Research,                             **;
** University of California at Berkeley. All rights reserved.                          **;
**                                                                                     **;
** This program initially produce reports for CFSR1 Permanency Composite  A, measure 4 **;
** It has been revised to meet CFSR4 requirements - an exit cohort of both             **;
** reunification and guardianship (but not adoption) permanency exits                  **;
**                                                                                     **;
** Permanency C1C2Measure4: Permanency of reunification                                **;
** Of all children discharged from foster care to reunification in the 12 month period **;
** prior to FY2004, what percent re-entered foster care in less than 12 months from    **;
** the date of discharge?                                                              **;
**                                                                                     **;
** Original program called:  fed3-4.sas                                                **;
** Original Programmer: Stephanie Cuccaro-Alamin & Terry Shaw                          **;
** Adapted for new CFSR measures by Sean Lee                                           **;
** Modified for CFSR4 by Joe Magruder                                                  **;
**                                                                                     **;
** Revision History:                                                                   **;
**    2007.07.23 Unduplicate by child for a given time period                          **;
**    2007.07.27 Code reviewed with UCB and CDSS Review Team.                          **;
**               Promoted Code to production.                                          **; 
**    2008.07.28 County assignment change - assigning county at the exit instead of    **;
**               at the entry                                                          **;
**    2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group'              **;
**    2009.07.02 Divide data step with merge into 2 steps to avoid possible data       **;
**               handling problem                                                      **;
**    2021.10.18 modify to include guardianship exits to conform to CFSR4 P4           **;
*****************************************************************************************;

options nodate nonumber macrogen mprint missing='.';

*NEED TO CHANGE;
%let q=4;
%let y=2024;
%let eox='01JAN2025'd ;

libname library "/wss1/SAS/SASDATA/CWS_CMS/Q&q._&y./SOURCE";
libname cws     "/wss1/SAS/SASDATA/CWS_CMS/Q&q._&y./SOURCE";
libname cms     "/wss1/SAS/SASDATA/CWS_CMS/Q&q._&y./SOURCE";

libname ca      "/ssa4/SAS/SASDATA/CWS_CMS/Q&q._&y./";
libname elf     "/ssa4/SAS/SASDATA/CWS_CMS/Q&q._&y./";
libname dwh     "/ssa4/SAS/SASDATA/CWS_CMS/Q&q._&y./";
libname temp    '/pool01/NOSAVE/REPORT_DATA/REENTRY';
*libname steph   '/dss/long';
*libname terry   '/dss/TSHAW';
*libname cfsr    "/ssa3/SAS/REPORT_DATA/&y.Q&q./CFSR";
libname dvlp    "/ssa3/SAS/REPORT_DATA/&y.Q&q.Dvlp/CFSR";
libname test    "/ssa3/SAS/REPORT_DATA/&y.Q&q.Test/CFSR";

***
Individual Measure C1.4: Of all children discharged from foster care to reunification in the 12-month
period prior to the target 12-month period, what percent re-entered foster care in less than 12 months
from the date of discharge?

The denominator for this measure includes children who meet ALL of the following criteria:
        The child has a date of discharge from foster care that occurs during the 12-month period just prior
    to the 12-month target year.
        At the time of the date of discharge, the reason for discharge is either reunification or live with
    relative. (Note:  There are additional specifications and clarifications regarding this measure that
    address issues relevant to the AFCARS structure.  These are incorporated in the pseudo code and syntax)

The numerator for this measure includes children who meet ALL of the criteria for inclusion in the denominator
    and also meet the following criterion:
        The childs date of latest removal from home occurs in less than 12 months from the date of discharge
    from foster care that occurs during the 12-month period just prior to the 12-month target year.
	The table initial_dataset includes the numerator and denominator data.
***;

data initial_dataset;
   set dwh.ucb_fc_afcars(rename=(ethnic=eth gender_cd=gender ));
   where pe_e_dt ne .;

   *** time in care - to identify those in care less than 8 days ***;
   *** there will always be an end date (exit cohort)            ***;
   tic = pe_e_dt - pe_s_dt;
   if tic ge 8 then flag8=1;
   else flag8=2;

   if oh_e_dt = . then sort_dt = &eox.;
   	else sort_dt = oh_e_dt;

   if cnty_rem ne ' ' then cnty = input(cnty_rem, best2.);
   else county=98;
   if agy_rspc ne 34 and cnty in (.,0,59,98,99) then cnty=input(rsp_cnty,best2.) ;
   if not (1 le cnty le 58) then cnty=98;

   * agency responsible type *;
   agency=.;
   if agy_rspc = 34 then agency=1;
   else if agy_rspc=33 or agy_rspc=5603 then agency=2;
   else agency=3;

   ** 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/PI *;
      otherwise ETHNIC=99 ;                         * Invalid Value *;
   end ;
   if HISP_CD eq 'Y' then ETHNIC=3 ;                * Hispanic *;

   if gender='M' then gender_cd=2;
   else if gender='F' then gender_cd=1;
   else if gender='I' then gender_cd=3;
   else gender_cd=99;

   *** removal reason ***;
   if RMV_RSNC in (1609, 1615, 1619) then REMREAS = 1;   * Neglect *;
   else if RMV_RSNC = 1617 then REMREAS = 2;             * Physical *;
   else if RMV_RSNC = 1620 then REMREAS = 3;             * Sexual *;
   else if RMV_RSNC in (1611, .) Then REMREAS = 99;      * Missing *;
   else REMREAS = 4;                                     * Other *;

   ** region **;
   if county in(1, 7, 21, 23, 27, 28, 35, 38, 41, 43, 44, 48, 49) then region=1;
   else if county in(2, 3, 5, 9, 22, 26, 29, 31, 34, 46, 51, 55, 57, 58) then region=2;
   else if county in(4, 6, 8, 11, 12, 17, 18, 25, 32, 45, 47, 52, 53) then region=3;
   else if county in(13. 14. 19, 30, 33, 36, 37, 42, 56) then region=4;
   else if county in(10, 15, 16, 20, 24, 39, 40, 50, 54) then region=5;
   else region=6;

  select(PLC_FCLC);
      when('2222') FED = 1;            /* Pre-Adopt */
      when('1421','1422') FED = 2;     /* Kin */
      when('1415','1416') FED = 3;     /* Foster */
      when('1414', '2200') FED = 4  ;  /* FFA */
      when('1419') FED = 9;            /* Court Specified Home */
      when('7208') FED = 9.2 ; 	       /* Tribally Approved Home*/
      when('1417') FED = 10;           /* Group */
      when('1418','7027') FED = 11;    /* Shelter */
      when('5411') FED = 14;           /* Guardian */
      when(' ','0','.') FED = 99;      /* Missing */
      otherwise FED = 29;              /* Other */
   end;

select(scp_rltc);
      when('1636') scpr=1;
      when('1637') scpr=2;
      when('1638') scpr=3;
      when('1639') scpr=4;
      when('6715') scpr=5;
      when('6990') scpr=6;              /* NREFM Guardian */
      when('6991') scpr=7;              /* NREFM Nonguardian */
      otherwise scpr = 99;              /* Missing */
   end;

   *** Termination reason type ***;

   if TERM_TY_C in (5439, 5440, 5513) then termin  = 1;
   if TERM_TY_C in (5434, 5516) then termin = 4;
   	/*added to add guardianship exits to conform to CFSR4 standard*/

   if pe_s_dt gt pe_e_dt then delete;
   
   if SPELL = 1 then ENTRY = 1;
   else ENTRY = 2;

	TEMP_ID = FKCLIENT_T || compress(put(SPELL,Z2.)); /*add for CFSR4 measure*/
run;

***** Only want exits to reunification or guardianship in denominator***;
/*originally measure only considered reunification, but CFSR4 broadened measure
to include both reunfication and guardianship of all children
exiting in a time period*/

data reunification;
   set initial_dataset;
   where termin in(1,4);
run;

*** take last record for a child/episode***;

proc sort data = reunification;  by fkclient_t pe_s_dt pe_e_dt oh_s_dt SORT_DT; run;

data reunification1;
   set reunification;
   by fkclient_t pe_s_dt pe_e_dt oh_s_dt SORT_DT; ;
   if last.pe_s_dt;

   *** Last placement type will come from this file - it is renamed later ***;

run;

/*revise to use UCB_OFFICE and ST_ID for assignment data*/
/*search for assignment at placement episode end*/
/*code modified from CFSR3 P1 program*/
proc sql;
	create table reuni_2 as
		select reunification1.*,
			ucb_office.*
			from reunification1 left join dwh.ucb_office
		on ucb_office.fkchld_clt = reunification1.fkclient_t
			and (ucb_office.office_s_dt < pe_e_dt 
				or pe_e_dt = .)
			and (ucb_office.office_e_dt ge pe_e_dt or ucb_office.office_e_dt = .)
		order by fkclient_t, temp_id, office_s_dt;
		;
	quit;

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

data reuni_2a reuni_2b (drop = fkchld_clt cnty_spfcd office_s_dt office_e_dt type);
	set reuni_2;
	if office_s_dt ne . then output reuni_2a;
		else output reuni_2b;
	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 reuni_2b1 as
		select st_id.*,
			reuni_2b.*
			from reuni_2b left join dwh.st_id
		on st_id.fkchld_clt = reuni_2b.fkclient_t
			and (st_id.office_s_dt < pe_e_dt 
				or pe_e_dt = .)
			and (st_id.office_e_dt ge pe_e_dt or st_id.office_e_dt = .)
		order by fkclient_t, temp_id, office_s_dt;
		;
	quit;

/* Put two files back together - i.e., add state ID county derrived identifiers to those records in 
   reuni_2 that are lacking cnty_spfcd, office_s_dt and office_e_dt data */
/* At this point there are multiple records for many temp_ids - i.e., multiple
   office (or just county) assignments during a given placement episodes) */
/* if no office and no state id for a given placement, or if only identified county = 99 then
   use the payment county for the 
   placement from the Placement Payment County table (rsp_cnty in revised UCB_FC).*/  
data reuni_3;
	set reuni_2a reuni_2b1;
	if (office_s_dt =. or cnty_spfcd = 99) and rsp_cnty le "58" then do;
		office_s_dt = oh_s_dt;
		office_e_dt = oh_e_dt;
		type = "P";
		cnty_spfcd = rsp_cnty;
	end;
	run;

proc sort data = reuni_3;
	by fkclient_t temp_id office_s_dt;
	run;

/*although there don't appear to be duplicate records, this step is a check*/
data reuni_4;
	set reuni_3;
	by fkclient_t temp_id office_s_dt;
	if last.temp_id;
	run;


proc print data = reuni_4 (obs = 20);
	var fkclient_t temp_id office_s_dt office_e_dt pe_e_dt type;
	*where office_s_dt = .;
	run;

data reuni_5;
set reuni_4;
if cnty_spfcd in( '99', ' ') then cnty = '98';
else cnty = cnty_spfcd;
run;

data reuni (drop = x_cws_off_no x_fkcws_offt x_cnty);
set reuni_5;
  x_cws_off_no = cws_off_no;
  x_fkcws_offt = fkcws_offt;
  x_cnty = cnty;
       
%include "/dss/SAS/PROGRAM/CWS_CMS/SAS_MACRO/county.sas";
    run;


***** Start the main program in the MACRO C1M4 *****;

%macro C1M4(sq,sy,eq,ey);

*sq = start quarter, sy = start year, eq = end quarter, ey = end year;

%let empty=0;

%do SYEAR = &sy. %to &ey. ;

*** The following statements select the appropriate quarters for each year ***;

data _null_;

%if &sq. ^= 1 & &SYEAR.=&sy. %then
%do;

*** if the starting quarter is not 1 then process accordingly ***;

%if &sy.=&ey. %then
    %do;
    start_qtr=&sq. ;
    end_qtr=&eq. ;
    %end;

    %else
    %do;
    start_qtr=&sq. ;
    end_qtr=4 ;
    %end;

%end;

%else %if &eq. ^= 4 & &SYEAR.=&ey. %then
%do;

*** If the ending quarter is not 1 then process accordingly ***;

%if &sy.=&ey. %then
    %do;
    start_qtr=&sq. ;
    end_qtr=&eq. ;
    %end;

    %else
    %do;
    start_qtr=1 ;
    end_qtr=&eq. ;
    %end;

%end;

%else
%do;

*** Otherwise standard quarters ***;

start_qtr=1 ;
end_qtr=4;
%end;

call symput('stqtr',start_qtr);
call symput('enqtr',end_qtr);

run;

%do SQTR = &stqtr. %to &enqtr. ;

** Bring in the specific time period ***;

data exit(rename=(fed=p_plc scpr=p_scpr));
   set reuni;

   where PE_E_DT between yyq(&SYEAR,&SQTR) and yyq(&SYEAR+1,&SQTR)-1 ;
  
   *** This is age at the exit ***;
   if birth_dt ne . then  age = int((intck('month',birth_dt,pe_e_dt)-(day(pe_e_dt) lt day(birth_dt)))/12);

   else age=.;

   if not (0 le age le 20) then age=99;

   PERIOD_DT=yyq(&SYEAR,&SQTR);

run;

*** Get the most recient single record per child ***;

proc sort data = exit;
by fkclient_t pe_s_dt;
run;

data exit1;
set exit;

by fkclient_t;
if first.fkclient_t;

run;

/* need to create exit summary table */

*** Find the reentries from the initial dataset ***;
*** Reentries are episodes within 1 to 365 days or 1 to 730 days. ***;

proc sql;
   create table reentry as
   select a.*,
          b.pe_s_dt as reentry_start
   from exit1 as a, dwh.ucb_fc_afcars as b
   where a.fkclient_t=b.fkclient_t and
         a.pe_e_dt+1 le b.pe_s_dt le a.pe_e_dt+730 ;  
quit;

proc sort data=exit1; by fkclient_t pe_s_dt; run;
proc sort data=reentry; by fkclient_t reentry_start; run;

data reentry2;
   set reentry;
   by fkclient_t;
   if first.fkclient_t;
run;

/* Divide data step with merge into 2 steps to avoid possible data handling problem */

data measure0;
   merge exit1(in=a)
         reentry2(in=b);
   by fkclient_t;
run;

data measure1;
set measure0;

   timing=reentry_start - pe_e_dt ;
   if 1 le timing le 365 then reentry12=1; 
   if 1 le timing le 730 then reentry24=1; 

   if reentry12 = . then reentry12 = 2;
   if reentry24 = . then reentry24 = 2; 
  
run;

*** Create AGENCY sum ***;

data measure1a;
   set measure1;

   AGENCY = 4;

run;

data measure2;
   set measure1 measure1a;
run;


proc append base=tempmeasure data=measure2;


proc sql;

create table c1m4_cnty_&SYEAR.Q&SQTR as
select CNTY, COUNTY, FLAG8, ENTRY, AGENCY, AGE, GENDER_CD, ETHNIC, 
       P_PLC, P_SCPR, REENTRY12, REENTRY24, termin, PERIOD_DT, count(*) as COUNT
from measure2
group by CNTY, COUNTY, PERIOD_DT, FLAG8, ENTRY, AGENCY, AGE, GENDER_CD, ETHNIC, P_PLC, P_SCPR, REENTRY12, REENTRY24,
	termin;
quit;

*** Create California sum ***;

data measure3;
set measure2(drop=CNTY);
 
CNTY = 0;

run;

proc sql;

create table c1m4_state_&SYEAR.Q&SQTR as
select CNTY, COUNTY, FLAG8, ENTRY, AGENCY, AGE, GENDER_CD, ETHNIC, 
       P_PLC, P_SCPR, REENTRY12, REENTRY24, termin, PERIOD_DT, count(*) as COUNT
from measure3
group by CNTY, COUNTY, PERIOD_DT, FLAG8, ENTRY, AGENCY, AGE, GENDER_CD, ETHNIC, P_PLC, P_SCPR, REENTRY12, REENTRY24,
	termin;
quit;

proc append base=county data=c1m4_cnty_&SYEAR.Q&SQTR;
proc append base=state data=c1m4_state_&SYEAR.Q&SQTR;

%end;
%end;
%mend C1M4;
****CHANGE TO UPDATE QUARTER;
%C1M4(1,1998,4,2024);

data c1m4_la;
set county state;
run;

data p4la;
  set c1m4_la;
  county=1900;
  if cnty=19;
run;

data c1m4_la_r1;
  set c1m4_la p4la;
  if cnty=0 then county=0;
run;

data dvlp.c1m4_la;
  set c1m4_la_r1;
run; 

data test.c1m4_la;
  set c1m4_la_r1;
run; 

proc sql;
create index INDX1 on dvlp.c1m4_la(FLAG8, ENTRY, AGENCY, CNTY, PERIOD_DT);
quit;

proc sql;
create index INDX1 on test.c1m4_la(FLAG8, ENTRY, AGENCY, CNTY, PERIOD_DT);
quit;