*placement_days.sas;

*time in care by year by facility type;
***group home counts are too high - see make sense of this comments;;;
*2012.10.15 j magruder;
*2012.10.16 j magruder;
*2012.10.19 j magruder;
*2012.10.22 j magruder;
*2012.11.09 j magruder correct for nested placements;
*2012.11.26 j magruder - to work with all periods;
*2012.11.27 j magruder more on all periods;
*2012.11.29 j magruder;
*2012.12.04 j magruder;
*2012.12.07 j magruder add oh_s_dt to sort;
*2013.01.17 j magruder attempt to look at aid code;
*2013.01.22 j magruder change to cutoff more on aid code;
*2013.01.23 j magruder more on aid codes;
*2013.01.28 j magruder identifying IVE eligible, based on aid code;

/**************************************************************/

*create basic file with all placements except adoptive placements
 and placements nested within another placement (placement starts
 after base placement starts and ends before base placement ends).
 Adjust placement end dates so that no placement ends after succeeding
 placement starts;

*exclude adoptive placements as they do not represent cost to
 foster care funds - although do represent cost to Title IV-E AAP
 funds;
*modify ucb_fc to have artifical end date for open placements;
*placement start and end dates as shown in ucb_fc become orig_oh_s_dt and orig_oh_e_dt;

proc sort data = dwh.UCB_FC_AFCARS out=UCB_FC;
by FKCLIENT_T SPELL PLCMNT;
run;

data days_101 (keep = FKCLIENT_T OH_S_DT OH_E_DT ORIG_OH_E_DT PE_S_DT PE_E_DT SPELL PLCMNT 
                      AGENCY P_PLC CNTY_REM GENDER ETHNIC CENS_RC HISP_CDX BIRTH_DT);
set UCB_FC (rename = (OH_E_DT = orig_OH_E_DT));

by FKCLIENT_T SPELL PLCMNT; 

*if PLC_FCLC not in (2222,1420) and AGY_RSPC not in (0036, 5605, 6133, 6134) and OH_S_DT NE OH_E_DT;

if PLC_FCLC not in (2222,1420,6363,6366,6367,7164,7181)and AGY_RSPC not in (0036, 5605, 6133, 6134) and OH_S_DT NE OH_E_DT;

if orig_OH_E_DT = . then OH_E_DT = '01JAN2050'D;
else OH_E_DT = orig_OH_E_DT;
format OH_E_DT mmddyy10.;
    
* Assign Gender Class;
select(GENDER_CD);
when('F') GENDER = 1;                                /* Female */
when('M') GENDER = 2;                                /* Male */
when('I') GENDER = 3;                                /* Intersex */
otherwise GENDER = 99;                               /* Unknown/Missing */     
end;

*summarize placement types;
if PLC_FCLC in ('2222') then P_PLC = 1;                 /* Pre-Adopt */
else if PLC_FCLC in ('1421','1422') then P_PLC = 2;     /* Kin */
else if PLC_FCLC in ('1415','1416') then P_PLC = 3;     /* Foster */
else if PLC_FCLC in ('1414','2200') then P_PLC = 4;     /* FFA */
else if PLC_FCLC = '1419' then P_PLC = 9;               /* Court Specified Home */
else if PLC_FCLC in ('7208') then P_PLC = 9.2;          /* Tribally Approved Home */
else if PLC_FCLC = '1417' then P_PLC = 10;              /* Group */
else if PLC_FCLC in ('1418','7027') then P_PLC = 11;    /* Shelter */
else if PLC_FCLC = '1420' then P_PLC = 13;              /* Non-FC */
else if PLC_FCLC = '5411' then P_PLC = 14;              /* Guardian */
else if PLC_FCLC = '6716' then P_PLC = 19;              /* SILP */
else if PLC_FCLC not in (' ','0') then P_PLC = 29;      /* Other */
else if PLC_FCLC in (' ','0') then P_PLC = 99;          /* Missing */

label P_PLC = "Simplified Placement Type";
     
* 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;

label AGENCY = "Responsible Agency";

run;

proc sort data = days_101;
by FKCLIENT_T SPELL OH_S_DT PLCMNT;
run; 

*delete nested placements;
data days_102;
set days_101;

by FKCLIENT_T SPELL OH_S_DT PLCMNT;

retain xFKCLIENT_T xOH_S_DT xOH_E_DT; 

if FKCLIENT_T = xFKCLIENT_T and 
   OH_S_DT    > xOH_S_DT and 
   OH_E_DT    < xOH_E_DT
then delete;

else do;
xFKCLIENT_T = FKCLIENT_T;
xOH_S_DT = OH_S_DT;
xOH_E_DT = OH_E_DT;
end;

run;

*the above file contains overlapping placements;

proc sort data = days_102;
by FKCLIENT_T descending SPELL descending OH_S_DT descending PLCMNT;
run;

*to correct for overlappping placements, reset placement start dates if overlapping with prior end date;
*(in 2011 this changed end dates on 1332 of 138,637 placements);

data days_103 (drop = xFKCLIENT_T xOH_S_DT xOH_E_DT);
set days_102;

by FKCLIENT_T descending SPELL descending OH_S_DT descending PLCMNT;

retain test_dt;

*note - first.FKCLIENT_T would be last placement in the study period because sort is descending;

if first.FKCLIENT_T then rev_OH_E_DT = OH_E_DT;
else if test_dt < OH_E_DT then rev_OH_E_DT = test_dt;
else rev_OH_E_DT = OH_E_DT;

test_dt = OH_S_DT;

format rev_OH_E_DT test_dt mmddyy10.;
label rev_OH_E_DT = "Adjusted Placement End Date";

run;

proc sort data = days_103;
by FKCLIENT_T SPELL OH_S_DT PLCMNT;
run;


*begin identification of county - the part here is generic to any time period in macro;
*case_assign contains all case assignments for children in care at any point in time;
* from c2m3;

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

data days_201 (keep = FKCLIENT_T);
set days_103;

by FKCLIENT_T SPELL OH_S_DT PLCMNT ;
if first.FKCLIENT_T;

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

run;

data ASGNM_T;
set cws.ASGNM_T;

if END_DT = . then tEND_DT = '31DEC3000'd;
else tEND_DT = END_DT;
format tEND_DT mmddyy10.;

run;

*** Join the CASE and ASGNM_T tables together ***;

proc sql;

create table CASE_ASSIGN as
select x.FKCHLD_CLT,
       x.IDENTIFIER as caseid,
       y.START_DT,
       y.tEND_DT,
       y.END_TM,
       y.CNTY_SPFCD
from cws.CASE_T as x, ASGNM_T as y
where x.IDENTIFIER = y.ESTBLSH_ID and
      y.ASGNMNT_CD = 'P' and
      y.ESTBLSH_CD = 'C' and
      x.FKCHLD_CLT in (select FKCLIENT_T from days_201)
order by FKCHLD_CLT, START_DT, tEND_DT, END_TM;

*** Join the REFERRAL and ASGNM_T tables together ***;

create table REFE as
select x.IDENTIFIER,
       y.START_DT, y.tEND_DT, y.END_TM, y.CNTY_SPFCD
from CWS.REFERL_T as x, ASGNM_T as y
where x.IDENTIFIER = y.ESTBLSH_ID and
      y.ASGNMNT_CD = 'P' and
      y.ESTBLSH_CD = 'R' 
order by IDENTIFIER, START_DT, tEND_DT, END_TM;

create table REFE_1 as
select /*A.PLCEP_ID,
         A.PE_E_DT,*/
         B.FKREFERL_T,
         A.FKCLIENT_T
from days_201 A, CWS.REFR_CLT B
where A.FKCLIENT_T eq B.FKCLIENT_T
order by FKREFERL_T;

*added for this program;

create table refe_1a as
select refe.*,
       refe_1.*
from refe, refe_1
where refe.IDENTIFIER = refe_1.FKREFERL_T
order by FKCLIENT_T;

quit;

/*
proc print data = refe_1a (obs = 30);
format START_DT tend_dt mmddyy10.;
run;
*/

*** Get the State ID county assignment at period start date***;

data ST_ID;
set CWS.ST_ID_T;

if END_DT eq . then END_DT = '31DEC3000'd ;

run ;

proc datasets;
    delete asgnm_t days_101 days_102;
    run;

**now move to specific periods as a macro;
  
%macro days(sq,sy,eq,ey);

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

%let empty=0;

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

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

data _null_;

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

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

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

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

%end;

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

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

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

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

%end;

%else 
%do;

*** Otherwise standard quarters ***;

start_qtr=1 ;
end_qtr=4;
%end;

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

run;

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


*find all placements for each child in period by type and and calculate length of each placement;
*exclude placements 0 days in length (adjusted placement end on adjusted start date);

data days_104x;
set days_103;

by FKCLIENT_T SPELL OH_S_DT PLCMNT;

if OH_S_DT < yyq(&SYEAR+1,&SQTR) and REV_OH_E_DT ge yyq(&SYEAR,&SQTR);

if OH_S_DT < yyq(&SYEAR,&SQTR) then wOH_S_DT = yyq(&SYEAR,&SQTR);
else wOH_S_DT = OH_S_DT;

if rev_OH_E_DT > yyq(&SYEAR+1,&SQTR) then wOH_E_DT = yyq(&SYEAR+1,&SQTR);
else wOH_E_DT = rev_OH_E_DT;

format wOH_S_DT wOH_E_DT mmddyy10.;

if wOH_S_DT = wOH_E_DT then delete;

W_PMT_DAY = wOH_E_DT - wOH_S_DT;

*if PE_S_DT < yyq(&SYEAR,&SQTR) then IN_PE_S = 1;
*else IN_PE_S = 0;

if OH_S_DT < yyq(&SYEAR,&SQTR) then IN_OH_S = 1;
else IN_OH_S = 0;

*label IN_PE_S = "Episode Open on Start Date";
label WOH_S_DT = "Adjusted Placement Start Date";
label WOH_E_DT = "Adjusted Placement End Date";
label IN_OH_S = "In Placement on Start Date";    
label W_PMT_DAY = "Adjusted Placement days";
run;

*identify those placements where aid code was Federal IVE at any time duing the placement in the 12 month study period;

proc sql;

create table stid as
select a.*,
       b.IDENTIFIER as STID_ID
from days_104x as a left join cws.ST_ID_T as b
on a.FKCLIENT_t =  b.FKCLIENT_T and
   b.START_DT   le wOH_E_DT and
 ( b.END_DT     = . or
   b.END_DT     ge wOH_S_DT )
;

create table staid as
select a.*,
       b.AID_TPC,
       b.START_DT as AID_START format mmddyy10.,
       b.END_DT as AID_END format mmddyy10.,
         case
           when b.AID_TPC = 44 then 1
           else 0
       end as IVE
from STID as a left join cws.STAIDC_T as b
on a.STID_ID  = b.FKST_ID_T and
   b.START_DT < (wOH_E_DT) and
 ( b.END_DT   = . or
   b.END_DT   ge wOH_S_DT )
order by FKCLIENT_T, SPELL, WOH_S_DT, PLCMNT, IVE
	  ; 
quit;

data days_104;
set staid;

by FKCLIENT_T SPELL wOH_S_DT PLCMNT IVE;
if last.PLCMNT;

run;

*find first placement for child in the study period;

data days_104a (keep = FKCLIENT_T PE_S_DT PE_E_DT IN_PE_S SPELL AGE wOH_S_DT AGENCY
                rename= (PE_S_DT = pES_S_DT PE_E_DT = pES_E_DT SPELL = SPELL_s));
set days_104;

by FKCLIENT_T SPELL OH_S_DT PLCMNT;
if first.FKCLIENT_T;

if PE_S_DT < yyq(&SYEAR,&SQTR) then IN_PE_S = 1;
else IN_PE_S = 0;

label IN_PE_S = "Episode Open on Start Date";

*** Age at later of first day or period or date first entered care in period ***;

*if BIRTH_DT ne . then AGE = int((wOH_S_DT-BIRTH_DT)/365.25);

if BIRTH_DT ne . then  AGE = int((intck('month',BIRTH_DT,wOH_S_DT)-(day(wOH_S_DT) lt day(BIRTH_DT)))/12);
else AGE = .;

if AGE = . or AGE lt 0 or AGE gt 20 then AGE=99;

label AGE = "Age at start of first placement in period";

run;

***** Bring in the assignment county *****;
*** Get the case county assignment at the beginning of first placement in study period ***;

proc sql;

create table county_at_sdate as
select A.FKCLIENT_T,
     /*A.PLCEP_ID,*/
       B.CNTY_SPFCD as case_cnty,
       B.CASEID,
       B.START_DT,
       B.END_TM,
       B.tEND_DT
from days_104a A, CASE_ASSIGN B
where A.FKCLIENT_T = B.FKCHLD_CLT and
      B.START_DT ne  . and
      B.START_DT <= wOH_S_DT <= B.tEND_DT
order by FKCLIENT_T, B.START_DT, B.tEND_DT, END_TM
;
quit;

*proc sort data = county_at_sdate; *by FKCLIENT_T START_DT tEND_DT END_TM; *run;
*proc print data = county_at_sdate (obs = 30); *format start_dt tend_dt mmddyy10.;
*run;

*** Where more than 1 county_at_sdate, use the most recent. ***;

data case_cnty ;
set county_at_sdate ;

by FKCLIENT_T ;
if last.FKCLIENT_T then output ;

drop START_DT tEND_DT END_TM;

run;

*** Get the referral county assignment at the period start date ***;
*revised from c2m3 to use refe_1a table;
proc sql;

create table REFE_2 as
select A.FKCLIENT_T,
       A.wOH_S_DT,
       B.CNTY_SPFCD as ref_cnty,
       B.START_DT,
       B.tEND_DT,
       B.END_TM
from days_104a A, refe_1a B
where A.FKCLIENT_T = B.FKCLIENT_T and 
      B.START_DT ne .  and 
      B.START_DT <= wOH_S_DT <= B.tEND_DT
order by FKCLIENT_T, B.START_DT, B.tEND_DT, END_TM;
quit;

data REFE_CNTY ;
set REFE_2 ;

by FKCLIENT_T ;
if last.FKCLIENT_T then output ;

drop START_DT tEND_DT END_TM wOH_S_DT;

run ;


*** Get the State ID county assignment at period start date***;

proc sql;

create table STID as
select /*A.PLCEP_ID,*/
     put(B.GVR_ENTC-1067,Z2.) as STID_CNTY length=2 ,
         A.FKCLIENT_T,
         B.START_DT,
         B.END_DT
from days_104a A, ST_ID B
where A.FKCLIENT_T eq B.FKCLIENT_T and
      B.START_DT ne . and
      A.AGENCY ne 1 and
      B.START_DT <= wOH_S_DT < B.END_DT
order by A.FKCLIENT_T, B.START_DT, B.END_DT
;
quit;

/* Where more than 1 STID-COUNTY, use the one with latest START_DT. */

data STID_CNTY ;
set STID ;

by FKCLIENT_T ;
if last.FKCLIENT_T then output ;

drop START_DT END_DT;

run ;

*** Join the county at end file with the original file ***;

proc sql;

create table cnty_1 as
select a.*, b.case_cnty
from days_104a as a left join case_cnty as b
on a.FKCLIENT_T = b.FKCLIENT_T ;

create table cnty_2 as
select a.*, b.ref_cnty
from cnty_1 as a left join refe_cnty as b
on a.FKCLIENT_T = b.FKCLIENT_T ;

create table cnty_3 as
select a.*, b.stid_cnty
from cnty_2 as a left join stid_cnty as b
on a.FKCLIENT_T = b.FKCLIENT_T ;

quit;

data county_104a(drop=CASE_CNTY REF_CNTY STID_CNTY NCASE_CNTY NREF_CNTY NSTID_CNTY);
set cnty_3;

NCASE_CNTY = input(CASE_CNTY, best2.);
NREF_CNTY  = input(REF_CNTY, best2.);
NSTID_CNTY = input(STID_CNTY, best2.);

if      1 <= NCASE_CNTY <= 58 then CNTY = NCASE_CNTY;
else if 1 <= NREF_CNTY <= 58 then CNTY = NREF_CNTY;
else if 1 <= NSTID_CNTY <= 58 and agency ne 1 then CNTY = NSTID_CNTY;
else CNTY  = '98';

if ctype = . then ctype = 2;

run;

*find last placement for child in the study period;

proc sort data=days_104;
by FKCLIENT_T SPELL PLCMNT;
run;

data days_104b (keep = FKCLIENT_T PE_S_DT PE_E_DT SPELL
       rename= (PE_S_DT = PEe_S_DT PE_E_DT = PEe_E_DT SPELL = SPELL_e));
set days_104;

by FKCLIENT_T SPELL PLCMNT;
if last.FKCLIENT_T;

run;

*find number of episodes in time period;

data days_104c (keep = FKCLIENT_T AGE IN_PE_S SPELL_CT CNTY);
merge county_104a days_104b;

by fkclient_t;

SPELL_CT = spell_e - spell_s + 1;
label SPELL_CT = "Number of episodes in study year";
run;

*create file with one row per placement;

data days_105 (keep = FKCLIENT_T CNTY_REM CNTY ETHNIC CENS_RC HISP_CDX GENDER P_PLC AGENCY 
                      W_PMT_DAY IN_OH_S IN_PE_S AGE SPELL_CT PERIOD_DT IVE);
merge days_104 days_104c;

by FKCLIENT_T;

PERIOD_DT=yyq(&SYEAR,&SQTR);

run;

data days_105a;
set days_105;

AGENCY = 4;

run;

data days_106;
set days_105 days_105a;
run;

data days_106a;
set days_106;

CNTY = 0;

run;

data days_107;
set days_106 days_106a;

run;


*create condensed file - this reduces the number of rows from ca 140,000 to 120,000;

proc sql;

create table sum_01 as
select CNTY, ETHNIC, /* CENS_RC, HISP_CDX, */ GENDER as GENDER_CD, P_PLC, AGENCY, 
       W_PMT_DAY, IN_PE_S, AGE, /* SPELL_CT, */ IVE, PERIOD_DT length=4, 
       COUNT(*) as count length=4 format = comma12.
from days_107
group by PERIOD_DT, CNTY, ETHNIC, /* CENS_RC, HISP_CDX, */ GENDER_CD, P_PLC, AGENCY, 
         W_PMT_DAY, IN_PE_S, AGE, /* SPELL_CT, */ IVE;
quit;

data sum_01_&SYEAR._Q&SQTR.;
set sum_01;

COUNT2 = W_PMT_DAY*COUNT;

run;
 
proc append base=PLCMNTDAYS data = sum_01_&SYEAR._Q&SQTR.;
run;
 

%end;
%end;
%mend days;

%days(1,1998,4,2025);

proc sql;

create table plcmntdays1 as
select CNTY, ETHNIC, GENDER_CD, P_PLC, AGENCY,
       IN_PE_S, AGE, IVE, PERIOD_DT length=4,
       sum(COUNT) as COUNT length=4 format = comma12.,
       sum(COUNT2) as COUNT2 length=4 format = comma12.
from plcmntdays
group by PERIOD_DT, CNTY, ETHNIC, GENDER_CD, P_PLC, AGENCY, IN_PE_S, AGE, IVE;

quit;

data dvlp.plcmntdays;
set plcmntdays1;

length CNTY ETHNIC GENDER_CD /* P_PLC */ AGENCY IN_PE_S AGE IVE PERIOD_DT COUNT COUNT2 4.;

label
AGENCY='Agency: CW,Prob.,Other,Total'
AGE='Age at start of period'
ETHNIC='Child Ethnicity'
GENDER_CD='Child Gender'
PERIOD_DT='The start of the period'
IVE='IV-E'
P_PLC='Placementy Type'
CNTY='County Responsible'
COUNT='Placement'
COUNT2='Placement Days'
IN_PE_S='Episode Open on Start Date'
;

run;

proc sort data = dvlp.plcmntdays;
by AGENCY CNTY PERIOD_DT;
run;

data test.plcmntdays;
set dvlp.plcmntdays;
run;