/* referrals.sas */
/* Pre-summarized data for dynamic report generation.
 * Summary-level: 3(state)   
 * Dimensions:
 * RSP_AGY_CD (RESPONSIBLE_AGENCY_CODE)
 * AGE (in years)
 * DISPOSITION 
 * ETHNIC CLASS 
 * GENDER 
 * ALLEGATION TYPE 
 * REPORTER TYPE
 * COUNTY
 * PERIOD (analysis interval)
 * Construct a frequency table for the ALLEGATION data, which 
 * counts children, rather than reports. Each child is counted
 * only once per year.
 * For a given child, only reports with the highest disposition
 * weight are retained.
 * Order of retention:
 * 	SUBSTANTIATED (highest)
 * 	INCONCLUSIVE
 * 	UNFOUNDED     (lowest)
 * Where there are then multiple reports for a given child/year
 * the report with the most severe DISPOSITION is selected.
 * The identity of a child is determined by the key FKCLIENT_T.
 * Usage: A macro call at the end of this file determines the starting
 * year, the ending year and the ending quarter. The macro then computes
 * data for each 4-quarter period specified (see comments at end).
 * Programmer: M. Armijo
 * Revision History:
 * 2007.10.12 Working from earlier code, reorganized to combine separate
 * processing steps into a single program. Full table scan on the source
 * table is limited to a single pass, with indexed retreival used for
 * each analysis period.
 * 2008.10.28 The processing of ALG_DSPC into values for SEVERITY 
 * has been modified as follows:
 * 1. ALG_DSPC with values 5369 (Unknown at conversion) and 5918 (Entered in error)
 * are no longer included in this report.
 * 2. The value for SEVERITY is set to Assessment Only (4) when ALG_DSPC=0.
 * 3. If DISPOSITION_DATE is missing, SEVERITY is set to 4 (Assessment Only).
 * 2009.01.06 Changed the age at point-in-time computation to the new standard.
 * Does NOT use the AGE macro (in-line).
 * 2010.09.23 Added UCB_REF.RSP_AGY_CD (RESPONSIBLE_AGENCY_CODE) to REFERRALS.
 * The contingency where a child could have more than one referral during 
 * an analysis period with more than RESPONSIBLE_AGENCY_CODE, is handled by
 * partitioning the data by RESPONSIBLE_AGENCY_CODE, prior to the processing.
 * That is, the scope of analysis is the RSP_AGY_CD, where events under different
 * RSP_AGY_CD are independent and unrelated (out-of-scope events are not known about). 
 * 2011.08.18 Disposition type "Assessment Only" is further subdivided into
 * "Evaluated Out" (where REFR_CLT.DISPSTN_CD='A')and the remainder, "Not Yet Determined".
 */

/* Adding Census Ethnic information */

proc sql;

create table REFERRALS0 as
select x.*, y.CENS_RC, y.HISP_CDX
from dwh.UCB_REF as x left join dwh.CENSUS_RC as y
on x.FKCLIENT_T = y.FKCLIENT_T;
quit;

data UCB_REF;
set REFERRALS0;

/* Assign Census Ethnicity Class */

     if HISP_CDX = 0 and CENS_RC = 1 then CENS_ETHNIC = 1;  /* Black */
else if HISP_CDX = 0 and CENS_RC = 2 then CENS_ETHNIC = 2;  /* White */
else if HISP_CDX = 0 and CENS_RC = 4 then CENS_ETHNIC = 4;  /* Asian/PI */
else if HISP_CDX = 0 and CENS_RC = 5 then CENS_ETHNIC = 5;  /* Nat Amer */
else if HISP_CDX = 0 and CENS_RC = 8 then CENS_ETHNIC = 8;  /* Mixed */
else if HISP_CDX = 0 and CENS_RC = 9 then CENS_ETHNIC = 9;  /* Missing */
else if HISP_CDX = 1                 then CENS_ETHNIC = 10; /* Hispanic */

run;


options msglevel=I 
NOMLOGIC NOMPRINT NOMRECALL NOSYMBOLGEN ;

/* Partition the data by RSP_AGY_CD */
data 
A
C
I
K
M
O
P
S
X
;
set UCB_REF ;
select(RSP_AGY_CD) ;
when( 'A' ) output A ;
when( 'C' ) output C ;
when( 'I' ) output I ;
when( 'K' ) output K ;
when( 'M' ) output M ;
when( 'O' ) output O ;
when( 'P' ) output P ;
when( 'S' ) output S ;
otherwise output X ;
end ;

run ;

proc sql ;
drop table SUMMARY.REFERRALS ;
quit ;

%macro AGENCY(AGY_CD) ;

/* present a view of the data which simplifies later processing. */
data REFERRALS /view=REFERRALS ;
set &AGY_CD (rename=(GENDER_CD=GTEMP ETHNIC=ETEMP)) ;

/* Assign severity level to Disposition. */
if ALG_DSPC in (5369,5918) then delete ;
select(ALG_DSPC) ;
   when(45) SEVERITY=1 ;  /* Substantiated */
   when(47) SEVERITY=2 ;  /* Inconclusive */
   when(46) SEVERITY=3 ;  /* Unfounded */
   when(0)  SEVERITY=4 ;  /* Assessment only, Evaluated Out */
   otherwise SEVERITY=99 ; /* UNEXPECTED VALUE */
end ;
if DISPSN_DT eq . then SEVERITY=4 ; /* Assessment only, Evaluated Out */
/* Not Yet Determined */
if SEVERITY eq 4 and DISPSTN_CD ne 'A' then SEVERITY=5 ; /* Not Yet Determined */

/* Assign severity level to Allegation. */
select(ALG_TPC) ;
   when(2181) SOAT=1 ; /* Sexual Abuse */
   when(2179) SOAT=2 ; /* Physical Abuse */
   when(2180) SOAT=3 ; /* Severe Neglect */
   when(2178) SOAT=4 ; /* General Neglect */
   when(2177) SOAT=5 ; /* Exploitation */
   when(2176) SOAT=6 ; /* Emotional Abuse */
   when(2169) SOAT=7 ; /* Caretaker Absence/Incapacity */
   when(5001) SOAT=8 ; /* At Risk, sibling abused */
   when(5624) SOAT=9 ; /* Substantial Risk */
   otherwise SOAT=99 ; /* other/missing */
end ;

/* compute age in years */
/* (superceded) AGE =int( (REF_RCV_DT-BIRTH_DT)/365.25) ; */
/* New method */
AGE=floor((intck('month',BIRTH_DT,REF_RCV_DT)-(day(REF_RCV_DT) lt day(BIRTH_DT)))/12); 
if AGE eq .  then AGE=99 ;
if AGE lt 0  then AGE=99 ;
if AGE ge 21 then AGE=99 ;

/* Assign Reporter Type */
select(COL_RELC) ;
   when(572,573,577,580,581,583,584,585,586,596,598,601,6243,
        7282,7283,7284,7285,7286,7287,7288,7289,7290,
	7291,7292,7293,7294,7295,7296,7297,7298,7299) 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 ;

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

select(ETEMP) ;
   when('1') ETHNIC=1 ;
   when('2') ETHNIC=2 ;
   when('3') ETHNIC=3 ;
   when('4') ETHNIC=4 ;
   when('5') ETHNIC=5 ;
   otherwise ETHNIC=99 ;
end ;
run ;

proc sql ;
create table REF as
select
CNTY_SPFCD,
COUNTY,
INVESTIGATE,
REF_RCV_DT,
AGE,
SEVERITY,
SOAT,
REPORTER,
DISPSTN_CD, 
ETHNIC, 
CENS_RC,
HISP_CDX,
CENS_ETHNIC,
GENDER_CD,
FKCLIENT_T
from REFERRALS
order by REF_RCV_DT 
;
create index REF_RCV_DT on REF(REF_RCV_DT) ;
quit ;

/* macro is called at end of file... */

%macro REFERRAL(START, STOP, EQTR) ;
%do SYEAR = &START %to &STOP ;
%do SQTR = 1 %to 4 ;

data _null_ ;
call symput('BEGWIN',yyq(&SYEAR,&SQTR)) ;
call symput('ENDWIN',yyq(&SYEAR+1,&SQTR)-1) ;
run ;

/* process the state total */
/* create the ordered subset */
proc sql ;
create table PASS0 as
select *
from REF(idxname=REF_RCV_DT)
where REF_RCV_DT between &BEGWIN and &ENDWIN
;
create table PASS1 as
select *
from PASS0
order by FKCLIENT_T, INVESTIGATE, SEVERITY, SOAT, REF_RCV_DT, REPORTER
;

/* select one record for each child per year (most severe) */
data PASS2 ;
set pass1 ;
by  FKCLIENT_T INVESTIGATE SEVERITY SOAT ;
if first.FKCLIENT_T then output ;
run ;

/* Generate the group counts */
proc sql ;
create table PASS3 as
select
AGE,
SEVERITY as ALG_DSPC label="DISPOSITION-TYPE" ,
SOAT as ALG_TPC label="ALLEGATION-TYPE" ,
ETHNIC label="ETHNIC_CLASS",
CENS_RC label="CENSUS RACE CODE",
INVESTIGATE label ="INVESTIGATED REFERRAL",
HISP_CDX label="CENSUS HISPANIC CODE",
CENS_ETHNIC label="CENSUS ETHNIC CLASS",
GENDER_CD label="GENDER_CODE",
REPORTER label="REPORTER_CLASS",
count(*) as COUNT label="INCIDENCE" format=comma12. 
from PASS2
group by 
AGE, 
INVESTIGATE,
SEVERITY, 
SOAT, 
ETHNIC, 
CENS_RC,
HISP_CDX,
CENS_ETHNIC,
GENDER_CD, 
REPORTER
;

/* add values unique to this pass */
data PASS4 ;
length
RSP_AGY_CD $1
CNTY_SPFCD $2
AGE ALG_DSPC ALG_TPC REPORTER GENDER_CD ETHNIC CENS_RC HISP_CDX CENS_ETHNIC PERIOD_DT COUNT 4 ;
set PASS3 ;
PERIOD_DT=yyq(&SYEAR,&SQTR) ;
CNTY_SPFCD='00' ;
COUNTY = 0000;
RSP_AGY_CD="&AGY_CD" ;
run ;

/* Add table to master database */
proc datasets NOLIST ;
append base=SUMMARY.referrals data=PASS4 ;
delete PASS1 PASS2 PASS3 PASS4 ;
run ;

/* Process the counties */
/* create the ordered subset */
proc sql ;
create table PASS1 as
select *
from PASS0
order by CNTY_SPFCD, FKCLIENT_T, SEVERITY, SOAT, REF_RCV_DT, REPORTER
;

/* select one record for each child per year (most severe) */
data PASS2 ;
set PASS1 ;
by  CNTY_SPFCD FKCLIENT_T SEVERITY SOAT ;
if first.FKCLIENT_T then output ;
run ;

/* Generate the group counts */
proc sql ;
create table PASS3 as
select
CNTY_SPFCD,
COUNTY,
AGE,
INVESTIGATE,
SEVERITY as ALG_DSPC label="DISPOSITION-TYPE" ,
SOAT as ALG_TPC label="ALLEGATION-TYPE" ,
ETHNIC label="ETHNIC_CLASS",
CENS_RC label="CENSUS RACE CODE",
HISP_CDX label="CENSUS HISPANIC CODE",
CENS_ETHNIC label="CENSUS ETHNIC CLASS",
GENDER_CD label="GENDER_CODE",
REPORTER label="REPORTER_CLASS",
count(*) as COUNT label="INCIDENCE" format=comma12. 
from PASS2
group by 
CNTY_SPFCD,
COUNTY,
AGE, 
INVESTIGATE,
SEVERITY, 
SOAT, 
ETHNIC,
CENS_RC,
HISP_CDX,
CENS_ETHNIC,
GENDER_CD,
REPORTER
;

/* add values unique to this pass */
data PASS4 ;
length
RSP_AGY_CD $1
CNTY_SPFCD $2
AGE ALG_DSPC ALG_TPC REPORTER GENDER_CD ETHNIC CENS_RC HISP_CDX CENS_ETHNIC PERIOD_DT COUNT 4 ;
set PASS3 ;
PERIOD_DT=yyq(&SYEAR,&SQTR) ;
RSP_AGY_CD="&AGY_CD" ;
run ;

/* Add table to master database */
proc datasets NOLIST ;
append base=SUMMARY.referrals data=PASS4 ;
delete PASS0 PASS1 PASS2 PASS3 PASS4 ;
run ;

%if &SYEAR=&STOP and &SQTR=&EQTR %then %goto finished;
%end ;
%end ;
%finished: %mend REFERRAL;
/* use current extract minus 1 year for start of last interval (extent).
 * e.g. current extract is:
 * 2009Q1 use 2008Q2
 * 2009Q2 use 2008Q3
 * 2009Q3 use 2008Q4
 * 2009Q4 use 2009Q1
 */
%REFERRAL(1998,2024,1) ;

%mend AGENCY;
%AGENCY(A) ;
%AGENCY(C) ;
%AGENCY(I) ;
%AGENCY(K) ;
%AGENCY(M) ;
%AGENCY(O) ;
%AGENCY(P) ;
%AGENCY(S) ;