/* 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/Q3_2025/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 2025;  /*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/Q3_2025/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=2025 ;
%let QTR=Q3;

/* specify time periods for analysis here */
/*** NEED TO CHANGE ***/
%let firstyear=1998 ;
%let lastyear=2025 ;
%let lastmonth=9 ; /* 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 ;
/* state_total.sas */
/* Measure 2S
 * From the monthly data, generate totals at the state level for each analysis interval.
 * M. Armijo
 * 2013.05.21
 * Revsions:
 * 2014.06.23 change test in age computation edit from lt 0 to eq -1.
 */

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

*CHANGE ETHNIC FROM CHARACTER TO NUMERIC;

data temp01 (rename=(ethnic=ethnic_num));
  set DATA2F.monthly_db;
run;

data temp01;
  set temp01;
  ethnic=put(ethnic_num,2.);
run;  
 

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

/* Analysis interval definitions (one year, rolling) */
data interval_a ;
do RYEAR=1998 to 2026 ; /*CHANGE AS NEEDED*/
   do QTR=1 to 4 ;
      RI_S_DT=yyq(RYEAR,QTR) ; /* Report Interval start date */
      ENDINT=intnx('YEAR',RI_S_DT,1,'SAME') -1 ;
      output ;
   end ;
end ;
keep RI_S_DT ENDINT ;
run ;

/* Combine analysis intervals with visit data. 
 * One visit can fall in multiple analysis intervals
 */

proc sql ;
create table report_data as
select A.*, 
       B.RI_S_DT
from temp01 A, interval_a B
   where A.month_s_dt between B.RI_S_DT and B.ENDINT
;

/* compute age at start of analysis interval */
/* If age computation results in -1 we will use AGE=0 */
data report_data ;
set report_data ;
AGE = %age(RI_S_DT, BIRTH_DT) ;
if AGE eq -1 then AGE=0 ;
run ;

/* Aggregate using defined analysis intervals */

proc sql ;
create table report_data12 as
select
/* columns */
AGE         length=4 label="Age",
/* assign_cnty as CNTY  label="County", */
ETHNIC               label="Ethnic Class",
GENDER_CD            label="Gender", 
RI_S_DT as PERIOD_DT length=4 label="StartOfReportInterval",
/* AGY_RSPC         length=4 label="Agency Responsible", */
RSP_AGY_CD,
count(*) as VISITS_D      label="Eligible Month", /* denominator */
sum(visit) as VISITS_N    label="Visit Occured", /* numerator 1 */
sum(in_home) as VISITS_NR label="Visit in Residence" /* numerator 2 */
from report_data
group by
/* dimensions */
AGE,
/* assign_cnty, */
ETHNIC,
GENDER_CD,
RI_S_DT,
RSP_AGY_CD
/* AGY_RSPC */
;

/* count of children with one or more full months in care within analysis period */
/* 1. produce one row per child per period */
proc sql ;
create table CHILD_N as
select distinct
AGE,
ETHNIC,
GENDER_CD, 
RI_S_DT,
/* AGY_RSPC, */
RSP_AGY_CD,
fkclient_t
from report_data
;

/* 2. produce the counts (state total) */

proc sql ;
create table child_data12 as
select distinct
/* columns */
AGE         length=4 label="Age",
/* assign_cnty as CNTY  label="County", */
ETHNIC               label="Ethnic Class",
GENDER_CD            label="Gender", 
RI_S_DT as PERIOD_DT length=4 label="StartOfAnalysisInterval",
/* AGY_RSPC         length=4 label="Agency Responsible", */
RSP_AGY_CD,
count(*) as CHILD_N  label="Children In Care"
from CHILD_N
group by
/* dimensions */
AGE,
/* assign_cnty, */
ETHNIC,
GENDER_CD,
RI_S_DT,
/* AGY_RSPC */
RSP_AGY_CD
;

/* DIAGNOSTIC */
select 
PERIOD_DT format=YYQ6. ,
sum(CHILD_N) as COUNT format=comma10. 
from child_data12
group by PERIOD_DT
;
/* end DIAGNOSTIC */

/* 3. Add the child-count column to the month-visit table */

proc sql ;
create table report_data as
select
A.AGE,
/* A.CNTY, */
A.ETHNIC,
A.GENDER_CD,
A.PERIOD_DT,
/* A.AGY_RSPC, */
A.RSP_AGY_CD,
A.VISITS_D,
A.VISITS_N,
A.VISITS_NR,
B.CHILD_N
from report_data12 A left join child_data12 B
on
A.AGE eq B.AGE
and A.ETHNIC eq B.ETHNIC
and A.GENDER_CD eq B.GENDER_CD
and A.PERIOD_DT eq B.PERIOD_DT
/* and A.AGY_RSPC eq B.AGY_RSPC */
and A.RSP_AGY_CD eq B.RSP_AGY_CD
;

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





/* county_totals.sas */
/* Measure 2S
 * From the monthly data, generate totals at the county level for each analysis interval.
 * M. Armijo
 * 2013.05.21
 * Revision History
 * 2014.06.23 change test in age computation edit from lt 0 to eq -1.
 */

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

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

/* Analysis interval definitions (one year, rolling) */
data interval_a ;
do RYEAR=1998 to 2026 ; /*CHANGE AS NEEDED*/
   do QTR=1 to 4 ;
      RI_S_DT=yyq(RYEAR,QTR) ; /* Report Interval start date */
      ENDINT=intnx('YEAR',RI_S_DT,1,'SAME') -1 ;
      output ;
   end ;
end ;
keep RI_S_DT ENDINT ;
run ;

/* Combine analysis intervals with visit data. 
 * One visit can fall in multiple analysis intervals
 */

proc sql ;
create table report_data as
select A.*, 
       B.RI_S_DT
from DATA2F.monthly_db A, interval_a B
   where A.month_s_dt between B.RI_S_DT and B.ENDINT
;

/* compute age at start of analysis interval */
/* If age computation results in -1 we will use AGE=0 */
data report_data ;
set report_data ;
AGE = %age(RI_S_DT, BIRTH_DT) ;
if AGE eq -1 then AGE=0 ;
run ;

/* Aggregate using defined analysis intervals */

proc sql ;
create table report_data12 as
select
/* columns */
AGE         length=4 label="Age",
assign_cnty,
ETHNIC               label="Ethnic Class",
GENDER_CD            label="Gender", 
RI_S_DT as PERIOD_DT length=4 label="StartOfReportInterval",
RSP_AGY_CD                label="Agency Responsible",
count(*) as VISITS_D      label="Eligible Month", /* denominator */
sum(visit) as VISITS_N    label="Visit Occured", /* numerator 1 */
sum(in_home) as VISITS_NR label="Visit in Residence" /* numerator 2 */
from report_data
group by
/* dimensions */
AGE,
assign_cnty,
ETHNIC,
GENDER_CD,
RI_S_DT,
RSP_AGY_CD
;

/* count of children with one or more full months in care within analysis period */
/* 1. produce one row per child per period */
proc sql ;
create table CHILD_N as
select distinct
AGE,
ETHNIC,
GENDER_CD, 
RI_S_DT,
RSP_AGY_CD,
assign_cnty,
fkclient_t
from report_data
;

/* 2. produce the counts (county totals) */

proc sql ;
create table child_data12 as
select distinct
/* columns */
AGE  length=4 label="Age",
assign_cnty,
ETHNIC               label="Ethnic Class",
GENDER_CD            label="Gender", 
RI_S_DT as PERIOD_DT length=4 label="StartOfAnalysisInterval",
RSP_AGY_CD         length=4 label="Agency Responsible",
count(*) as CHILD_N  label="Children In Care"
from CHILD_N
group by
/* dimensions */
AGE,
assign_cnty,
ETHNIC,
GENDER_CD,
RI_S_DT,
RSP_AGY_CD
;

/* DIAGNOSTIC */
select 
PERIOD_DT format=YYQ6. ,
sum(CHILD_N) as COUNT format=comma10. 
from child_data12
group by PERIOD_DT
;
/* end DIAGNOSTIC */

/* 3. Add the child-count column to the month-visit table */

proc sql ;
create table DATA2F.county_total as
select
A.AGE,
A.assign_cnty,
A.ETHNIC,
A.GENDER_CD,
A.PERIOD_DT,
A.RSP_AGY_CD,
A.VISITS_D,
A.VISITS_N,
A.VISITS_NR,
B.CHILD_N
from report_data12 A left join child_data12 B
on
A.AGE eq B.AGE
and A.ETHNIC eq B.ETHNIC
and A.GENDER_CD eq B.GENDER_CD
and A.PERIOD_DT eq B.PERIOD_DT
and A.RSP_AGY_CD eq B.RSP_AGY_CD
and A.assign_cnty eq B.assign_cnty
;




/* 2S_idx.sas */
/* Construct a single report db from state and county totals dbs.
 * Adjust values as specified by the report requirements.
 * M. Armijo
 */

/*** NEED TO CHANGE ***/
%let DATA_VERSA=2025Q3Dvlp;
%let DATA_VERSB=2025Q3Test;

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.STATE_TOTAL DATA2S.COUNTY_TOTAL ;

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

/* 2013.06.27 J. Magruder, CDSS change request */
/*
if AGY_RSPC in (35, 5604, 6133, 6134) then delete ;
select(AGY_RSPC) ;
  when(34)      AGENCY_CD=1 ; * Child Welfare;
  when(33,5603) AGENCY_CD=2 ; * Probation;
  otherwise     AGENCY_CD=3 ; * Other;
end;
*/
AGENCY_CD=1 ;

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

run ;

proc sql ;
create table CFSR.CDSS_2S 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_CD length=4 label="Agency"
,input(ethnic,2.) as ETHNIC     length=4 label="Ethnicity"
,GENDER as GENDER_CD length=4 label="Gender"
,CHILD_N label="Children Receiving In-Home Services"
,VISITS_D label="Service Months"
,VISITS_N label="Months with Visits"
,VISITS_NR label="Months with Visits in Residence"
from PASS1
order by PERIOD_DT, CNTY
;

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

data Test.CDSS_2S;
set CFSR.CDSS_2S;

run;

proc sql;

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