*****************************************************************************************; ** P4 **; ** Copyright (c) 2007 Center for Social Services Research, **; ** University of California at Berkeley. All rights reserved. **; ** **; ** This program produces reports for CFSR3 Permanency Performance Area 4 **; ** **; ** Permanency Performance Area 4: Re-entry to foster care **; ** Measure: Of all children who enter foster care in a 12-month period who discharged **; ** within 12 months to reunification, living with a relative(s), or guardianship, what **; ** percent re-enter foster care within 12 months of their discharge? **: ** **: ** This program creates a first entry cohort of children who have been in care for at **; ** least 8 days. The First entry cohort will differ from the standard method used by **; ** UCB to produce our reports because it does not differentiate between CW and PROB. **; ** The main program is contained in a MACRO designed to get first entries during the **; ** required time period and count any reunifications within 12 months (365 days), also **; ** included is the THV adjustment (explained later in the program). **; ** **; ** The revised Federal measure shifts to all children entering care, not just those **; ** entering for the first time and measures all exits to "permanency" not just to **; ** reunification. "Permanency" is defined as reunification, adoption, guardianship **; ** and placement with kin [later not California option]. **; ** **; ** **; ** **; ** **; ** Programmer: Terry V. Shaw **; ** Updates: **; ** 2006.11.30 - remove initial placement of guardian. **; ** 2007.04.12 Reformulate macro to allow for the PERIOD_DT that Michael has set up **; ** as the date field - this uses Quarter and Year. **; ** 2007.04.24 Add the FLAG8 variable (1 for 8 days or longer, 2 otherwise) **; ** 2007.07.05 Fix the ENTRY variable (1 for first entry, 2 for other entry) **; ** by Sean Lee **; ** 2007.07.16 Code reviewed with UCB and CDSS Review Team. **; ** Promoted Code to production. **; ** 2007.09.07 Fix EXIT_TIME **; ** 2008.05.06 Add PLACE-Last Placement Counter for Placement Stability reports **; ** 2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group' **; ** 2014.07.21 modifications for new federal measure, correction for month **; ** calculations. j magruder **; ** 2015.11.20 Add Reunified and Guardianship Filter **; ** 2016.07.28 Los Angeles Office modifications j magruder **; ** 2016.08.23 Modified to use office and state ID assignment tables **; ** 2017.12.22 Modified to eliminated redundant guardianship exclusion j magruder **; ** 2022.06.28 changed reference to UCB_CNTY_REM to RSP_CNTY **; ** **; ** **; *****************************************************************************************; data ucb_fc; set dwh.ucb_fc_afcars(rename=(ethnic=eth gender_cd=gender)); if agy_rspc in (35, 6133, 6134) then delete; if oh_e_dt ne . then toh_e_dt=oh_e_dt; else toh_e_dt=&eox.; if cnty_rem ne ' ' then cnty = input(cnty_rem, best2.); if /* agy_rspc ne 34 and */ cnty in (.,0,59,98,99) then cnty=input(rsp_cnty,best2.) ; /*2022.06.28 j magruder rev to replace ucb_cnty_rem with rsp_cnty*/ if CNTY < 1 or CNTY > 58 then CNTY = 98; *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; age_18 = intnx('year', birth_dt,18, 'sameday'); format age_18 mmddyy10.; *** Age at entry ***; if birth_dt ne . then AGE = int((intck('month',BIRTH_DT,PE_S_DT)-(day(PE_S_DT) lt day(BIRTH_DT)))/12); if AGE = . or AGE lt 0 or AGE gt 20 then AGE=99; if AGE = 0 then AGE = 0.5; if 0 <= PE_S_DT - BIRTH_DT < 29 then AGE = 0; *** 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 */ * First and last Placement Type *; 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; 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('1420') FED = 13; */ /* Non-FC */ when('5411') FED = 14; /* Guardian */ when(' ','0','.') FED = 99 ; /* Missing */ otherwise FED = 29; /* Other */ end; *** Termination reason type *** Reunified 5439 0036 14 Reunified with Parent/Guardian (Court) 5440 0037 15 Reunified with Parent/Guardian (Non-Crt) 5513 0009 Child Released Home Adopted 5426 0030 01 Adoption Finalized 5438 0038 13 Private Adoption Agency (Non-CWS) 5505 0016 Adoption Finalized 5519 0015 Private Adoption Agency (non-CWS) Guardianship 5434 0013 09 Guardianship 5516 0012 Guardianship Emancipation 5427 0031 02 Age of Majority 5433 0033 08 Emancipation 5506 0017 Age of Majority 5510 0006 Emancipation Other 5435 0039 10 * Other 5517 0013 * Other 5428 0017 03 Child Abducted 5507 0018 Child Abducted 5511 0002 Child Ran Away from Placement 5430 0009 05 Child Ran away from Placement 5429 0019 04 Child in Med Fac (Dependency Susp/Dism) 5508 0001 Chld Cmtd to State Hosp(Dpnd Susp/Dism) 5632 0040 17 Incarcerated 5431 0010 06 Child Refused Services 5432 0032 07 Death of Child 5515 0011 Death of Child 5436 0034 11 Other non-CWS Agency has Jurisdiction 5437 0035 12 Parent Rejects Voluntary (FR) Services 5509 0005 Child Dependent in Other State 5512 0008 Child Refused Services 5514 0010 Other non-CWS Agency has Jurisdiction 5518 0014 Parent Rejects Voluntary (FR) Services 5611 0016 16 CWS Agency has Jurisdiction ***; if term_ty_c in (5439, 5440, 5513) then exit_type=1; /*reunify*/ else if term_ty_c in (5426, 5438, 5505, 5519 , 6530, 6531) then exit_type = 2; /*adopt*/ else if term_ty_c in (5434, 5516) then exit_type = 3; /*guard*/ else if term_ty_c in (5427, 5433, 5506, 5510, 6805, 6814, 6803, 6812, 6806, 6815, 6804, 6813) then exit_type = 4; /*emancip.*/ else if term_ty_c in (5428, 5429, 5430, 5431, 5432, 5436, 5437, 5611, 5632, 5507, 5508, 5509, 5511, 5512, 5514, 5515, 5518, 8192, 5517, 5435, 6309, 6310) then exit_type =5; /*other*/ else exit_type=7 ; /* Still in Care */ if pe_e_dt ne . and exit_type=7 then exit_type=5; if term_ty_c in (5439, 5440, 5513) then reunify=1; else reunify=0; if term_ty_c in (5426, 5438, 5505, 5519, 6530, 6531) then adopt=1; else adopt=0; if term_ty_c in (5434, 5516) then guard = 1; else guard=0; if term_ty_c in (5427, 5433, 5506, 5510, 6805, 6814, 6803, 6812, 6806, 6815, 6804, 6813) then emanc=1; else emanc=0; if term_ty_c in (5428, 5429, 5430, 5431, 5432, 5436, 5437, 5611, 5632, 5507, 5508, 5509, 5511, 5512, 5514, 5515, 5518, 8192, 5517, 5435, 6309, 6310 ) or exit_type=5 then other=1; else other=0; /*other*/ if exit_type=7 then incare=1; else incare=0; /* Still in Care */ if pe_e_dt=. then tpe_e_dt=&eox. ; else tpe_e_dt=pe_e_dt; if tpe_e_dt lt pe_s_dt then delete; tic = tpe_e_dt - pe_s_dt; if tic ge 8 then flag8=1; else flag8=2; temp_id = FKCLIENT_T || compress(put(SPELL,Z2.)); run; *** Interested in listing out first entries vs all others ***; proc sort data = ucb_fc; by temp_id pe_s_dt plcep_id plcmnt oh_s_dt tpe_e_dt toh_e_dt; run; data entry; set ucb_fc; by temp_id; if first.temp_id; if spell = 1 then entry = 1; else entry = 2; run; *** Identify instance where a child is in care for less than 8 days ***; *moved to initial data stepprior to 2007 (flag 8); *** Remove instances where Guardian is the initial placement ***; /*Removed because redundant in view of the base file being UCB_FC_AFCARS initial guardianship placements removed by this step should, according to UCB_FC_AFCARS logic be retained because authority for placement is WIC 300 dependency or WIC 600 wardship This increases the number of placement episodes in the file by 0.81% - 0.61% for first episodes and 1.46% for subsequent episodes j magruder 2017.12.22 2017Q4*/ /*data no_guards (keep = plcep_id); * identifying and kicking out initial placement of guardian; set entry; if plc_fclc in('5411') then delete; run; proc sql ; create table ucb_fc_noguard as select entry.fkclient_t, entry.temp_id, entry.plcep_id, entry.fed, entry.scpr, entry.entry from entry where plcep_id in (select plcep_id from no_guards) ; quit; */ /*take placement type, caregiver relationship, and whether first entry from data on first placement in placement episode*/ proc sql; create table entries_01 as select a.*, b.entry, b.fed as f_plc, b.scpr as f_scpr from ucb_fc as a, /*ucb_fc_noguard as b*/ entry as b /*where a.fkclient_t=b.fkclient_t and a.plcep_id=b.plcep_id*/ where a.temp_id = b.temp_id ; quit; proc contents data = entries_01; run; /* find assignment county and office using file dwh.CWS_OFFICE as test of that file*/ /*reduce entries_01 to one record - the last record - per episode*/ data entries_01a (keep = fkclient_t temp_id birth_dt age_18 pe_s_dt pe_e_dt oh_s_dt toh_e_dt scpr PLCMNT agy_rspc CNTY FLAG8 ENTRY AGENCY AGE GENDER_CD ETHNIC FED PREDOM_FT EXIT_TYPE f_plc f_scpr); set entries_01; format toh_e_dt mmddyy10.; run; proc sort data = entries_01a; by temp_id oh_s_dt; run; data entries_01b (rename = (fed = L_PLC scpr = l_scpr)); set entries_01a; by temp_id oh_s_dt; if last.temp_id; run; /*Bring in office assignment data*/ proc sql; create table entries_02 as select entries_01b.*, ucb_office.* from entries_01b left join dwh.ucb_office on ucb_office.fkchld_clt = entries_01b.fkclient_t and (ucb_office.office_s_dt < pe_e_dt /*should this be =< pe_e_dt?*/ or pe_e_dt = .) and ucb_office.office_e_dt > pe_s_dt /*should this be => pe_s_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 (entries_02a) from those that don't (entries_02b)*/ data entries_02a entries_02b (drop = fkchld_clt cnty_spfcd office_s_dt office_e_dt type); set entries_02; if office_s_dt ne . then output entries_02a; else output entries_02b; run; /* proc print data = entries_02b (obs = 10); where year(toh_e_dt) ge 2013; 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 entries_02b1 as select st_id.*, entries_02b.* from entries_02b left join dwh.st_id on st_id.fkchld_clt = entries_02b.fkclient_t and (st_id.office_s_dt < pe_e_dt /*should this be =< pe_e_dt?*/ or pe_e_dt = .) and st_id.office_e_dt > pe_s_dt /*should this be => pe_s_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 entries_02 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)*/ data entries_03; set entries_02a entries_02b1; run; proc sort data = entries_03; by fkclient_t temp_id office_s_dt; run; /*delete hotline/emergency response office case assignments in Los Angeles when subsequent office assignments exist*/ /*this could be expanded to include similar offices in other counties*/ /*delete referral office if there also is a case office unless it is the last assignment This follows methodology of using county/office assignment of case unless there only is referral data*/ data entries_04; set entries_03; by fkclient_t temp_id office_s_dt; if cws_off_no in ("S1278") and last.temp_id = 0 then delete; if type = "R" and last.temp_id = 0 then delete; if office_e_dt = '31DEC3000'D then office_e_dt = .; run; /*find first office assignment and last office assignment for each placement episode. Note temp_id is unique for each child in each episode.*/ /*first office assignment*/ data entries_04_first (drop = oh_s_dt fkchld_clt rename= (cnty_spfcd = f_cnty_spfcd cws_off_no = f_cws_off_no fkcws_offt = f_fkcws_offt type = f_type office_s_dt = f_office_s_dt office_e_dt = f_office_e_dt)); set entries_04; by fkclient_t temp_id office_s_dt; if first.temp_id; label type = 'First Type' cnty_spfcd = 'First County' cws_off_no = 'First Office' fkcws_offt = 'First Office ID' office_s_dt = 'First Office Start Date' office_e_dt = 'First Office End Date' l_scpr = "Last SCP Relationship" l_plc = "Last Placement Type" f_plc = "First Placement Type" f_scpr = "First SCP Relationship" cnty = "Removal County"; run; data entries_04_last (keep = fkclient_t temp_id cnty_spfcd cws_off_no fkcws_offt office_s_dt office_e_dt type oh_s_dt toh_e_dt /*f_scpr f_plc l_scpr l_plc*/ rename= (cnty_spfcd = l_cnty_spfcd cws_off_no = l_cws_off_no fkcws_offt = l_fkcws_offt type = l_type office_s_dt = l_office_s_dt office_e_dt = l_office_e_dt)); set entries_04; by fkclient_t temp_id office_s_dt; if last.temp_id; label type = 'Last Type' cnty_spfcd = 'Last County' cws_off_no = 'Last Office' fkcws_offt = 'Last Office ID' office_s_dt = 'Last Office Start Date' office_e_dt = 'Last Office End Date'; run; /*Merge files together. Modify placement counter. When no county/office assignment and no state id county, then use removal county as first county. This affects early CWS/CMS records and some more recent probation records, almost all from Los Angeles*/ data entries ; merge entries_04_first entries_04_last; by fkclient_t temp_id; /* Adding Placement Count Indicator */ if PLCMNT = 1 then PLACE = 1; else if PLCMNT = 2 then PLACE =2; else if PLCMNT = 3 then PLACE =3; else if PLCMNT = 4 then PLACE =4; else if PLCMNT >= 5 then PLACE = 5; label place = "Modified Placement Counter"; if f_cnty_spfcd = ' ' then f_cnty_spfcd = put((cnty), Z2.); if l_cnty_spfcd = ' ' then l_cnty_spfcd = put((cnty), Z2.); run; proc sort data = entries; by fkclient_t temp_id; run; proc contents data = entries varnum; options pageno = 1; run; /* data test; set entries; if first_cnty_spfcd ne last_cnty_spfcd then change = 1; else change = 0; run; proc freq data = test; table change; run; proc tabulate data = entries missing format =comma8.0; class first_type last_type; table first_type all, last_type all; where agency = 2; run; proc tabulate data = test missing format = 6.0 noseps; class agency last_type; table agency=' ' all, last_type*(N*(f=comma7.0) pctn='%'*(f=5.1)) all*(N*(f=comma7.0) pctn='%'*(f=5.1)) /rts = 5; where year(pe_s_dt) = 2015; run; */ ** Individual Measure C1.3: Of all children entering foster care for the first **; ** time in the 6-month period just prior to the target 12-month period, and who **; ** remained in foster care for 8 days or longer, what percent were discharged **; ** from foster care to reunification in less than 12 months from the date of **; ** latest removal from home? (This measure includes the trial home visit **; ** adjustment.) **; ** **; ** The denominator for this measure includes children who meet ALL of the **; ** following criteria: **; ** - The child's date of first removal from home occurs during the 6-month **; ** period just prior to the 12-month target period, and **; ** - The child is in foster care for 8 days or longer **; ** **; ** The numerator for this measure includes children who meet ALL of the criteria **; ** for inclusion in the denominator and who also meet at least ONE of the **; ** following criteria: **; ** - The child has a date of discharge from foster care that is less than 12 **; ** months from the date of first removal from home, and the discharge reason**; ** 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), OR **; ** - On the date of discharge from foster care, the child has a discharge **; ** reason of reunification or live with relative, and the child is in a **; ** current placement setting of Trial Home Visit that lasted longer than 30 **; ** days, and the date of placement in the current placement setting plus 30 **; ** days is less than 12 months from the date of latest removal from foster **; ** care. **; *** The programs below are for testing purposes only. They are placed inside of a macro so that the program above can be run ; %macro c1m3(sq,sy,eq,ey); %let empty=0; %do year = &sy. %to &ey. ; *** The following statements select the appropriate quarters for each year ***; data _null_; %if &sq. ^= 1 & &year.=&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 & &year.=&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 qtr = &stqtr. %to &enqtr. ; ***** Organize the date fields *****; ***** The information is submitted based on the year of interest so the variable spdate is the start of the period that is six months immediately prior as discussed in the measure *****; /******changed to 12-month period*/ data _null_; length sm $5 ; if &qtr=1 then do; sm='01JAN'; pm='01JAN'; py=&year.-1; end; if &qtr=2 then do; sm='01APR'; pm='01APR'; py=&year.-1; end; if &qtr=3 then do; sm='01JUL'; pm='01JUL'; py=&year.-1; end; if &qtr=4 then do; sm='01OCT'; pm='01OCT'; py=&year.-1; end; spdate=compress("'"||pm||py||"'d"); sdate=compress("'"||sm||&year.||"'d"); edate=compress("'"||sm||%eval(&year.+1)||"'d"); call symput('spdate',spdate); call symput('sdate',sdate); call symput('edate',edate); output; run; /* proc sort data = entries0; by fkclient_t spell pe_s_dt oh_s_dt tpe_e_dt toh_e_dt; run; data entries1; set entries0; by fkclient_t; if first.fkclient_t; run; data entries2; set entries1; UNDUP = 1; run; proc sql; create table entries3 as select x.*, y.UNDUP from entries0 as x left join entries2 as y on x.temp_id = y.temp_id; quit; data entries; set entries3; if UNDUP = . then UNDUP = 0; run; */ /* test time periods %let spdate='01JAN2013'D; %let sdate='01JAN2014'D; %let edate='01JAN2015'D; */ /*change to 12 months, not 6 months 2014.05.07 j magruder*/ *** First entries in 12 months immediately prior to time of interest ***; data base; set entries; where &spdate. le pe_s_dt lt &sdate. ; year = year(&spdate.); qtr = qtr(&spdate.); period_dt=yyq(year,qtr) ; run; /* proc sort data=base; by fkclient_t pe_s_dt oh_s_dt toh_e_dt ; run; */ /*change to following to find last placement in episode and then to find first episode in study year 2014.05.07 j magruder*/ proc sort data = base; by temp_id pe_s_dt oh_s_dt toh_e_dt; run; *data last_ohp /*(rename=(fed=l_plc scpr=l_scpr))*/; *set base; /* by fkclient_t; if last.fkclient_t; */ *by temp_id; *if last.temp_id; /* Adding Placement Count Indicator */ /*if PLCMNT = 1 then PLACE = 1; else if PLCMNT = 2 then PLACE =2; else if PLCMNT = 3 then PLACE =3; else if PLCMNT = 4 then PLACE =4; else if PLCMNT >= 5 then PLACE = 5; run;*/ /* 2014.09.25 adding last placement type back to data */ /*proc sql; create table base1 as select x.*, y.l_plc, y.l_scpr, y.place from base as x left join last_ohp as y on x.temp_id = y.temp_id; quit;*/ /*proc sort data = base1; by fkclient_t pe_s_dt oh_s_dt toh_e_dt ; run; */ /*note: file entries already contains first and last placement, first and last caretaker relationship, and PLACE for each placement episode*/ data base2; *set base1; set base; by fkclient_t; if first.fkclient_t; run; /*add following to identify first placement episode in year*/ /* data last_ohp; set last_ohp_a; by fkclient_t temp_id; if first.fkclient_t then ep_1 = 1; else ep_1 = 0; run; */ data exits ; set base2; if pe_e_dt ne . then do; /*correct lagmon so that any child in care more than X months is assigned to next group - e.g., child in care 12 mos 2 days assigned to 13-18 month, not 7-12 month group 2014.05.07 j magruder*/ /*The following deals with situations where the last placement in the placement episode ended with a trial home visit. It's effect is to truncate placement episodes 30 days after the start of the trial home visit, or when the episode actually ended, whichever came first. Original version limited this adjustment to ones where it would have shortened a placement episode to less than one year. Modified to make adjustment to all episodes ending with trial home visit that lasted more than 30 days.*/ /* if PLCG_RNC=1440 and oh_e_dt ne . and pe_e_dt - oh_e_dt > 30 then pe_e_dt_m = oh_e_dt+ 30; else pe_e_dt_m = pe_e_dt; format pe_e_dt_m mmddyy10.; */ /*trial home visit adjustment removed to match federal requirement and methodology*/ pe_e_dt_m = pe_e_dt; /* group time by time to exit with 3-month intervals if 6 months below then 6-month intervals between 6 months and 4 years then 12-month intevals if over 4 years*/ select ; when(pe_e_dt_m - pe_s_dt ge (252 * 30.4375)) exit_time = 264; when(pe_e_dt_m - pe_s_dt ge (240 * 30.4375)) exit_time = 252; when(pe_e_dt_m - pe_s_dt ge (228 * 30.4375)) exit_time = 240; when(pe_e_dt_m - pe_s_dt ge (216 * 30.4375)) exit_time = 228; when(pe_e_dt_m - pe_s_dt ge (204 * 30.4375)) exit_time = 216; when(pe_e_dt_m - pe_s_dt ge (192 * 30.4375)) exit_time = 204; when(pe_e_dt_m - pe_s_dt ge (180 * 30.4375)) exit_time = 192; when(pe_e_dt_m - pe_s_dt ge (168 * 30.4375)) exit_time = 180; when(pe_e_dt_m - pe_s_dt ge (156 * 30.4375)) exit_time = 168; when(pe_e_dt_m - pe_s_dt ge (144 * 30.4375)) exit_time = 156; *when((144 * 30.4375) <= pe_e_dt_m - pe_s_dt < (156 * 30.4375)) exit_time = 156; when(pe_e_dt_m - pe_s_dt ge (132 * 30.4375)) exit_time = 144; when(pe_e_dt_m - pe_s_dt ge (120 * 30.4375)) exit_time = 132; when(pe_e_dt_m - pe_s_dt ge (108 * 30.4375)) exit_time = 120; when(pe_e_dt_m - pe_s_dt ge ( 96 * 30.4375)) exit_time = 108; when(pe_e_dt_m - pe_s_dt ge ( 84 * 30.4375)) exit_time = 96; when(pe_e_dt_m - pe_s_dt ge ( 72 * 30.4375)) exit_time = 84; when(pe_e_dt_m - pe_s_dt ge ( 60 * 30.4375)) exit_time = 72; when(pe_e_dt_m - pe_s_dt ge ( 48 * 30.4375)) exit_time = 60; when(pe_e_dt_m - pe_s_dt ge ( 36 * 30.4375)) exit_time = 48; when(pe_e_dt_m - pe_s_dt ge ( 30 * 30.4375)) exit_time = 36; when(pe_e_dt_m - pe_s_dt ge ( 24 * 30.4375)) exit_time = 30; when(pe_e_dt_m - pe_s_dt ge ( 18 * 30.4375)) exit_time = 24; when(pe_e_dt_m - pe_s_dt ge ( 12 * 30.4375)) exit_time = 18; when(pe_e_dt_m - pe_s_dt ge ( 6 * 30.4375)) exit_time = 12; when(pe_e_dt_m - pe_s_dt ge ( 3 * 30.4375)) exit_time = 6; when(pe_e_dt_m - pe_s_dt ge ( 0 * 30.4375)) exit_time = 3; otherwise exit_time = 999; end; end; if exit_time = . then exit_time = 999; /*this needs to be outside of the DO statement because records with missing PE_E_DT are excluded from DO statement;*/ if exit_time le 12 and exit_time ne . and pe_e_dt_m le age_18 and exit_type in (1,2,3) then fed_num = 1; else fed_num = 0; if fed_num = 1 and exit_type in(1, 2, 3) then exit_type_p = exit_type; else exit_type_p = 0; run; /*Measure P4 reentries within 2 years*/ /*identify cohort at risk of reentry within 2 years: children who entered care within the study year and then exited to reunification or guardianship within one year of entry but before 18th birthday. When a child has multiple entries in the study year, only the first entry is considered*/ *this is a module that might fit in the P1 program to add the P4 data; *2014.07.22 j magruder; *2014.08.12 j magruder - corected to 12-month reentry period; proc format; value yes_no 1 = "Yes" 2 = "No"; run; /*identify cohort at risk of reentry within 2 years: children who entered care within the study year and then exited to reunification or guardianship within one year of entry but before 18th birthday. When a child has multiple entries in the study year, only the first entry is considered*/ data reentry_01; set exits (rename=(predom_ft=p_plc)); where exit_type_p in(1,3) and pe_e_dt_m < age_18; run; *proc contents data = reentry_01 varnum; run; /*find all placement starts after the permanency exit - because this uses ucb_fc_afcars, reentries to probate guardianship homes should be excluded. However although most are, there there seem to be some reentries to guardianship homes - perhaps a dozen in a year*/ proc sql; create table reentry_02 as select reentry_01.fkclient_t, reentry_01.exit_type, reentry_01.exit_type_p, reentry_01.birth_dt, reentry_01.age_18, reentry_01.age, reentry_01.flag8, reentry_01.entry, reentry_01.gender_cd, reentry_01.ethnic, reentry_01.pe_e_dt_m, reentry_01.cnty, reentry_01.agency, reentry_01.period_dt, reentry_01.f_plc, reentry_01.l_plc, reentry_01.f_cnty_spfcd, reentry_01.f_cws_off_no, reentry_01.f_fkcws_offt, reentry_01.l_cnty_spfcd, reentry_01.l_cws_off_no, reentry_01.l_fkcws_offt, fc.pe_s_dt as reentry_pe_s_dt label "Reentry episode start date", fc.oh_s_dt as reentry_oh_s_dt label "Reentry Placement Start Date", fc.agy_rspc as reentry_agy_rspc label "Reentry Agency Responsible", fc.cnty_rem as reentry_cnty_rem label "Reentry County of Removal", fc.plc_fclc as reentry_plc_fclc label "Reentry Placement Type" from reentry_01 left join dwh.ucb_fc_afcars as fc on reentry_01.fkclient_t = fc.fkclient_t and reentry_01.pe_e_dt_m le fc.pe_s_dt and reentry_01.agy_rspc not in(35, 6133, 6134) and fc.pe_s_dt < age_18 order by fkclient_t, fc.pe_s_dt, fc.oh_s_dt; quit; /*take the first reentry. If the child had more than one entry in the study year, this would take the first reentry after the first exit*/ data reentry_03; set reentry_02; by fkclient_t reentry_pe_s_dt reentry_oh_s_dt; if first.fkclient_t; three_month=intnx('month',pe_e_dt_m,3, 'sameday'); six_month = intnx('month',pe_e_dt_m,6, 'sameday'); one_year = intnx('year',pe_e_dt_m,1, 'sameday'); two_year = intnx('year',pe_e_dt_m,2, 'sameday'); three_year=intnx('year',pe_e_dt_m,3, 'sameday'); format three_month six_month one_year two_year three_year mmddyy10.; label three_month = "Three Month Cut Off Date"; label six_month = "Six Month Cut Off Date"; label one_year = "Twelve Month Cut Off Date"; label two_year = "Twenty four Month Cut Off Date"; label three_year = "Thirty six Month Cut Off Date"; if (reentry_pe_s_dt ne . and reentry_pe_s_dt le three_month) then reentry3 = 1; else reentry3 = 2; label reentry3 = "Reentered Within 3 Months"; if (reentry_pe_s_dt ne . and reentry_pe_s_dt le six_month) then reentry6 = 1; else reentry6 = 2; label reentry6 = "Reentered Within 6 Months"; if (reentry_pe_s_dt ne . and reentry_pe_s_dt le one_year) then reentry12 = 1; else reentry12 = 2; label reentry12 = "Reentered Within 12 Months"; if (reentry_pe_s_dt ne . and reentry_pe_s_dt le two_year) then reentry24 = 1; else reentry24 = 2; label reentry24 = "Reentered Within 24 Months"; if (reentry_pe_s_dt ne . and reentry_pe_s_dt le three_year) then reentry36 = 1; else reentry36 = 2; label reentry36 = "Reentered Within 36 Months"; days_to_reentry = reentry_pe_s_dt - pe_e_dt_m; select(reentry_PLC_FCLC); when('2222') reentry_FED = 1; /* Pre-Adopt */ when('1421','1422') reentry_FED = 2; /* Kin */ when('1415','1416') reentry_FED = 3; /* Foster */ when('1414', '2200') reentry_FED = 4 ; /* FFA */ when('1419') reentry_FED = 9; /* Court Specified Home */ when('7208') FED = 9.2; /* Tribally Approved Home */ when('1417') reentry_FED = 10; /* Group */ when('1418','7027') reentry_FED = 11; /* Shelter */ /* when('1420') reentry_FED = 13; */ /* Non-FC */ when('5411') reentry_FED = 14; /* Guardian */ when(' ','0','.') reentry_FED = 99 ; /* Missing */ otherwise reentry_FED = 29; /* Other */ end; label reentry_fed = "First Reentry Placement Type"; L_CNTY = input(l_cnty_spfcd, best2.); if l_cnty = 19 then do; if l_cws_off_no in("S3253") then COUNTY = 1901; /*Belvedere*/ else if l_cws_off_no in("S1277") then COUNTY = 1902; /*Compton*/ else if l_cws_off_no in("S1280") then COUNTY = 1903; /*El Monte*/ else if l_cws_off_no in("S1254") then COUNTY = 1904; /*Glendora*/ else if l_cws_off_no in("S2218") then COUNTY = 1905; /* Hawthorne old Wateridge South*/ else if l_cws_off_no in("S8234") then COUNTY = 1906; /*Lancaster*/ else if l_cws_off_no in("S3239") then COUNTY = 1907; /*Metro North*/ else if l_cws_off_no in("S8236") then COUNTY = 1908; /*Palmdale*/ else if l_cws_off_no in("S5252") then COUNTY = 1909; /*Pasadena*/ else if l_cws_off_no in("S1255") then COUNTY = 1910; /*Pomona*/ else if l_cws_off_no in("S8251") then COUNTY = 1911; /*Santa Clarita*/ else if l_cws_off_no in("S4261") then COUNTY = 1912; /*Santa Fe Springs*/ else if l_cws_off_no in("S7207") then COUNTY = 1913; /*South County*/ else if l_cws_off_no in("S2213") then COUNTY = 1914; /*Torrance*/ else if l_cws_off_no in("S5211") then COUNTY = 1915; /*Van Nuys*/ else if l_cws_off_no in("S6219") then COUNTY = 1916; /*Vermont Corridor*/ else if l_cws_off_no in("S2217") then COUNTY = 1917; /*Wateridge old Wateridge North*/ *else if l_cws_off_no in("S2218") then COUNTY = 1917; /*Wateridge South*/ else if l_cws_off_no in("S6260") then COUNTY = 1918; /*West Los Angeles*/ else if l_cws_off_no in("S5212") then COUNTY = 1919; /*West SF Valley*/ else if l_cws_off_no in("S0216") then COUNTY = 1920; /*Adoptions*/ else if l_cws_off_no in("S0222") then COUNTY = 1924; /*Medical & ASFA*/ else if l_cws_off_no in("S1251") then COUNTY = 1931; /*American Indian*/ else if l_cws_off_no in("S1250") then COUNTY = 1932; /*Asian Pacific Project*/ else if l_cws_off_no in("S0249") then COUNTY = 1933; /*Deaf Services*/ else if l_cws_off_no in("S0237") then COUNTY = 1934; /*Pomona Family First*/ else if l_cws_off_no in("S1288") then COUNTY = 1935; /*CSEC*/ else if /*l_cws_off_no in("S1278") and */ l_fkcws_offt = "1iyrzAg196" then COUNTY = 1940; /*ROU*/ else if l_cws_off_no in("S0224") then COUNTY = 1941; /*MART*/ else if /*l_cws_off_no in("S1278") and */ l_fkcws_offt in("PHmifFE197","QxMGV6D197") then COUNTY = 1942; /*ER Command/Hotline*/ /* else if l_cws_off_no in("S1278") then COUNTY = 1940; */ /*ERCP*/ /* else if l_cws_off_no in("S0224") then COUNTY = 1941; */ /*MART*/ /* else if l_cws_off_no in("S1278") then COUNTY = 1942; */ /*Hotline*/ else if l_cws_off_no in("S1286") then COUNTY = 1943; /*Juvenile Court Services*/ else if l_cws_off_no in("S0246", "S0247", "S9056", "S4261", "S0394") then COUNTY = 1945; /*Administration*/ else if l_cws_off_no in("S0299") then COUNTY = 1950; /*Probation*/ else if l_cws_off_no in("S9072") then COUNTY = 1960; /*KinGAP*/ else COUNTY = 1999; /*missing*/ end; else if l_cnty = 01 then COUNTY = 0100; else if l_cnty = 02 then COUNTY = 0200; else if l_cnty = 03 then COUNTY = 0300; else if l_cnty = 04 then COUNTY = 0400; else if l_cnty = 05 then COUNTY = 0500; else if l_cnty = 06 then COUNTY = 0600; else if l_cnty = 07 then COUNTY = 0700; else if l_cnty = 08 then COUNTY = 0800; else if l_cnty = 09 then COUNTY = 0900; else if l_cnty = 10 then COUNTY = 1000; else if l_cnty = 11 then COUNTY = 1100; else if l_cnty = 12 then COUNTY = 1200; else if l_cnty = 13 then COUNTY = 1300; else if l_cnty = 14 then COUNTY = 1400; else if l_cnty = 15 then COUNTY = 1500; else if l_cnty = 16 then COUNTY = 1600; else if l_cnty = 17 then COUNTY = 1700; else if l_cnty = 18 then COUNTY = 1800; *else if l_cnty = 19 then COUNTY = 1900; else if l_cnty = 20 then COUNTY = 2000; else if l_cnty = 21 then COUNTY = 2100; else if l_cnty = 22 then COUNTY = 2200; else if l_cnty = 23 then COUNTY = 2300; else if l_cnty = 24 then COUNTY = 2400; else if l_cnty = 25 then COUNTY = 2500; else if l_cnty = 26 then COUNTY = 2600; else if l_cnty = 27 then COUNTY = 2700; else if l_cnty = 28 then COUNTY = 2800; else if l_cnty = 29 then COUNTY = 2900; else if l_cnty = 30 then COUNTY = 3000; else if l_cnty = 31 then COUNTY = 3100; else if l_cnty = 32 then COUNTY = 3200; else if l_cnty = 33 then COUNTY = 3300; else if l_cnty = 34 then COUNTY = 3400; else if l_cnty = 35 then COUNTY = 3500; else if l_cnty = 36 then COUNTY = 3600; else if l_cnty = 37 then COUNTY = 3700; else if l_cnty = 38 then COUNTY = 3800; else if l_cnty = 39 then COUNTY = 3900; else if l_cnty = 40 then COUNTY = 4000; else if l_cnty = 41 then COUNTY = 4100; else if l_cnty = 42 then COUNTY = 4200; else if l_cnty = 43 then COUNTY = 4300; else if l_cnty = 44 then COUNTY = 4400; else if l_cnty = 45 then COUNTY = 4500; else if l_cnty = 46 then COUNTY = 4600; else if l_cnty = 47 then COUNTY = 4700; else if l_cnty = 48 then COUNTY = 4800; else if l_cnty = 49 then COUNTY = 4900; else if l_cnty = 50 then COUNTY = 5000; else if l_cnty = 51 then COUNTY = 5100; else if l_cnty = 52 then COUNTY = 5200; else if l_cnty = 53 then COUNTY = 5300; else if l_cnty = 54 then COUNTY = 5400; else if l_cnty = 55 then COUNTY = 5500; else if l_cnty = 56 then COUNTY = 5600; else if l_cnty = 57 then COUNTY = 5700; else if l_cnty = 58 then COUNTY = 5800; else if l_cnty = 98 then COUNTY = 9800; else if l_cnty = 99 then COUNTY = 9800; if l_cnty = 99 then l_cnty = 98; run; proc sql; create table reentry_03a as select reentry_03.*, cws_offt.cws_off_nm as l_cws_off_nm from reentry_03 left join cws.cws_offt on reentry_03.l_fkcws_offt = cws_offt.identifier; *order by IDENTIFIER, START_DT, tEND_DT, END_TM; quit; quit; data reentry_04; set reentry_03a; AGENCY = 4; run; data reentry_05; set reentry_03 reentry_04; run; proc sql; create table p4_cnty_&sy.q&sq as select L_CNTY, COUNTY, PERIOD_DT, FLAG8, ENTRY, AGENCY, AGE, GENDER_CD, ETHNIC, L_PLC, EXIT_TYPE, REENTRY3, REENTRY6, REENTRY12, REENTRY24, REENTRY36, count(*) as COUNT from reentry_05 group by L_CNTY, COUNTY, PERIOD_DT, FLAG8, ENTRY, AGENCY, AGE, GENDER_CD, ETHNIC, L_PLC, EXIT_TYPE, REENTRY3, REENTRY6, REENTRY12, REENTRY24, REENTRY36; quit; data reentry_06; set reentry_05(drop=L_CNTY); L_CNTY = 00; COUNTY = 0000; run; proc sql; create table p4_state_&sy.q&sq as select L_CNTY, COUNTY, PERIOD_DT, FLAG8, ENTRY, AGENCY, AGE, GENDER_CD, ETHNIC, L_PLC, EXIT_TYPE, REENTRY3, REENTRY6, REENTRY12, REENTRY24, REENTRY36, count(*) as COUNT from reentry_06 group by L_CNTY, COUNTY, PERIOD_DT, FLAG8, ENTRY, AGENCY, AGE, GENDER_CD, ETHNIC, L_PLC, EXIT_TYPE, REENTRY3, REENTRY6, REENTRY12, REENTRY24, REENTRY36; quit; proc append base=county data=p4_cnty_&SY.Q&SQ; proc append base=state data=p4_state_&SY.Q&SQ; %end; %end; %mend c1m3; %c1m3(1,1999,1,2024); data p4_la1; set county state; exit_time=12; run; proc sort data = p4_la1; by L_CNTY COUNTY PERIOD_DT FLAG8 ENTRY; run; proc sql; create table p4_la2 as select L_CNTY, COUNTY, PERIOD_DT, FLAG8, ENTRY, AGENCY, AGE, GENDER_CD, ETHNIC, L_PLC, EXIT_TYPE, EXIT_TIME, REENTRY3, REENTRY6, REENTRY12, REENTRY24, REENTRY36, sum(COUNT) as COUNT from p4_la1 where L_CNTY = 19 group by L_CNTY, COUNTY, PERIOD_DT, FLAG8, ENTRY, AGENCY, AGE, GENDER_CD, ETHNIC, L_PLC, EXIT_TYPE, EXIT_TIME, REENTRY3, REENTRY6, REENTRY12, REENTRY24, REENTRY36; quit; data p4_la3; set p4_la2; COUNTY = 1900; L_CNTY = 19; run; data dvlp.p4(rename=(L_CNTY=CNTY)); set p4_la1 p4_la3; if L_CNTY = 19 and COUNTY = . then COUNTY = 1999; else if COUNTY = . then COUNTY = 9800; if REENTRY12 = 1 then do; COUNT1 = COUNT * REENTRY12; end; else COUNT1 = 0; run; proc sort data = dvlp.p4; by AGENCY ENTRY CNTY PERIOD_DT; run; data test.p4; set dvlp.p4; run; proc sql; create index IDX on dvlp.p4(AGENCY, ENTRY, CNTY, PERIOD_DT); create index IDX on test.p4(AGENCY, ENTRY, CNTY, PERIOD_DT); quit;