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

/**************************************************************/
/*** NEED TO CHANGE ***/
%include "/dss/SAS/PROGRAM/CWS_CMS/Q4_2024/autoexec.sas" ;
/**************************************************************/
/* NEED TO CHANGE YEAR BELOW AS NEEDED */

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

/* 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, 5524) 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 DATA2F.contacts ;
    set contacts_02;
    by fkclient_t cdsyear cdsmonth descending in_home;
    if first.cdsmonth;
    run;

/* DIAGNOSTIC */
/* 
proc print data = DATA2F.contacts (obs = 40);
   var fkclient_t cdsdate in_home cdsyear cdsmonth;
   run;
*/
/* case_assign.sas */
/* Measure 2F */
/* 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 DATA2F "/pool01/NOSAVE/REPORT_DATA/2F" ;

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 DATA2F.case_assign;
   set case_assign;
   if assign_end=. then assign_end='31dec3000'd;
run;
/* X_monthly_db.sas */
/* Measure 2F */
/* For each month, identify children in care for the entire month, and determine
 * if they had a visit during tht month, and if that visit was in the home.
 * Analyst: J. Magruder 
 * 2013.04.30 Initial code by J. Magruder. Modified for producion and maintainted by M. Armijo.
 * 2013.06.11 code simplification and restructuring.
 * 2014.04.15 Added classification by Placement Type (FED) and Agency Responsible (AGENCY).
 * Non-conforming Placement types are dropped from the data.
 * 2014.04.21 J. Magruder - Added transitional housing (Placement Type).
 */ 

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 DATA2F "/pool01/NOSAVE/REPORT_DATA/2F" ;

%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 DATA2F.monthly_db ;
quit ;

%global xyear xmonth ; 

%macro bymonth (xyear, xmonth);

/* find children with open placements for the entire month, ages 17 and under
 * at start of month.
 * modify to limit to placements ending after start of month - last clause
 * add spell and plcmnt;
 */
data month_01 (keep = fkclient_t birth_dt pe_s_dt pe_e_dt agy_rspc plc_fclc
     gvr_entc oh_s_dt oh_e_dt orig_plcep_id age_sm ethnic gender_cd month_s_dt
     spell plcmnt plcg_rnc fkplc_hm_t);
    set dwh.ucb_fc_afcars;
    if BIRTH_DT eq . then delete ;
    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 ;
    if (pe_s_dt le month_s_dt and ((pe_e_dt ge month_e_dt) or (pe_e_dt eq .)))
    and oh_s_dt le month_e_dt
    and oh_s_dt ge BIRTH_DT
    and pe_s_dt ge BIRTH_DT 
    /* and (oh_e_dt = . or oh_e_dt ge month_s_dt) */
    and agy_rspc in(33, 34, 5603);
    run;

proc sql;
   create table month_01a as
   select x.*, y.TRNHSG_FAC
   from month_01 as x left join cws.PLC_HM_T as y
   on x.FKPLC_HM_T = y.IDENTIFIER;

quit;

proc sort data = month_01a ;
    by fkclient_t spell plcmnt oh_s_dt;
    run;
    
/* record of last placement in month */
data month_02a ;
    set month_01a ;
    by fkclient_t spell plcmnt oh_s_dt;
    if last.fkclient_t;
select(PLC_FCLC);
  when(2222)      FED = 1 ;  /* Pre-Adopt */
  when(1421,1422) FED = 2 ;  /* Kin*/
  when(1415,1416) FED = 3 ;  /* Foster */
  when(1414,2200) FED = 4 ;  /* FFA */
  when(1419)      FED = 9 ;  /* Court Specified Home */
  when(7208)      FED = 9.2; /* Tribally Approved Home*/
  when(1417)      FED = 10 ; /* Group */
  when(1418,7027) FED = 11 ; /* Shelter 7027 added 2021.12.03*/
  when(5411)      FED = 15 ; /* Guardian-Dependent */
  when(7164,7181) FED = 17 ; /* Runaway added 2021.12.03*/
  when(6716)      FED = 19 ; /* SILP */
  otherwise       FED = 99 ; /* mark Other | Non-FC for deletion */
end;

/* if placement ended before last day of month, use change reason to assign PT */
if oh_e_dt < intnx('month',month_s_dt,1, 'sameday')-1 and oh_e_dt ne . then do;
  select(PLCG_RNC) ;
    when(5535) FED = 1 ;   /* Pre-Adopt */
    when(1431) FED = 17 ;  /* Runaway */
    when(1440) FED = 18 ;  /* Trial Home Visit */
    otherwise ;
  end ;
end;
if TRNHSG_FAC = 'Y' then FED = 20 ;  /* Transitional Housing */
if FED eq 99 then delete ;           /* drop if missing or unknown */

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

/* Need to find if non foster care placement in month.
 * This only creates a file of those situations where there is a non-foster care placement in the
 * month and then deletes those months from the denominator.
 */
proc sql;
	create table nfc_01 as
	select a.fkclient_t,
		b.start_dt as nfc_start_dt,
		b.end_dt as nfc_end_dt 
  		from month_02a as a,
		cws.nfc_plct as b where
        a.fkclient_t= b.fkplc_epst and  a.orig_plcep_id= b.fkplc_eps0 and
	    b.start_dt < intnx('month',mdy(&xmonth,1,&xyear),1, 'sameday') and 
        (b.end_dt = . or b.end_dt >= mdy(&xmonth,1,&xyear))
	   and NFC_FCLC not in(7164, 7181) /*added 2021.12.03*/
        order by fkclient_t, nfc_start_dt;  /* sort for NFC start date added. -VLS */
   quit;

data nfc_02;
    set nfc_01;
    by fkclient_t nfc_start_dt;
    if first.fkclient_t;
    nfc_flag= 1;
    run;

*** file excluding month if it contained a nfc placement;
data month_02;
    merge month_02a nfc_02;
    by fkclient_t;
    if nfc_flag = 1 then delete;
    run;

   *** find if visit in month;
proc sql;
    create table month_03 as
    select month_02.*,
        contacts.in_home,
        contacts.cdsyear,
        contacts.cdsmonth
    from month_02 left join DATA2F.contacts
        on month_02.fkclient_t = contacts.fkclient_t
            and cdsyear = &xyear. and cdsmonth = &xmonth.
    order by fkclient_t, cdsyear, cdsmonth;
    quit;

data month_04 ;
    set month_03;
    /* if in_home = . then in_home = 9; */
    if cdsmonth ne . then visit=1 ;
    /* study_year = &xyear.; */
    /* study_month = &xmonth.; */
    run;

/* MIA Append to master database */
proc datasets NOLIST ;
append base=DATA2F.monthly_db data=month_04 ;
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 ;

/* 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 DATA2F.monthly_db as A left join DATA2F.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 DATA2F.monthly_db ;
set CNTY_RESP ;
by month_s_dt fkclient_t ;
if last.fkclient_t ;
drop assign_start ;
run ;
/* state_total.sas */
/* Measure 2F
 * 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/2F" ; 

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

/* Analysis interval definitions (one year, rolling) Change last year to 1 year post current year*/
data interval_a ;
do RYEAR=1998 to 2025 ;
   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 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",
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,
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,
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",
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
;

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

/* 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 2F
 * 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/2F" ;

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

/* Analysis interval definitions (one year, rolling) Change last year to 1 year post current year */
data interval_a ;
do RYEAR=1998 to 2025 ;
   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",
AGY_RSPC         length=4 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,
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,
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",
AGY_RSPC         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,
AGY_RSPC
;

/* 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.AGY_RSPC,
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.assign_cnty eq B.assign_cnty
;






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

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

libname DATA2F "/pool01/NOSAVE/REPORT_DATA/2F" ;
libname CFSR "/ssa3/SAS/REPORT_DATA/&DATA_VERSA/CFSR" ;
libname Test "/ssa3/SAS/REPORT_DATA/&DATA_VERSB/CFSR" ;

*GYS 082124 Changed ETHNIC from character to numeric;

data PASS1 ;

set DATA2F.STATE_TOTAL DATA2F.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;

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

/* create AGENCY_CD(4) "all agencies" */
proc sql ;
create table PASS2 as
select
 period_dt
,assign_cnty
,AGE
,ETHNIC 
,GENDER
,sum(CHILD_N) as CHILD_N
,sum(VISITS_D) as VISITS_D
,sum(VISITS_N) as VISITS_N
,sum(VISITS_NR) as VISITS_NR
from PASS1
group by PERIOD_DT, ASSIGN_CNTY, AGE, ETHNIC, GENDER
;

data AGY_ALL ;
set PASS2 ;
AGENCY_CD=4 ;
run ;

data PASS3 ;
set PASS1 AGY_ALL ;
run ;

proc sql ;
create table CFSR.CDSS_2F 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"
,ETHNIC as ETHNIC     length=4 label="Ethnicity"
,GENDER as GENDER_CD length=4 label="Gender"
,CHILD_N label="Children in Out-of-Home Placement"
,VISITS_D label="Placement Months"
,VISITS_N label="Months with Visits"
,VISITS_NR label="Months with Visits in the Residence"
from PASS3
order by PERIOD_DT, CNTY
;

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

data Test.CDSS_2F;
set CFSR.CDSS_2F;
run;

proc sql;

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


/* discard intermediate tables */
/* 
drop table local.contacts ;
drop table local.case_assign ;
drop table local.monthly_db ;
drop table local.state_total ;
drop table local.county_total ;
*/