/* s1_episodes.sas */
/* From the placement-level file ucb_fc_afcars,
 * consruct the episode-level file.
 * Restrict to episodes:
 *   length of 8 days or more
 *   persons with non-missing birthdates
 *   birthdate is prior to or on start-date.
 *   one episode per start-date (see last section).
 * This file is required for the construction of the care-days file.
 * Programer: M. Armijo
 */

/*** extract-specific reference: CHANGE ***/
libname DWH "/ssa4/SAS/SASDATA/CWS_CMS/Q4_2024";
libname S1 "/pool01/NOSAVE/REPORT_DATA/CFSR3_S1" ;

data ucb_fc_afcars /view=ucb_fc_afcars ;
set DWH.UCB_FC_AFCARS ;
if PE_E_DT eq . then PE_E_DT='31DEC2025'd ; /*NEED TO CHANGE TO THE END OF THE FOLLOWING YEAR*/
run ;

proc sql ;
create table P_LEVEL as
select 
 AGY_RSPC
,BIRTH_DT
,ETHNIC
,FKCLIENT_T
,GENDER_CD
,PE_E_DT
,PE_S_DT
,PLC_FCLC
,CNTY_REM

from ucb_fc_afcars
where BIRTH_DT is not null 
      and PE_E_DT-PE_S_DT gt 7
      and BIRTH_DT le PE_S_DT
order by FKCLIENT_T,  PE_S_DT, PE_E_DT desc
;

/* where multiple episodes with same start date occur,
 * select the longest episode (latest end date).
 */

data S1.S1_EPISODES ;
set P_LEVEL ;
by FKCLIENT_T PE_S_DT ;
if first.PE_S_DT ;
run ;
/* care_days_s1.sas */
/* Create a record for each client each day they are in care:
 * 1. prior to 18th birthday
 * 2. for complete episodes of length 8 days or longer
 * Flag variable (RULE3) is initialized with "I" (Include) or "X" (eXclude).
 * Used to determine if a maltreatment was 7 days or more after in care.
 * Programmer: M. Armijo 
 * Revision History:
 * 2016.06.15 Added code to assign responsible county to each person-care-day,
 * using a 3 sources:
 * case. if no county was assigned using case, try referral;
 * if no county was assigned using case or referral, try state-id.
 * The assigned responsible county is COMP_CNTY.
 * Logic and code borrowed from Point-In-Time (PIT) measure.
 */

/*** extract-specific reference: CHANGE ***/
%inc "/pool01/vol01/SAS/PROGRAM/CWS_CMS/Q4_2024/autoexec.sas" ;
libname S1 "/pool01/NOSAVE/REPORT_DATA/CFSR3_S1" ;

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

data PASS1(keep= 
DAY
FKCLIENT_T
BIRTH_DT
ETHNIC
GENDER_CD
AGY_RSPC
PLC_FCLC
RULE3
) ;
length DAY 4 ;
file stdout ;

/*** extract-dependent dates: CHANGE ***/
/* Every Q1, day one is incremented 1 year, so that 11 full calendar years are represented.
 * Last day is changed to the last day of the extract data.
 */
do DAY='01JAN2005'd to '31DEC2024'd ;
   if day(DAY) eq 1 then put DAY YYMMDDS10. ;
   do i=1 to NOBS ;
      set S1.S1_EPISODES nobs=NOBS point=i ;
      if PE_E_DT eq . then PE_E_DT='31DEC2025'd ; /*CHANGE TO END OF FOLLOWING YEAR*/
      RULE3='I' ;
      /* 2016.05.03. changed statement from lt 7 to le 7 to conform to given S1 description. */
      if (DAY - PE_S_DT) le 7 then RULE3='X' ;
      if ( (DAY ge PE_S_DT) and (DAY le PE_E_DT) ) then output ;
   end ;
end ;
STOP ;
run ;

/* Because an episode end date and a subsequent episode start date can be the same day,
 * 2 care-day records are generated for that day. We want to store only one of these.
 * This code selects the care-day associated with the earlier episode (last day of the episode).
 */
data PASS2 ;
set PASS1 ;
by DAY FKCLIENT_T ;
if first.FKCLIENT_T then output ;
run ;

/* Only days spent in care while under age 18 are retained. */

data S1_CARE_DAYS(drop=AGE) ;
set PASS2 ;
AGE = %age(DAY, BIRTH_DT) ;
if AGE le 17 then output ;
run ;

/*** Case Assignment County ***/

/* Assign each person care-day to a responsible county, using the assignment and case tables. */

data assign;
   set cws.ASGNM_T;

   if END_DT = . then END_DT = '31DEC3000'd;

run;

/*** Join the CASE and ASSIGN tables together ***/

proc sql;

   create table CASE_ASSIGN as
   select x.FKCHLD_CLT,
          x.IDENTIFIER as caseid,
          y.START_DT,
          y.END_DT,
          y.END_TM,
          y.CNTY_SPFCD
   from cws.CASE_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'C'
   order by FKCHLD_CLT, START_DT, END_DT, END_TM;

quit;

/* For each care-day, get the county responsible. */
/* letf-join preserves non-matching rows from primary table */

proc sql;

   create table CARE_DAYS_AC as
   select A.*,
          B.CNTY_SPFCD as ASG_CASE_CNTY,
          B.START_DT,
          B.END_DT,
          B.END_TM
   from S1_CARE_DAYS A left join CASE_ASSIGN B
   on    A.FKCLIENT_T eq B.FKCHLD_CLT and
         B.START_DT ne . and
         B.START_DT <= DAY <= B.END_DT
   order by A.DAY, A.FKCLIENT_T, B.START_DT, B.END_DT, B.END_TM ;
   ;
quit;

/* Where more than one CASE_ASSIGN_COUNTY is produced, use the most recent. */
/* COMP_CNTY is the assigned county "computed county". */

data S1_CARE_DAYS_PLUS ;
set CARE_DAYS_AC ;
by DAY FKCLIENT_T START_DT END_DT END_TM ;
if last.FKCLIENT_T then output ;
drop START_DT END_DT END_TM ;
run ;

/* assign county from the referral table, for care-days where was no
 * county assignment from the case-assign table
 */

data assign;
   set cws.ASGNM_T;

   if END_DT = . then END_DT = '31DEC3000'd;

run;

/*** Referral County ***/

/*** Join the REFERRAL and ASSIGN tables together ***/

proc sql;

   create table REFE_ASSIGN as
   select x.IDENTIFIER as refid,
          y.START_DT,
          y.END_DT,
          y.END_TM,
          y.CNTY_SPFCD
   from cws.REFERL_T as x, assign as y
   where x.IDENTIFIER = y.ESTBLSH_ID and
         y.ASGNMNT_CD = 'P' and
         y.ESTBLSH_CD = 'R'
   order by refid, START_DT, END_DT, END_TM;

quit;


/* To go through the referrals to the ASSIGNMENT table, first link to the
 * REFER_CLT table in order to use FKREFERL_T to link to the REFERL_T.
 */

proc sql;

   /* the view restricts join to care-days with no county assignment */
   create view asg_case_cnty_null as
   select *
   from S1_CARE_DAYS_PLUS
   where ASG_CASE_CNTY is null ;


   create table getrefassign1 as
   select A.fkclient_t,
          B.FKREFERL_T,
          A.DAY
   from asg_case_cnty_null A, CWS.REFR_CLT B
   where A.FKCLIENT_T eq B.FKCLIENT_T;

quit;

/*** Link to the REFERL_T table using FKREFERL_T ***/

proc sql;

   create table getrefassign2 as
   select A.fkclient_t,
              b.CNTY_SPFCD as assign_ref_cntyspfcd,
                  b.refid,
          B.START_DT,
          B.END_DT,
          B.END_TM
   from getrefassign1 A, REFE_ASSIGN B
   where A.FKREFERL_T eq B.refid and
         B.START_DT ne . and
         B.START_DT <= DAY <= B.END_DT
   order by fkclient_t, B.START_DT, B.END_DT, END_TM;
quit;

proc sort data = getrefassign2; by fkclient_t START_DT END_DT; run;

* Where more than 1 CNTY_REF, use the one with latest REF_RCV_DT. *;

data getrefassign3 ;
   set getrefassign2 ;
   by fkclient_t ;
   if last.fkclient_t then output ;
   drop START_DT END_DT END_TM;
run ;

proc sql;
/* view restricts join to care-days with no county assignment */
   create table S1.ASG_REF_CNTY as
   select A.DAY,
          A.FKCLIENT_T,
          B.assign_ref_cntyspfcd as ASG_REF_CNTY
   from asg_case_cnty_null A left join getrefassign3 B
   on A.fkclient_t = B.fkclient_t
   order by DAY, FKCLIENT_T ;

quit;

/*** State ID County ***/

/* assign county from the state id table, for care-days where no
 * assignment from the case-assign or ref-assign processes was made. 
 */

proc sql;
   /* the view restricts join to care-days with no county assignment */
   create view asg_ref_cnty_null as
   select *
   from S1.ASG_REF_CNTY
   where ASG_REF_CNTY is null ;

   create table ASG_STID_CNTY1 as
   select A.*,
          B.GVR_ENTC as stid_gvrentc,
          B.START_DT
   from asg_ref_cnty_null A left join cws.ST_ID_T B
   on A.FKCLIENT_T eq B.FKCLIENT_T and 
   ((B.START_DT ne . and B.START_DT <= A.DAY) and (B.END_DT = . or B.END_DT > A.DAY))
   order by DAY, FKCLIENT_T, START_DT
;
quit;

data  ASG_STID_CNTY2 ;
   set ASG_STID_CNTY1 ;
   by DAY FKCLIENT_T;
   if last.FKCLIENT_T;
run;

proc sql;
   create table S1.ASG_STID_CNTY as
   select
   A.FKCLIENT_T,
   A.DAY,
   /* UCB format library used to translate encoding for county */
   put(B.stid_gvrentc,LCODE.) as ASG_STID_CNTY length=2
   from asg_ref_cnty_null A left join ASG_STID_CNTY2 B
   on A.FKCLIENT_T = B.FKCLIENT_T and A.DAY = B.DAY
   order by DAY, FKCLIENT_T
;
quit ;

data S1_CARE_DAYS ;
merge
S1_CARE_DAYS_PLUS
S1.ASG_REF_CNTY
S1.ASG_STID_CNTY ;
by DAY FKCLIENT_T ;
run ;

data S1.S1_CARE_DAYS ;
set S1_CARE_DAYS ;
COMP_CNTY = ASG_CASE_CNTY ;
if COMP_CNTY eq ' ' then COMP_CNTY=ASG_REF_CNTY ;
if COMP_CNTY eq ' ' then COMP_CNTY=ASG_STID_CNTY ;
if COMP_CNTY eq ' ' then COMP_CNTY='98' ;
run ;
/* s1_summary1.sas */
/* Measure safety 1.
 * Produce the state and county summary files by 12 month intervals.
 * input: s1_care_days
 * output: state_total
 * output: cnty_total
 * Programmer: M. Armijo
 * Issue: Requires a large workspace and several hours of runtime.
 * 2014.06.09
 * 2016.06.01 Revised to process assigned-county (COMP_CNTY) in place of UCB_CNTY_REM.
 */
 
libname S1 "/pool01/NOSAVE/REPORT_DATA/CFSR3_S1" ;

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


options msglevel=I ;

/* Generate analysis interval definitions (one year, rolling) */
data interval_a ;
/*** extract-dependent RYEAR and QTR: CHANGE ***/
do RYEAR=2005 to 2024 ;
   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 ;

/* Create a base_day interval membership table, 
 * for assignment of care-days to intervals, using an equi-join
 * (more resource-efficient).
 */

data base_days ;
/*** CHANGE NEEDED extract-dependent end-date ***/
do DAY='01JAN2005'd to '31DEC2024'd ;
   output ;
end ;
run ;

proc sql _method BUFFERSIZE=64m ;
create table base_days_int as
select A.*, 
       B.RI_S_DT
from base_days A, interval_a B
   where A.DAY between B.RI_S_DT and B.ENDINT
;


/* Assign care-days to analysis intervals. */
proc sql /* _method BUFFERSIZE=512M */ ;
create table CARE_DAYS_INT as
select A.*, 
       B.RI_S_DT
from S1.S1_CARE_DAYS A, base_days_int B
   where A.DAY eq B.DAY
;

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

/* Produce the State summary-level */

proc sql ;
create table STATE_TOTAL as
select
/*** columns ***/
/* COMP_CNTY as CNTY  label="County", */
 AGE                  length=3 label="Age"
,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 CARE_DAYS label="Care Days" /* denominator */
from CARE_DAYS_INT
group by
/*** dimensions ***/
/* UCB_CNTY_REM */
 AGE
,ETHNIC
,GENDER_CD
,RI_S_DT
,AGY_RSPC
;

/* Assign the value 0 to county to indicate state total */
data S1.STATE_TOTAL ;
set STATE_TOTAL ;
CNTY='00' ;
run ;

/* Produce the County summary-level */

proc sql ;
create table S1.CNTY_TOTAL as
select
/*** columns ***/
 COMP_CNTY as CNTY label="County"
,AGE                  length=3 label="Age"
,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 CARE_DAYS label="Care Days" /* denominator */
from CARE_DAYS_INT
group by
/*** dimensions ***/
 COMP_CNTY
,AGE
,ETHNIC
,GENDER_CD
,RI_S_DT
,AGY_RSPC
;

/* maltreatment.sas */
/* For children with a substantiated report of maltreatment,
 * determine if they were in FC at the time.
 * This is the numerator for maltreatment in FC.
 * Restricted to reports that occur after the first 7 days of removal (by RULE3).
 * Unduplicated.
 * input: s1_care_days
 * input: ucb_ref
 * output: maltreatment
 * Programmer: M. Armijo
 * Revision History:
 * 2014.10.21
 * 2015.04.24 removed ALG_TPC from the select distinct. The resulting output table contains no more than
 * one event per child per day.
 * Filtering out "at risk" allegation event types.
 * 2018.04.30 revised to select relevant referrals at start of process and to determine whether
   child in care on earlier of referral received date or date abuse reported to have started j magruder
 * 2022.10.10 revised to exclude referrals received one day after initial referrals to conform
   to CSFR4 revision j magruder;
 */
/*** extract-specific reference: CHANGE ***/
libname DWH "/ssa4/SAS/SASDATA/CWS_CMS/Q4_2024";
libname S1  "/pool01/NOSAVE/REPORT_DATA/CFSR3_S1" ;

/*Create file from UCB_REF that only includes substantiated referrals (ALG_DSPC = 45) and excludes
	at risk, sibling abused and substantial risk referrals (ALG_TPC 5001 and 5624).
	Create a pseudo date that is the earlier of the referral received date
	or the abuse start date. This is a change from using only the referral received date.*/
/*Notes: This methodology treats two referrals received on the same date, or on adjacent dates,
	as a single referral
	This methodology treats associated referrals that aren't reported on the 
  	same date as separate referrals. This is consistent with the
	federal methodology that doesn't provide for associated referrals*/
data mtx_11;
	set DWH.UCB_REF;
	where 
		ALG_DSPC   = 45 and
		ALG_TPC not in(5001,5624);
		/*pseudo date for each allegation is earlier of abuse start date or referral received date*/
		pseudo_dt = ref_rcv_dt;
		if abuse_stdt ne . and ref_rcv_dt > abuse_stdt then pseudo_dt = abuse_stdt;
	  	format ref_rcv_dt pseudo_dt mmddyy10.;
	*--- Referral Allegation.;
	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 ;
	run;

proc sort data = mtx_11;
	by fkclient_t ref_rcv_dt pseudo_dt algtpc;
	run;

/*Select the record with the earliest pseudo date for referrals received on that one date*/
data mtx_12;
	set mtx_11;
	by fkclient_t ref_rcv_dt pseudo_dt algtpc;
	if first.ref_rcv_dt;
	run;

/*identify referrals within either the same date or one-day of previous
  referral. Designed to not daisy chain - the third of three referrals
  each a day apart would be a new referral. This uses the pseudo date
  which is the earlier of the referral received date or the abuse date.
  The Federal direction about this is somewhat unclear because it speaks
  of maltreatent report dates but allows/encourages use of abuse dates.*/
proc sort data = mtx_12;
	by fkclient_t pseudo_dt;
	run;

data mtx_14;
	set mtx_12;
	by fkclient_t pseudo_dt;
	retain rfkclient_t rpseudo_dt;
	if fkclient_t = rfkclient_t
		and (rpseudo_dt+1 = pseudo_dt
			or rpseudo_dt = pseudo_dt)
		then delete;
	rfkclient_t = fkclient_t;
	rpseudo_dt = pseudo_dt;
	run;

/*find all children in care on the earlier of the date the substantiated referral was
	received or the abuse was reported to have started, i.e., on the pseudo date*/
proc sql _method ;
create table S1.MALTREATMENT as
select distinct
 B.DAY format mmddyy10.
,B.FKCLIENT_T
,B.BIRTH_DT format mmddyy10.
,B.ETHNIC
,B.GENDER_CD
,B.AGY_RSPC
,B.COMP_CNTY
,B.PLC_FCLC
,a.fkreferl_t
,a.ref_rcv_dt format mmddyy10.
,a.abuse_endt format mmddyy10.
,a.abuse_stdt format mmddyy10.
,a.alg_tpc
,a.algtpc
,a.non_prt_cd
,a.clntrelc
,a.pseudo_dt
from mtx_14 A, S1.S1_CARE_DAYS B 
where 
A.FKCLIENT_T = B.FKCLIENT_T and
A.pseudo_dt = B.DAY and
B.RULE3 eq 'I'
order by fkclient_t, fkreferl_t;
quit;


/* s1_summary2.sas */
/* Measure safety 1.
 * Produce the state and county summaries by 12 month intervals for maltreatment-in-care.
 * input: maltreatment
 * output: state_total_n
 * output: cnty_total_n
 * Programmer: M. Armijo
 * 2014.06.09
 * 2016.06.01 Revised to process assigned-county (COMP_CNTY) in place of UCB_CNTY_REM.
 */

libname S1 "/pool01/NOSAVE/REPORT_DATA/CFSR3_S1" ;

%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 ;
/*** extract-dependent RYEAR: CHANGE ***/
do RYEAR=2005 to 2024;
   do QTR=1 to 4 ;
      RI_S_DT=yyq(RYEAR,QTR) ;                    /* Report Interval start date */
      RI_E_DT=intnx('YEAR',RI_S_DT,1,'SAME') -1 ; /* Report Interval end date */
      output ;
   end ;
end ;
keep RI_S_DT RI_E_DT ;
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 S1.MALTREATMENT A, interval_a B
   where A.DAY between B.RI_S_DT and B.RI_E_DT
;

/* 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 ;
if AGE lt 0 then delete ;
if AGE gt 20 then delete ;
if AGE eq . then delete ;
run ;

/* Produce the State summary-level */

proc sql ;
create table STATE_TOTAL as
select
/*** columns ***/
/* ac_cntyspfcd as CNTY  label="County", */
 AGE                  length=3 label="Age"
,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"
/* ,PLC_FCLC             length=3 label="Facility Type" */
,count(*)as MALTREAT  label="Maltreatment Reports" /* numerator */
from report_data
group by
/*** dimensions ***/
/* COMP_CNTY */
 AGE
,ETHNIC
,GENDER_CD
,RI_S_DT
,AGY_RSPC
/* ,PLC_FCLC */
;

/* Assign the value 0 to county to indicate state total */
data S1.STATE_TOTAL_N ;
set STATE_TOTAL ;
CNTY='00' ;
run ;

/* Produce the County summary-level */

proc sql ;
create table S1.CNTY_TOTAL_N as
select
/*** columns ***/
 COMP_CNTY as CNTY label="County"
,AGE                  length=3 label="Age"
,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"
/* ,PLC_FCLC             length=3 label="Facility Type" */
,count(*)as MALTREAT  label="Maltreatment Reports" /* numerator */
from report_data
group by
/*** dimensions ***/
 COMP_CNTY
,AGE
,ETHNIC
,GENDER_CD
,RI_S_DT
,AGY_RSPC
/* ,PLC_FCLC */
;
/* s1_summary3.sas */
/* Measure safety 1.
 * Combine the Demoniator db with the Numerator db.
 * input: state_total, cnty_total, state_total_n, cnty_total_n
 * output: report_data3
 * Programmer: M. Armijo
 * 2014.06.09
 */

libname S1 "/pool01/NOSAVE/REPORT_DATA/CFSR3_S1" ;

data DENOMINATOR ;
set S1.STATE_TOTAL S1.CNTY_TOTAL ;
run ;

data NUMERATOR ;
set S1.STATE_TOTAL_N S1.CNTY_TOTAL_N ;
run ;

proc sql ;
create table S1.REPORT_DATA3 as
select
 A.*
,B.MALTREAT
from DENOMINATOR A left join NUMERATOR B
on
    A.AGY_RSPC=B.AGY_RSPC
and A.PERIOD_DT=B.PERIOD_DT
and A.CNTY=B.CNTY
and A.AGE=B.AGE
and A.ETHNIC=B.ETHNIC
and A.GENDER_CD=B.GENDER_CD
/* and A.PLC_FCLC=B.PLC_FCLC */
;
/* report4_idx.sas */
/* Create the report-ready db.
 * input: report_data3
 * output: CFSR3.S1
 * 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 S1 "/pool01/NOSAVE/REPORT_DATA/CFSR3_S1" ;

data temp01 (rename=(ethnic=ethnic_num));
  set S1.REPORT_DATA3;
run;

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

data PASS1;
length ETHNIC $2 ;
set temp01;
*set S1.REPORT_DATA3 ;
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 CNTY in('99',' ') then CNTY='98' ;

run ;

/* create AGENCY_CD(4) "all agencies" */
proc sql ;
create table PASS2 as
select
 PERIOD_DT
,CNTY
,AGE
,ETHNIC 
,GENDER
,sum(CARE_DAYS) as CARE_DAYS
,sum(MALTREAT) as MALTREAT
from PASS1
group by PERIOD_DT, CNTY, AGE, ETHNIC, GENDER
;

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

data PASS3 ;
set PASS1 AGY_ALL ;
run ;

proc sql ;
create table CFSR3.S1 as
select
 period_dt as PERIOD_DT length=4 label="Period"  
,input(CNTY,2.) as CNTY length=3 label="County"
,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"
,CARE_DAYS label="Care Days"
,MALTREAT label="Rate of substantiated maltreatment"
from PASS3
order by PERIOD_DT, CNTY
;

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


*REMOVE TEST SAVE;
data Test.S1;
set CFSR3.S1;
run;

proc sql;

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