/* s2_data.sas */

/* CFSR3 measure S2 Recurrence of Maltreatment:
 * Of all children who were victims of a substantiated report of
 * maltreatment during a 12-month period, what percent were victims of
 * another substantiated report of maltreatment within 12 months of their
 * initial report?
 * Denominator: number of children with at least one substantiated report
 * of maltreatment within a 12-month period.
 * The first substantiated allegation of the 12-month period is selected.
 * Where more than one, the referral type of highest severity is selected.
 * Numerator: number of children in the denominator with another substantiated
 * report within 12 months of the initial report.
 * Subsequent reports within 14 days of the initial do not qualify.
 * When reporting by county, a subsequence recurrence in any county qualifies as
 * recurrence in the county of the initial allegation.
 * This program produces counts by:
 * 12-month intervals
 * county + state total
 * age, gender, ethnicity, allegation type, reporter.
 * Recurrences are counted by allegation and reporter type of the initial (base) allegation.
 * 
 * 2014.10.16 Initial methodology code J. Magruder
 * 2014.12.01 Production implementation M. Armijo
 */

/*** extract-specific reference: CHANGE ***/
%include "/dss/SAS/PROGRAM/CWS_CMS/Q4_2024/autoexec.sas" ;

libname CFSR3_s2 "/pool01/NOSAVE/REPORT_DATA/CFSR3_S2" ;

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

/* retrieve all records of substantiated allegations
 * exclude records where: 
 * referral received before birth,
 * referral received on or after 18th birthday
 * age unknown
 * exclude substantial risk allegations and at risk, sibling abused allegations
 */

data S2_01;
set dwh.ucb_ref;
length CNTY ALGTPC REPORTER 4 ;

AGE = %age(ref_rcv_dt, birth_dt); 
/*revised to name variable AGE rather than ref_age.
  See data step creating first_report table for removal of calculation
  of age at PERIOD_DT. This all to conform to CFSR3 S2 instructions and CFSR3 S2 
  methodology regarding callculation of referral age. 2022.07.11 j magruder*/

where alg_dspc = 45
 and alg_tpc in(2181, 2179, 2180, 2178, 2177, 2176, 2169)
 and rsp_agy_cd in("C", "P", "I", "S");

if age < 0 or age > 17 or age = . then delete; /*revised variable name  2022.07.11 j magruder*/

/* Referral Allegation in order of severity */
select(alg_tpc) ;
  when(2181) ALGTPC=1 ;  %* Sexual Abuse;
  when(2179) ALGTPC=2 ;  %* Physical Abuse;
  when(2180) ALGTPC=3 ;  %* Severe Neglect;
  when(2178) ALGTPC=4 ;  %* General Neglect;
  when(2177) ALGTPC=5 ;  %* Exploitation;
  when(2176) ALGTPC=6 ;  %* Emotional Abuse;
  when(2169) ALGTPC=7 ;  %* Caretaker Absence/Incapacity;
  when(5001) ALGTPC=8 ;  %* At Risk, sibling abused;
  when(5624) ALGTPC=9 ;  %* Substantial Risk;
  otherwise  ALGTPC=99 ; %* Other/Missing;
 end ;

select(COL_RELC) ;
   when(572,573,577,580,581,583,584,585,586,596,598,601) REPORTER=1 ;  %* Family/Friend *;
   when(591)             REPORTER=2 ;  %* Neighbor *;
   when(571,589,594,595) REPORTER=3 ;  %* Law Enforce/Legal *;
   when(574,587)         REPORTER=4 ;  %* CASA/GAL *;
   when(576)             REPORTER=5 ;  %* Couselor/Therapist *;
   when(578)             REPORTER=6 ;  %* CWS Staff *;
   when(579,599)         REPORTER=7 ;  %* Day Care/Fost Care *;
   when(590,5924)        REPORTER=8 ;  %* Medical *;
   when(597,600)         REPORTER=9 ;  %* Education *;
   when(593)             REPORTER=10 ; %* Other Professional *;
   when(.,0,25)          REPORTER=99 ; %* Missing/Unknown *;
   otherwise             REPORTER=11 ; %* Other *;
 end ;

cnty=cnty_spfcd*1 ;
if cnty lt 1 then cnty=98 ;
if cnty gt 58 then cnty=98 ;

keep 
FKCLIENT_T       
ALGTPC
REF_RCV_DT  
CNTY  
RSP_AGY_CD    
REPORTER  
ETHNIC      
GENDER_CD
BIRTH_DT
AGE /*added 2022.07.11 j magruder*/
;

run;

/* limit to one substantiated referral record per child per day and the 
 * most severe referral reason on that date
 */

proc sort data = S2_01;
	by fkclient_t ref_rcv_dt algtpc;
	run;

data S2_base;
	set S2_01;
	by fkclient_t ref_rcv_dt algtpc;
	if first.ref_rcv_dt; 
	run;

/* Generate the report interval definitions (one year, rolling) */
data interval_a ;
length PERIOD_DT 4 ;
do RYEAR=1998 to 2024 ; /* Change - update to capture latest events for the extract */
   do QTR=1 to 4 ;
      PERIOD_DT=yyq(RYEAR,QTR) ;                   /* Report Interval START date */
      ENDINT=intnx('YEAR',PERIOD_DT,1,'SAME') -1 ; /* Report Interval END date */
      output ;
   end ;
end ;
keep PERIOD_DT ENDINT ;
run ;

/* Assign referral events to analysis intervals (PERIOD_DT) */
proc sql ;
create table s2_base2 as
select A.*, 
       B.PERIOD_DT
from S2_base A, interval_a B
   where A.REF_RCV_DT between B.PERIOD_DT and B.ENDINT
;

/* select the first allegation of each interval */
create table s2_base2_s as
select *
from s2_base2
order by PERIOD_DT, FKCLIENT_T, REF_RCV_DT /* jm rev */
;

data first_report ; /*finds first report in each 12-month reporting period*/
*length AGE 4 ;
set s2_base2_s ;
by PERIOD_DT FKCLIENT_T REF_RCV_DT ; /* jm rev */
if first.FKCLIENT_T ;
/* age at start of interval */ /*removed 2022.07.11 see data step creating S2_01 j magruder*/
/*AGE=%age(PERIOD_DT, BIRTH_DT); 
if AGE eq -1 then AGE=0 ; */
drop BIRTH_DT ;
run ;

/* find other substantiated referrals received more than 14 days after first (of each interval) */

proc sql ;
    create table r_6mos_u as
    select A.*
    from first_report A, s2_base B
    where A.FKCLIENT_T eq B.FKCLIENT_T and
          B.REF_RCV_DT-A.REF_RCV_DT gt 14 and
          B.REF_RCV_DT le (intnx('month',A.REF_RCV_DT,6,'sameday'))
    order by PERIOD_DT, FKCLIENT_T
;

data r_6mos ;
set r_6mos_u ;
by PERIOD_DT FKCLIENT_T ;
if first.FKCLIENT_T ;
run ;

proc sql ;
    create table r_12mos_u as
    select A.*
    from first_report A, s2_base B
    where A.FKCLIENT_T eq B.FKCLIENT_T and
          B.REF_RCV_DT-A.REF_RCV_DT gt 14 and
          B.REF_RCV_DT le (intnx('month',A.REF_RCV_DT,12,'sameday'))
    order by PERIOD_DT, FKCLIENT_T
;

data r_12mos ;
set r_12mos_u ;
by PERIOD_DT FKCLIENT_T ;
if first.FKCLIENT_T ;
run ;

proc sql ;
    create table r_18mos_u as
    select A.*
    from first_report A, s2_base B
    where A.FKCLIENT_T eq B.FKCLIENT_T and
          B.REF_RCV_DT-A.REF_RCV_DT gt 14 and
          B.REF_RCV_DT le (intnx('month',A.REF_RCV_DT,18,'sameday'))
    order by PERIOD_DT, FKCLIENT_T
;

data r_18mos ;
set r_18mos_u ;
by PERIOD_DT FKCLIENT_T ;
if first.FKCLIENT_T ;
run ;

proc sql ;
    create table r_24mos_u as
    select A.*
    from first_report A, s2_base B
    where A.FKCLIENT_T eq B.FKCLIENT_T and
          B.REF_RCV_DT-A.REF_RCV_DT gt 14 and
          B.REF_RCV_DT le (intnx('month',A.REF_RCV_DT,24,'sameday'))
    order by PERIOD_DT, FKCLIENT_T
;

data r_24mos ;
set r_24mos_u ;
by PERIOD_DT FKCLIENT_T ;
if first.FKCLIENT_T ;
run ;

/* generate counts for denominator */

%let groupvars=RSP_AGY_CD, PERIOD_DT, CNTY, AGE, ALGTPC, REPORTER, ETHNIC, GENDER_CD ;

proc sql ;
create table bcount as
select count(*) as bcount, &groupvars
from first_report
group by &groupvars
;

/* generate counts for numerator(s) */
create table rcount6 as
select count(*) as rcount6, &groupvars
from r_6mos
group by &groupvars
;

create table rcount12 as
select count(*) as rcount12, &groupvars
from r_12mos
group by &groupvars
;

create table rcount18 as
select count(*) as rcount18, &groupvars
from r_18mos
group by &groupvars
;

create table rcount24 as
select count(*) as rcount24, &groupvars
from r_24mos
group by &groupvars
;

data county_total ;
merge bcount rcount6 rcount12 rcount18 rcount24 ;
by RSP_AGY_CD PERIOD_DT CNTY AGE ALGTPC REPORTER ETHNIC GENDER_CD ;
run ;

/* generate total for state */
proc sql ;
create table state_total as
select
 sum(bcount)  as bcount
,sum(rcount6) as rcount6
,sum(rcount12) as rcount12
,sum(rcount18) as rcount18
,sum(rcount24) as rcount24
,RSP_AGY_CD, PERIOD_DT, AGE, ALGTPC, REPORTER, ETHNIC, GENDER_CD
from county_total
group by RSP_AGY_CD, PERIOD_DT, AGE, ALGTPC, REPORTER, ETHNIC, GENDER_CD
;

data state_total ;
set state_total ;
CNTY=0 ;
run ;

data CFSR3_S2.S2 ;
set county_total state_total ;
run ;
/* report_idx.sas */
/* Final db tuneup for reporting.
 * input: S2
 * output: CFSR3.S2
 * Programmer: M. Armijo
 */

/*** CHANGE destination reference ***/
%let DATA_VERSA=2024Q4Dvlp;
%let DATA_VERSB=2024Q4Test;

libname CFSR3 "/pool01/vol04/SAS/REPORT_DATA/&DATA_VERSA/CFSR3" ;
libname Test "/pool01/vol04/SAS/REPORT_DATA/&DATA_VERSB/CFSR3";
libname CFSR3_S2 "/pool01/NOSAVE/REPORT_DATA/CFSR3_S2" ;

data PASS1 ;
length ETHNIC $2 ;
set CFSR3_S2.S2 ;

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

n_ethnic=ethnic*1 ;
if n_ethnic eq 0 then n_ethnic=99 ;

if RCOUNT6  eq . then RCOUNT6=0 ;
if RCOUNT12 eq . then RCOUNT12=0 ;
if RCOUNT18 eq . then RCOUNT18=0 ;
if RCOUNT24 eq . then RCOUNT24=0 ;

run ;

proc sql ;
create table CFSR3.S2 as
select
 period_dt as PERIOD_DT length=4 label="Period"  
,CNTY length=4 label="SupervisingCounty"
,AGE as AGE length=4 label="Age"
,RSP_AGY_CD
,n_ethnic as ETHNIC length=4 label="Ethnicity"
,GENDER as GENDER_CD length=4 label="Gender"
,ALGTPC as ALG_TPC length=4 label="Allegation Type"
,REPORTER length=4 label="Reporter"
,BCOUNT   label="Base Count"
,RCOUNT6  label="6 months"
,RCOUNT12 label="12 months"
,RCOUNT18 label="18 months"
,RCOUNT24 label="24 months"

from PASS1
order by PERIOD_DT, CNTY
;

create index PERIOD_DT on CFSR3.S2(PERIOD_DT) ;
create index CNTY on CFSR3.S2(CNTY) ;

data Test.S2;
set CFSR3.S2;

run;

proc sql;

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