/****************************************************************************************
** Copyright (c) 2007 Center for Social Services Research,                             **
** University of California at Berkeley. All rights reserved.                          **
**                                                                                     **
** This program produces data for Exits Per Year Reports                               **
**                                                                                     **
** Exits                                                                               **
**                                                                                     **
**                                                                                     **
** Programmer: S. Sean Lee                                                             **
**                                                                                     **
**    2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group               **
**    2009.10.15 Modifying undup by child level to county level                        **
**    2024.05.31 Add Transitional Housing and Indian status                            **
****************************************************************************************/
*%include "/pool01/home/joemagruder/1basic_start.sas";

data data1;
set dwh.UCB_FC_AFCARS(drop=ETHNIC);

where PE_E_DT ne . and PE_E_DT <= &CUTOFF and AGY_RSPC not in (0036, 5605, 6133, 6134);

if PE_E_DT = . or PE_E_DT - PE_S_DT >= 8 then FLAG_8 = 1;
else if PE_E_DT - PE_S_DT < 8 then FLAG_8 = 2;

if OH_E_DT = . then SORT_DT = &DODEX; /* Date of data Extract */
else SORT_DT = OH_E_DT;

TEMP_ID = FKCLIENT_T || compress(put(SPELL,Z2.));

/* Agency Type */

select(AGY_RSPC);
   when(34) AGENCY = 1;         /* Child Welfare */
   when(33, 5603) AGENCY = 2;   /* Probation */
   otherwise AGENCY = 3;        /* Other */
end;

/* 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;

/* Assign Ethnic 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 */

/* Assign Census Ethnicity Class */

     if HISP_CDX = 0 and CENS_RC = 1 then CENS_ETHNIC = 1;  /* Black */
else if HISP_CDX = 0 and CENS_RC = 2 then CENS_ETHNIC = 2;  /* White */
else if HISP_CDX = 0 and CENS_RC = 4 then CENS_ETHNIC = 4;  /* Asian/PI */
else if HISP_CDX = 0 and CENS_RC = 5 then CENS_ETHNIC = 5;  /* Nat Amer */
else if HISP_CDX = 0 and CENS_RC = 8 then CENS_ETHNIC = 8;  /* Mixed */
else if HISP_CDX = 0 and CENS_RC = 9 then CENS_ETHNIC = 9;  /* Missing */
else if HISP_CDX = 1                 then CENS_ETHNIC = 10; /* Hispanic */

AGE = int((intck('month',birth_dt,pe_e_dt)-(day(pe_e_dt) lt day(birth_dt)))/12);

if AGE lt 0 or AGE gt 21 then AGE = 99;

if AGE = 18 then AGE = 18.5;
if 18 <= ((intck('month',birth_dt,pe_e_dt)-(day(pe_e_dt) lt day(birth_dt)))/12) <= 18.165 then AGE = 18;

/* Last Placement Type */
*add transitional housing 2024.05.31 j magruder;
if TRNHSG_FAC = 'Y' then P_PLC = 20;    /* Transitional Housing */
else 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 = '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 */

/* Caregiver Relationship */

select(SCP_RLTC);
   when('1636') P_SCPR=1;
   when('1637') P_SCPR=2;
   when('1638') P_SCPR=3;
   when('1639') P_SCPR=4;
   when('6715') P_SCPR=5;
   when('6990') P_SCPR=6;              /* NREFM Guardian */
   when('6991') P_SCPR=7;              /* NREFM Nonguardian */
   otherwise P_SCPR = 99;              /* Missing */
end;

/* Termination Reason Type */

if TERM_TY_C in (.,0) and PE_E_DT = . then EXIT_TYPE=7;                          /* Still in care */
else 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=4;                              /* Guard */
else if TERM_TY_C in (5427, 5433, 5506, 5510, 6803, 6804, 
                      6805, 6806, 6812, 6813, 6814, 6815) then EXIT_TYPE=5;      /* Aged Out/Emancip. */
else EXIT_TYPE=6;                                                                /* Other */



/*Add ICWA and Tribal status codes 2024.05.31 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;
 
run;

/* Add in KIN GAP exits */
/* [1] project closed to kingap cases [5950=kingap] */

data test1(keep = FKCHLD_CLT START_DT END_DT CNTY_SPFCD);
set cws.CASE_T;
where CLS_RSNC = 5950;
run;

/* Determine which records link to case closure to KG */

proc sql;
create table test2 as
select a.FKCLIENT_T, a.CNTY_REM, a.PE_S_DT, a.PE_E_DT, /* a.A34_SPELL_5PLUS, */ a.PLCMNT, a.TEMP_ID,
b.START_DT as CAS_STRT, b.END_DT as KG_START
from data1 as a, test1 as b
where a.FKCLIENT_T=b.FKCHLD_CLT;
quit;

/* Code as KG those records where closure to KG within 60 days of spell end */

data test3;
set test2;

where PE_E_DT ne . and KG_START-PE_E_DT ge -60 and KG_START-PE_E_DT le 60;

kingap='1';
keep FKCLIENT_T TEMP_ID KINGAP KG_START ;

run;

proc sort data = test3;
by TEMP_ID KG_START KINGAP;
run;

data test4;
set test3;

by TEMP_ID;
if last.TEMP_ID;

run;

proc sql;
create table data2 as
select x.*, y.kingap
from data1 as x left join test4 as y
on x.TEMP_ID=y.TEMP_ID;
quit;

data exit1;
set data2;

if kingap='1' then EXIT_TYPE=3;   /* Kin-Gap */

/*
if PLC_FCLC = '2222' and SCP_RLTC = 1639 then kinbin = 1;
else if PLC_FCLC in ('1421','1422') then kinbin = 1;
else if PLC_FCLC in (' ', '  ', '.', '0') then kinbin = 3;
else kinbin = 2;
*/

run;

/* County */

/* Case County */

data assign;
   set cws.ASGNM_T;

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

run;

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

proc sql;

   create table CASE_ASSIGN as
   select x.FKCHLD_CLT,
          x.IDENTIFIER as caseid,
          y.START_DT,
          y.END_DT,
          y.END_TM,
          y.CNTY_SPFCD
   from cws.CASE_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'C'
   order by FKCHLD_CLT, START_DT, END_DT, END_TM;

quit;

*** Get the County from the active case for the dataset of interest ***;

proc sql;

   create table ca1 as
   select A.FKCLIENT_T,
          A.TEMP_ID,
          B.CNTY_SPFCD as ASSIGN_CASE_CNTYSPFCD,
          b.caseid,
          B.START_DT,
          B.END_TM,
          B.END_DT
   from exit1 A, CASE_ASSIGN B
   where A.FKCLIENT_T eq B.FKCHLD_CLT and
         B.START_DT ne . and
         B.START_DT <= PE_E_DT <= B.END_DT
   order by A.TEMP_ID, B.START_DT, B.END_DT, END_TM
   ;
quit;

proc sort data = ca1; by TEMP_ID START_DT END_DT END_TM; run;

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

data ca2 ;
   set ca1 ;
   by TEMP_ID ;
   if last.TEMP_ID then output ;
   drop START_DT END_DT END_TM;
run ;

*** Join the ASSIGN_COUNTY back to the original file ***;

proc sql;

   create table ca3 as
   select x.*,
          y.CASEID,
          y.ASSIGN_CASE_CNTYSPFCD
   from exit1 as x left join ca2 as y
   on x.TEMP_ID = y.TEMP_ID
     ;
quit;

*** Referral County ***;

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

proc sql;

   create table REFE_ASSIGN as
   select x.IDENTIFIER as REFID,
          y.START_DT,
          y.END_DT,
          y.END_TM,
          y.CNTY_SPFCD
   from cws.REFERL_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'R'
   order by refid, START_DT, END_DT, END_TM;

quit;


*** To go through the referrals to the ASSIGNMENT table, first link to the
    REFER_CLT table in order to use FKREFERL_T to link to the REFERL_T.
***;

proc sql;

   create table getrefassign1 as
   select A.FKCLIENT_T,
          A.TEMP_ID,
          A.PE_E_DT,
          B.FKREFERL_T
   from ca3 A, CWS.REFR_CLT B
   where A.FKCLIENT_T eq B.FKCLIENT_T;

quit;

*** Link to the REFERL_T table using FKREFERL_T ***;

proc sql;

   create table getrefassign2 as
   select A.FKCLIENT_T,
          A.TEMP_ID,
          A.PE_E_DT,
          B.CNTY_SPFCD as ASSIGN_REF_CNTYSPFCD,
          B.REFID,
          B.START_DT,
          B.END_DT,
          B.END_TM
   from getrefassign1 A, REFE_ASSIGN B
   where A.FKREFERL_T eq B.REFID and
         B.START_DT ne . and
         B.START_DT <= A.PE_E_DT <= B.END_DT
   order by FKCLIENT_T, B.START_DT, B.END_DT, END_TM;
quit;

proc sort data = getrefassign2; by TEMP_ID START_DT END_DT; run;

* Where more than 1 CNTY_REF, use the one with latest REF_RCV_DT. *;

data getrefassign3 ;
   set getrefassign2 ;
   by TEMP_ID ;
   if last.TEMP_ID then output ;
   drop START_DT END_DT END_TM;
run ;

proc sql;

   create table ca4 as
   select x.*,
          y.REFID,
          y.ASSIGN_REF_CNTYSPFCd
   from ca3 as x left join getrefassign3 as y
   on x.TEMP_ID = y.TEMP_ID;

quit;

*** State ID County ***;

data ST_ID;
set CWS.ST_ID_T;

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

run ;

proc sql;

create table temp3a as
select
A.TEMP_ID,
B.GVR_ENTC as STID_GVRENTC,
A.FKCLIENT_T,
B.START_DT,
B.END_DT
from ca4 A, ST_ID B
where A.FKCLIENT_T eq B.FKCLIENT_T and
B.START_DT ne . and
B.START_DT <= A.PE_E_DT < B.END_DT
order by TEMP_ID, B.START_DT, B.END_DT
;

proc sort data = temp3a;
by TEMP_ID START_DT END_DT;
run;

data temp3b;
   set temp3a;

   by TEMP_ID;
   if last.TEMP_ID;

run;

proc sql;

   create table temp4 as
   select x.*, y.STID_GVRENTC
   from ca4 as x left join temp3b as y
   on x.TEMP_ID = y.TEMP_ID;

quit;

data exit2(drop=GENDER_CD);
   set temp4 ;

   *** Accept values from the CASE_ASSIGN link first ***;
   if assign_case_cntyspfcd not in (' ','99') then
      CNTY=input(ASSIGN_CASE_CNTYSPFCD,best2.);
   else if ASSIGN_CASE_CNTYSPFCD='99' then CNTY=98;
   else do;
      *** If there is no valid CASE_ASSIGN (1-58 or 99) then
          look at the REFE_ASSIGN link to fill in the county ***;
      if ASSIGN_REF_CNTYSPFCD not in (' ','99') then
         CNTY=input(ASSIGN_REF_CNTYSPFCD,best2.);
      else if ASSIGN_REF_CNTYSPFCD = '99' then CNTY=98;
   end;
   *** If all else fails then assign the assignment county to missing ***;
   if CNTY=. then CNTY=98;

   *** For non-foster care children also use the STATE_ID county ***;
   if AGY_RSPC ne 34 then do;
      non_cw=1;
      if 1068 le stid_gvrentc le 1126 then stid_county=stid_gvrentc-1067;
          else stid_county=98;   **used to be 98 ***;

      if CNTY in (., 0,59,98) and
             (1 le stid_county le 58) then CNTY=stid_county;
   end;

run;

proc sort data = exit2;
by TEMP_ID PE_E_DT SORT_DT;
run;

data exit3;
set exit2;

where PE_E_DT ne . and PE_E_DT < &CUTOFF;

by TEMP_ID;
if last.TEMP_ID;

run;

proc sort data = exit3;
by FKCLIENT_T PE_E_DT SORT_DT;
run;

data exit3a;
set exit3;

by FKCLIENT_T;
if last.FKCLIENT_T;

run;

data exit3b;
set exit3a;

EXIT = 1;

run;


proc sql;

create table exit4 as
select x.*, y.EXIT
from exit3 as x left join exit3b as y
on x.TEMP_ID = y.TEMP_ID;

quit;

/* macro is called at end of file... */

%macro EXIT(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. ;

data exit5;
set exit4;

where PE_E_DT ne . and PE_E_DT between yyq(&SYEAR,&SQTR) and yyq(&SYEAR+1,&SQTR)-1;

CNTY_ID = FKCLIENT_T || compress(put(CNTY,Z2.));

if EXIT = . then EXIT = 2;

PERIOD_DT=yyq(&SYEAR,&SQTR);

run;

/* Add Unduplcate by county level */

proc sort data = exit5;
by CNTY_ID PE_E_DT SORT_DT;
run;

data exit5a;
set exit5;

by CNTY_ID;
if last.CNTY_ID;

UNDUP = 1;

run;

/* Add UNDUP back to main data */

proc sql;

create table exit6 as
select x.*, y.UNDUP length=3
from exit5 as x left join exit5a as y
on x.TEMP_ID = y.TEMP_ID;

quit;

data exit6a;
set exit6(drop=AGENCY);

AGENCY = 4;

run;

data exit7;
set exit6 exit6a;

where AGE ne 99;

run;

proc sql;

create table exit_cnty_1 as
select CNTY length=3, FLAG_8 length=3, EXIT length=3, AGE length=3,
       GENDER as GENDER_CD length=3, ETHNIC length=3, CENS_RC length=3, 
       CENS_ETHNIC length=3, HISP_CDX length=3, EXIT_TYPE length=3, UNDUP length=3,
       P_PLC, P_SCPR length=3, PERIOD_DT length=4, AGENCY length=3,
	   ICWA length=4, TRIBAL_STC length=4,
       count(*) as COUNT length=4 format=comma12.
from exit7
group by CNTY, FLAG_8, EXIT, AGENCY, AGE, GENDER_CD, ETHNIC, CENS_RC, CENS_ETHNIC, HISP_CDX, 
	EXIT_TYPE, UNDUP, P_PLC, P_SCPR, ICWA, TRIBAL_STC, PERIOD_DT;

quit;

data exit_cnty_&SYEAR.Q&SQTR;
length COUNT2 3;
set exit_cnty_1;

COUNT2 = COUNT;

run;


data exit8;
set exit7(drop=CNTY);

CNTY=0;

run;

proc sort data=exit6 out=exit8a;
by FKCLIENT_T PE_E_DT SORT_DT;
run;

data exit8b;
set exit8a;

by FKCLIENT_T;
if last.FKCLIENT_T;

UNDUP=1;
CNTY=0;

run;

data exit8c;
set exit8b;

AGENCY = 4;

run;

data exit8d;
set exit8b exit8c;

where AGE ne 99;

if UNDUP = . then UNDUP = 0;

run;

proc sql;

create table exit_state_1 as
select CNTY length=3, FLAG_8 length=3, EXIT length=3, AGE length=3,
       GENDER as GENDER_CD length=3, ETHNIC length=3, EXIT_TYPE length=3, CENS_RC length=3,
       CENS_ETHNIC length=3, HISP_CDX length=3, UNDUP length=3,
       P_PLC, P_SCPR length=3, PERIOD_DT length=4, AGENCY length=3,
	   ICWA length=4, TRIBAL_STC length=4,
       count(*) as COUNT2 length=4 format=comma12.
from exit8d
group by CNTY, FLAG_8, EXIT, AGENCY, AGE, GENDER_CD, ETHNIC, CENS_RC, CENS_ETHNIC, HISP_CDX, 
	EXIT_TYPE, UNDUP, P_PLC, P_SCPR, ICWA, TRIBAL_STC, PERIOD_DT;


create table exit_state_2 as
select CNTY length=3, FLAG_8 length=3, EXIT length=3, AGE length=3,
       GENDER as GENDER_CD length=3, ETHNIC length=3, EXIT_TYPE length=3, CENS_RC length=3,
       CENS_ETHNIC length=3, HISP_CDX length=3, UNDUP length=3,
       P_PLC, P_SCPR length=3, PERIOD_DT length=4, AGENCY length=3,
	   ICWA length=4, TRIBAL_STC length=4,
       count(*) as COUNT length=4 format=comma12.
from exit8
group by CNTY, FLAG_8, EXIT, AGENCY, AGE, GENDER_CD, ETHNIC, CENS_RC, CENS_ETHNIC, HISP_CDX, 
	EXIT_TYPE, UNDUP, P_PLC, P_SCPR, ICWA, TRIBAL_STC, PERIOD_DT;

create table exit_state_&SYEAR.Q&SQTR as
select x.*, y.COUNT2
from exit_state_2 as x left join exit_state_1 as y
on x.CNTY        = y.CNTY and
   x.FLAG_8      = y.FLAG_8 and
   x.EXIT        = y.EXIT and
   x.AGE         = y.AGE and
   x.UNDUP       = y.UNDUP and
   x.GENDER_CD   = y.GENDER_CD and
   x.ETHNIC      = y.ETHNIC and
   x.CENS_RC     = y.CENS_RC and
   x.CENS_ETHNIC = y.CENS_ETHNIC and
   x.HISP_CDX    = y.HISP_CDX and
   x.EXIT_TYPE   = y.EXIT_TYPE and
   x.P_PLC       = y.P_PLC and
   x.PERIOD_DT   = y.PERIOD_DT and
   x.AGENCY      = y.AGENCY and
   x.P_SCPR      = y.P_SCPR and
   x.ICWA        = y.ICWA and
   x.TRIBAL_STC  = y.TRIBAL_STC

group by x.CNTY, x.FLAG_8, x.EXIT, x.AGENCY, x.AGE, x.GENDER_CD, x.ETHNIC, x.CENS_RC, x.CENS_ETHNIC, x.HISP_CDX, x.EXIT_TYPE, 
         x.UNDUP, x.P_PLC, x.P_SCPR, x.ICWA, x.TRIBAL_STC, x.PERIOD_DT;

quit;

proc append base=county data=exit_cnty_&SYEAR.Q&SQTR;
proc append base=state data=exit_state_&SYEAR.Q&SQTR;

%end;
%end;
%mend EXIT;

%EXIT(1,1998,1,2024);

data dvlp.exits;
set county state;
run;

proc sort data = dvlp.exits;
by FLAG_8 AGENCY EXIT PERIOD_DT CNTY;
run;

data test.exits;
set dvlp.exits;
run;