*recurrence v4.sas;

*2023.02.10 j magruder;


*%include "/pool01/home/joemagruder/1basic_start.sas";


/* Create referral file for a specific year. */
data REFERRALS_01 ;
set dwh.UCB_REF (rename=(GENDER_CD=GTEMP ETHNIC=ETEMP)) ;
	if RSP_AGY_CD in ("I", "C");
		/*child welfare and Indian referrals only - exclude probation*/
	/* 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 */

/* Assign severity level to Disposition. */
/*delete "Unknown at conversion" 5918 = "Entered in Error"*/
if ALG_DSPC in (5369,5918) then delete ;
/*delete at risk, sibling abused and substantial risk*/
if ALG_TPC in (5001,5624) 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 INVESTIGATE = 1 then SEVERITY=5 ; /* Not Yet Determined */

/* Assign severity level to Allegation by allegation type. */
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) ; */
/* set age to 99 if impossible (before birth, 21 or older, missing*/
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 ;

if age = 99 then delete;

/* 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 ;
ref_rcv_yr = year(ref_rcv_dt);
format ref_rcv_dt mmddyy10.;
/*establish priority order for child-referral disposition to deal with
  multiple same-day referrals and roll-up referrals*/
select(dsp_rsnc);
	when (671) dsp_rsnz = 	 2 ; /*   Child Dead Prior To Referral Date */
	when (672) dsp_rsnz = 	 1 ; /*   Child Died During Investigation */
	when (681) dsp_rsnz = 	11 ; /*   Open New CWD-CWS Case */
	when (6450) dsp_rsnz = 	12 ; /*   Open New Non-CWD Case */
	when (673) dsp_rsnz = 	19 ; /*   Child Does Not Exist */
	when (679) dsp_rsnz = 	20 ; /*   Contact Attempted, Can't Locate */
	when (670) dsp_rsnz = 	21 ; /* 	Child Appears On Linked Referral */
	when (676) dsp_rsnz = 	22 ; /*   Child Not Involved in Incident */
	when (678) dsp_rsnz = 	22 ; /*   Child Unrelated to Family */
	when (682) dsp_rsnz = 	23 ; /*   Situation Stabilized */
	when (675) dsp_rsnz = 	15 ; /*   Child Not At Risk */ /*modified from 24*/
	when (677) dsp_rsnz = 	26 ; /*   Child Placed For Adoption */
	when (674) dsp_rsnz = 	30 ; /*   Child Emancipated/Reached Majority */
	when (680) dsp_rsnz = 	32 ; /*   Loss Of Contact With Child */
	when (669) dsp_rsnz = 	63 ; /*   Child Already in a CWD-CWS Case */
	when (6451) dsp_rsnz = 	72 ; /*   Child Already in a Non-CWD Case */
	otherwise dsp_rsnz = 99; /* Missing */
end;

*ivy's order:;
*select(dsp_rsnc);
*when (671) dsp_rsnz = 1 ; /*   Child Dead Prior To Referral Date */
*when (672) dsp_rsnz = 1 ; /*   Child Died During Investigation */
*when (681) dsp_rsnz = 11 ; /*   Open New CWD-CWS Case */
*when (6450) dsp_rsnz = 12 ; /*   Open New Non-CWD Case */
*when (677) dsp_rsnz = 26 ; /*   Child Placed For Adoption */
*when (669) dsp_rsnz = 63 ; /*   Child Already in a CWD-CWS Case */
*when (6451) dsp_rsnz = 72 ; /*   Child Already in a Non-CWD Case */
*when (680) dsp_rsnz = 32 ; /*   Loss Of Contact With Child */
*when (679) dsp_rsnz = 20 ; /*   Contact Attempted, Can't Locate */
*when (682) dsp_rsnz = 23 ; /*   Situation Stabilized */
*when (675) dsp_rsnz = 24 ; /*   Child Not At Risk */
*when (676) dsp_rsnz = 22 ; /*   Child Not Involved in Incident */
*when (674) dsp_rsnz = 30 ; /*   Child Emancipated/Reached Majority */
*when (678) dsp_rsnz = 22 ; /*   Child Unrelated to Family */
*when (670) dsp_rsnz = 21 ; /* Child Appears On Linked Referral */
*when (673) dsp_rsnz = 20 ; /*   Child Does Not Exist */
*otherwise dsp_rsnz = 99; /* Missing */
run ;

/*
proc contents data = REFERRALS_01;
	run;

proc tabulate data = referrals_01 missing format =comma9.0;
	class alg_tpc soat;;
	table alg_tpc all, soat all;
	run;
*/

proc sort data = referrals_01;
	by fkclient_t ref_rcv_dt severity soat;
	run;

/*separate out children with only one referral ever to speed processing*/ 
data single_05 repeat_05 (rename=(ref_rcv_dt = oref_rcv_dt));
	set referrals_01;
	by fkclient_t ref_rcv_dt severity soat;
	if first.fkclient_t and last.fkclient_t then output single_05;
	else output repeat_05;
	run;


/*roll up those that are not at least 14 days after early referral
  but do not allow daisy chaining - i.e., if 14 days from initial referral
  in cluster start count over*/

proc sort data = repeat_05;
	by fkclient_t oref_rcv_dt severity soat;
	run;

data roll_up_01;
	set repeat_05;
	by fkclient_t oref_rcv_dt severity soat;
	retain ref_rcv_dt;
	if first.fkclient_t then ref_rcv_dt = oref_rcv_dt;
	else if oref_rcv_dt - ref_rcv_dt > 14 then do;
		ref_rcv_dt = oref_rcv_dt;
		end;
	interval = oref_rcv_dt - ref_rcv_dt; /*testing only*/
	format ref_rcv_dt  mmddyy10.;
	run;

/*note ref_rcv_dt now is the rolled up first referral date in 14-day period
  oref_rcv_dt [old ref_rcv_dt] is the actual referral date*/
proc sort data = roll_up_01;
	by fkclient_t ref_rcv_dt oref_rcv_dt severity soat;
	run;


/*find primary disposition data for rolled up referrals*/
/*dsp_rsnz represents a prioritization of dsp_rsnc
  dsp_rsnz1 is the highest priority dsp_rsnz in 14-day group
  and dsp_rsncz is thus the highet priority dsp_rsnc in 14-day group*/ 
data disp_13 (keep = fkclient_t fkreferl_t ref_rcv_dt 
		oref_rcv_dt dsp_rsnz dsp_rsnc dsp_rsnz1 dsp_rsnc1);	
	set roll_up_01;
	by fkclient_t ref_rcv_dt oref_rcv_dt severity soat;
	retain dsp_rsnz1 dsp_rsnc1;
	if first.ref_rcv_dt then do;
		dsp_rsnc1 = dsp_rsnc;
		dsp_rsnz1 = dsp_rsnz;
		rdsp_rsnz = dsp_rsnz;
		end;
	else if dsp_rsnz le dsp_rsnz1 then do; 
		/*if next dsp_rsnz has higher priority replace dsp_rsnz1 etc.*/
		dsp_rsnc1 = dsp_rsnc;
		dsp_rsnz1 = dsp_rsnz;
		rdsp_rsnz = dsp_rsnz;
		end;
	run;

/*keep primary disposition for each group of rolled up referrals*/	
data disp_15;
	set disp_13;
	by fkclient_t ref_rcv_dt oref_rcv_dt;
	if last.ref_rcv_dt;
	run;
/*end calculation of primary disposition data for rolled up referrals*/	

/*repeat the limit to one referral on a given date with referrals
  within 14 days treated as one*/
proc sort data = roll_up_01 (drop = interval dsp_rsnc dsp_rsnz);
	by fkclient_t ref_rcv_dt severity soat;
	run;

data roll_up_02;
	set roll_up_01;
	by fkclient_t ref_rcv_dt severity soat;
	if first.ref_rcv_dt;
	run;
 

/*add primary disposition data back in*/
proc sql;
	create table roll_up_03 as select
		roll_up_02.*,
		disp_15.dsp_rsnc1 as dsp_rsnc,
		disp_15.dsp_rsnz1 as dsp_rsnz
	from roll_up_02 left join disp_15
	on roll_up_02.fkclient_t = disp_15.fkclient_t
		and roll_up_02.ref_rcv_dt = disp_15.ref_rcv_dt
	order by roll_up_02.fkclient_t, roll_up_02.ref_rcv_dt;
	quit;

/*recalculate interval between referrals for rolled up referrals*/
/*first how long after the prior referral*/
data roll_up_07;
	set roll_up_03;
	by fkclient_t ref_rcv_dt severity soat;
	retain rref_rcv_dt;
	if first.fkclient_t = 0 then 
		days_last_ref = ref_rcv_dt - rref_rcv_dt;
	rref_rcv_dt = ref_rcv_dt;
	run;

proc sort data = roll_up_07 (drop = rref_rcv_dt);
	by fkclient_t descending ref_rcv_dt;
	run;

/*next how long until the next referral*/
data roll_up_09;
	set roll_up_07;
	by fkclient_t descending ref_rcv_dt;
	retain rdays_last_ref rseverity;
	if first.fkclient_t = 0 then do; 
		days_next_ref = rdays_last_ref;
		sub_severity = rseverity;
		end;
	rdays_last_ref = days_last_ref;
	rseverity = severity;
	run;

proc sort data = roll_up_09 (drop = rdays_last_ref);
	by fkclient_t ref_rcv_dt;
	run;

/*put records when child has only one referral back together with those where child
  has multiple referrals*/
data referrals_07;
	set single_05 roll_up_09;
	run;

/*add in known child death dates*/
proc sql;
	create table referrals_08 as select
	referrals_07.*,
	client_t.death_dt format mmddyy10.
	from referrals_07 left join cws.client_t
	on referrals_07.fkclient_t = client_t.identifier
	order by fkclient_t, ref_rcv_dt;
	quit;


/*add in known referral-associated placement history - i.e., whether child was
  in care at the time of the referral or whether the child entered care
  within 30 days of the referral*/
/*create minimalist files to speed process due to large nunmber of potential joins*/
	/*what about different approach of calculating days from ref_rcv_dt to pe_s_dt?*/
data pl_hx_01 (keep = fkclient_t ref_rcv_dt severity);
	set referrals_08;
	run;

/*take UCB_FC2 data and reduce to one record per placement episode*/
proc sort data = dwh.ucb_fc2 
		out = ucb_fc (keep = fkclient_t pe_s_dt pe_e_dt agy_rspc) nodupkey;
	by fkclient_t pe_s_dt pe_e_dt;
	run;
/*
proc print data = ucb_fc (obs = 40);
	run;
*/
proc sql;  /*children in care at time of referral*/
	create table pl_hx_03 as select
	pl_hx_01.*,
	ucb_fc.pe_s_dt,
	ucb_fc.pe_e_dt,
	1 as ref_in_care
	from pl_hx_01, ucb_fc
	where pl_hx_01.fkclient_t = ucb_fc.fkclient_t
		and ucb_fc.pe_s_dt < ref_rcv_dt-5
		and (pl_hx_01.ref_rcv_dt le ucb_fc.pe_e_dt
			or ucb_fc.pe_e_dt = .)
	order by pl_hx_01.fkclient_t, pl_hx_01.ref_rcv_dt, ucb_fc.pe_s_dt;
	quit;

/*limit to one placement on referral date*/
data pl_hx_05 (keep = fkclient_t ref_rcv_dt ref_in_care);
	set pl_hx_03;
	by fkclient_t ref_rcv_dt pe_s_dt;
	if first.ref_rcv_dt;
	run;


proc sql; /*children entering care after referral*/
	create table pl_hx_13 as select
	pl_hx_01.*,
	ucb_fc.pe_s_dt,
	ucb_fc.pe_e_dt,
	ucb_fc.pe_s_dt - pl_hx_01.ref_rcv_dt as days_to_pe_s_dt
	from pl_hx_01, ucb_fc
	where pl_hx_01.fkclient_t = ucb_fc.fkclient_t
		and ucb_fc.pe_s_dt ge ref_rcv_dt
	order by pl_hx_01.fkclient_t, pl_hx_01.ref_rcv_dt, days_to_pe_s_dt;
	quit;


data pl_hx_15 (keep = fkclient_t ref_rcv_dt ref_pre_care days_to_pe_s_dt);
	set pl_hx_13;
	by fkclient_t ref_rcv_dt days_to_pe_s_dt;
	if first.ref_rcv_dt;
	if days_to_pe_s_dt le 30 then ref_pre_care = 1;
	run;

/*merge placement history files*/
data pl_hx_20;
	merge pl_hx_05 pl_hx_15;
	by fkclient_t ref_rcv_dt;
	if ref_pre_care = 1 and ref_in_care = 1 then do;
		ref_pre_care = .;
		days_to_pe_s_dt = .;
		end;
	if ref_pre_care = 1 or ref_pre_care = 1 then ref_care = 1;
		else ref_care = 9;
	label ref_care = "In care at referral or within 30 days"; 
	run;


/*add placement data to referrals file*/
proc sql;
	create table referrals_10 as select
	referrals_08.*,
	pl_hx_20.ref_in_care,
	pl_hx_20.ref_pre_care,
	pl_hx_20.ref_care
	from referrals_08 left join pl_hx_20
	on referrals_08.fkclient_t = pl_hx_20.fkclient_t
		and referrals_08.ref_rcv_dt = pl_hx_20.ref_rcv_dt
	order by fkclient_t, ref_rcv_dt;
	quit;


/*repeat process for case involvement*/

data case_01 (keep = fkchld_clt start_dt end_dt rsp_agy_cd
		rename = (fkchld_clt = fkclient_t start_dt = case_s_dt end_dt = case_e_dt));
	set cws.case_t;
	run;

proc sql;  /*children in case at time of referral*/
	create table case_hx_03 as select
	pl_hx_01.*,
	case_t.start_dt as case_s_dt format mmddyy10.,
	case_t.end_dt as case_e_dt format mmddyy10.,
	case_t.rsp_agy_cd,
	1 as ref_in_case
	from pl_hx_01, cws.case_t
	where pl_hx_01.fkclient_t = case_t.fkchld_clt
		and case_t.start_dt < ref_rcv_dt-5
		and (pl_hx_01.ref_rcv_dt le case_t.end_dt
			or case_t.end_dt = .)
		and rsp_agy_cd in("C","I","P")
	order by pl_hx_01.fkclient_t, pl_hx_01.ref_rcv_dt, case_t.start_dt;
	quit;

/*limit to one case on referral date*/
data case_hx_05 (keep = fkclient_t ref_rcv_dt ref_in_case rsp_agy_cd);
	set case_hx_03;
	by fkclient_t ref_rcv_dt case_s_dt;
	if first.ref_rcv_dt;
	run;


proc sql; /*children entering case after referral*/
	create table case_hx_13 as select
	pl_hx_01.*,
	case_t.start_dt as case_s_dt format mmddyy10.,
	case_t.end_dt as case_e_dt format mmddyy10.,
	case_t.rsp_agy_cd,
	case_t.start_dt - pl_hx_01.ref_rcv_dt as days_to_case_s_dt
	from pl_hx_01, cws.case_t
	where pl_hx_01.fkclient_t = case_t.fkchld_clt
		and case_t.start_dt ge ref_rcv_dt
	order by pl_hx_01.fkclient_t, pl_hx_01.ref_rcv_dt, days_to_case_s_dt;
	quit;

data case_hx_15 (keep = fkclient_t ref_rcv_dt ref_pre_case days_to_case_s_dt);
	set case_hx_13;
	by fkclient_t ref_rcv_dt days_to_case_s_dt;
	if first.ref_rcv_dt;
	if days_to_case_s_dt le 30 then ref_pre_case = 1;
	run;

/*merge case history files*/
data case_hx_20;
	merge case_hx_05 case_hx_15;
	by fkclient_t ref_rcv_dt;
	if ref_pre_case = 1 and ref_in_case = 1 then do;
		ref_pre_case = .;
		days_to_case_s_dt = .;
		end;
	if ref_pre_case = 1 or ref_pre_case = 1 then ref_case = 1;
		else ref_case = 9;
	label ref_case = "In open case at referral or within 30 days"; 
	run;
/*
proc tabulate data = case_hx_20 missing format = comma9.0;
	class ref_pre_case ref_in_case;
	table ref_pre_case all, ref_in_case all;
	run;
*/

/*add case data to referrals file*/
proc sql;
	create table referrals_11 as select
	referrals_10.*,
	case_hx_20.ref_in_case,
	case_hx_20.ref_pre_case,
	case_hx_20.ref_case
	from referrals_10 left join case_hx_20
	on referrals_10.fkclient_t = case_hx_20.fkclient_t
		and referrals_10.ref_rcv_dt = case_hx_20.ref_rcv_dt
	order by fkclient_t, ref_rcv_dt;
	quit;

/*find first referral ever and first referral in last 365 days to be able to identify first
  referral of a cluster*/
/*convert days to next referral to months to next referral - 30.4375 = days in month*/

data referrals_13;
	set referrals_11;
	by fkclient_t ref_rcv_dt;
	if first.fkclient_t then first_ref = 1;
	if first_ref = 1 or (first.fkclient_t = 0 and days_last_ref ge 366) then first_clu = 1;
	mos_next_ref = floor(days_next_ref/30.4375);
	run;
/*
proc tabulate data = referrals_13 missing format = comma9.0;
	class severity sub_severity;
	table severity=' ' all, sub_severity*n=' ' all*n=' '
		/box="First Referral Disposition";
	where first_ref = 1;
	format severity sub_severity severity.;
	*where days_next_ref ne . and days_next_ref < 183 and year(ref_rcv_dt) = 2019; 
	title3 "Children with First Referrals in 2019";
	title4 "Limited to children with subsequent referrals within 6 months";
	label severity = "First Referral Disposition";
	label sub_severity = "Next Referral Disposition"; 
	run;

title3 ' ';
title4 ' ';
*/
/*
proc freq data = referrals_13;
	table DSP_RSNC;
	format DSP_RSNC syscode.;
	*where days_next_ref ne . and days_next_ref < 183 and year(ref_rcv_dt) = 2019; 
	where severity = 1 and first_ref = 1 and year(ref_rcv_dt) = 2019;
	run;
 
proc tabulate data = referrals_13 missing format = comma10.0 noseps;
	class severity sub_severity DSP_RSNC;
	table  DSP_RSNC=' ' all, severity=' '*n=' ' all*n=' '
		/box="Disposition Closure Reason" rts = 30;
	where first_ref = 1;
	format severity sub_severity severity.;
	format dsp_rsnc syscode.;
	*where days_next_ref ne . and days_next_ref < 183 and year(ref_rcv_dt) = 2019; 
	where first_ref = 1 and year(ref_rcv_dt) = 2019;
	title3 "Children with First Referrals in 2019";
	*title4 "Limited to children with subsequent referrals within 6 months";
	label severity = "First Referral Disposition";
	label sub_severity = "Next Referral Disposition"; 
	run;
title3 ' ';
title4 ' ';
*/


/*find days to next referral of given type*/
proc sort data = referrals_13;
	by fkclient_t descending ref_rcv_dt soat;
	run;


/*calculate time from any given referral to the next referral of a specific
  certainty (aka severity) - e.g., to next substantiated referral, etc. to allow 
  determination of whether there was another referral within the defined analysis 
  periods (6, 12, 18, 24 months) and, if there was, what the most certain referral
  within that time period and what was the reported abuse type of that referral*/
data referrals_15;
	set referrals_13;
	by fkclient_t descending ref_rcv_dt soat;
	retain sdate idate udate adate ndate rs_soat ri_soat ru_soat ra_soat rn_soat;
	/*clear values for first (i.e., last) record of each client*/
	if first.fkclient_t then do;
		/*substantiated*/
		sdate = .;
		s_gap = .;
		s_gap_mo = .;
		s_soat = .;
		/*inconclusive*/
		idate = .;
		i_gap = .;
		i_gap_mo = .;
		i_soat = .;
		/*unfounced*/
		udate = .;
		u_gap = .;
		u_gap_mo = .;
		u_soat = .;
		/*assessment only*/
		adate = .;
		a_gap = .;
		a_gap_mo = .;
		a_soat = .;
		/*no finding yet*/
		ndate = .;
		n_gap = .;
		n_gap_mo = .;
		n_soat = .;

		end;
	/*find time from referral to next referral in months by finding type*/
	if first.fkclient_t = 0 then do;
		if sdate ne . then do;
			s_gap_mo = floor((sdate - ref_rcv_dt)/30.4375);
			s_soat = rs_soat;
			end;
		if idate ne . then do;
			i_gap_mo = floor((idate - ref_rcv_dt)/30.4375);
			i_soat = ri_soat;
			end;
		if udate ne . then do;
			u_gap_mo = floor((udate - ref_rcv_dt)/30.4375);
			u_soat = ru_soat;
			end;
		if adate ne . then do;
			a_gap_mo = floor((adate - ref_rcv_dt)/30.4375);
			a_soat = ra_soat;
			end;
		if ndate ne . then do;
			n_gap_mo = floor((ndate - ref_rcv_dt)/30.4375);
			n_soat = rn_soat;
			end;
		end;

	/*create retained values*/
	if severity = 1 then do;
		sdate = ref_rcv_dt;
		rs_soat = soat;
		end;
	if severity = 2 then do;
		idate = ref_rcv_dt;
		ri_soat = soat;
		end;
	if severity = 3 then do;
		udate = ref_rcv_dt;
		ru_soat = soat;
		end;
	if severity = 4 then do;
		adate = ref_rcv_dt;
		ra_soat = soat;
		end;
	if severity = 5 then do;
		ndate = ref_rcv_dt;		
		rn_soat = soat;
		end;
	/*not correct*/
	/*for each 6 month increment, if there was a referral indicate the
	  most certain outcome within that reporting period*/
	if mos_next_ref ne . and mos_next_ref < 6 then do;
		if s_gap_mo < 6 and s_gap_mo ne . then do;
			sub_sev_lt6 = 1;
			sub_soat_lt6 = s_soat;
			end;
			else if i_gap_mo < 6 and i_gap_mo ne . then do;
			sub_sev_lt6 = 2;
			sub_soat_lt6 = i_soat;
			end;
			else if u_gap_mo < 6 and u_gap_mo ne . then do;
			sub_sev_lt6 = 3;
			sub_soat_lt6 = u_soat;
			end;
			else if a_gap_mo < 6 and a_gap_mo ne . then do;
			sub_sev_lt6 = 4;
			sub_soat_lt6 = a_soat;
			end;
			else if n_gap_mo < 6 and n_gap_mo ne . then do;
			sub_sev_lt6 = 5;
			sub_soat_lt6 = n_soat;
			end;
			else sub_sev_lt6 = 99;
		end;
	if mos_next_ref ne . and mos_next_ref < 12 then do;
		if s_gap_mo < 12 and s_gap_mo ne . then do;
			sub_sev_lt12 = 1;
			sub_soat_lt12 = s_soat;
			end;
			else if i_gap_mo < 12 and i_gap_mo ne . then do;
			sub_sev_lt12 = 2;
			sub_soat_lt12 = i_soat;
			end;
			else if u_gap_mo < 12 and u_gap_mo ne . then do;
			sub_sev_lt12 = 3;
			sub_soat_lt12 = u_soat;
			end;
			else if a_gap_mo < 12 and a_gap_mo ne . then do;
			sub_sev_lt12 = 4;
			sub_soat_lt12 = a_soat;
			end;
			else if n_gap_mo < 12 and n_gap_mo ne . then do;
			sub_sev_lt12 = 5;
			sub_soat_lt12 = n_soat;
			end;
			else sub_sev_lt12 = 99;
		end;
	if mos_next_ref ne . and mos_next_ref < 18 then do;
		if s_gap_mo < 18 and s_gap_mo ne . then do;
			sub_sev_lt18 = 1;
			sub_soat_lt18 = s_soat;
			end;
			else if i_gap_mo < 18 and i_gap_mo ne . then do;
			sub_sev_lt18 = 2;
			sub_soat_lt18 = i_soat;
			end;
			else if u_gap_mo < 18 and u_gap_mo ne . then do;
			sub_sev_lt18 = 3;
			sub_soat_lt18 = u_soat;
			end;
			else if a_gap_mo < 18 and a_gap_mo ne . then do;
			sub_sev_lt18 = 4;
			sub_soat_lt18 = a_soat;
			end;
			else if n_gap_mo < 18 and n_gap_mo ne . then do;
			sub_sev_lt18 = 5;
			sub_soat_lt18 = n_soat;
			end;
			else sub_sev_lt18 = 99;
		end;
	if mos_next_ref ne . and mos_next_ref < 24 then do;
		if s_gap_mo < 24 and s_gap_mo ne . then do;
			sub_sev_lt24 = 1;
			sub_soat_lt24 = s_soat;
			end;
			else if i_gap_mo < 24 and i_gap_mo ne . then do;
			sub_sev_lt24 = 2;
			sub_soat_lt24 = i_soat;
			end;
			else if u_gap_mo < 24 and u_gap_mo ne . then do;
			sub_sev_lt24 = 3;
			sub_soat_lt24 = u_soat;
			end;
			else if a_gap_mo < 24 and a_gap_mo ne . then do;
			sub_sev_lt24 = 4;
			sub_soat_lt24 = a_soat;
			end;
			else if n_gap_mo < 24 and n_gap_mo ne . then do;
			sub_sev_lt24 = 5;
			sub_soat_lt24 = n_soat;
			end;
			else sub_sev_lt24 = 99;
		end;
	if ref_care = . then ref_care = 9;
	if ref_case = . then ref_case = 9;
	label
		first_ref = "First ever referral"
		first_clu = "At least 1 year after prior referral, if any"
		sub_sev_lt6 = "Most certain referral within 6 months"
		sub_sev_lt12 = "Most certain referral within 12 months"
		sub_sev_lt18 = "Most certain referral within 18 months"
		sub_sev_lt24 = "Most certain referral within 24 months"
		sub_soat_lt6 = "Allegation type of most certain referral within 6 months"
		sub_soat_lt12 = "Allegation type of most certain referral within 12 months"
		sub_soat_lt18 = "Allegation type of most certain referral within 18 months"
		sub_soat_lt24 = "Allegation type of most certain referral within 24 months";
	run;
/*
proc print data = referrals_15 (obs = 30);
	var fkclient_t ref_rcv_dt severity soat s_gap_mo 
		s_soat i_gap_mo i_soat u_gap_mo 
		u_soat a_gap_mo a_soat n_gap_mo n_soat sub_sev_lt6 sub_soat_lt6
		sub_sev_lt12 sub_soat_lt12 sub_sev_lt18 sub_soat_lt18 sub_sev_lt24 sub_soat_lt24;
	run;
*/
proc sort data = referrals_15;
	by fkclient_t ref_rcv_dt;
	run;
/*
proc contents data = referrals_15;
	run;
*/
/*example*/

/*
ods html file="/pool01/home/joemagruder/referrals/recurrence_test_01.xls" RS = none; 
proc tabulate data = referrals_15 missing format = comma10.0 noseps;
	class severity sub_sev_lt6 sub_sev_lt12 sub_sev_lt18 sub_sev_lt24;
	table  sub_sev_lt6 sub_sev_lt12 sub_sev_lt18 sub_sev_lt24 all, severity*n=' ' all*n=' '
		/box = "Subsequent Referral Findings";
	format  sub_sev_lt6 sub_sev_lt12 sub_sev_lt18 sub_sev_lt24 severity severity.;
	where year(ref_rcv_dt) = 2019 and first_clu = 1
		and ref_in_care = . and ref_pre_care = .;
	label severity = "Initial Referral Finding"
		sub_sev_lt6 = "Within 6 months"
		sub_sev_lt12 = "Within 12 months"
		sub_sev_lt18 = "Within 18 months"
		sub_sev_lt24 = "Within 24 months";
	title3 "Referrals in 2019";
	title4 "Children with no referrals in prior 12 months";
	run;

ods html close;

title3 ' ';
title4 ' ';
*/

data working_file (keep = fkclient_t birth_dt age gender_cd ethnic hisp_cd hisp_cdx cens_ethnic cens_rc
		cnty_spfcd county ref_rcv_dt alg_dspc investigate dsp_rsnc reporter 
		p_ethnctyc severity soat first_clu first_ref ref_case ref_care 
		sub_sev_lt6 sub_sev_lt12 sub_sev_lt18 sub_sev_lt24
		sub_soat_lt6 sub_soat_lt12 sub_soat_lt18 sub_soat_lt24);
	set referrals_15;

if sub_sev_lt6 = . then sub_sev_lt6 = 99;
if sub_sev_lt12 = . then sub_sev_lt12 = 99;
if sub_sev_lt18 = . then sub_sev_lt18 = 99;
if sub_sev_lt24 = . then sub_sev_lt24 = 99;

if COUNTY = 5900 then COUNTY = 9800;
run;

proc contents data = working_file varnum;
	run;

/*test of working file*/
/*
	ods html file="/pool01/home/joemagruder/referrals/recurrence_test_01.xls" RS = none; 
proc tabulate data = working_file missing format = comma10.0 noseps;
	class severity sub_sev_lt6 sub_sev_lt12 sub_sev_lt18 sub_sev_lt24 ref_care;
	table ref_care all, sub_sev_lt6 sub_sev_lt12 sub_sev_lt18 sub_sev_lt24 all, severity*n=' ' all*n=' '
		/box = "Subsequent Referral Findings";
	format  sub_sev_lt6 sub_sev_lt12 sub_sev_lt18 sub_sev_lt24 severity severity.;
	where year(ref_rcv_dt) = 2019 and first_clu = 1;
	label severity = "Initial Referral Finding"
		sub_sev_lt6 = "Within 6 months"
		sub_sev_lt12 = "Within 12 months"
		sub_sev_lt18 = "Within 18 months"
		sub_sev_lt24 = "Within 24 months";
	title3 "Referrals in 2019";
	title4 "Children with no referrals in prior 12 months";
	run;

ods html close;
*/


**now move to specific periods as a macro;
/*testing only
%let syear = 2019;
%let sqtr = 1;
*/

/* macro is called at end of file... */
/*macro from referral program*/
proc datasets;
delete out_file;
run;

%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 LA total */
/* create the ordered subset */
proc sql ;
create table PASS0 as
select *
from working_file
where REF_RCV_DT between &BEGWIN and &ENDWIN
;
create table PASS1 as
select *
from PASS0
where (1901 <= COUNTY < 2000)
order by FKCLIENT_T, REF_RCV_DT
;

/* select one record for each child per year (most severe) */
data PASS2 ;
set pass1 ;
by  FKCLIENT_T REF_RCV_DT ;
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",
first_clu,
first_ref,
/*
ref_case,
ref_care,
*/
sub_sev_lt6,
sub_sev_lt12,
sub_sev_lt18,
sub_sev_lt24,
/*
sub_soat_lt6,
sub_soat_lt12,
sub_soat_lt18,
sub_soat_lt24,
*/

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,
first_clu,
first_ref,
/*
ref_case,
ref_care,
*/
sub_sev_lt6,
sub_sev_lt12,
sub_sev_lt18,
sub_sev_lt24 /*,
sub_soat_lt6,
sub_soat_lt12,
sub_soat_lt18,
sub_soat_lt24 */
;

proc contents data = pass3;
run;


/* 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
        first_clu first_ref /* ref_case ref_care */ sub_sev_lt6 sub_sev_lt12 sub_sev_lt18
        sub_sev_lt24 /* sub_soat_lt6 sub_soat_lt12 sub_soat_lt18 sub_soat_lt24 */ 4;
set PASS3 ;
PERIOD_DT=yyq(&SYEAR,&SQTR) ;
/* CNTY_SPFCD='19' ; */
COUNTY = 1900;
*RSP_AGY_CD="&AGY_CD" ;
run ;

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

/* process the state total */
/* create the ordered subset */
proc sql ;
create table PASS0 as
select *
from working_file
where REF_RCV_DT between &BEGWIN and &ENDWIN
;
create table PASS1 as
select *
from PASS0
order by FKCLIENT_T, REF_RCV_DT
;

/* select one record for each child per year (most severe) */
data PASS2 ;
set pass1 ;
by  FKCLIENT_T REF_RCV_DT ;
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",
first_clu,
first_ref,
/*
ref_case,
ref_care,
*/
sub_sev_lt6,
sub_sev_lt12,
sub_sev_lt18,
sub_sev_lt24,
/*
sub_soat_lt6,
sub_soat_lt12,
sub_soat_lt18,
sub_soat_lt24,
*/

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,
first_clu,
first_ref,
/*
ref_case,
ref_care,
*/
sub_sev_lt6,
sub_sev_lt12,
sub_sev_lt18,
sub_sev_lt24 /* ,
sub_soat_lt6,
sub_soat_lt12,
sub_soat_lt18,
sub_soat_lt24 */
;

proc contents data = pass3;
run;


/* 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
	first_clu first_ref /* ref_case ref_care */ sub_sev_lt6 sub_sev_lt12 sub_sev_lt18
	sub_sev_lt24 /* sub_soat_lt6 sub_soat_lt12 sub_soat_lt18 sub_soat_lt24 */ 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=dvlp.recurr 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 COUNTY, FKCLIENT_T, REF_RCV_DT
;

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

/* Generate the group counts */
proc sql ;
create table PASS3 as
select
/*
CNTY_SPFCD,
*/
COUNTY,
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",
first_clu,
first_ref,
/*
ref_case,
ref_care,
*/
sub_sev_lt6,
sub_sev_lt12,
sub_sev_lt18,
sub_sev_lt24,
/*
sub_soat_lt6,
sub_soat_lt12,
sub_soat_lt18,
sub_soat_lt24,
*/
count(*) as COUNT label="INCIDENCE" format=comma12. 
from PASS2
group by
COUNTY, 
AGE, 
/*
INVESTIGATE,
*/
SEVERITY, 
SOAT, 
ETHNIC, 
/*
CENS_RC,
HISP_CDX,
CENS_ETHNIC,
*/
GENDER_CD, 
REPORTER,
first_clu,
first_ref,
/*
ref_case,
ref_care,
*/
sub_sev_lt6,
sub_sev_lt12,
sub_sev_lt18,
sub_sev_lt24  /* ,
sub_soat_lt6,
sub_soat_lt12,
sub_soat_lt18,
sub_soat_lt24  */
;

/* add values unique to this pass */
data PASS4 ;
length
/*RSP_AGY_CD $1*/
AGE ALG_DSPC ALG_TPC REPORTER GENDER_CD ETHNIC /* CENS_RC HISP_CDX CENS_ETHNIC */ PERIOD_DT COUNT 
	first_clu first_ref /* ref_case ref_care */ sub_sev_lt6 sub_sev_lt12 sub_sev_lt18
	sub_sev_lt24 /* sub_soat_lt6 sub_soat_lt12 sub_soat_lt18 sub_soat_lt24 */ 4;
set PASS3 ;
PERIOD_DT=yyq(&SYEAR,&SQTR) ;
/*RSP_AGY_CD="&AGY_CD" ;*/
run ;

/* Add table to master database */
proc datasets NOLIST ;
append base=dvlp.recurr 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,2023,4) ;

proc sort data = dvlp.recurr;
by PERIOD_DT COUNTY;
run;

proc sql;

create index COUNTY on dvlp.RECURR(COUNTY) ;
create index PERIOD_DT on dvlp.RECURR(PERIOD_DT) ;
/*
create index AT1 on dvlp.RECURR(COUNTY,PERIOD_DT) ;
create index FIRST_CLU on dvlp.RECURR(FIRST_CLU) ;
create index FIRST_REF on dvlp.RECURR(FIRST_REF) ;
*/

quit;

data test.recurr;
set dvlp.recurr;
;

proc sql;

create index COUNTY on test.RECURR(COUNTY) ;
create index PERIOD_DT on test.RECURR(PERIOD_DT) ;
/*
create index AT1 on test.RECURR(COUNTY,PERIOD_DT) ;
create index FIRST_CLU on test.RECURR(FIRST_CLU) ;
create index FIRST_REF on test.RECURR(FIRST_REF) ;
*/

quit;