********************************************************************************************; ** P1 **; ** 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 1, **; ** **; ** Permanency Performance Area 1: Permanency in 12 months for children entering foster **; ** care **; ** **: ** Measure: Of all children who enter foster care in a 12-month period, what percent **: ** discharged to permanency within 12 months of entering foster care? **; ** **; ** 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 **; ** 2016.08.23 Modified to use office and state ID assignment tables **; ** 2017.12.22 corrected trial home visit coding **; ** added placement status at 12 months **; ** eliminated redundant guardianship exclusion j magruder **; ** 2018.04.02 Corrected referral office exclusion to < 185 days after **; ** episode start **; ** **; ** 2018.08.16 Updated Exit Status **; ** 2022.06.28 changed reference to UCB_CNTY_REM to RSP_CNTY **; ** 2024.01.25 added ICWA and Tribal status codes to allow ICWA status on entry **; ** cohort reports **; ** **; ********************************************************************************************; options nodate nonumber macrogen mprint missing='.'; *** Bring in the records and set initial values ***; 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 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; /* Reunify */ if term_ty_c in (5426, 5438, 5505, 5519, 6530, 6531) then adopt=1; else adopt=0; /* Adopt */ if term_ty_c in (5434, 5516) then guard = 1; else guard=0; /* Guard */ if term_ty_c in (5427, 5433, 5506, 5510, 6805, 6814, 6803, 6812, 6806, 6815, 6804, 6813) then emanc=1; else emanc=0; /* Emancip. */ 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; /*Add ICWA and Tribal status codes 2024.01.25 j magruder*/ select (ICWA_ELGCD) ; when ("Y") ICWA = 1; /*ICWA Eligible*/ when ("N") ICWA = 2; /*ICWA Not Eligible*/ when ("P") ICWA = 3; /*ICWA Status Pending*/ otherwise ICWA = 99; /*ICWA Not asked, unknown, mssing*/ end; select (INDN_STC) ; when ("1212") TRIBAL_STC = 1; /*Tribal Member of at least one tribe*/ when ("1211") TRIBAL_STC = 2; /*Eligible for Tribal Membership but not member of any tribe*/ when ("1214") TRIBAL_STC = 3; /*Neither of above but Pending Verification*/ when ("1210") TRIBAL_STC = 4; /*None of above but Claims Tribal membership*/ when ("1213") TRIBAL_STC = 5; /*none of above and 1+ tribes found ineligible for membership*/ when ("6532") TRIBAL_STC = 6; /*None of above and no tribe has responded after 60 days*/ otherwise TRIBAL_STC = 99; /*No Tribal Membership data available*/ end; temp_id = FKCLIENT_T || compress(put(SPELL,Z2.)); format toh_e_dt tpe_e_dt mmddyy10.; run; proc format; value icwa 1 = "ICWA Eligible" 2 = "ICWA Not Eligible" 3 = "ICWA Status Pending" 99 = "No ICWA Status Data"; value Tribal 1 = "Tribal Member" 2 = "Eligible for Tribal Membership" 3 = "Pending verification of Tribal Status" 4 = "Claims Tribal Membership" 5 = "Not Eligible for Tribal Membership" 6 = "No Tribal Response after 60 days" 99 = "No Tribal Status Data"; 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; /*first placement in each episode*/ set ucb_fc; by temp_id; if first.temp_id; if spell = 1 then entry = 1; else entry = 2; run; *** Remove instance where a child is in care for less than 8 days ***; *moved to initial data step before 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; * 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 order by fkclient_t, temp_id, oh_s_dt, toh_e_dt; ; quit; proc contents data = entries_01 varnum; run; /*find placement status at earlier of episode end or 12 months after episode start*/ /*this only incudes children who have had at least one placement during the year after their first entry into care j magruder 2017.09.01*/ /*find all placements before 12-month point*/ data mo_12_01; set entries_01; by fkclient_t temp_id oh_s_dt toh_e_dt; if oh_s_dt le intnx('year', pe_s_dt,1, 'sameday'); run; data mo_12 (keep = fkclient_t temp_id plc_fclc FED scp_rltc scpr plcg_rnc rename = (plc_fclc = MO12_PLC_fclc FED = MO12_PLC scp_rltc = mo12_scp_rltc scpr = mo12_scpr plcg_rnc = mo12_plcg_rnc)); set mo_12_01; by fkclient_t temp_id oh_s_dt toh_e_dt; if last.temp_id; label plc_fclc = "Placement at exit/12 months"; label fed = "Fed Placement at exit/12 months"; label scpr = "Caretaker at exit/12 months"; label plcg_rnc = "End reason exit/12 mos place."; run; /* find assignment county and office using file dwh.UCB_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 REMREAS reunify adopt guard emanc other incare CNTY FLAG8 ENTRY AGENCY AGE GENDER_CD ETHNIC FED PREDOM_FT EXIT_TYPE PLCG_RNC ICWA TRIBAL_STC IDN_TRBCx f_plc f_scpr); /*placement change reason added 2017.12.15*/ set entries_01; 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; /* 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 unless open to that office for > 185 days*/ /* 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 */ /* Modified for 2018Q1 to take 185 days from episode start to office end and to apply to Los Angeles hotline office - which also provides CSEC services*/ data entries_04; set entries_03; by fkclient_t temp_id office_s_dt; /*if Los Angeles hotline followed by other office, delete*/ *if cws_off_no in ("S1278") and last.temp_id = 0 then delete; if cws_off_no in ("S1278") and last.temp_id = 0 and office_e_dt - pe_s_dt < 185 and office_e_dt ne '31DEC3000'D then delete; /*if referral status less than 185 days followed by regular assignment, then delete may result in change of county responsible*/ *if type = "R" and last.temp_id = 0 and office_e_dt - office_s_dt < 185 and office_e_dt ne . then delete; if type = "R" and last.temp_id = 0 and office_e_dt - pe_s_dt < 185 and office_e_dt ne '31DEC3000'D then delete; /*if opened at Los Angeles MART office as referral or case and then transferred to other office (or county) within 185 days, delete*/ *if cws_off_no = "S0224" and last.temp_id = 0 and office_e_dt - office_s_dt < 185 and office_e_dt ne . then delete; if cws_off_no = "S0224" and last.temp_id = 0 and office_e_dt - pe_s_dt < 185 and office_e_dt ne '31DEC3000'D 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 in episode*/ data entries_04_first (drop = oh_s_dt fkchld_clt plcg_rnc 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; /*last office assignment in episode - placement change reason added 2017.12.15*/ 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 plcg_rnc/*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 plcg_rnc = l_plcg_rnc)); 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' plcg_rnc = 'Last Placement Change Reason'; run; /*find county, office assignment at earlier of exit or 12 months after entry added 2018.05.15*/ data entries_04_12mo_a; set entries_04; by fkclient_t temp_id office_s_dt; if office_s_dt le intnx('year', pe_s_dt,1, 'sameday'); run; data entries_04_12mo (keep = fkclient_t temp_id cnty_spfcd cws_off_no fkcws_offt office_s_dt rename = (cnty_spfcd = mo12_cnty_spfcd cws_off_no = mo12_cws_off_no fkcws_offt = mo12_fkcws_offt office_s_dt = mo12_office_s_dt)); set entries_04_12mo_a; by fkclient_t temp_id office_s_dt; if last.temp_id; label cnty_spfcd = "County at exit/12 months"; label cws_off_no = "Office at exit/12 months"; label fkcws_offt = "Office ID at exit/12 months"; label office_s_dt = "Exit/12 month pffoce Start 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 mo_12 entries_04_12mo; 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.); if mo12_cnty_spfcd = ' ' then mo12_cnty_spfcd = f_cnty_spfcd;/*if no 12 month data, then assume still 1st county*/ if mo12_plc = . then mo12_plc = 99; if mo12_scpr = . then mo12_scpr = 99; run; proc sort data = entries; by fkclient_t temp_id; 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; /*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. ; 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 fkclient_t temp_id pe_s_dt oh_s_dt toh_e_dt; run; /* Find first placement episode in study year */ data base2; set base; by fkclient_t temp_id pe_s_dt oh_s_dt toh_e_dt; if first.fkclient_t; run; data exits1 ; set base2; if pe_e_dt ne . then do; /* Adjust for trial home visit - Trial Home Visit that lasted longer than 30 days, and the date of placement in the current placement setting plus 30 days is less 12 months from the date of latest removal from foster care. (1) If the child is grouped as being in care for more than 12 months look at THV. (2) Then, if the child has a OOHP episode ending for THV then see if it is between 0 and 335 days from the start (this plus 30 days has to be less than one year). (3) Then, if the THV lasted for 30 days or longer it can be used as the end date. Note - this step is looking only at last placement in closed placement episode. */; /* 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. Corrected 2017.12.15 to use L_PLCG_RNC and toh_e_dt j magruder*/ if L_PLCG_RNC=1440 and pe_e_dt ne . and pe_e_dt - toh_e_dt > 30 then pe_e_dt_m = toh_e_dt+ 30; else pe_e_dt_m = pe_e_dt; format pe_e_dt_m mmddyy10.; /* 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; proc sql; create table exits2 as select x.*, y.cws_off_no, y.cws_off_nm from exits1 as x left join cws.cws_offt as y on x.l_fkcws_offt = y.identifier order by fkclient_t, oh_s_dt; quit; data exits; set exits2; F_CNTY = input(f_cnty_spfcd, best2.); if 1 <= F_CNTY <= 58 then CNTY = F_CNTY; else CNTY = 98; if cnty = 19 then do; if f_cws_off_no in("S3253") then COUNTY = 1901; /*Belvedere*/ else if f_cws_off_no in("S1277") then COUNTY = 1902; /*Compton-Carson*/ else if f_cws_off_no in("S1280") then COUNTY = 1903; /*El Monte*/ else if f_cws_off_no in("S1254") then COUNTY = 1904; /*Glendora*/ else if f_cws_off_no in("S2218") then COUNTY = 1905; /* Hawthorne old Wateridge South*/ else if f_cws_off_no in("S8234") then COUNTY = 1906; /*Lancaster*/ else if f_cws_off_no in("S3239") then COUNTY = 1907; /*Metro North*/ else if f_cws_off_no in("S8236") then COUNTY = 1908; /*Palmdale*/ else if f_cws_off_no in("S5252") then COUNTY = 1909; /*Pasadena*/ else if f_cws_off_no in("S1255") then COUNTY = 1910; /*Pomona*/ else if f_cws_off_no in("S8251") then COUNTY = 1911; /*Santa Clarita*/ else if f_cws_off_no in("S4261") then COUNTY = 1912; /*Santa Fe Springs*/ else if f_cws_off_no in("S7207") then COUNTY = 1913; /*South County*/ else if f_cws_off_no in("S2213") then COUNTY = 1914; /*Torrance*/ else if f_cws_off_no in("S5211") then COUNTY = 1915; /*Van Nuys*/ else if f_cws_off_no in("S6219") then COUNTY = 1916; /*Vermont Corridor*/ else if f_cws_off_no in("S2217") then COUNTY = 1917; /*Wateridge old Wateridge North*/ *else if f_cws_off_no in("S2218") then COUNTY = 1917; /*Wateridge South*/ else if f_cws_off_no in("S6260") then COUNTY = 1918; /*West Los Angeles*/ else if f_cws_off_no in("S5212") then COUNTY = 1919; /*West SF Valley*/ else if f_cws_off_no in("S0216") then COUNTY = 1920; /*Adoptions*/ else if f_cws_off_no in("S0222") then COUNTY = 1924; /*Medical & ASFA*/ else if f_cws_off_no in("S1251") then COUNTY = 1931; /*American Indian*/ else if f_cws_off_no in("S1250") then COUNTY = 1932; /*Asian Pacific Project*/ else if f_cws_off_no in("S0249") then COUNTY = 1933; /*Deaf Services*/ else if f_cws_off_no in("S0237") then COUNTY = 1934; /*Pomona Family First*/ else if f_cws_off_no in("S1288") then COUNTY = 1935; /*CSEC*/ else if /*f_cws_off_no in("S1278") and */ f_fkcws_offt = "1iyrzAg196" then COUNTY = 1940; /*ROUC*/ else if f_cws_off_no in("S0224") then COUNTY = 1941; /*MART*/ else if /*f_cws_off_no in("S1278") and */ f_fkcws_offt in("PHmifFE197","QxMGV6D197") then COUNTY = 1942; /*ER Command/Hotline*/ /* else if f_cws_off_no in("S1278") then COUNTY = 1940; */ /*ERCP*/ /* else if f_cws_off_no in("S0224") then COUNTY = 1941; */ /*MART*/ /* else if f_cws_off_no in("S1278") then COUNTY = 1942; */ /*Hotline*/ else if f_cws_off_no in("S1286") then COUNTY = 1943; /*Juvenile Court Services*/ else if f_cws_off_no in("S0246", "S0247", "S9056", "S4261", "S0394") then COUNTY = 1945; /*Administration*/ else if f_cws_off_no in("S0299") then COUNTY = 1950; /*Probation*/ else if f_cws_off_no in("S9072") then COUNTY = 1960; /*KinGAP*/ else COUNTY = 1999; /*missing*/ end; else if cnty = 01 then COUNTY = 0100; else if cnty = 02 then COUNTY = 0200; else if cnty = 03 then COUNTY = 0300; else if cnty = 04 then COUNTY = 0400; else if cnty = 05 then COUNTY = 0500; else if cnty = 06 then COUNTY = 0600; else if cnty = 07 then COUNTY = 0700; else if cnty = 08 then COUNTY = 0800; else if cnty = 09 then COUNTY = 0900; else if cnty = 10 then COUNTY = 1000; else if cnty = 11 then COUNTY = 1100; else if cnty = 12 then COUNTY = 1200; else if cnty = 13 then COUNTY = 1300; else if cnty = 14 then COUNTY = 1400; else if cnty = 15 then COUNTY = 1500; else if cnty = 16 then COUNTY = 1600; else if cnty = 17 then COUNTY = 1700; else if cnty = 18 then COUNTY = 1800; *else if cnty = 19 then COUNTY = 1900; else if cnty = 20 then COUNTY = 2000; else if cnty = 21 then COUNTY = 2100; else if cnty = 22 then COUNTY = 2200; else if cnty = 23 then COUNTY = 2300; else if cnty = 24 then COUNTY = 2400; else if cnty = 25 then COUNTY = 2500; else if cnty = 26 then COUNTY = 2600; else if cnty = 27 then COUNTY = 2700; else if cnty = 28 then COUNTY = 2800; else if cnty = 29 then COUNTY = 2900; else if cnty = 30 then COUNTY = 3000; else if cnty = 31 then COUNTY = 3100; else if cnty = 32 then COUNTY = 3200; else if cnty = 33 then COUNTY = 3300; else if cnty = 34 then COUNTY = 3400; else if cnty = 35 then COUNTY = 3500; else if cnty = 36 then COUNTY = 3600; else if cnty = 37 then COUNTY = 3700; else if cnty = 38 then COUNTY = 3800; else if cnty = 39 then COUNTY = 3900; else if cnty = 40 then COUNTY = 4000; else if cnty = 41 then COUNTY = 4100; else if cnty = 42 then COUNTY = 4200; else if cnty = 43 then COUNTY = 4300; else if cnty = 44 then COUNTY = 4400; else if cnty = 45 then COUNTY = 4500; else if cnty = 46 then COUNTY = 4600; else if cnty = 47 then COUNTY = 4700; else if cnty = 48 then COUNTY = 4800; else if cnty = 49 then COUNTY = 4900; else if cnty = 50 then COUNTY = 5000; else if cnty = 51 then COUNTY = 5100; else if cnty = 52 then COUNTY = 5200; else if cnty = 53 then COUNTY = 5300; else if cnty = 54 then COUNTY = 5400; else if cnty = 55 then COUNTY = 5500; else if cnty = 56 then COUNTY = 5600; else if cnty = 57 then COUNTY = 5700; else if cnty = 58 then COUNTY = 5800; else if cnty = 98 then COUNTY = 9800; run; /* Measure P3 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 */ data reentry_01; set exits; where exit_type_p in(1,3) and pe_e_dt_m < age_18; 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.*, 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.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 reentries; set reentry_02; by fkclient_t reentry_oh_s_dt; if first.fkclient_t; two_year = intnx('year',pe_e_dt_m,2, 'sameday'); format two_year mmddyy10.; label two_year = "Two Year Cut Off Date"; if (reentry_pe_s_dt ne . and reentry_pe_s_dt le two_year) then p3_reentry = 1; else p3_reentry = 0; label p3_reentry = "Reentered Within 2 Years"; 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"; run; ***** Produce summary reports by County *****; ***** Need to run once for the state and once for the counties *****; %macro summs(lvl); *** Get an entry count ***; proc summary data=exits nway; class %if &lvl=cnty %then %do; cnty COUNTY %end; entry flag8 /* undup ep_1 */ agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place ICWA TRIBAL_STC; output out=entry_counta_&lvl.; run; proc summary data=exits nway; class %if &lvl=cnty %then %do; cnty COUNTY %end; entry flag8 /* undup ep_1 */ age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place ICWA TRIBAL_STC; output out=entry_countb_&lvl.; run; data entry_count_&lvl.; set entry_counta_&lvl. entry_countb_&lvl.(in=b); if b then agency=4; %if &lvl=state %then %do; cnty = 0; COUNTY = 0000; %end; run; *** Run for the exit times *****; proc summary data=exits nway; class %if &lvl=cnty %then %do; cnty COUNTY %end; entry flag8 /* undup ep_1 */ agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place exit_type exit_time ICWA TRIBAL_STC; ; var reunify adopt guard emanc other incare ; output out=exits_summ sum=; run; *** We want to have a total for AGENCY as well, so there needs to be a second series of summaries excluding AGENCY ***; proc summary data=exits nway; class %if &lvl=cnty %then %do; cnty COUNTY %end; entry flag8 /* undup ep_1 */ age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place exit_type exit_time ICWA TRIBAL_STC ; var reunify adopt guard emanc other incare ; output out=aexits_summ sum=; run; proc sort data=exits_summ(rename=(_freq_=count )); by %if &lvl=cnty %then %do; cnty COUNTY %end; entry flag8 /* undup ep_1 */ agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place exit_type exit_time ICWA TRIBAL_STC ; run; proc sort data=aexits_summ(rename=(_freq_=count )); by %if &lvl=cnty %then %do; cnty COUNTY %end; entry flag8 /* undup ep_1 */ age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place exit_type exit_time ICWA TRIBAL_STC ; run; data column_&lvl.; set exits_summ aexits_summ (in=a); %if &lvl=state %then %do; cnty = 0; COUNTY = 0000; %end; if a then agency=4 ; run; proc sort data=column_&lvl.; by cnty COUNTY entry flag8 /* undup ep_1 */ agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place exit_type exit_time ICWA TRIBAL_STC ; run; %mend summs; %summs(state); %summs(cnty); data columnsa; set column_state column_cnty ; run; proc sort data=columnsa noduplicates; by _all_; run; data ec (drop=_type_ rename=(_freq_=denom)) ; set entry_count_state entry_count_cnty ; exit_type=0; count=0; run; proc sort data=ec ; by cnty county agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place entry flag8 /* undup ep_1 */ ICWA TRIBAL_STC; run; proc sort data=columnsa; by cnty county agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place entry flag8 /* undup ep_1 */ ICWA TRIBAL_STC; run; data columnsb; set columnsa ec ; by cnty county agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place entry flag8 /* undup ep_1 */ ICWA TRIBAL_STC; run; proc sort data=columnsb; by cnty county agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place entry flag8 /* undup ep_1 */ exit_time ICWA TRIBAL_STC; proc univariate data=columnsb noprint; var exit_time; output out=howmany max=mymax ; run; data _null_; set howmany; call symput('howmany',mymax); run; *** Sean wants to be able to use the EXIT_TYPE variable as a row variable. The following code summarizes the data by all of the dimensions and places the value into a variable called COUNT2. ***; %macro innerpeace(timeframe); proc summary data=columnsb nway ; where exit_type ne 7 and exit_time le &timeframe. ; class cnty county entry flag8 /* undup ep_1 */ agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place ICWA TRIBAL_STC; var reunify adopt guard emanc other denom; output out=incare sum=; run; proc sort data=incare; by cnty county entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place ICWA TRIBAL_STC /* undup ep_1 */ ; run; data f_incare; set %if &timeframe. gt 3 %then %do; f_incare %end; incare(in=b); if b then do; if reunify=. then reunify=0; incare=denom - sum(reunify, adopt, guard, emanc, other); exit_time=&timeframe.; exit_type=7; count2=incare; end; drop reunify adopt guard emanc other denom; run; proc summary data=columnsb nway ; where exit_type in (1,2,3,4,5) and exit_time le &timeframe. ; class cnty county entry flag8 /* undup ep_1 */ agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place exit_type ICWA TRIBAL_STC; var reunify adopt guard emanc other ; output out=in2care sum=; run; proc sort data=in2care; by cnty county entry flag8 /* undup ep_1 */ agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place exit_type ICWA TRIBAL_STC; run; data f2_incare; set %if &timeframe. gt 3 %then %do; f2_incare %end; in2care(in=b); if b then do; exit_time=&timeframe.; if exit_type=1 then count2=reunify; if exit_type=2 then count2=adopt ; /* Sean */ if exit_type=3 then count2=guard ; if exit_type=4 then count2=emanc ; if exit_type=5 then count2=other ; end; keep cnty county entry flag8 /* undup ep_1 */ agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place exit_type exit_time count2 ICWA TRIBAL_STC; run; %mend innerpeace; %innerpeace(3); %if &howmany. ge 6 %then %do; %innerpeace(6); %end; %if &howmany. ge 12 %then %do; %innerpeace(12); %end; %if &howmany. ge 18 %then %do; %innerpeace(18); %end; %if &howmany. ge 24 %then %do; %innerpeace(24); %end; %if &howmany. ge 30 %then %do; %innerpeace(30); %end; %if &howmany. ge 36 %then %do; %innerpeace(36); %end; %if &howmany. ge 48 %then %do; %innerpeace(48); %end; %if &howmany. ge 60 %then %do; %innerpeace(60); %end; %if &howmany. ge 72 %then %do; %innerpeace(72); %end; %if &howmany. ge 84 %then %do; %innerpeace(84); %end; %if &howmany. ge 96 %then %do; %innerpeace(96); %end; %if &howmany. ge 108 %then %do; %innerpeace(108); %end; %if &howmany. ge 120 %then %do; %innerpeace(120); %end; %if &howmany. ge 132 %then %do; %innerpeace(132); %end; %if &howmany. ge 144 %then %do; %innerpeace(144); %end; %if &howmany. ge 156 %then %do; %innerpeace(156); %end; %innerpeace(999); data et1_5 et7; set columnsb; if exit_type=7 then output et7; else output et1_5; run; proc sort data=et1_5 nodupkey ; by cnty county entry flag8 /* undup ep_1 */ agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place exit_type exit_time ICWA TRIBAL_STC; run; proc sort data=f2_incare nodupkey ; by cnty county entry flag8 /* undup ep_1 */ agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place exit_type exit_time ICWA TRIBAL_STC; run; data et1_5b; merge et1_5(in=a) f2_incare(in=b); by cnty county entry flag8 /* undup ep_1 */ agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr mo12_plc mo12_scpr remreas place exit_type exit_time ICWA TRIBAL_STC; run; data columns; set et1_5b f_incare; year = year(&spdate.); qtr = qtr(&spdate.); period_dt=yyq(year,qtr) ; if reunify in (0..) and adopt in (0,.) and guard in (0,.) and emanc in (0,.) and other in (0,.) and count in (0,.) and count2 in (0,.) and denom in (0,.) then delete; drop _type_ ; run; %if &empty.=0 %then %do; data _null_; set sashelp.vtable(keep=libname memname); where upcase(libname)="TEMP" ; if upcase(memname)="P1" then call symput('empty',1); run; %end; data temp.p1(drop = year qtr _FREQ_) ; length agency age ethnic gender_cd cnty county flag8 remreas place /* f_plc l_plc mo12_plc */ denom entry exit_type exit_time count reunify adopt guard emanc other incare f_scpr l_scpr mo12_scpr period_dt count2 ICWA TRIBAL_STC/* undup ep_1 */ 4.; set %if &empty.=1 %then %do; temp.p1 %end; columns ; %if &empty.=1 %then %do; label agency='Agency: CW,Prob.,Other,Total' age='Age at start of period' ethnic='Childs ethnicity' gender_cd='Childs gender' period_dt='The start of the period' cnty='County Responsible' county='County/Office Responsible' flag8='In care for 8 days or more (1), or less (2)' remreas='Reason for Removal from home' place='Last Placement Counter' f_plc='First Placement Type' l_plc='Last Placement Type' f_scpr='First SCP Relationship' l_scpr='Last SCP Relationship' mo12_plc='Exit or 12 mo Placement Type' mo12_scpr='Exit or 12 mo SCP Relationship' denom='Base count of entries' entry='1=first entry, 2=all other entries' exit_type='Type of Exit from Care' exit_time='Timing of exit from care' count='Count of Children - use with exit_type' reunify='Count of children reunified' adopt='Count of children adopted' guard='Count of children in guardian' emanc='Count of children emancipated' other='Count of other exits' incare='Count of children still in care' ICWA='ICWA Status' TRIBAL_STC="Indian Tribal Status" ; %end; run; %end; %end; %mend c1m3; %c1m3(3,1998,4,2024); proc sql; create table P1_1 as select AGENCY, AGE, ETHNIC, GENDER_CD, PERIOD_DT, CNTY, COUNTY, FLAG8, REMREAS, PLACE, F_PLC, L_PLC, F_SCPR, L_SCPR, mo12_plc, mo12_scpr ,ENTRY, EXIT_TYPE, EXIT_TIME, DENOM, ICWA, TRIBAL_STC, COUNT2 from temp.p1; create table P1_1a as select AGENCY, AGE, ETHNIC, GENDER_CD, PERIOD_DT, CNTY, COUNTY, FLAG8, REMREAS, PLACE, F_PLC, L_PLC, F_SCPR, L_SCPR, mo12_plc, mo12_scpr, ENTRY, EXIT_TYPE, EXIT_TIME, DENOM, ICWA, TRIBAL_STC, COUNT2 from temp.p1 where CNTY = 19; quit; data P1_1b; set P1_1a(drop=COUNTY); COUNTY = 1900; run; proc sql; create table P1_1c as select AGENCY, AGE, ETHNIC, GENDER_CD, PERIOD_DT, CNTY, COUNTY, FLAG8, REMREAS, PLACE, F_PLC, L_PLC, F_SCPR, L_SCPR, mo12_plc, mo12_scpr, ENTRY, EXIT_TYPE, EXIT_TIME, ICWA, TRIBAL_STC, sum(COUNT2) as COUNT2, sum(DENOM) as DENOM from P1_1b group by AGENCY, AGE, ETHNIC, GENDER_CD, PERIOD_DT, CNTY, COUNTY, FLAG8, REMREAS, PLACE, F_PLC, L_PLC, F_SCPR, L_SCPR, mo12_plc, mo12_scpr, ENTRY, EXIT_TYPE, EXIT_TIME,ICWA, TRIBAL_STC; quit; data Dvlp.P1; set P1_1 P1_1c; if CNTY = 19 and COUNTY = . then COUNTY = 1999; else if COUNTY = . then COUNTY = 9800; if exit_type in (1,2,3) then permanency = 1; else permanency = 0; count1 = permanency * Count2; run; proc sort data = Dvlp.P1; by FLAG8 ENTRY AGENCY EXIT_TIME PERIOD_DT; run; proc sql; create index AT1 on dvlp.P1(FLAG8,ENTRY,AGENCY,EXIT_TIME,PERIOD_DT); create index AT2 on dvlp.P1(FLAG8,ENTRY,AGENCY,PERIOD_DT); create index AT3 on dvlp.P1(EXIT_TYPE,EXIT_TIME); quit; data test.P1; set dvlp.P1; run; proc sql; create index AT1 on test.P1(FLAG8,ENTRY,AGENCY,EXIT_TIME,PERIOD_DT); create index AT2 on test.P1(FLAG8,ENTRY,AGENCY,PERIOD_DT); create index AT3 on test.P1(EXIT_TYPE,EXIT_TIME); quit;