*****************************************************************************************; ** **; ** Copyright (c) 2007 Center for Social Services Research, **; ** University of California at Berkeley. All rights reserved. **; ** **; ** This program produces reports for Permanency Composite 1, Component A, Measure 3. **; ** **; ** Permanency Composite 1: Component A: Timeliness of Reunification **; ** Measure 3: Of all children entering foster care for the first time in the second **; ** 6 months of FY 2003 who remained in foster care for 8 days or longer, what percent **; ** were reunified in less than 12 months of the date of entry into 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). **; ** **; ** 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.09.25 Fix Sorting order **; ** 2021.11.28 Change from 6 month to 12 month interval **; ** **; *****************************************************************************************; 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=&cutoff.; 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(ucb_cnty_rem,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; *** 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('1417') FED = 10; /* Group */ when('1418') 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) 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) 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) 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) 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) 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 ) 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=&cutoff. ; 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; *** Remove instance where a child is in care for less than 8 days ***; *** Remove instances where Guardian is the initial placement ***; 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 * from entry where plcep_id in (select plcep_id from no_guards) ; quit; proc sql; create table entries 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 where a.fkclient_t=b.fkclient_t and a.plcep_id=b.plcep_id ; quit; ** 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 *****; data _null_; length sm $5 ; if &qtr=1 then do; sm='01JAN'; pm='01JUL'; py=&year.-1; end; if &qtr=2 then do; sm='01APR'; pm='01OCT'; py=&year.-1; end; if &qtr=3 then do; sm='01JUL'; pm='01JAN'; py=&year. ; end; if &qtr=4 then do; sm='01OCT'; pm='01APR'; py=&year. ; end; /* revise from 6-month interval to 12-month interval - pm and py become irrelevant*/ spdate=compress("'"||sm||%eval(&year-1)||"'d"); /*12 month version*/ *spdate=compress("'"||pm||py||"'d"); /*6 month version*/ 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; *** First entries in 6 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 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 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; data base2; set base1; by fkclient_t; if first.fkclient_t; run; data exits ; 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 tahn 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. ***; /*replace following with correct calculation of time-in-care, see comments below*/ /* if PLCG_RNC=1440 then do; if oh_e_dt ne . then thvmon = oh_e_dt-pe_s_dt; if oh_e_dt ne . then thvlngth = pe_e_dt - oh_e_dt ; if thvmon ne . and (0 le thvmon le 335) and thvlngth ge 30 then lagthv=int( (thvmon + 30)/30.4375 ); end; if lagthv ne . then lagmon=lagthv; else lagmon=int( (pe_e_dt-pe_s_dt)/30.4375 ); if 0 le lagmon le 3 then exit_time=3; else if 4 le lagmon le 6 then exit_time=6; else if 7 le lagmon le 12 then exit_time=12; else if 13 le lagmon le 18 then exit_time=18; else if 19 le lagmon le 24 then exit_time=24; else if 25 le lagmon le 30 then exit_time=30; else if 31 le lagmon le 36 then exit_time=36; else if 37 le lagmon le 48 then exit_time=48; else if 49 le lagmon le 60 then exit_time=60; else if 61 le lagmon le 72 then exit_time=72; else if 73 le lagmon le 84 then exit_time=84; else if 85 le lagmon le 96 then exit_time=96; else if 97 le lagmon le 108 then exit_time=108; else if 109 le lagmon le 120 then exit_time=120; else if 121 le lagmon le 132 then exit_time=132; else if 133 le lagmon le 144 then exit_time=144; else if 145 le lagmon le 156 then exit_time=156; */ /*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.; /* 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; 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 %end; entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place; output out=entry_counta_&lvl.; run; proc summary data=exits nway; class %if &lvl=cnty %then %do; cnty %end; entry flag8 age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place ; 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; %end; run; *** Run for the exit times *****; proc summary data=exits nway; class %if &lvl=cnty %then %do; cnty %end; entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place exit_type exit_time ; ; 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 %end; entry flag8 age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place exit_type exit_time ; 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 %end; entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place exit_type exit_time ; run; proc sort data=aexits_summ(rename=(_freq_=count )); by %if &lvl=cnty %then %do; cnty %end; entry flag8 age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place exit_type exit_time ; run; data column_&lvl.; set exits_summ aexits_summ (in=a); %if &lvl=state %then %do; cnty = 0; %end; if a then agency=4 ; run; proc sort data=column_&lvl.; by cnty entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place exit_type exit_time ; 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 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place entry flag8 ; run; proc sort data=columnsa; by cnty agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place entry flag8 ; run; data columnsb; set columnsa ec ; by cnty agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place entry flag8 ; run; proc sort data=columnsb; by cnty agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place entry flag8 exit_time ; 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 entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place ; var reunify adopt guard emanc other denom; output out=incare sum=; run; proc sort data=incare; by cnty entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place ; 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 entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place exit_type; var reunify adopt guard emanc other ; output out=in2care sum=; run; proc sort data=in2care; by cnty entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place exit_type; 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 ; if exit_type=3 then count2=guard ; if exit_type=4 then count2=emanc ; if exit_type=5 then count2=other ; end; keep cnty entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place exit_type exit_time count2; 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 entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place exit_type exit_time; run; proc sort data=f2_incare nodupkey ; by cnty entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place exit_type exit_time; run; data et1_5b; merge et1_5(in=a) f2_incare(in=b); by cnty entry flag8 agency age ethnic gender_cd f_plc l_plc f_scpr l_scpr remreas place exit_type exit_time; 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)="DVLP" ; if upcase(memname)="C1M3" then call symput('empty',1); run; %end; data c1m3(drop = year qtr _FREQ_) ; length agency age ethnic gender_cd cnty flag8 remreas place f_plc l_plc denom entry exit_type exit_time count reunify adopt guard emanc other incare f_scpr l_scpr period_dt count2 4.; set %if &empty.=1 %then %do; c1m3 %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' 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' 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' ; %end; run; %end; %end; %mend c1m3; *NEED TO CHANGE; %c1m3(3,1998,4,2024); proc sort data = c1m3; by FLAG8 ENTRY AGENCY EXIT_TIME PERIOD_DT; run; data stability1(drop=exit_type); set c1m3(keep=age agency cnty count2 entry ethnic exit_time exit_type f_plc f_scpr gender_cd l_plc l_scpr period_dt place remreas); where exit_type = 7; run; data dvlp.stability; set stability1; rename EXIT_TIME = INCARE_TIME; run; proc sort data = dvlp.stability; by INCARE_TIME AGENCY ENTRY PLACE PERIOD_DT; run; data test.stability; set dvlp.stability; run; proc sql; create index AT on dvlp.stability(INCARE_TIME,AGENCY,ENTRY,PLACE,PERIOD_DT); create index AT on test.stability(INCARE_TIME,AGENCY,ENTRY,PLACE,PERIOD_DT); quit;