/****************************************************************************************
** Copyright (c) 2014 Center for Social Services Research,                             **
** University of California at Berkeley. All rights reserved.                          **
**                                                                                     **
** This program produces data for Case Ends 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                        **
**    2014.02.20 Modified placement exit program to case ending program j magruder     **
**    2014.03.18 Further modifications j magruder                                      ** 
**    2015.11.19 Updated Case Closure Reason                                           **
**                                                                                     ** 
****************************************************************************************/


proc sort data = dwh.UCB_CASE_SVC_COMP out = data1a;
    by fkchld_clt case_s_dt fkcase_t csc_s_dt;
    where CASE_E_DT ne . and CASE_E_DT <= &CUTOFF and RSP_AGY_CD in ("C" "P")
        and case_s_dt ge (birth_dt-30) and year(case_s_dt) > 1970;
    run;

proc print data = data1a (obs = 70);
    var fkchld_clt fkcase_t case_s_dt case_e_dt csc_s_dt csc_e_dt srv_cmpt case_e_cnty;
    run;

*keep last record for each case - i.e., showing county and case service component
 at the time of case closure - and make other usual transformations;
data data1 (drop = GENDER_CD);
    set data1a(drop=ETHNIC);
    by fkchld_clt case_s_dt fkcase_t csc_s_dt;
    if last.fkcase_t;
    

where RSP_AGY_CD in ("C", "P");

if CASE_E_DT = . or CASE_E_DT - CASE_S_DT >= 8 then FLAG_8 = 1;
else if CASE_E_DT - CASE_S_DT < 8 then FLAG_8 = 2;

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

TEMP_ID = FKCHLD_CLT || compress(put(CASE_CT,Z2.));

/* Agency Type */

select(RSP_AGY_CD);
   when("C") AGENCY = 1;         /* Child Welfare */
   when("P") 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,CASE_E_DT)-(day(CASE_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,CASE_E_DT)-(day(CASE_E_DT) lt day(birth_dt)))/12) <= 18.165 
        then AGE = 18;

/* Adding 302 as Adoption  7/30/15 */

**need case end reason type codes;
**put code here;;
select(CLS_RSNC);
    when (317) CLOSE_REAS = 1;                      /* Family Stabilized */
    when (313) CLOSE_REAS = 2;                      /* Court Ordered Termination */
    when (320, 321) CLOSE_REAS = 3;                 /* Reunification */
    when (302, 303, 304, 305) CLOSE_REAS = 5;       /* Adoption */
    when (318, 5950) CLOSE_REAS = 7;                /* Guardianship */
    when (315, 6543) CLOSE_REAS = 9;                /* Aged Out/Emancipation */
    when (322) CLOSE_REAS = 10;                     /* Refused Services */
    when (316) CLOSE_REAS = 11;                     /* Exceeded Time Limits */
    when (6541, 6542) CLOSE_REAS = 12;              /* NMD/NRLG Eligible for Reentry */
    when (308, 5634, 5635, 6307) CLOSE_REAS = 13;   /* Criminal Justice Systm Invlmnt */
    when (0, 5370, 312, .) CLOSE_REAS = 99;         /* Not Reported/Missing */
    otherwise CLOSE_REAS = 18;                      /* Other */
    end;


*Calculate months case open;
mo_in_care = ((case_e_dt - case_s_dt)/30.4375);
label mo_in_care = "Months Case Open";
format mo_in_care 5.1;
label age = "Age at Case Closure";
format birth_dt mmddyy10.;

run;


*any history of Family Reunification or Permanent Placement case (i.e., out of home care)?;
data ohc_01;
    set data1a;
    by fkchld_clt case_s_dt fkcase_t csc_s_dt;
    where srv_cmpt in("FR", "PP");
    run;

data ohc_02;
    set ohc_01;
    by fkchld_clt case_s_dt fkcase_t csc_s_dt;
    if last.fkcase_t;
    ohc_e_dt = csc_e_dt;
    format ohc_e_dt mmddyy10.;
    ohc_srv_cmpt = srv_cmpt;
    label ohc_e_dt = "Date Last OHC Service Component Ended";
    label ohc_srv_cmpt = "Last OHC Service Component Type";
    run;

proc sql;
    create table data2 as
        select data1.*,
            ohc_02.ohc_srv_cmpt,
            ohc_02.ohc_e_dt
        from data1 left join ohc_02
        on data1.fkcase_t = ohc_02.fkcase_t
    /*order by fkchld_clt, case_s_dt, fkcase_t*/
    order by temp_id;
    quit;

data data3;
    set data2;
    by temp_id;
    if ohc_e_dt ne . and ohc_e_dt < case_e_dt and srv_cmpt = "FM" 
        then srv_cmpt = "AP" ;
select(SRV_CMPT) ;
   when('ER') SRV_CMP=1 ;
   when('FM') SRV_CMP=2 ;
   when('AP') SRV_CMP=3 ;
   when('FR') SRV_CMP=4 ;
   when('PP') SRV_CMP=5 ;
   when('ST') SRV_CMP=6 ;
   otherwise SRV_CMP=99 ;
end ;
if ohc_srv_cmpt in ("PP", "FR") then ohc_hx = 1;
    else ohc_hx = 0;
label ohc_hx = "In FR or PP During Episode";
label srv_cmp = "Last Service Component";
    run;

/*
proc print data = data3 (obs = 30);
    var temp_id fkcase_t birth_dt case_s_dt case_e_dt srv_cmpt ohc_srv_cmpt ohc_e_dt mo_in_care;
    *where mo_in_care > 100;
    *where year(case_s_dt) ge 1910 and year(case_s_dt) le 1960;
    run;

proc freq data = data3;
    table ohc_srv_cmpt ohc_hx / missing;
    format ohc_hx yes_no.;
    run;

proc means data = data3 mean median n;
    var mo_in_care;
    class srv_cmpt;
    run;
*/
/*
*closure reason testing routine;
proc format;
    value close_reas
    1 = "Family Stabilized"
    2 = "Court Ordered"
    3 = "Runification"
    5 = "Adoption"
    7 = "Guardianship"
    9 = "Age/Emancipation"
    18 = "Other"
    99 = "Missing";

    value close_reasp
    1,3,5,7,9 = "Specific Reason"
    2,18,20 = "Non-Specific Reason";

    value srv_cmp
    1 = "ER"
    2 = "FM reg"
    3 = "FM Post FR/PP"
    4 = "FR"
    5 = "PP"
    6 = "ST"
    99 = "Missing";

    run;

proc tabulate data = data3 missing format = comma8.0;
    options pageno = 1;
    class close_reas srv_cmp;
    table close_reas=' ' all, srv_cmp*N=' ' all*N=' '
        /box = "Case Closure Reason";
    where year(case_e_dt) = 2009;
    format close_reas close_reas.;
    *format close_reas close_reasp.;
    format srv_cmp srv_cmp.;
    label srv_cmp = "Last Service Component";
    run;
*/

/* 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. ;
/*
*test periods;
%let syear = 2013;
%let sqtr = 1;
*/

data exit5;
*set exit4;
set data3;

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

*CNTY_ID = FKCHLD_CLT || compress(put(CASE_E_CNTY,Z2.));
cnty_id = fkchld_clt || case_e_cnty;

*if EXIT = . then EXIT = 2;

PERIOD_DT=yyq(&SYEAR,&SQTR);

run;

/* Add Unduplcate by county level */

proc sort data = exit5;
by CNTY_ID CASE_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;

CNTY = CASE_E_CNTY * 1;

run;

proc sql;

create table exit_cnty_1 as
select CNTY length=3, FLAG_8 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, CLOSE_REAS length=3, UNDUP length=3,
       PERIOD_DT length=4, AGENCY length=3, OHC_HX length=3,SRV_CMP length = 3,
       count(*) as COUNT length=4 format=comma12.
from exit7
group by CNTY, FLAG_8, AGENCY, AGE, GENDER_CD, ETHNIC, CENS_RC, 
        CENS_ETHNIC, HISP_CDX, CLOSE_REAS, UNDUP, PERIOD_DT, OHC_HX, SRV_CMP;

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 FKCHLD_CLT CASE_E_DT SORT_DT;
run;

data exit8b;
set exit8a;

by FKCHLD_CLT;
if last.FKCHLD_CLT;

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, AGE length=3,
       GENDER as GENDER_CD length=3, ETHNIC length=3, CLOSE_REAS length=3, CENS_RC length=3,
       CENS_ETHNIC length=3, HISP_CDX length=3, UNDUP length=3,
       PERIOD_DT length=4, AGENCY length=3, OHC_HX length=3, SRV_CMP length = 3,
       count(*) as COUNT2 length=4 format=comma12.
from exit8d
group by CNTY, FLAG_8, AGENCY, AGE, GENDER_CD, ETHNIC, CENS_RC, 
        CENS_ETHNIC, HISP_CDX, CLOSE_REAS, UNDUP, PERIOD_DT, OHC_HX, SRV_CMP;


create table exit_state_2 as
select CNTY length=3, FLAG_8 length=3, AGE length=3,
       GENDER as GENDER_CD length=3, ETHNIC length=3, CLOSE_REAS length=3, CENS_RC length=3,
       CENS_ETHNIC length=3, HISP_CDX length=3, UNDUP length=3,
       PERIOD_DT length=4, AGENCY length=3, OHC_HX length=3, SRV_CMP length = 3,
       count(*) as COUNT length=4 format=comma12.
from exit8
group by CNTY, FLAG_8, AGENCY, AGE, GENDER_CD, ETHNIC, CENS_RC, 
        CENS_ETHNIC, HISP_CDX, CLOSE_REAS, UNDUP, PERIOD_DT, OHC_HX, SRV_CMP;

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.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.CLOSE_REAS  = y.CLOSE_REAS and
   x.PERIOD_DT   = y.PERIOD_DT and
   x.AGENCY      = y.AGENCY and
   x.OHC_HX      = y.OHC_HX and
   x.SRV_CMP     = y.SRV_CMP

group by x.CNTY, x.FLAG_8, x.AGENCY, x.AGE, x.GENDER_CD, x.ETHNIC, 
        x.CENS_RC, x.CENS_ETHNIC, x.HISP_CDX, x.CLOSE_REAS, x.UNDUP, x.PERIOD_DT, x.OHC_HX,
        x.SRV_CMP;

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,4,2024);

data dvlp.case_close;
set county state;

if cnty = 99 then cnty = 98;

run;

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

data test.case_close;
set dvlp.case_close;
run;