/* 2D_data.sas */
/* California Department of Social Services
 * Child Welfare Data Analysis Bureau 
 * Programming by Tom Reiner(CDSS), Pam Ward (CDSS), Arthur Lomboy (Monterey) and Terry Shaw (UCB)
 * 2-22-07 Test/CRC LT or = to 9 day, since we are counting 1st day as zero- Donald Hamilton
 * May 2008 Converted for dynamic website - LVB
 * June 2008 Adapted for UCB computing environment by M. Armijo
 * Revision History:
 * 2008.09.03 M. Armijo
 * Changed classification of age from multi-year to single-year classes.
 * Changed coding of PIM and PTEN to use 0 rather than null.
 * Changed coding of ETHNIC=0 to ETHNIC=99 for MISSING.
 * 2009.01.20 M. Armijo
 * Changed the age at point-in-time computation to the new standard.
 * Changed the analysis period: the ending date for each quarter is one day prior to the start date
 * for the next quarter (subract 1 day).
 * 2011.01.12 M. Armijo Restrict referrals to RSP_AGY_CD is (child welfare).
 * 2011.09.22 Lois VanBeers included all values for agency (RSP_AGY_CD), not just
 * child welfare.  Included agency as a summary variable.
 * 2012.05.18 M. Armijo. Made correction to mapping of P_ETHNCTYC to derived var ETHNIC. 
 */

/* Description: 
 * Percent of child abuse and neglect referrals that require an in-person investigation stratified by immediate  
 * response and ten-day referrals, for both planned and actual visits.
 * Methodology:
 * For both immediate and 10-day response types, the denominator consists of the number of child abuse/neglect
 * referrals received within the quarterly report period that was assigned either an immediate or 10-day response
 * requirement.
 */ 

/* Frequency data for dynamic report generation.
 * Dimensions:
 * AGE (in years) 
 * ETHNIC CLASS 
 * GENDER 
 * COUNTY
 * PERIOD (analysis interval)
 * Indicator variables:
 * Total Referrals (TOTAL_REF)
 * Immediate Resonse, Timely (IMCNT)
 * Immediate Resonse, Planned (PIM)
 * 10-Day Response, Timely (TENCNT)
 * 10-Day Response, Planned (PTEN)
 */



/* Other changes May 2008
 * Calculates age at time of referral rather than at end of quarter.
 * Adds client.Hisp_cd = 'Y' to hispanic ethnicity to conform to UCB's coding for dynamic website.
 * Standardizes ethnic and age computations to UCB's coding for dynamic website.
 */

/***
MIA: code specific to CDSS computing environment removed from here
***/

/* Get extract of delivered service table */
data WORK.delivered_service  (keep = identifier start_dt);
   set CWS.dl_svc_t;
   where (cnt_vst_cd='C' or cnt_vst_cd='V') and
         (svc_cntc=435) and      /* Investigate referral */
         (cmm_mthc=408) and  /* in-person  */
       (provd_bycd = 'S') and      /* staff person */
         (status_cd='C');  /*attempted or complete */
run;

/* Select the referrals */
proc sql;
 create table WORK.client as
 select  a.cnty_spfcd as SupCounty,
         a.rfr_rspc,
	 a.RSP_AGY_CD as Agency,
         a.ref_rcv_dt,
         a.identifier as refid,
         b.fkclient_t as child,
         b.fkreferl_t,
         c.birth_dt,
         c.gender_cd as gender,
         c.p_ethnctyc,
         c.hisp_cd

   from CWS.referl_t a,
        CWS.refr_clt b,
        CWS.client_t c

    where 
         (a.rfr_rspc in (1516, 1517, 1518, 1520)) and
         (c.chld_clt_b='Y') and   
         (a.identifier=b.fkreferl_t) and
         (b.fkclient_t=c.identifier) and

         (a.ref_rcv_dt GE '01jan1998'd) and
         floor((intck('month',c.birth_dt,a.ref_rcv_dt)-(day(a.ref_rcv_dt) lt day(birth_dt)))/12) ge 0 and
         floor((intck('month',c.birth_dt,a.ref_rcv_dt)-(day(a.ref_rcv_dt) lt day(birth_dt)))/12) le 20    
  
   order by  refid, child; 

; quit ;

/* Link client and service */
proc sql;
 create table client2 as
   select a.*,
   b.fkdl_svc_t
   from WORK.client as a left join CWS.rfdlsvct as b
   on a.child=b.fkrefr_clt and
   a.fkreferl_t = b.fkrefr_cl0
;

/* Join clients and delivered service   */
create table WORK.ClientSvc as
   select a.*,
   e.start_dt
   from client2 as a left join WORK.delivered_service as e
   on a.fkdl_svc_t = e.identifier and
      (e.start_dt >= a.ref_rcv_dt)
; quit ;

/* Put missing start_dt's for delivered service last to optimize chances
 * for getting a completed contact when selecting by first.
 */
data date_sort;
   set WORK.ClientSvc ;
   if start_dt=. then date_sort=1;
   else date_sort=0;
run;

proc sql;
   create table date_sort2 as
   select *
   from date_sort
   order by refid, date_sort, start_dt, child
; quit ;

/* Select first.referral.  Assign values. */    
data WORK.combine;
set date_sort2;
   by refid; 
   if first.refid; /*  selects just one follow-up per referral/family */

/* New method (in-line) */
   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 ;

select(agency) ;
   when('A') agency_cd=1 ; /* Private Adoption Agency */
   when('C') agency_cd=2 ; /* County Welfare Department */
   when('I') agency_cd=3 ; /* Indian Child Welfare */
   when('K') agency_cd=4 ; /* Kin-Gap */
   when('M') agency_cd=5 ; /* Mental Health */
   when('O') agency_cd=6 ; /* Out of State Agency */
   when('P') agency_cd=7 ; /* Probation */
   when('S') agency_cd=8 ; /* State Adoption District Office */
   otherwise agency_cd=99 ; /* unknown */
end ;

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

   select(P_ETHNCTYC) ;
      when(0,6351,6352,6453,.) ETHNIC=99 ;     /* Missing */
      when(823,826) ETHNIC=1 ;                 /* Black */
      when(839,840,841,842,843,844) ETHNIC=2 ; /* White */
      when(830,3162,3163,3164,3165) ETHNIC=3 ; /* Hispanic */
      when(822,824,825,827,828,829,831,832,833,834,835,836,837,838,5922,5923) ETHNIC=4 ; /* Asian */
      when(820,821) ETHNIC=5 ;                 /* Native Am */
      otherwise ETHNIC=9 ;                     /* UNKNOWN VALUE */                    
   end ;

      if HISP_CD eq 'Y' then ETHNIC=3 ;        /* Hispanic */
        
   select(RFR_RSPC) ;
      when(1520) PIM=1 ; /* Immediate Response Planned */
      otherwise PIM=0 ;
   end ;

   select(RFR_RSPC) ;
      when(1516,1517,1518) PTEN=1 ; /* Ten Day Response Planned */
      otherwise PTEN=0 ;
   end ;
      
   if pim = 1 and 0<=(start_dt - ref_rcv_dt)<=1 then imcnt=1; else imcnt = 0; *Immediate Response Timely;
   if pten = 1 and 0<=(start_dt - ref_rcv_dt)<=9 then tencnt=1; else tencnt= 0; *Ten Day Response Timely;

   total_resp = sum(imcnt,tencnt); *Total Timely Investigations;
   total_ref = 1; *Total Referrals;  
 
run ;

/* create the index on date of referral  */
proc sql ;
   create index ref_rcv_dt on WORK.combine(ref_rcv_dt);
quit;

/* drop the old tables so the new one can replace it and be added to */
proc sql;
   drop table LOCAL.state, LOCAL.counties ;
quit;

options obs=MAX macrogen symbolgen ;

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

data _null_ ;
/* define start of analysis period */
call symput('BEGWIN',yyq(&SYEAR,&SQTR)) ;

/* define end of analysis period, (use quarter/year rollover handling) */
%if &SQTR^=4 %then %do; call symput('ENDWIN',yyq(&SYEAR,%eval(&SQTR+1))-1) ;  %end;
%if &SQTR=4  %then %do; call symput('ENDWIN',yyq(%eval(&SYEAR+1),1)-1)  ; %end;

run ;

proc sql ;
   create table pass1 as
   select * from WORK.combine(idxname = ref_rcv_dt)
   where ref_rcv_dt between &BEGWIN and &ENDWIN
;

/* Create group counts */
/* First the state counts */
proc sql;
   Create table statecounts as
   select
   total_ref  label = "Total Referrals",   
   pim label = "Immediate Response (Planned)", 
   imcnt   label = "Immediate Response (Timely)",
   pten   label = "10-Day Response (Planned)"    ,
   tencnt   label = "10-Day Response (Timely)"    ,
   AGE   label = "Age",
   ethnic   label = "Ethnicity",
   agency_cd   label = "Agency", 
   gender_cd   label = "Gender",   
   count(*) as count  
   from pass1
   group by 
	total_ref, pim, imcnt, pten, tencnt,
	AGE,agency_cd,ethnic,gender_cd 
   ;

/* Add quarter and year of referral */
data state;
   length supcounty $2
   total_ref pim imcnt pten tencnt AGE 
   agency_cd ethnic gender_cd period_dt count 4 ;
   set statecounts;
   PERIOD_DT=yyq(&SYEAR,&SQTR) ;
   supcounty = '00';
run;
 
/* Store each quarter's counts.  */
proc datasets NOLIST ;
   append base = LOCAL.state  data = state;
   delete  statecounts state;
run;

/* Now the county counts */
proc sql;
   Create table countyCounts as
   select
   supcounty   label = "County", 
   total_ref  label = "Total Referrals",   
   pim label = "Immediate Response (Planned)", 
   imcnt   label = "Immediate Response (Timely)",
   pten   label = "10-Day Response (Planned)"    ,
   tencnt   label = "10-Day Response (Timely)"    ,
   AGE   label = "Age",
   agency_cd  label = "Agency",
   ethnic      label = "Ethnicity",
   gender_cd   label = "Gender",    
   count(*) as count  
   from pass1
   group by 
	supcounty, total_ref, pim, imcnt, pten,
	tencnt, AGE, agency_cd, ethnic, gender_cd 
;

/* Add quarter and year of referral */
data counties;
   length supcounty $2
   total_ref pim imcnt pten tencnt AGE agency_cd ethnic
   gender_cd  period_dt count 4 ;
   set countyCounts;
   PERIOD_DT=yyq(&SYEAR,&SQTR) ;
run;

/* Store each quarter's counts.  */
proc datasets NOLIST ;
   append base = LOCAL.counties  data = counties;
   delete pass1 countycounts counties;
run;

%if &SYEAR=&STOP and &SQTR=&LAST_QTR %then %goto finished;
%end ;
%end ;
%finished: %mend REFERRAL;
%REFERRAL(1998,2024,4) ;