*UCB_FC_AFCARS_ndlg.sas;

*2023.12.19 j magruder;

/* Copyright (c) 2003 Center for Social Services Research,
 * University of California at Berkeley. All rights reserved.
 *
 * Programmer: T. Shaw
 *
 * UCB_FC_AFCARS is an analysis database, based on UCB_FC, designed to mirror 
 * the data submissions to the federal Adoption and Foster Care Analysis and 
 * Reporting System (AFCARS).
 *
 * 2007.08.07 Took ucb_fc9 - ucb_fc11 program parts out during the WebEX meeting
 *            with Terry 
 * 2012.04.18 Added new plc_athc = 6539 to positively identify Probate non-dependent 
 *            legal guardian placements
 * 2012.06.07 currect placement counter adjuster j magruder;
 * 2021.12.17 correct placement counter for runaways j magruder;
 * 2022.01.25 further placement counter adjustment to deal with runaways having
              been added to file j magruder;
 * 2024.01.l0 revised to use legal authority at end of placement. To usually use
 			  pmt_order for sorting j magruder;
 */

options macrogen mprint symbolgen;

proc format;
   value vcnty
      00='California'           01='Alameda'           02='Alpine'
      03='Amador'               04='Butte'             05='Calaveras'
      06='Colusa'               07='Contra Costa'      08='Del Norte'
      09='El Dorado'            10='Fresno'            11='Glenn'
      12='Humboldt'             13='Imperial'          14='Inyo'
      15='Kern'                 16='Kings'             17='Lake'
      18='Lassen'               19='Los Angeles'       20='Madera'
      21='Marin'                22='Mariposa'          23='Mendocino'
      24='Merced'               25='Modoc'             26='Mono'
      27='Monterey'             28='Napa'              29='Nevada'
      30='Orange'               31='Placer'            32='Plumas'
      33='Riverside'            34='Sacramento'        35='San Benito'
      36='San Bernardino'       37='San Diego'         38='San Francisco'
      39='San Joaquin'          40='San Luis Obispo'   41='San Mateo'
      42='Santa Barbara'        43='Santa Clara'       44='Santa Cruz'
      45='Shasta'               46='Sierra'            47='Siskiyou'
      48='Solano'               49='Sonoma'            50='Stanislaus'
      51='Sutter'               52='Tehama'            53='Trinity'
      54='Tulare'               55='Tuolumne'          56='Ventura'
      57='Yolo'                 58='Yuba'              59='Missing'
      99='Missing';

   value vagegrp
       1='<1 yr'                2='1-2 yrs'             3='3-5 yrs'
       4='6-10 yrs'             5='11-15 yrs'           6='16-17 yrs'
       7='Missing'              .='Total';

   value vrace
       1='Black'                2='White'               3='Hispanic'
           4='Asian/Oth.'           5='Nat Amer'            6='Missing'
           7='Totals';

   value $ethnic
       '1'='Black'                '2'='White'               '3'='Hispanic'
           '4'='Asian/Oth.'           '5'='Nat Amer'            '6'='Missing'
           ' '='Total'
           ;

   value vgender
       2='Male'                 1='Female'              3='Unknown/Missing'
           4='Totals';

   value $gender
       ' '='Total'
           'F'='Female'
           'M'='Male'
           'U'='Missing';
   value return
           1='Reentry Within 12 Months'
           2='No Reentry Within 12 Months';

   value agency
       .='Total'
           1='Child Welfare'
           2='Probation'
       3='Other';

   value vagency
      1='Child Welfare'
          2='Probation'
          3='Other';

   value vt 1='Char'  2='Num'  .='  ';

   picture ntemp (round) low-<0=' '     1-<5='000,009' 5-high='000,009';
   picture dtemp (round) low-<0='009.9%'     0.0-high='009.9%';

run;

***** The UCB_FC file is a composite file designed for ease of analysis.  Some of the
      variable names have been renamed.

      PLC_EPST.THIRD_ID = PLCEP_ID
      PLC_EPST.REMOVAL_DT = PE_S_DT
      PLC_EPST.PLEPS_ENDT = PE_E_DT
      O_HM_PLT.IDENTIFIER = OHMPL_ID
      O_HM_PLT.START_DT = OH_S_DT
      O_HM_PLT.END_DT = OH_E_DT

      For ease of sorting create a temporary end date for both the placement
      episode end date and the out of home placement end date.
*****;

proc sort data=dwh.ucb_fc out=ucb_fc;  
	*by fkclient_t SPELL pe_s_dt;
	by fkclient_t pmt_order; /*2024.01.02 j magruder*/
	run;

data ucb_fc ;
   set ucb_fc ;
   *by fkclient_t SPELL pe_s_dt;
   by fkclient_t pmt_order;

   if agy_rspc in (35, 6133, 6134, 5604) then delete;
	/*none of the potentially deleted records are in UCB_FC so no records deleted*/
   if pe_e_dt=. then tpe_e_dt='01jan3000'd;
   else tpe_e_dt=pe_e_dt;
   if oh_e_dt=. then toh_e_dt='01jan3000'd;
   else toh_e_dt=oh_e_dt;

   *** remove episodes of 1 day or less ***;
   if tpe_e_dt-pe_s_dt in (0,1) then delete; /*no such records in UCB_FC*/
   *** remove episodes that do not have a placement facility listed ***;
   if plc_fclc=. then delete; /*no such records in UCB_FC*/

run;

***** Identify guardian placements and move them into a separate file.
      We want to separate out non-dependent guardian placements because
      they are not actively receiving child welfare services, although their
      placement is being paid for out of AFDC-FC funds.
*****;

data guards;
  set ucb_fc;
  where plc_fclc=5411;
run;

***** Bring in the legal authority codes for the guardian placements *****;

proc sql;
   create table guard_lglath as
   select a.*,
          b.plc_athc,
                  b.effctv_dt
   from guards as a left join cws.lg_autht as b
   on a.plcep_id=b.fkplc_eps0 and
      a.fkclient_t=b.fkplc_epst and
         /*b.effctv_dt le (a.oh_s_dt + 3)*/

	  (b.effctv_dt < toh_e_dt or (b.effctv_dt = toh_e_dt and toh_e_dt = oh_s_dt))
	  /*CDSS code note: 4-2017: As approved by ACF, identify last guardianship status 
	    during the placement instead of status at the beginning of placement
	  	CCWIP change 2023.12.19 j magruder*/
	  /*note: status on day before end of placement unless one-day placement*/
      ;

      ;
quit;

*** Unduplicate guardian placements by selecting the most recent legal authority
    (The legal authority operating on or right after the start of the out of home placement).
	(changed to The legal authority operating on the day before the end of the out of hone 
	 placement 2023.12.19 j magruder)
***;

proc sort data=guard_lglath;
  *by fkclient_t pe_s_dt oh_s_dt ohmpl_id effctv_dt;
  by fkclient_t pmt_order effctv_dt; /*2024.01.02 j magruder*/
run;

data guard_lglath2;
   set guard_lglath;
   *by fkclient_t pe_s_dt oh_s_dt ohmpl_id ;
   *if last.ohmpl_id;
    by fkclient_t pmt_order effctv_dt;
	if last.pmt_order;
run;

*** For all placements that are dependent guardians -- Legal Authority
    (1409 thru 1413) -- recode as being in kin or foster homes,
    depending on substitute care relationship of relative or non-relative.

   Legal Authority Code
    1409 = WIC 300 a,b,c,d,f,g,I, or j
    1410 = WIC 300e
    1411 = WIC 300h
    1412 = WIC 601
    1413 = WIC 602
	6536 = NMD - WIC 300
	6537 = NMD - WIC 450
	6538 = NMD - WIC 602	

   SCP Relationship to Child
    1636 = Nonrelative Guardian
    1637 = Nonrelative NonGuardian
    1638 = Relative Guardian
    1639 = Relative NonGuardian
***;

data guard_dependent guard_nondependent;
   set guard_lglath2;
   if plc_athc in (1409,1410,1411,1412,1413,6536,6537,6538) then do;
   		/*add non minor dependents 2022.01.27 j magruder*/
   *if plc_athc in (1409,1410,1411,1412,1413,6536,6537,6538, 6075, 6076, .) then do;
   		/*CDSS is inconsistent about whether to include
          missing legal authority, legal authority not yet determined
   		  and findings never made in dependent category - i.e., in AFCARS file
   		  2024.01.02 j magruder - pending disucssion with CDSS*/
      if scp_rltc in (1638,1639) then plcmnt_type="k"; /*"k" for kin*/
          else plcmnt_type="f";
      output guard_dependent;
   end;
   else if plc_athc in (1404, 6539) then output guard_nondependent; 
   		/*1404 added (no effect) 2024.01.02 j magruder*/
   else output guard_nondependent;
run;

***** For the dependent guardians insert the updated placement types
      onto the original file
*****;

proc sql;
   create table ucb_fc2 as
   select a.*,
          b.plcmnt_type
   from ucb_fc as a left join guard_dependent as b
   on a.fkclient_t=b.fkclient_t and
      a.ohmpl_id=b.ohmpl_id and
          a.plcep_id=b.plcep_id
          ;
quit;

proc sort data=ucb_fc2; *by fkclient_t pe_s_dt tpe_e_dt SPELL oh_s_dt toh_e_dt; 
	by fkclient_t spell pmt_order; run;

*** At this point the file contains all placement episodes and all placements.
    The placement facility type for dependents is adjusted based on the
    relationship to the child.
***;

data ucb_fc3;
   set ucb_fc2;
   *by fkclient_t pe_s_dt tpe_e_dt SPELL oh_s_dt ;
   by fkclient_t spell pmt_order;

   *** Change the placement facility type for dependent guardians ***;

   if plcmnt_type='k' then plc_fclc=1421;
   if plcmnt_type='f' then plc_fclc=1416;

   *** At this point the plc_fclc=5411 in the dataset fullfile3 are only
       non-dependent guardians.
   ***;

   if first.SPELL and last.SPELL then only_place=1;
   else only_place=0;

run;

*************************************************************************************;
*****
  Remove placement=non-dependent guardians.

  (1) In this process if a child has a first placement of GUARDIAN and ONLY_PLACE=1 then the
      entire placement episode can be deleted (only one placement and it is non-dep guard).
  (2) If non-dependent guardian is the only placement type in the placement episode, when
      there are multiple placements, then the entire placement episode can be deleted.
  (3) For all other cases where non-dep guardian is the first ever placement for a child
      the placement needs to be deleted and there needs to be an adjustment of the placement
      start dates (to account for the removal of the first placement).
*****;
*************************************************************************************;

proc sort data=ucb_fc3; *by fkclient_t pe_s_dt tpe_e_dt SPELL oh_s_dt toh_e_dt; 
	by fkclient_t pe_s_dt tpe_e_dt SPELL pmt_order; run;

*** The following code creates an out of home placement patterns for the child
  in a placement episode.
  BOTHER is a placement before the first Guardian placement.
  AOTHER is a placement after the first Guardian placement.
  STRING consists of a pattern of characters A,B,G showing the placement history
  of the child in the current episode. It is used to help figure out future
  steps as necessary.
***;

%macro patterns(ds);

data patterns;
   set &ds.;
   retain guard bother aother string first_guard mid_guard last_guard;
   length string $10;
   *by fkclient_t pe_s_dt tpe_e_dt SPELL oh_s_dt toh_e_dt; 
	by fkclient_t pe_s_dt tpe_e_dt SPELL pmt_order;;
   if first.SPELL then do;
      guard=0;
          bother=0;
          aother=0;
          first_guard=0;
          mid_guard=0;
          last_guard=0;
          string=' ';
   end;
   if plc_fclc=5411 then do;
      if string=' ' then first_guard=1;
          if last.SPELL then last_guard=1;
          else if string ne ' ' then mid_guard=1;
          if guard=0 then string=compress(string||'G');
      guard=1;
   end;
   else if plc_fclc ne . then do;
      if guard=0 and bother=0 then do;
         bother=1;
             string=compress(string||'B');
      end;
          else if guard=1 and aother=0 then do;
         aother=1;
             string=compress(string||'A');
      end;
   end;

   if last.SPELL then output;
run;

/*
 proc freq data=patterns;
    table bother*guard*aother first_guard*mid_guard*last_guard string / list missing;
 run;
*/

%mend patterns;

%patterns(ucb_fc3);

*** Put the pattern information back into the full file ***;

proc sql;
   create table ucb_fc4 as
   select a.*,
          b.bother,
                  b.guard,
                  b.aother,
          b.string
   from ucb_fc3 as a left join patterns as b
   on a.SPELL=b.SPELL and
      a.fkclient_t=b.fkclient_t
         ;
quit;

*** Adjust the initial guardian placements and delete instances where the
    only placement is non-dependent guardian.
***;

data cleanup_guardians;
   set ucb_fc4;
   where guard=1 ;
run;

data delete_episode adjust_episode;
   set cleanup_guardians;
   if only_place=1 then output delete_episode;
   else if bother=0 and guard=1 and aother=0 then output delete_episode;
   else output adjust_episode;
run;

*** Remove the non-dependent guardian only episodes ***;

proc sql;
   create table ucb_fc5 as
   select *
   from ucb_fc4
   where plcep_id not in (select plcep_id
                          from delete_episode )
                ;
quit;

PROC SORT DATA=ucb_fc5; *BY FKCLIENT_T PE_S_DT SPELL OH_S_DT ; 
	BY FKCLIENT_T PE_S_DT SPELL pmt_order ; 	RUN;

*** Bring all records for the other instances where the initial guardian
    placement needs to be removed
***;

proc sql;
   create table adjust_episode2 as
   select *
   from ucb_fc5
   where plcep_id in (select plcep_id
                      from adjust_episode )
                ;
quit;

*** Because the first placement is the guardian placement that needs to be
    removed it is easiest to just peel off the first row.
***;

proc sort data=adjust_episode2; *by fkclient_t pe_s_dt tpe_e_dt SPELL oh_s_dt oh_e_dt PLCMNT;
	by fkclient_t pe_s_dt tpe_e_dt SPELL pmt_order;run;

data adjust_ohp ;
   set adjust_episode2;
   by fkclient_t;
   if bother=0 and guard=1 then output adjust_ohp;
run;

*** Separate the first OHP (the guardian OHP) from the rest of the ohplacements ***;

data first_guard_ohp other_ohp ;
   set adjust_ohp;
   retain guardgone other_plcfclc;
   by fkclient_t pe_s_dt tpe_e_dt SPELL ;
   if first.SPELL then do;
      guardgone=0;
          other_plcfclc=0;
   end;

   if plc_fclc not in (5411) then other_plcfclc=1;

   *** The initial non-dependent guardians are placed in the FIRST_GUARD_OHP
       dataset.  If there are other (non guardian) facility types before the
       guardian placement it remains and is placed in OTHER_OHP
   ***;
   if plc_fclc=5411 then do;
      if other_plcfclc=0 then do;
         output first_guard_ohp;
             guardgone=1;
      end;
          else output other_ohp;
   end;
   else if guardgone=1 then output other_ohp;
run;

*** For the remainder of placements in an episode, the pe_s_dt has to be adjusted ***;

proc sort data=other_ohp; *by fkclient_t pe_s_dt tpe_e_dt SPELL oh_s_dt toh_e_dt PLCMNT; 
	by fkclient_t pe_s_dt tpe_e_dt SPELL pmt_order;run;

data other_ohp2;
   set other_ohp;
   retain upd_pe_s_dt;
   *by fkclient_t pe_s_dt tpe_e_dt SPELL oh_s_dt toh_e_dt  ;
   by fkclient_t pe_s_dt tpe_e_dt SPELL pmt_order ;
   if first.fkclient_t then upd_pe_s_dt=oh_s_dt;
run;

proc sort data=other_ohp2 nodupkey out=other_ohp3(keep=fkclient_t plcep_id upd_pe_s_dt); 
	by fkclient_t plcep_id; run;

*** Remove the first out of home placement(first ever placement = guardian) ***;
*** Place the new removal date onto the placement episode file. ***;

proc sql;
   create table ucb_fc6 as
   select *
   from ucb_fc5
   where ohmpl_id not in (select ohmpl_id
                            from first_guard_ohp )
                ;
   create table ucb_fc7 as
   select a.*,
          b.upd_pe_s_dt
   from ucb_fc6 as a left join other_ohp3 as b
   on a.plcep_id=b.plcep_id and
      a.fkclient_t=b.fkclient_t
                ;
quit;

data ucb_fc8;
   set ucb_fc7;
   if upd_pe_s_dt ne . then pe_s_dt=upd_pe_s_dt;
run;

proc sort data=ucb_fc8; *by fkclient_t pe_s_dt SPELL oh_s_dt; 
	by fkclient_t pe_s_dt SPELL pmt_order; run;

data ucb_fc8;
   set ucb_fc8;
   *by fkclient_t pe_s_dt SPELL oh_s_dt;
   by fkclient_t pe_s_dt SPELL pmt_order;
   retain first_ohpsd ;

   if first.SPELL then first_ohpsd=oh_s_dt;

   orig_plcepid=plcep_id;
   orig_pe_s_dt=pe_s_dt;
   orig_pleps_endt=tpe_e_dt;
   orig_SPELL=SPELL;
   uniqplace=compress(put(SPELL,z2.)||"-"||fkclient_t);

run;

proc sort data=ucb_fc8;
  *by fkclient_t pe_s_dt tpe_e_dt plcep_id oh_s_dt toh_e_dt ohmpl_id;
  by fkclient_t pe_s_dt tpe_e_dt plcep_id pmt_order ohmpl_id;
run;

*** The next adjustment is for instances where there is a non-dependent
    guardian placement somewhere in between other placements.
***;

data ucb_fc8b;
   set ucb_fc8(rename=(guard=p1_guard bother=p1_bother aother=p1_aother
                       string=p1_string ));
run;

%patterns(ucb_fc8b);


proc sql;
   create table ucb_fc9 as
   select a.*,
          b.bother,
                  b.guard,
                  b.aother,
          b.string
   from ucb_fc8b as a left join patterns as b
   on a.SPELL=b.SPELL and
      a.fkclient_t=b.fkclient_t
         ;
quit;

proc sort data=ucb_fc9; *by fkclient_t pe_s_dt tpe_e_dt plcep_id oh_s_dt toh_e_dt ohmpl_id; 
	by fkclient_t pe_s_dt tpe_e_dt plcep_id pmt_order ohmpl_id; run;

data adjust_middle;
   set ucb_fc9;
   length new_plcep_id $13;
   *by fkclient_t pe_s_dt tpe_e_dt plcep_id oh_s_dt toh_e_dt ohmpl_id;
   by fkclient_t pe_s_dt tpe_e_dt plcep_id pmt_order ohmpl_id;
   retain am_pe_s_dt am_pleps_endt new_plcep_id am_guard am_count;

   *** Create a series of retained variables - the most important being another
       version of the plcep_id ***;
   if first.plcep_id then do;
      am_pe_s_dt=.;
          am_pleps_endt=.;
          am_guard=0;
          am_count=0;
          new_plcep_id=compress(left(plcep_id));
   end;

   *** there is a placement before the guardian placement ***;
   if am_guard=0 and guard=1 then am_pleps_endt=toh_e_dt;

   if plc_fclc=5411 then am_guard=1;

   *** If there is another placement after a guardian placement then update the
       new plcep_id variable by placing a count after it. ***;
   if plc_fclc ne 5411 and am_guard=1 then do;
      am_guard=0;
      am_count=sum(am_count,1);
          am_pleps_endt=.;
          new_plcep_id=compress(left(plcep_id)||'-'||put(am_count,z2.));
          am_pe_s_dt=oh_s_dt;
   end;
   if last.plcep_id and am_guard=1 then updend=1;
run;

*** for instances where there is a guardian last in a placement episode ***;
*** adjust the placement episode end date. ***;

data adjust_last_guard;
   set adjust_middle;
   where updend=1;
   keep fkclient_t new_plcep_id updend;
run;

proc sql;
  create table alg2 as
  select a.*,
         b.updend  as updend2
  from adjust_middle as a left join adjust_last_guard as b
  on a.fkclient_t=b.fkclient_t and
     a.new_plcep_id=b.new_plcep_id ;
quit;

proc sort data=alg2; by fkclient_t pe_s_dt tpe_e_dt new_plcep_id   ; run;

data alg3;
   set alg2;
   by fkclient_t pe_s_dt tpe_e_dt new_plcep_id  ;
   where updend2=1;
   if last.new_plcep_id;
run;

proc sql;
  create table alg4 as
  select a.*,
         b.oh_e_dt as new_end_dt
  from adjust_middle as a left join alg3 as b
  on a.fkclient_t=b.fkclient_t and
     a.new_plcep_id=b.new_plcep_id ;
quit;

data alg5;
   set alg4;
   if new_end_dt ne . then do;
      tpe_e_dt=new_end_dt;
      term_ty_c=5434;
   end;
run;

proc sort data=alg5; *by    fkclient_t pe_s_dt tpe_e_dt new_plcep_id  oh_s_dt ;
	 by    fkclient_t pe_s_dt tpe_e_dt new_plcep_id pmt_order ; run;

*** Remove all the non-dependent guardian placements now ***;

data adjust_middle2;
   set alg5;
   if am_guard ne 1;
run;

proc sort data=adjust_middle2;
  *by fkclient_t pe_s_dt tpe_e_dt new_plcep_id oh_s_dt toh_e_dt ohmpl_id;
  by fkclient_t pe_s_dt tpe_e_dt new_plcep_id pmt_order ohmpl_id;
run;

*** Take the last NEW placement episode and use it to update the full file ***;

data am3;
   set adjust_middle2;
   by fkclient_t pe_s_dt tpe_e_dt new_plcep_id ;
   if last.new_plcep_id;
run;

Proc sort data=adjust_middle2(drop=upd_pe_s_dt); by fkclient_t; run;

proc sql;
   create table am4 as
   select a.*,
          b.am_pe_s_dt as upd_pe_s_dt,
                  b.am_pleps_endt as upd_pleps_endt
   from adjust_middle2 as a left join am3 as b
   on a.fkclient_t=b.fkclient_t and
      a.new_plcep_id=b.new_plcep_id
          ;
quit;

data ucb_fc12;
   set am4;
   if upd_pe_s_dt ne . and
      upd_pe_s_dt ne pe_s_dt then pe_s_dt=upd_pe_s_dt;
   if upd_pleps_endt ne . and
      upd_pleps_endt ne tpe_e_dt then do;
         tpe_e_dt=upd_pleps_endt;
         term_ty_c=5434;
      end;

   *drop am_pe_s_dt am_pleps_endt upd_pe_s_dt upd_pleps_endt am_guard;
run;

proc sort data=ucb_fc12 ; *by fkclient_t pe_s_dt tpe_e_dt new_plcep_id oh_s_dt ; 
	by fkclient_t pe_s_dt tpe_e_dt new_plcep_id pmt_order ; run;

data ucb_fc12b(rename=(new_plcep_id=PLCEP_ID));
   set ucb_fc12(rename=(SPELL=OLD_SPELL plcep_id=ORIG_PLCEP_ID));
   by fkclient_t pe_s_dt tpe_e_dt new_plcep_id;
   retain SPELL plc_cnt;
   if first.fkclient_t then do; SPELL=0; plc_cnt=0; end;
   if first.new_plcep_id then SPELL=sum(SPELl,1);
   plc_cnt=sum(plc_cnt,1);

   if last.new_plcep_id and not last.fkclient_t and tpe_e_dt='01jan3000'd then do;
     tpe_e_dt=oh_e_dt;
     term_ty_c=5434;
   end;

   uniqplace=compress(put(SPELL,z2.)||"-"||fkclient_t);
   uniqrecord=compress(put(SPELL,z2.)||put(plc_cnt,z2.)||"-"||fkclient_t);
run;


PROC SORT DATA=ucb_fc12b ; *BY FKCLIENT_T PE_S_DT SPELL OH_S_DT ; 
	 BY FKCLIENT_T PE_S_DT SPELL pmt_order ; RUN;

*** Rename some variables so that we keep the original values ***;

data bridge1_undup;
   set ucb_fc12b;
   *by fkclient_t pe_s_dt SPELL oh_s_dt;
   by fkclient_t pe_s_dt SPELL pmt_order;

   if last.SPELL ;
run;

*** Separate out any instances of only one placement episode - this will allow
    the process to run faster and have less potential for errors.
    Summarize the information by child - if a child has only one placement episode
    they go into the BRIDGE_SINGLES file otherwise they go into the BRIDGE_MULTIS file.
***;

proc summary data=bridge1_undup;
   class fkclient_t;
   output out=spell_count;
run;

data singles multiples;
   set spell_count;
   where fkclient_t ne ' ';
   if _freq_=1 then output singles;
   else output multiples;
run;

proc sql;
   create table bridge_singles as
   select *
   from bridge1_undup
   where fkclient_t in (select fkclient_t
                        from singles)
                                                ;

   create table bridge_multis as
   select *
   from bridge1_undup
   where fkclient_t in (select fkclient_t
                        from multiples)
                                                ;
quit;

*** Proceed to the bridging macro using the multiple placement episodes ***;

proc sort data=bridge_multis;
  *by fkclient_t pe_s_dt SPELL oh_s_dt;
  by fkclient_t pe_s_dt SPELL pmt_order;
run;

*** The BRIDGE macro runs through the process 5 times.  Each time the process
    looks to see if the start date and end dates for different placement episodes
    for a child are either 0 or 1 day apart.  If they are then the dates are adjusted.
    IMPORTANT:  No information is lost during this process.  Just as many placement
    episode observations come out as go in.  The difference is that the placement
    counter is adjusted and the dates/plcep_id variable is adjusted.  We will need
    the full file with the original information to adjust the out of home placements
    in the next step.
***;

%macro bridge;

*** Rename the dataset so that it can be processed without losing the original data ***;

proc sort data=bridge_multis out=bm; *by fkclient_t pe_s_dt SPELL oh_s_dt ; 
	by fkclient_t pe_s_dt SPELL pmt_order ; run;
%do lvl = 1 %to 5;

*** Get LAG information for each child ***;

data bridge_lag;
   set bm;

   lag_end=lag(tpe_e_dt);
   lag_rem=lag(pe_s_dt);
   lag_info=lag(uniqplace);
   lag_fkc=lag(fkclient_t);
   lag_id=lag(plcep_id);
   *** Add the ohp end date ***;
   lag_ohend=lag(toh_e_dt);
   lag_rmv_rsnc=lag(rmv_rsnc);
   lag_agency=lag(agy_rspc);

   *** If it is for the same child and the time between periods is 0 or 1 day
       then this record needs to be bridged.
   ***;

   if fkclient_t = lag_fkc and
      plcep_id ne lag_id then do;
         if lag_end ne . then time_between=pe_s_dt-lag_end;
                 if first_ohpsd ne . then ohp_time_between=first_ohpsd-pe_s_dt;

         if time_between in (0,1) and
            ((lag_agency=34 and agy_rspc in (33,5603) ) or
             (lag_agency in (33,5603) and agy_rspc=34 ) ) then bridge=1;
   end;
   else do;
      lag_info=' ';
          lag_end=.;
      lag_id=' ';
          lag_rem=.;
          lag_rement=.;
          lag_endent=.;
   end;

run;

*** Take the information that needs to be bridged and use it to update the previous
    episodes end dates
***;

proc sort data=bridge_lag out=bridge_lag2; by fkclient_t SPELL; where bridge=1; run;

data updates;
   set bridge_lag2;
   by fkclient_t ;
   if first.fkclient_t;
run;

*** Change the end date of the initial record ***;

proc sql;
   create table upd_enddts as
   select a.*,
          b.bridge as chnge_end,
                  b.tpe_e_dt as pleps_endt1,
                  b.term_ty_c as term_ty_c1,
                  b.uniqplace as uniqplace1
   from bridge_lag as a left join updates as b
   on a.fkclient_t=b.lag_fkc and
      a.plcep_id=b.lag_id;

   create table upd_startdts as
   select a.*,
          b.bridge as chnge_start,
                  b.lag_id as plcep_id1,
                  b.lag_rem as pe_s_dt1,
                  b.rmv_rsnc as rmv_rsnc1
   from upd_enddts as a left join updates as b
   on a.fkclient_t=b.fkclient_t and
      a.plcep_id=b.plcep_id;
quit;

*** Re-standardize the variable names and keep a record of the changes ***;

data updates2;
   set upd_startdts ;

   if chnge_end=1 then do;
          opleps_endt_&lvl.=tpe_e_dt;
      tpe_e_dt=pleps_endt1;
          oend_ent_dt&lvl.=end_ent_dt;
      end_ent_dt=end_ent_dt1;
          oterm_ty_c_&lvl. =term_ty_c;
          term_ty_c = term_ty_c1;
   end;
   if chnge_start = 1 then do;
          ormv_rsnc_&lvl.=rmv_rsnc;
          rmv_rsnc=rmv_rsnc1;
      orem_dt_&lvl.=pe_s_dt;
      pe_s_dt=pe_s_dt1 ;
      ormv_ent_dt_&lvl.=rmv_ent_dt;
          rmv_ent_dt=rmv_ent_dt1;
          oplcep_id_&lvl.=new_plcep_id;
          plcep_id=plcep_id1;
   end;
run;


*** Recalculate the placement counter ***;

proc sort data=updates2; by fkclient_t pe_s_dt tpe_e_dt plcep_id  ; run;

data updates3;
   set updates2(rename=(SPELL=SPELL_&lvl.));
   by fkclient_t pe_s_dt tpe_e_dt plcep_id  ;
   if chnge_start=1 or
      chnge_end then bridge_&lvl. = 1;
   else bridge_&lvl. = 0 ;

   retain SPELL;
   if first.fkclient_t then SPELL=0;
   if first.plcep_id then SPELL=sum(SPELL,1);
run;

*** If a record has been bridged then there is now in effect two records with the same
    placement counter.  This could lead to unnecessary processing so remove one of them
    and save it for after the loop.
***;

proc sort data=updates3; by fkclient_t SPELL; run;

**** Remove any instances where there might not be more possible roll ups ***;

proc sort data=updates3 nodupkey out=updates4; by fkclient_t orig_SPELL; run;

proc summary data=updates4;
   class fkclient_t;
   output out=placement_count;
run;

data done_ ongoing_;
   set placement_count;
   where fkclient_t ne ' ';
   if _freq_ le &lvl. then output done_;
   else output ongoing_;
run;

proc sql;
   create table bridge_done_&lvl. as
   select *
   from updates3
   where fkclient_t in (select fkclient_t
                        from done_ )
                                                ;

   create table bridge_ongoing as
   select *
   from updates3
   where fkclient_t in (select fkclient_t
                        from ongoing_)
                                                ;
quit;

*** Rename the datafile that is going back through the loop (ONGOING) to the
    first dataset name (BM).
***;

data bm; set bridge_ongoing;
proc sort data=bridge_ongoing out=bm(drop=plcep_id1 pleps_endt1 end_ent_dt1 TERM_TY_C1 RMV_RSNC1
                                     UNIQPLACE1 bridge pe_s_dt1 rmv_ent_dt1 chnge_end chnge_start);
   by fkclient_t SPELL;
run;



%end;

*** Bring together all of the pieces of the bridged file ***;

data multi_bridged(rename=(SPELL=SPELL_6));
   set bridge_done_1
       bridge_done_2
       bridge_done_3
       bridge_done_4
       bridge_done_5

       bm;
   drop lag_end lag_rem lag_rement lag_endent lag_info lag_fkc lag_id time_between
        pleps_endt1 end_ent_dt1 uniqplace1 plcep_id1 pe_s_dt1
        orem_dt_1-orem_dt_5 orm_ent_dt_1-orm_ent_dt_5 oplcep_id_1-oplcep_id_5
        opleps_endt_1-opleps_endt_5 oend_ent_dt1-oend_ent_dt5 ormv_ent_dt_1-ormv_ent_dt_5
                SPELL_1-SPELL_5 rmv_ent_dt1
                /* chnge_start chnge_end bridge */
   ;
run;

proc sort data=multi_bridged;
  by fkclient_t pe_s_dt tpe_e_dt SPELL_6 plcep_id ;
run;

data multi_bridged;
   set multi_bridged;

   by fkclient_t pe_s_dt tpe_e_dt SPELL_6 plcep_id ;

   retain SPELL;
   if first.fkclient_t then SPELL=0;
   if first.plcep_id then SPELL=sum(SPELL,1);

run;


%mend bridge;

%bridge;

*** the dataset final bridged should have the same number of observations
    as the BRIDGE1_UNDUP file
***;

data final_bridged;
   set bridge_singles
       multi_bridged;

   if tpe_e_dt=. or
      tpe_e_dt gt &cutoff. then plc_end=&cutoff.;
   else plc_end=tpe_e_dt;

   plos=plc_end-pe_s_dt;

run;

proc sort data=final_bridged; *by fkclient_t PLCMNT; 
	by fkclient_t pmt_order; run;

proc sql;
   create table togetheragain as
   select a.*,
          b.term_ty_c as new_term_ty_c,
          b.plcep_id as new_plcep_id2 ,
                  b.pe_s_dt as new_pe_s_dt,
          b.tpe_e_dt as new_tpe_e_dt,
                  b.toh_e_dt as new_toh_e_dt,
          b.rmv_rsnc as new_rmv_rsnc,
                  b.SPELL as new_SPELL
   from ucb_fc12b as a left join final_bridged as b
   on a.uniqplace = b.uniqplace ;
quit;

data togetheragain2;
   set togetheragain;

   if new_plcep_id2 ne ' ' then plcep_id=new_plcep_id2;
   if new_pe_s_dt ne . then pe_s_dt=new_pe_s_dt;
   if new_tpe_e_dt ne . then tpe_e_dt=new_tpe_e_dt;
   if new_rmv_rsnc ne . then rmv_rsnc=new_rmv_rsnc;
   if new_SPELL ne . and new_SPELL ne 0 then SPELL=new_SPELL;
   if new_term_ty_c ne . then term_ty_c=new_term_ty_c    ;
run;

proc sort data=togetheragain2; *by fkclient_t pe_s_dt tpe_e_dt plcep_id oh_s_dt toh_e_dt ohmpl_id;
	by fkclient_t pe_s_dt tpe_e_dt plcep_id pmt_order ohmpl_id; run;

data oohpe;
   set togetheragain2;
   *by fkclient_t pe_s_dt tpe_e_dt plcep_id oh_s_dt toh_e_dt ohmpl_id;
   by fkclient_t pe_s_dt tpe_e_dt plcep_id pmt_order ohmpl_id;
   lagfkc_1=lag(fkclient_t);
   lagnc_1=lag(PLCMNT);
run;

proc sort data=oohpe;
  by fkclient_t pe_s_dt tpe_e_dt plcep_id  oh_s_dt PLCMNT toh_e_dt ;
run;

/*separate runaways from other records, bring back in at end 2021.12.17 j magruder*/
/*this is to avoid negative placement counts when there are multiple runaways*/
data oohpe2a;
	set oohpe;
	if plc_fclc not in(7164, 7181);
	run;

data oohpe2;
   set oohpe2a;
   by fkclient_t pe_s_dt tpe_e_dt plcep_id oh_s_dt PLCMNT  toh_e_dt ;
   retain new_ooh_cnt ;

   if first.plcep_id then do;
      new_ooh_cnt=0;
   end;

   	if first.PLCMNT and plc_fclc ne 7181 then new_ooh_cnt=sum(new_ooh_cnt,1); 
   	/*runaway exclusion added 2022.01.25 j magruder*/
run;

*************************************************************************************;
*** Step 3b:  Adjust IBM counter dealing with placement moves                     ***;
*************************************************************************************;

*** The IBM counter supposedly accounts for instances where a child moves back
    into the same out of home placement episode (if it is the same home do not increment
    the out of home placement counter).  Because of the placement episode bridging we need
    to go back and make sure that we are not incorrectly incrementing the out of home
    placement counter.  This involves linking to the placement home and substitute care
    provider tables
***;

/*test section*/
/*only adjust placement counter on actual placements. Thus, runaways are removed.
  They will come back in later from togetheragain2 j magruder 2022.01.25*/
data oohpe2a;
	set oohpe2;
	if plc_fclc ne 7181;
	run;





proc sql;

   create table plchm as
   select a.plcep_id as plcmnt_id,
          a.ohmpl_id as ooh_id,
                  a.PLCMNT,
                  a.pe_s_dt,
                  a.fkclient_t,
                  a.new_ooh_cnt,
                  a.plcg_rnc,
                  a.oh_s_dt,
                  a.uniqplace,
				  a.pmt_order,
                  b.*
   from oohpe2a as a left join cws.plc_hm_t as b
   on a.fkplc_hm_t=b.identifier ;

   create table subcare as
   select a.*,
          b.fksb_pvdrt,
                  b.prprvdr_cd,
                  b.scprvd_ind
   from plchm as a left join cws.hm_scp_t as b
   on a.identifier=b.fkplc_hm_t and
      (b.prprvdr_cd='Y' or
       b.scprvd_ind='Y') ;

quit;

*** It is important to remember that the variable IDENTIFIER is now associated with the subcare
    provider table - the variable OOH_ID is the out of home unique identifier.
***;

***** Check to see if the ooh placement counter needs to be incremented based on the
      same placement home or care providers (immediately prior to current ooh
      episode.
*****;

*** Roll up up to 4 providers per ooh placement episode ***;

proc sort data=subcare;
  by fkclient_t pe_s_dt PLCMNT plcmnt_id oh_s_dt new_ooh_cnt ooh_id identifier;
run;

data subcare2;
   set subcare;
   by fkclient_t pe_s_dt PLCMNT plcmnt_id oh_s_dt new_ooh_cnt ooh_id identifier;
   length cp1-cp4 $10;
   retain cp1-cp4 place;
   if first.identifier then do;
      cp1=' ';
          cp2=' ';
          cp3=' ';
          cp4=' ';
          place=1;
   end;
   array c{4} cp1-cp4;
   if fksb_pvdrt ne ' ' then do;
      if place=1 then do;
         c{place}=fksb_pvdrt;
             place=sum(place,1);
          end;
      else if 2 le place le 4 then do;
             if c(place-1) ne fksb_pvdrt then do;
           c{place}=fksb_pvdrt;
               place=sum(place,1);
                 end;
          end;
   end;
   if last.identifier then output;
run;

proc sort data=subcare2;
  by fkclient_t pe_s_dt PLCMNT plcmnt_id oh_s_dt new_ooh_cnt ooh_id identifier;
run;

data subcare3;
   set subcare2;
   by fkclient_t pe_s_dt PLCMNT plcmnt_id oh_s_dt new_ooh_cnt ooh_id identifier;
   length lcp1-lcp4 lfkc lpid lid $10;
   retain lcp1-lcp4 lfkc lpid lid lcnt;
   if first.identifier then do;
      lcp1=' ';
          lcp2=' ';
          lcp3=' ';
          lcp4=' ';
   end;
   lpid=lag(plcmnt_id);
   lid=lag(identifier);
   lcnt=lag(new_ooh_cnt);
   lfkc=lag(fkclient_t);
   lcp1=lag(cp1);
   lcp2=lag(cp2);
   lcp3=lag(cp3);
   lcp4=lag(cp4);
run;

data subcare4;
   set subcare3;
   by fkclient_t pe_s_dt PLCMNT plcmnt_id oh_s_dt new_ooh_cnt ooh_id identifier;
   retain adjuster;
   if first.plcmnt_id then adjuster=0;
   if lfkc=fkclient_t and
      lpid=plcmnt_id then do; *nothing; end;
   else do;
      lid=' ';
      lcp1=' ';
          lcp2=' ';
          lcp3=' ';
          lcp4=' ';
   end;

   if lid=identifier then do;
      if lcnt lt new_ooh_cnt then adjuster=adjuster-1;
   end;
   else if (fksb_pvdrt ne ' ' and
            lcnt ne new_ooh_cnt and
            (fksb_pvdrt=lcp1 or
               fksb_pvdrt=lcp2 or
               fksb_pvdrt=lcp3 or
               fksb_pvdrt=lcp4 ) ) then adjuster=adjuster-1;

   new_ooh_cnt3=sum(new_ooh_cnt,adjuster);
run;

**possible revision to correct placement counts;
*the revision changes the sort to not resent adjuster counter with each new plcmnt
 (CWS/CMS placement counter);

*as originally written the program set adjuster to 0 at every incrementing of the original 
 PLCMNT counter.  The revision of the sort sets it to 0 only at the beginning of an episode;
proc sort data = subcare3;
   by fkclient_t pe_s_dt /*PLCMNT*/ plcmnt_id oh_s_dt new_ooh_cnt ooh_id identifier;
    run;

data subcare4;
   set subcare3;
   by fkclient_t pe_s_dt /* PLCMNT*/ plcmnt_id oh_s_dt new_ooh_cnt ooh_id identifier;
   retain adjuster;
   if first.plcmnt_id then adjuster=0;
   if lfkc=fkclient_t and
      lpid=plcmnt_id then do; *nothing; end;
   else do;
      lid=' ';
      lcp1=' ';
          lcp2=' ';
          lcp3=' ';
          lcp4=' ';
   end;

   if lid=identifier then do;
      if lcnt lt new_ooh_cnt then adjuster=adjuster-1;
   end;
   /*else if (fksb_pvdrt ne ' ' and
            lcnt ne new_ooh_cnt and
            (fksb_pvdrt=lcp1 or
               fksb_pvdrt=lcp2 or
               fksb_pvdrt=lcp3 or
               fksb_pvdrt=lcp4 ) ) then adjuster=adjuster-1;

   new_ooh_cnt3=sum(new_ooh_cnt,adjuster);*/
   *this revision compares all caregivers in previous placement with
    caregivers in current placements;
    else if (cp1 ne ' ' and
            lcnt ne new_ooh_cnt and
            (cp1=lcp1 or
               cp1=lcp2 or
               cp1=lcp3 or
               cp1=lcp4 ) )
        or (cp2 ne ' ' and
            lcnt ne new_ooh_cnt and
            (cp2=lcp1 or
               cp2=lcp2 or
               cp2=lcp3 or
               cp2=lcp4 ) )
        or (cp3 ne ' ' and
            lcnt ne new_ooh_cnt and
            (cp3=lcp1 or
               cp3=lcp2 or
               cp3=lcp3 or
               cp3=lcp4 ) )
        or (cp4 ne ' ' and
            lcnt ne new_ooh_cnt and
            (cp4=lcp1 or
               cp4=lcp2 or
               cp4=lcp3 or
               cp4=lcp4 ) )

       then adjuster=adjuster-1;
   new_ooh_cnt3=sum(new_ooh_cnt,adjuster);
run;
**end possible revision;


/*runaways not included at this point but added back in
  from togetheragein2 file 2021.12.17 j magruder*/

***** Put the newest counter on the full file *****;
/*togetheragain2 includes runaways*/
proc sql;
   create table oohpc3 as
   select a.*,
                  b.new_ooh_cnt,
          b.new_ooh_cnt3,
                  b.gvr_entc
   from togetheragain2 as a left join subcare4 as b
   on a.ohmpl_id=b.ooh_id and
      a.fkclient_t=b.fkclient_t and
          a.plcep_id=b.plcmnt_id and
      a.uniqplace=b.uniqplace and
	a.oh_s_dt = b.oh_s_dt;
	/*placement start dates added to avoid multiple matches due to runaways not having 
	  placement ids. 2021.12.17 j magruder*/
quit;



proc sort data=oohpc3;
  by fkclient_t pe_s_dt oh_s_dt new_ooh_cnt3;
run;
/*add in counter for runaways - same count as previous placement, running away does
  not increment the placement counter j magruder 2022.01.25*/
data oohpc4;
	set oohpc3;
  	by fkclient_t pe_s_dt oh_s_dt new_ooh_cnt3;
	retain rnew_ooh_cnt rnew_ooh_cnt3;
	if first.pe_s_dt = 0 and plc_fclc = 7181 then do;
		new_ooh_cnt = rnew_ooh_cnt;
		new_ooh_cnt3 = rnew_ooh_cnt3;
		end;
	rnew_ooh_cnt = new_ooh_cnt;
	rnew_ooh_cnt3 = new_ooh_cnt3;
	run;
		

proc sort data=oohpc4 (drop = rnew_ooh_cnt rnew_ooh_cnt3);
  by fkclient_t pe_s_dt new_ooh_cnt3 oh_s_dt;
run;


***** At this stage the dataset OOHPC3 contains all placement episode
      and out of home placement episode observations.  The counters for these records
      have been adjusted.  The placement counter was calculated after rolling up
      placements that started and ended within one day of each other.  The out of
      home placement counter was adjusted to account for the rolling-up process in the
      placement episodes, to eliminate a counter increment when the oohp was one
      day or less, and to eliminate a counter increment when the oohp has the same
      placement home id or substitute care provider id compared to the immediately
      preceding out of home placement for the same child in the same placement.
*****;

data redo_guards_01(drop = am_guard am_pe_s_dt am_pleps_endt aother new_end_dt new_term_ty_c
                        p1_aother p1_bother p1_guard p1_string plc_cnt string tpe_e_dt
                        uniqrecord upd_pe_s_dt upd_pleps_endt updend am_count bother 
                        first_ohpsd guard new_ooh_cnt new_ooh_cnt3 new_pe_s_dt new_plcep_id2 
                        new_rmv_rsnc new_SPELL new_toh_e_dt new_tpe_e_dt only_place orig_pe_s_dt 
                        orig_plcepid orig_pleps_endt orig_SPELL plcmnt_type toh_e_dt uniqplace);
   set oohpc4(rename=(PLCMNT=OLD_PLCMNT)) ;
   if PLCMNT_type in ('f','k') then plc_fclc=5411;

   if tpe_e_dt ne '01jan3000'd then pe_e_dt=tpe_e_dt;

   PLCMNT=new_ooh_cnt3;
  

length PLCMNT SPELL 3;
format PLCMNT SPELL 6.;
format PLCEP_ID $13.;
informat PLCMNT SPELL 6.;
informat PLCEP_ID $13.;

label
SPELL = "ENHANCED SPELL COUNTER FOR AFCARS"
PLCMNT = "ENHANCED PLACEMENT COUNTER FOR AFCARS"
PLCEP_ID = "ENHANCED PLACEMENT EPISODE ID FOR AFCARS";

run;

/*add non-incremented placement counter for runaway records
  2012.12.17 j magruder*/
proc sort data = redo_guards_01;
	by fkclient_t pe_s_dt oh_s_dt;
	run;

data redo_guards;
	set redo_guards_01;
	by fkclient_t pe_s_dt oh_s_dt;
	retain rplcmnt;
	if first.pe_s_dt and plcmnt = . then plcmnt = 0;
		/*if first record is runaway, placement count is 0*/
	if first.pe_s_dt = 0 and 
		plcmnt = . then plcmnt = rplcmnt;
		/*otherwise, runaway retains placement count of prior placement*/
	rplcmnt = plcmnt;
	if plcmnt < 0 then plcmnt = 0;
	run;


proc sort data=redo_guards(drop=rplcmnt) out=dwh.ucb_fc_afcars; *by fkclient_t oh_s_dt; 
	by fkclient_t pmt_order; run;