/* x_contacts.sas */
/* Measure 2S */
/* Create file of contacts by social workers with children from 1997 to present
 * limiting to one contact per month;
 * j magruder 2013.04.25
 */

/**************************************************************/
/* use correct extract control file */
/*** NEED TO CHANGE ***/
%include "/dss/SAS/PROGRAM/CWS_CMS/Q4_2024/autoexec.sas" ;
/**************************************************************/

libname DATA2S "/pool01/NOSAVE/REPORT_DATA/2S" ;

/* find those delivered services that are delivered in person and
 * have been completed - as opposed to scheduled or attempted
 * All services are included for all time periods, even though many to children not in
 * care or by others than social workers
 */

data contacts_01 
        (keep = start_dt cnt_loc identifier in_home cdsyear cdsmonth
         rename=(start_dt = cdsdate cnt_loc = cdsplace identifier = cdsid));
    set cws.dl_svc_t;
    if cmm_mthc = 408
        and status_cd = "C" /*contact completed*/
        and year(start_dt) ge 1997
        and year(start_dt) le 2024;  /*CHANGE AS NEEDED*/
    if cnt_loc in (417) then in_home = 1; *identify in-home contacts;
        else in_home = 0;
    cdsyear = year(start_dt);
    cdsmonth = month(start_dt);
    run;

/* add child client identifier, limit file to contacts that are with a client
 * (del_idv_cd = "C", are staff person-child contacts (cnt_prtc = 425), and
 * are with a client who is on the child client (chld_clt) table
 */
proc sql;
    create table contacts_02 as
        select contacts_01.*,
            idv_svct.del_idv_id as fkclient_t,
            idv_svct.del_idv_cd
        from contacts_01,
            cws.idv_svct,
            cws.chld_clt,
            cws.cpty_svt
        where contacts_01.cdsid = idv_svct.fkdl_svc_t
            and contacts_01.cdsid = cpty_svt.fkdl_svc_t
            and idv_svct.del_idv_id = chld_clt.fkclient_t
            and idv_svct.del_idv_cd = "C"
            and cpty_svt.cnt_prtc = 425 
        order by fkclient_t, cdsyear, cdsmonth, in_home desc;
    quit;

/* Identify one contact (meeting above criteria) per month giving priority to in-home contact.
 * Resulting file has one row per child client per month from 1997 to present,
 * indicating whether there was a contact (row present) and whether that contact was
 * in the home (in-home = 1). This does not identify whether the child was in care,
 * but assumption is that if child in care for the entire month was seen in home, that contact was
 * in the placement facility.
 */
proc sort data = contacts_02;
    by fkclient_t cdsyear cdsmonth descending in_home;
    run;

data DATA2S.contacts ;
    set contacts_02;
    by fkclient_t cdsyear cdsmonth descending in_home;
    if first.cdsmonth;
    run;

/* DIAGNOSTIC */
/* 
proc print data = DATA2S.contacts (obs = 40);
   var fkclient_t cdsdate in_home cdsyear cdsmonth;
   run;
*/
/* case_assign.sas */
/* Measure 2S */
/* retrieve case-level records to later assign county at required date
 * M. Armijo
 * 4/30/2013
 */

/*** NEED TO CHANGE ***/
%include "/pool01/vol01/SAS/PROGRAM/CWS_CMS/Q4_2024/autoexec.sas" ;

libname DATA2S "/pool01/NOSAVE/REPORT_DATA/2S" ;

proc sql;

   create table CASE_ASSIGN as
    select x.FKCHLD_CLT,
           x.IDENTIFIER as caseid,
           /*x.CNTY_SPFCD as assign_cntyspfcd2, */
           y.START_DT as assign_start format mmddyy10.,
           y.END_DT as assign_end format mmddyy10.,
           y.END_TM as assign_endtm,
           y.CNTY_SPFCD as assign_cntyspfcd   
    from cws.CASE_T as x, cws.ASGNM_T as y
    where x.IDENTIFIER = y.ESTBLSH_ID
          and y.ASGNMNT_CD = 'P'
          and y.ESTBLSH_CD = 'C' 
    order by x.fkchld_clt, assign_start, assign_end, assign_endtm;

quit;

data DATA2S.case_assign;
   set case_assign;
   if assign_end=. then assign_end='31dec3000'd;
run;
/* x_monthly_db.sas */
/* Measure 2S */
/* For each month, identify children with an open case for the entire month, and determine
 * if they had a visit during tht month, and if they were in a placement.
 * Analyst: J. Magruder 
 * Code structure borrowed from measure 2F.
 * 2F uses UCB_FC_AFCARS to retrieve all children in out-of-home care fir the entire analysis 
 * period (month).
 * 2S uses UCB_CASE_SVC_COMP to retrieve all children with an open case
 * for the entire analysis period. Children with an out-of-home 
 * placement during the period are then removed (UCB_FC_AFCARS used for this purpose).
 * Revision History:
 * 2016.03.29 J. Magruder.
 * To better exclude non-dependent legal guardianship placements, using UCB_FC rather than UCB_FC_AFCARS
 * for determining if child had an out-of-home placement during the period.
 * Restrict to: agy_rspc in (33, 34, 5602, 5603, 5607) for determining if child had any 
 * days in FC during the period.
 */ 

options MPRINT NOSOURCE ;

/* identify source data (extract) here */
/*** NEED TO CHANGE ***/
%let YEAR=2024 ;
%let QTR=Q4 ;

/* specify time periods for analysis here */
/*** NEED TO CHANGE ***/
%let firstyear=1998 ;
%let lastyear=2024 ;
%let lastmonth=12 ; /* lastmonth values are one of 3, 6, 9, 12 */

%include "/dss/SAS/PROGRAM/CWS_CMS/&QTR._&YEAR/autoexec.sas" ;
libname DATA2S "/pool01/NOSAVE/REPORT_DATA/2S" ;

%macro age(date,birth);
  floor ((intck('month',&birth,&date)
  - (day(&date) < day(&birth))) / 12) 
%mend age;

/* Process each month (1998 to present) to identify children in care for the entire month.
 * For these children, determine if they had visit during the month, 
 * and if that visit was in the home.
 */

/* MIA remove master database (if present) */

proc sql ;
drop table DATA2S.monthly_db ;
quit ;

%global xyear xmonth ; 

%macro bymonth (xyear, xmonth);

/* 2F language modified for 2S:;
 * children with no birth_dt (thus no age) excluded - these are children for whom there is no
 * entry on the case table [available to UCB]
 */
data month_01 (keep = fkchld_clt fkcase_t birth_dt case_s_dt case_e_dt csc_s_dt rsp_agy_cd
     srv_cmpt case_e_cnty age_sm ethnic gender_cd month_s_dt month_e_dt);
    set DWH.UCB_CASE_SVC_COMP;
    month_s_dt = mdy(&xmonth,1,&xyear) ; /* first day of month */
    month_e_dt = intnx('month',month_s_dt,1, 'sameday')-1 ; /* last day of month */
    age_sm = %age(mdy(&xmonth,1,&xyear),BIRTH_DT); /* compute age on 1st day of month */
    if age_sm le 17 and age_sm ne .;
    if (case_s_dt le month_s_dt and ((case_e_dt ge month_e_dt) or (case_e_dt eq .)))
        and csc_s_dt le month_e_dt
        and case_s_dt ge BIRTH_DT 
        and rsp_agy_cd = "C";
    format month_s_dt month_e_dt mmddyy10.;
    run;

proc sort data = month_01;
    by fkchld_clt csc_s_dt;
    run;

*record of last case service component in month;
data month_02a;
    set month_01;
    by fkchld_clt csc_s_dt;
    if last.fkchld_clt;
    run;


/* get contact records for these cases */
proc sql;
    create table visit_01 as
        select A.*,
        B.in_home,
        B.cdsyear,
        B.cdsmonth,
        B.cdsdate format mmddyy10.
    from month_02a A left join DATA2S.contacts B
    on A.fkchld_clt = B.fkclient_t
        and cdsyear = &xyear.
        and cdsmonth = &xmonth.
    order by fkchld_clt;

/* find if visit in month */
data visit_02 ;
    set visit_01 ;
    if cdsmonth ne . then visit=1 ;
    run;

/* Append to master database */
proc datasets NOLIST ;
append base=monthly_db0 data=visit_02 ;
run ;

%mend bymonth ;

/* repeat for each month of each year... */
%macro dateloop ;
%do xyear = &firstyear %to &lastyear ;
   %do xmonth = 1 %to 12 ;
      %bymonth(&xyear, &xmonth) ;
      %if &xyear=&lastyear and &xmonth=&lastmonth %then %goto finished;
   %end;
%end;
%finished: %mend dateloop ;

%dateloop ;


/* revision 2016.03.29 J. Magruder. */
/* from the monthly open-case set, select those observations with an 
 * out-of-home placement during the entire month.
 * these will be withdrawn from the analysis.
 */

proc sql ;
create table in_fc as
select distinct
 A.month_s_dt
,A.FKCHLD_CLT
from monthly_db0 A, /*DWH.UCB_FC_AFCARS*/ DWH.UCB_FC B  /*Change back to UCB_FC_AFCARS for Q4 2023*/
where
(A.FKCHLD_CLT eq B.FKCLIENT_T) and
(A.month_s_dt ge B.PE_S_DT and ((A.month_e_dt le B.PE_E_DT) or (B.PE_E_DT eq .)))
and b.agy_rspc in (33, 34, 5602, 5603, 5607) 
order by A.month_s_dt, A.FKCHLD_CLT
;

/* end revision */

/* remove the out-of-home placement months */
proc sql ;
create view open_cases as
select 
 month_s_dt
,FKCHLD_CLT
from monthly_db0
;

create table NO_FC as
select *
from open_cases
except all 
select *
from IN_FC
;

create table DATA2S.monthly_db as
select
 A.FKCASE_T
,A.AGE_SM
,A.CSC_S_DT
,A.SRV_CMPT
,A.FKCHLD_CLT as FKCLIENT_T
,A.RSP_AGY_CD
,A.CASE_S_DT
,A.CASE_E_DT
,A.BIRTH_DT
,A.GENDER_CD
,A.ETHNIC
,A.CASE_E_CNTY
,A.month_s_dt
,A.month_e_dt
,A.visit length=4
,A.in_home length=4
from monthly_db0 A, NO_FC B
where A.FKCHLD_CLT eq B.FKCHLD_CLT
and
A.month_s_dt eq B.month_s_dt
order by month_s_dt, FKCLIENT_T
;

/* Add the county responsible for client at start of month */
proc sql;
   create table CNTY_RESP as
   select
      A.*,
      B.assign_cntyspfcd as assign_cnty,
      B.assign_start
   from DATA2S.monthly_db as A left join DATA2S.case_assign as B
   on A.fkclient_t eq B.fkchld_clt
   and A.month_s_dt between B.assign_start and B.assign_end
order by month_s_dt, fkclient_t, assign_start
;
quit;

/* Where there are multiple county assignments, use the last one.
 */

data DATA2S.monthly_db ;
set CNTY_RESP ;
by month_s_dt fkclient_t ;
if last.fkclient_t ;
drop assign_start ;
run ;
/* totals2SX.sas */
/* Measure 2SX
 * From the derived table monthly_db, 
 * generate totals at the state and county levels.
 * M. Armijo
 * 2014.07.28
 */

libname DATA2S "/pool01/NOSAVE/REPORT_DATA/2S" ;


/* Aggregate using defined analysis intervals */ 

proc sql ;
create table state_total as
select distinct
/* columns */
 AGE_SM as AGE length=4
,ETHNIC
,GENDER_CD 
,MONTH_S_DT as PERIOD_DT length=4
,SRV_CMPT
,count(*) as VISITS_D             /* denominator */
,sum(visit) as VISITS_N           /* numerator 1 */
,sum(in_home) as VISITS_NR        /* numerator 2 */
from DATA2S.monthly_db
group by
/* dimensions */
 AGE
,ETHNIC
,GENDER_CD
,PERIOD_DT
,SRV_CMPT
;

/* Assign the value 0 to county to indicate state total */
data state_total ;
set state_total ;
assign_cnty='00' ;
run ;

/* county totals */
proc sql ;
create table cnty_total as
select distinct
/* columns */
 AGE_SM as AGE length=4
,ASSIGN_CNTY
,ETHNIC
,GENDER_CD 
,MONTH_S_DT as PERIOD_DT length=4
,SRV_CMPT
,count(*) as VISITS_D             /* denominator */
,sum(visit) as VISITS_N           /* numerator 1 */
,sum(in_home) as VISITS_NR        /* numerator 2 */
from DATA2S.monthly_db
group by
/* dimensions */
 AGE
,ASSIGN_CNTY
,ETHNIC
,GENDER_CD
,PERIOD_DT
,SRV_CMPT
;

data DATA2S.TOTALS2SX ; 
set state_total cnty_total ;
run ;
/* 2SX_idx.sas */
/* Produce report db from output of (totals2SX.sas).
 * Adjust values as specified by the report requirements.
 * M. Armijo
 * Revision History
 * 2014.07.28 First Revision
 */

%let DATA_VERSA=2024Q4Dvlp;
%let DATA_VERSB=2024Q4Test;

libname DATA2S "/pool01/NOSAVE/REPORT_DATA/2S" ;
libname CFSR "/ssa3/SAS/REPORT_DATA/&DATA_VERSA/CFSR" ;
libname Test "/ssa3/SAS/REPORT_DATA/&DATA_VERSB/CFSR" ;



data PASS1 ;
length ETHNIC $2 ;
set DATA2S.TOTALS2SX ;

/* restrict to age 0-17 */
if AGE ge 0 and AGE le 17 ;

select(gender_cd) ;
  when('F') GENDER=1 ;
  when('M') GENDER=2 ;
  when('I') GENDER=3 ;
  otherwise GENDER=99 ;
end;

/* use current convention for missing */
if ETHNIC eq '0' then ETHNIC='99' ;

/* standardize value for missing county assignment */
if assign_cnty in('99',' ') then assign_cnty='98' ;

select(SRV_CMPT) ;
  when('ER') SRV_CMP=1 ; /* Emergency Response */
  when('FM') SRV_CMP=2 ; /* Family Maintenance */
  when('FR') SRV_CMP=3 ; /* Family Reunification */
  when('PP') SRV_CMP=4 ; /* Permanent Placement */
  when('ST') SRV_CMP=5 ; /* Supportive Transition */
  otherwise SRV_CMP=99 ; /* Missing */
end ;

AGENCY=1 ;

run ;

proc sql ;
create table CFSR.CDSS_2SX as
select
 period_dt as PERIOD_DT length=4 label="Period"  
,input(assign_cnty,2.) as CNTY length=3 label="SupervisingCounty"
,AGE as AGE length=4 label="Age" 
,AGENCY length=4 label="Agency"
,input(ethnic,2.) as ETHNIC length=4 label="Ethnicity"
,GENDER as GENDER_CD length=4 label="Gender"
,SRV_CMP length=4 label="Service Component Type"
,VISITS_D label="Children Receiving In-Home Services Entire Month"
,VISITS_N label="Children Visited"
,VISITS_NR label="Children Visited in Residence"
from PASS1
order by PERIOD_DT, CNTY
;

create index PERIOD_DT on CFSR.CDSS_2SX(PERIOD_DT) ;
create index CNTY on CFSR.CDSS_2SX(CNTY) ;

data Test.CDSS_2SX;
set CFSR.CDSS_2SX;

run;

proc sql;

create index PERIOD_DT on Test.CDSS_2SX(PERIOD_DT) ;
create index CNTY on Test.CDSS_2SX(CNTY) ;

/* discard any intermediate tables */