*****************************************************************************************;
** Copyright (c) 2007 Center for Social Services Research,                             **;
** University of California at Berkeley. All rights reserved.                          **;
**                                                                                     **;
** This program produces data for Dynamic Point-In-Time Reports                        **;
**                                                                                     **;
** PIT                                                                                 **;
**                                                                                     **;
** These reports include all children who have an open placement episode in the        **;
** CWS/CMS system (excluding children who have a placement type of 'Mental Health'     **;
** (6133), 'Private Adoption' (36), or 'KinGAP' (6134)). On the count day, children    **;
** are assigned to the county in which they have an open case or referral (for cases   **;
** under the supervision of Child Welfare, Probation, or Other) or state id county     **;
** code (for cases under the supervision of Probation and Other).                      **;
**                                                                                     **;
** Programmer: S. Sean Lee                                                             **;
**                                                                                     **;
** Revision History:                                                                   **;
** 2007.08.07 Children with a Substitute Care Provider Relationship coded as           **;
**            'Relative/Nonguardian' are no longer automatically classified as         **;
**            Kinship Care placements. We instead provide a Caregiver Relationship     **;
**            filter that allows users to restrict report data based on a child's      **;
**            relationship to the substitute care provider.                            **;
** 2009.03.25 Changing PLC_FCLC = '1415' value to 'Foster' not 'Group'                 **;
** 2014.02.12 Add Removal Reason                                                       **;
** 2018.04.18 modify to use UCB_OFFICE for county and office assignment j magruder     **; 
** 2019.03.25 modify to use revised UCB_FC and to use payment county                   **;
** 2019.07.30 modify to include identification of youths who are known parents         **;
** 2019.08.16 modify to include Service Component on PIT date                          **;
** 2020.02.14 modify to include AFCARS status of placement                             **;
** 2020.07.07 modify to include OHMPL_ID                                               **;
** 2020.12.04 modify to include runaways from non foster care placements               **;
** 2022.04.14 modify to add county codes for children placed out-of-state/country      **;
** 2022.10.10 modify to add ICWA and tribal membership status                          **;
** 2024.04.15 modify to exclude runaways who have a guardian authority for placement   **;
** 2024.07.05 modify to add geographic relationship of supervising and physical        **;
**            presence county (OH_LOC_PIT)                                             **;
**                                                                                     **; 
*****************************************************************************************;

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


/*the following data steps create temporary files that are used in each quarterly iteration
   of the Point In Time program*/

/* Voluntary Status:
   	find those times when children apparently were receiving voluntary services
	The macro identifies whether services were voluntary on the point in time date.
	This table ties voluntary status to the child rather than to case to make the 
	process more efficient.
*/
proc sql;
	create table vol_status as select
	case_t.fkchld_clt as fkclient_t,
	case_t.identifier as fkcase_t,
	csvol_st.start_dt as vs_s_dt format mmddyy10. label = "Voluntary Services start date",
	csvol_st.end_dt as vs_e_dt format mmddyy10. label = "Voluntary Services end date",
	csvol_st.VLNTRY_IND
	from cws.csvol_st, cws.case_t
	where csvol_st.fkcase_t = case_t.identifier
	order by fkclient_t, vs_s_dt;
	quit;



/* Case Assignment:
	create case assignment table to be used in each iteration of the macro*/
/*moved from within the macro to outside the macro j magruder 2019.06.14*/
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;



/* Child as Parent:

/*determine which of the youth in UCB_FC are known to be parents
	of children (as reported in CWS/CMS with assumption that no youth
	in care would have given birth before age 10*/

/*note (2022.05.20): work by USC CDN using birth certificate matches suggests 
	this is an undercount, especially of male parents, although birth 
	certificate matches also appear miss children identified by this
	method*/

/*create a list of unique youth in UCB/FC whose placement episodes ended
 	after their 10th birthday 
	also eliminate placement episodes starting before birth and after age 20
	(likely data entry errors)*/
data unique_02 (keep = fkclient_t birth_dt gender_cd pe_s_dt pe_e_dt 
		tpe_e_dt tpe_e_age pe_s_age agy_rspc);
	set dwh.ucb_fc2;
	if pe_e_dt = . then tpe_e_dt = &cutoff.;
		else tpe_e_dt = pe_e_dt;
	tpe_e_age = %age(tpe_e_dt, birth_dt);
	pe_s_age = %age(pe_s_dt, birth_dt);
	format tpe_e_dt mmddyy10.;
	if tpe_e_age < 10 or pe_s_age > 20 or pe_s_age < 0 then delete;
	if gender_cd = ' ' then gender_cd = "U";
	run;

/*take one record per youth per episode*/
proc sort data = unique_02 out = unique_04 nodupkey ;
	by fkclient_t pe_s_dt;
	run;

/*reduce to one record per youth - using last episode
	Thus unique_06 has one record for each youth who has been in care at any time after 10th birthday*/
data unique_05 (keep = fkclient_t birth_dt gender_cd pe_s_dt tpe_e_dt agy_rspc);
	set unique_04;
	by fkclient_t pe_s_dt;
	if last.fkclient_t;
	run;

/*add in youth's first placement episode ever*/
data firstpe_02(keep = fkclient_t removal_dt agy_rspc);
	set cws.plc_epst;
	if agy_rspc in (33, 34, 5603);
	run;

proc sort data = firstpe_02;
	by fkclient_t removal_dt;
	run;

data firstpe (rename= (removal_dt = pe1_s_dt));
	set firstpe_02;
	by fkclient_t removal_dt;
	if first.fkclient_t;
	format removal_dt mmddyy10.;
	label removal_dt = "First PE Start Date";
	run;

/*add first episode ever start date to file*/
proc sql;
	create table unique_06 as select
	unique_05.*,
	firstpe.pe1_s_dt
	from unique_05 left join firstpe
	on unique_05.fkclient_t = firstpe.fkclient_t
	order by fkclient_t, pe_s_dt;
	quit;

/*Find those youth identified on the child client table as being parents
	Note - these youth are included in the final totals only if a child
	can be linked to the youth*/
proc sql;
	create table unique_08 as select
	unique_06.*,
	chld_clt.mnrmom_ind
	from unique_06 left join cws.chld_clt
	on unique_06.fkclient_t = chld_clt.fkclient_t
	order by fkclient_t;
	quit;


**Identify children of these youth in care;

*use standard language for identifying parent, except select potential children
 of, rather than parents of, person in placement.  Do not include adoptive relationship;
proc sql;
	create table offspring_01a as
		select unique_08.fkclient_t,
            unique_08.birth_dt,
            unique_08.gender_cd,
			unique_08.mnrmom_ind,
			unique_08.pe1_s_dt,
			unique_08.pe_s_dt,
			unique_08.tpe_e_dt,
			unique_08.agy_rspc,
			cln_relt.clntrelc as ch_clntrelc,
			cln_relt.fkclient_0 as ch_id,
			cln_relt.start_dt as ch_rel_s format mmddyy10.,
			cln_relt.end_dt as ch_rel_e format mmddyy10.
		from unique_08,
			cws.cln_relt
		where unique_08.fkclient_t = cln_relt.fkclient_t and
         cln_relt.clntrelc in (247, 5620, 246, 252, 6361, 251, 205, 207, 204, 211, 213, 210,
			196, 198, 195, 291, 6360, 290, 190, 192, 189, 285, 287, 284);
	create table offspring_01b as
		select unique_08.fkclient_t,
            unique_08.birth_dt,
            unique_08.gender_cd,
			unique_08.mnrmom_ind,
			unique_08.pe1_s_dt,
			unique_08.pe_s_dt,
			unique_08.tpe_e_dt,
			unique_08.agy_rspc,
			cln_relt.clntrelc as ch_clntrelc,
			cln_relt.fkclient_t as ch_id,
			cln_relt.start_dt as ch_rel_s format mmddyy10.,
			cln_relt.end_dt as ch_rel_e format mmddyy10.
		from unique_08,
			cws.cln_relt
		where unique_08.fkclient_t = cln_relt.fkclient_0 and
         cln_relt.clntrelc in (247, 5620, 246, 252, 6361, 251, 205, 207, 204, 211, 213, 210,
			196, 198, 195, 291, 6360, 290, 190, 192, 189, 285, 287, 284);
	quit;

/*merge above files - the "children" are mostly parents of those in care*/
data offspring_03;
	set offspring_01a offspring_01b;
		label ch_rel_s = 'Parent relationship start date';
		label ch_rel_e = 'Parent relationship end date';
	run;

*limit to one record per possible child;
*most identified children actually mothers;

proc sort data = offspring_03;
	by fkclient_t ch_id;
	run;

data offspring_03a;
	set offspring_03;
	by fkclient_t ch_id;
	if first.ch_id = 1;
	run;

*get basic client data about child of youth parent in care;
proc sql;
	create table offspring_05 as
		select offspring_03a.*,
			client_t.birth_dt as ch_dob format mmddyy10.,
			client_t.death_dt as ch_dod format mmddyy10.,
			client_t.gender_cd as ch_sex,
			client_t.P_ETHNCTYC as ch_P_ETHNCTYC,
			client_t.hisp_cd as ch_hisp_cd,
			client_t.P_LANG_TPC as ch_p_lang_tpc,
			client_t.creatn_dt as ch_cr_dt format mmddyy10. label "Date child record created"
		from offspring_03a left join cws.client_t
		on offspring_03a.ch_id = client_t.identifier
		order by fkclient_t, ch_dob desc;
	quit;

*create one record for each child of each parent. Thus a youth may have more than
	one row if he or she has more than one child and a child may have more than
	one row if both the child's parents have a history of being youth in care;
*delete record where "child" is less than 10 years younger than youth in care;
data offspring_07 (drop = ch_p_ethnctyc ch_hisp_cd);
	set offspring_05;
    parent_age_at_birth = int((intck('month', birth_dt, ch_dob)-(day(ch_dob) lt day(birth_dt)))/12);
	/*delete records of parents of children in care (i.e., those where parent is younger
		than child) and those of improbable age (i.e., those where the parent is less
		than 10 years older than the child)*/
	if parent_age_at_birth < 10 then delete ;
	/*note problem of children born while in care, but parent age at birth > 21*/
	/*delete children born after parent left last placement episode
		or children born after parent 21 years of age*/
	if ch_dob > tpe_e_dt or parent_age_at_birth > 20 then delete;
	/* Assign Ethnicity Class */
	select(ch_P_ETHNCTYC) ;
	   when(.,0,6351,6352,6453) ch_ethnic= 99 ;          /* Missing */
	   when(823,826) ch_ethnic= 1 ;                     /* Black */
	   when(839,840,841,842,843,844) ch_ethnic= 2 ;     /* White */
	   when(830,3162,3163,3164,3165) ch_ethnic= 3 ;     /* Hispanic */
	   when(820,821) ch_ethnic= 5 ;                     /* Native Am */
       when(5922,5923,822,824,825,827,828,829,
          831,832,833,834,835,836,837,838) ch_ethnic= 4 ;  /* Asian */
       otherwise ch_ethnic= 00 ;                            /* Invalid Value */
	end ;
	if ch_HISP_CD eq 'Y' then ch_ethnic= 3 ;           /* Hispanic */
	label ch_ethnic = "Child Ethnicity";

    *calculate child's probable conception date - based on Mittendorf, et al. The Length of
     Uncomplicated Human Gestation, Obstetrics & Gynecology, 1990 Jun 75(6): 929-32;
    ch_conc = ch_dob - 274;
    /*if ch_conc > &start. then post_pit = 1; else post_pit = 0;*/
	if gender_cd = " " then gender_cd = "U";
    format ch_conc mmddyy10.;
	ch_ch_cr_dt_age = int((intck('month', ch_dob, ch_cr_dt)-(day(ch_cr_dt) lt day(ch_dob)))/12);
		if ch_ch_cr_dt_age < 0 then ch_ch_cr_dt_age = .;
	label ch_ch_cr_dt_age = "Age at which CWS/CMS child record created";
    label ch_conc = "Probable conception date";
    *label post_pit = "Baby conceived after PIT date";
	run;


/*  Identify those children of youth in offspring_07 who were subsequently adopted*/
/*FINALIZED ADOPTIONS*/
/*add data about finalized adoptions*/
/*identify finalized adoptions */
proc sql;
	create table fadopt_01 as select
		adplc_t.fkadchcl_t,
		adplc_t.final_dt as ad_final_dt format mmddyy10.,
		adchcl_t.fkchld_clt as ch_id
	from cws.adplc_t, cws.adchcl_t
	where adplc_t.fkadchcl_t = adchcl_t.identifier
		and ad_final_dt ne .
	order by fkchld_clt;
	quit;

/*	there are a few records (5 children) with two adoptive placement records
  	but all showing the same adoption finalization date. This step eliminates
	duplicates*/
data fadopt_02;
	set fadopt_01; 
	by ch_id;
	if first.ch_id;
	run;

proc sql;
	create table offspring_08 as select
	offspring_07.*,
	fadopt_02.ad_final_dt as ch_ad_final_dt
	from offspring_07 left join fadopt_02
	on offspring_07.ch_id = fadopt_02.ch_id
	order by fkclient_t, ch_id;
	quit;
/*establish censor_dt that is earliest of child adoption final date or child death data.
	In 201Q2 data there is one child with both, remainder (3045 - 173 death, 2873 adoption - 
	in 2018 Q2) have one or the other*/
data offspring_09;
	set offspring_08;
	if ch_ad_final_dt ne . and ch_dod ne . then do;
		if ch_ad_final_dt > ch_dod then ch_censor_dt  = ch_dod;
		else ch_censor_dt = ch_ad_final_dt;
		end;
	else if ch_dod ne . then ch_censor_dt = ch_dod;
	else if ch_ad_final_dt ne . then ch_censor_dt = ch_ad_final_dt;
	format ch_censor_dt mmddyy10.;
	run;

proc sort data = offspring_09;
	by fkclient_t parent_age_at_birth;
	run;

data offspring (keep = fkclient_t ch_id ch_dob ch_dod ch_ad_final_dt ch_censor_dt);
  set offspring_09;
run;


/*add afcars data to basic UCB_FC  file*/
data afcars_01a (keep = fkclient_t pmt_order ohmpl_id afcars);
	set dwh.ucb_fc_afcars;
	afcars = 1;
	run;

proc sort data = afcars_01a nodupkey;
	by fkclient_t pmt_order;
	run;

data ucb_fc_m;
	merge dwh.ucb_fc afcars_01a;
	by fkclient_t pmt_order;
	run;

proc freq data = ucb_fc_m;
	table afcars/missing;
	run;


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

**now move to specific periods as a macro;

%macro PIT(sq,sy,eq,ey);

*sq = start quarter, sy = start year, eq = end quarter, ey = end year;

%let empty=0;

%do SYEAR = &sy. %to &ey. ;

*** The following statements select the appropriate quarters for each year ***;

data _null_;

%if &sq. ^= 1 & &SYEAR.=&sy. %then
%do;

*** if the starting quarter is not 1 then process accordingly ***;

%if &sy.=&ey. %then
    %do;
    start_qtr=&sq. ;
    end_qtr=&eq. ;
    %end;

    %else
    %do;
    start_qtr=&sq. ;
    end_qtr=4 ;
    %end;

%end;

%else %if &eq. ^= 4 & &SYEAR.=&ey. %then
%do;

*** If the ending quarter is not 1 then process accordingly ***;

%if &sy.=&ey. %then
    %do;
    start_qtr=&sq. ;
    end_qtr=&eq. ;
    %end;

    %else
    %do;
    start_qtr=1 ;
    end_qtr=&eq. ;
    %end;

%end;

%else
%do;

*** Otherwise standard quarters ***;

start_qtr=1 ;
end_qtr=4;
%end;

call symput('stqtr',start_qtr);
call symput('enqtr',end_qtr);

run;

%do SQTR = &stqtr. %to &enqtr. ;

/* DATA cut */


data test1;/*all episodes open on PIT date*/
	/*OH_LOC_PIT recalculated at end of macro for PIT status*/
	*set dwh.ucb_fc2;
	*set dwh.ucb_fc;
	set ucb_fc_m;
	*if icpc_in = 1 or /*nfc_only = 1 or*/ oh_s_dt = . then delete;

*set ucb_fc2;
*set DWH.UCB_FC_AFCARS (drop = ETHNIC);
*set pit.ucb_fc_afcars;

where ((PE_S_DT <= yyq(&SYEAR,&SQTR)) and (PE_E_DT > yyq(&SYEAR,&SQTR) or PE_E_DT = .)) and 
        AGY_RSPC not in (0036, 5605, 6133, 6134); 

run;

data test2; /*one record per episode open on PIT date*/
set test1;

by FKCLIENT_T;
if first.FKCLIENT_T;

run;

data test3; /*limit to all placements in episode open on PIT date*/
set test1;

where (OH_S_DT ne .) and 
	((OH_S_DT <= yyq(&SYEAR,&SQTR)) and 
	(OH_E_DT > yyq(&SYEAR,&SQTR) or OH_E_DT = .));
/*modify placement location county code to add codes for out-of-state but in country (70)
	and out-of-country (80) placements*/ 
	if oh_state_cd ge 1822 and oh_state_cd le 1880 and oh_state_cd ne 1828 then oh_cnty = "70";
	if oh_cntry_c ge 470 and oh_cntry_c le 570 and oh_cntry_c ne 563 then oh_cnty = "80";
	if oh_cnty = '  ' then oh_cnty = "99";
	/*oh_cnty set to runaway after runaway status calculated in data step creating test12, below*/

run;

proc sort data = test3; 
by FKCLIENT_T SPELL PLCMNT;
run;

data test4;/*if >1 placement open on PIT date, take last one opened*/
set test3;

by FKCLIENT_T;
if last.FKCLIENT_T;

run;

/* LIMBO */

data test5a; ;/*assign limbo=0 to records where there was a placement on PIT date*/
set test4;

LIMBO = '0';

run;

proc sql;

create table test4a as /*open episodes but no open foster care placement on PIT date*/
select *
from test2
where FKCLIENT_T not in (select FKCLIENT_T from test4);

create table test4b as /*placements in episode for those with no open foster care placement on PIT date*/
select *
from test1
where FKCLIENT_T in (select FKCLIENT_T from test4a);

quit;

/* Get Out of Home Placement Info Prior to Study Date */

data test4c;
set test4b;

where OH_S_DT ne . and OH_S_DT <= yyq(&SYEAR,&SQTR);

run;

data test4d; /*last placement prior to PIT date where no PIT placement*/
set test4c;

by FKCLIENT_T;
if last.FKCLIENT_T;

run;

/* PLCMNT = 0, If There's No Valid Out of Home Placement Prior to Study Date */

proc sql;

create table test4e as
select *
from test4a
where FKCLIENT_T not in (select FKCLIENT_T from test4d);

quit;

data test4f;
set  test4e;

PLCMNT = 0;

run;

data test5b; /*those with no PIT placement but prior placement (test4d) or no prior placement (test4f)*/
set test4d test4f;

LIMBO = '1';

run;

data test5(drop = GENDER_CD); /*those with PIT placement (test5a) and those without (test5b)*/
set test5a test5b;

/* Caregiver Relationship */

select(SCP_RLTC);
   when('1636') PIT_SCPR=1;              /* Nonrelative Guardian */
   when('1637') PIT_SCPR=2;              /* Nonrelative Nonguardian */
   when('1638') PIT_SCPR=3;              /* Relative Guardian */
   when('1639') PIT_SCPR=4;              /* Relative Nonguardian */
   when('6715') PIT_SCPR=5;              /* Self */
   when('6990') PIT_SCPR=6;              /* NREFM Guardian */
   when('6991') PIT_SCPR=7;              /* NREFM Nonguardian */
   otherwise PIT_SCPR = 99;              /* Missing */
end;

/* Agency Type */

select(AGY_RSPC);
   when(34) AGENCY = 1;         /* Child Welfare */
   when(33, 5603) AGENCY = 2;   /* Probation */
   otherwise AGENCY = 3;        /* Other */
end;

/* Assign Ethnicity Class */

*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(820,821) ETHNIC=5 ;                          /* Native Am */
  * when(5922,5923,822,824,825,827,828,829,
        831,832,833,834,835,836,837,838) ETHNIC=4 ;  /* Asian */
    *otherwise ETHNIC=99 ;                            /* Invalid Value */
*end ;
*if HISP_CD eq 'Y' then ETHNIC=3 ;               /* Hispanic */

/* 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 Age Class */

AGE = int((intck('month',BIRTH_DT,yyq(&SYEAR,&SQTR))-(day(yyq(&SYEAR,&SQTR)) lt day(BIRTH_DT)))/12);

if AGE lt 0 or AGE gt 21 then AGE = 99;


if AGE = 0 then AGEGRP = 1;
else if AGE in (1, 2) then AGEGRP = 2;
else if AGE in (3, 4, 5) then AGEGRP = 3;
else if AGE in (6, 7, 8, 9, 10) then AGEGRP = 4;
else if AGE in (11, 12, 13, 14, 15) then AGEGRP = 5;
else if AGE in (16, 17, 18) then AGEGRP = 6;
else if AGE in (19, 20, 21) then AGEGRP = 7;
else if AGE lt 0 or AGE gt 21 then AGEGRP = 8;


TIME=INT((yyq(&SYEAR,&SQTR) - PE_S_DT)/30.438);

if GENDER_CD = 'M' then GENDER = 2;
else if GENDER_CD = 'F' then GENDER = 1;
else if GENDER_CD = "I" then GENDER = 3;
else GENDER = 99;

run;

/* ADD Transitional Housing Info */
/*this field is on the revised UCB_FC*/
/*
proc sql;

create table test6 as
select x.*, y.TRNHSG_IND, y.TRNHSG_FAC
from test5 as x left join cws.PLC_HM_T as y
on x.FKPLC_HM_T = y.IDENTIFIER;

quit;
*/
data test7;
set test5;


if LIMBO = '0' then do;
select(PLC_FCLC);
  when('2222') FED1 = 1;            /* Pre-Adopt */
  when('1421','1422') FED1 = 2;     	      /* Kin */
  when('1415','1416') FED1 = 3;     /* Foster */
  when('1414', '2200') FED1 = 4  ;  /* FFA */
  when('1419') FED1 = 9;            /* Court Specified Home */
  when('7208') FED1 = 9.2;  /* Tribe Specified/Approved Home */
  when('1417') FED1 = 10;           /* Group */
  when('1418','7027') FED1 = 11;    /* Shelter */
  when('1420', '6362', '6363', '6364', '6365', '6366', '6367',
		'6368', '6369', '6370', '6371') FED1 = 13;           /* Non-FC */
  when('5411') FED1 = 14;           /* Guardian */
  when('6716') FED1 = 19;           /* SILP */
/*  when('6914.1') FED1 = 5;  */        /* RFH - Relative */
/*  when('6914.2') FED1 = 6;  */        /* RFH - Non Relative */
/*  when('6915.1') FED1 = 7;  */        /* RFH FFA - Relative */
/*  when('6915.2') FED1 = 8;  */        /* RFH FFA - Non Relative */
/*  when('6916') FED1 = 12;   */        /* STRTP */
  when('7164','7181') FED1 = 17;    /* Runaway 2021.08.23 j magruder*/
  when('0',' ') FED1 = 99;          /* Missing */
  otherwise FED1 = 29;              /* Other */  
  /*otherwise FED1 = 99; */             /* Missing */
end;
end;

if LIMBO = '0' and TRNHSG_FAC = 'Y' then FED1 = 20;    /* Transitional Housing */

run;

/* Pre-Adoptive */

data test8a;
set cws.ADPLC_T;

	if FINAL_DT = . and 
		RMV_DT = . and ADAGSGN_DT <= yyq(&SYEAR,&SQTR) then PRE_ADOPT = 1;
	else if FINAL_DT > (yyq(&SYEAR,&SQTR) + 1) and 
		ADAGSGN_DT <= yyq(&SYEAR,&SQTR) then PRE_ADOPT = 1;
	else if RMV_DT > (yyq(&SYEAR,&SQTR) + 1) and 
		ADAGSGN_DT <= yyq(&SYEAR,&SQTR) then PRE_ADOPT = 1;

run;

proc sql;

create table adopt1 as
	select *
	from test7
	where FKCLIENT_T in (select FKCHLD_CLT from cws.ADCHCL_T where SERVEND_DT = .);

create table test8a1 as
	select x.PRE_ADOPT, y.FKCHLD_CLT
	from test8a as x left join cws.ADCHCL_T as y
	on x.FKADCHCL_T = y.IDENTIFIER
order by FKCHLD_CLT;

quit;

data test8a2;
set test8a1;

where PRE_ADOPT = 1;

by FKCHLD_CLT;
if first.FKCHLD_CLT;

run;

proc sql;

create table adopt2 as
	select FKCHLD_CLT, PRE_ADOPT
	from test8a2
		where FKCHLD_CLT in (select FKCHLD_CLT from adopt1)
	order by FKCHLD_CLT;

quit;

data adopt3;
set adopt2;

by FKCHLD_CLT;
if first.FKCHLD_CLT;

run;

/* Non Foster Care */

data test8b(keep = FKPLC_EPS0 FKPLC_EPST NFC);
set cws.NFC_PLCT;

	if (START_DT ne . and START_DT <= yyq(&SYEAR,&SQTR)) and 
		(END_DT > yyq(&SYEAR,&SQTR) or END_DT = .)
		then NFC = 1;

run;

data test8b1;
	set test8b;

	where NFC = 1;

run;

proc sort data = test8b1;
	by FKPLC_EPST;
run;

data test8b2;
set test8b1;

by FKPLC_EPST;
if first.FKPLC_EPST;

run;

proc sort data = test8b1;
by FKPLC_EPS0;
run;

data test8b3;
set test8b1;

by FKPLC_EPS0;
if first.FKPLC_EPS0;

run;

proc sql; /*add markers for pre-adopt and NFC to PIT FC file*/

	create table test9a as
	select x.*, y.PRE_ADOPT
	from test7 as x left join adopt3 as y
	on x.FKCLIENT_T = y.FKCHLD_CLT;

	create table test9b as
	select x.*, y.NFC as NFC_1
	from test9a as x left join test8b2 as y
	on x.FKCLIENT_T = y.FKPLC_EPST;

	create table test9c as
	select x.*, y.NFC as NFC_2
	from test9b as x left join test8b3 as y
	on x.PLCEP_ID = y.FKPLC_EPS0;

quit;

data test10;/*override FC type if pre-adopt, put in NFC only if no FC type*/
set test9c;

   *** Set values for FED2 ***
       1=Pre-adopt         13=Non-FC;

if PRE_ADOPT = 1 then FED2 = 1;                                   /* Pre-Adopt */
else if (NFC_1 = 1 or NFC_2 = 1) and LIMBO = '1' then FED2 = 13;  /* Non-FC */

run;

data test11;
set test10;

   *** Set values for FED3 ***
       1=Pre-adopt          17=Runaway     18=Trial Home Visit
	   29=Other(Limbo Other)
   ***;

if LIMBO = '1' and FED2 = . and PLCMNT ne 0 then
  do;
    if PLCG_RNC = 5535 then FED3 = 1;       /* Pre-Aopt */
    else if PLCG_RNC = 1431 then FED3 = 17;  /* Runaway */
    else if PLCG_RNC = 1440 then FED3 = 18;  /* Trial Home Visit */
    else FED3 = 29;                          /* Other */
  end;
run;

data test12;
set test11;

if FED1 ne 99 then FED = FED1;

if FED3 = 17 then FED = 17;
else if FED3 = 18 then FED = 18;    /*Trial Home Visit */
else if FED3 = 19 then FED = 19;    /*SILP*/
else if FED3 = 29 then FED = 29;	/*Other*/


if FED2 = 1 then FED = 1;
else if FED2 = 2 then FED = 2;
else if FED2 = 13 then FED = 13;

if FED3 = 1 then FED = 1;

if LIMBO = '1' and FED = . then FED = 29;

if FED1 = 20 then FED = 20;

/*if runaway and no placement location county, set oh_cnty to runaway*/
if oh_cnty = '  ' then oh_cnty = '99';
if oh_cnty in ('  ', '99') and fed = 17 then oh_cnty = '90';
run;

/* County */

/*modify program to use assignment from UCB_OFFICE table. If no assignment on that table,
	use ST_ID table. If no assignment on that table, use county of removal
	2018.04.18 j magruder*/
proc sql;
	create table assign_81 as
		select test12.*,
			ucb_office.*
			from test12 left join dwh.ucb_office
		on ucb_office.fkchld_clt = test12.fkclient_t
			and office_s_dt le yyq(&SYEAR,&SQTR)
			and (office_e_dt = . or office_e_dt ge yyq(&SYEAR,&SQTR))
		order by fkclient_t, office_s_dt;
		;
	quit;

/* Not all records will have office assignment data - especially probation records from 
   before about 2010. Separate those that do (assign_82a) from those that don't (assign_82b) */

data assign_82a assign_82b (drop = fkchld_clt cnty_spfcd office_s_dt office_e_dt type);
	set assign_81;
	if office_s_dt ne . then output assign_82a;
		else output assign_82b;
	run;

/* For those with no office data, see if the State ID county data identifies a county with 
   start and end dates */

proc sql;
	create table assign_82b1 as
		select st_id.*,
			assign_82b.*
			from assign_82b left join dwh.st_id
		on st_id.fkchld_clt = assign_82b.fkclient_t
			and (st_id.office_s_dt le yyq(&SYEAR,&SQTR)
			and (st_id.office_e_dt = . or st_id.office_e_dt ge yyq(&SYEAR,&SQTR)))
		order by fkclient_t, office_s_dt;
		;
	quit;

/* Put two files back together - i.e., add state ID county derrived identifiers to those records in 
   entries_02 that are lacking cnty_spfcd, office_s_dt and office_e_dt data */
/* If cnty_spfcd not valid (1 to 58) then set cnty_rspc to payment county (rsp_cnty) 2019.06.10 j magruder*/

data assign_83;
	set assign_82a assign_82b1;
	if cnty_spfcd = "  " or cnty_spfcd > "58" then cnty_spfcd = rsp_cnty;
	run;

proc sort data = assign_83 out = assign_84a;
	by fkclient_t office_s_dt;
	run;

/* program material that determined if the child's case was an incoming ICPC case removed
	because the revised UCB_FC file does not include incoming ICPC episodes*/
/*if no office assignment data, use county of removal*/
/*data assign_84a;
	set assign_83;
	by fkclient_t office_s_dt;
	if last.fkclient_t;
	if cnty_spfcd = '  ' then cnty_spfcd = rsp_cnty;
	run;*/

/* Adding Case ID */
/* Use the CASE_ASSIGN table moved to the start of the program (outside of the
	macro j magruder 2019.06.14*/
/*
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;
*/
proc sql;

   create table assign_84b as
   select A.fkclient_t,
          b.caseid,
          B.START_DT,
          B.END_TM,
          B.END_DT
   from assign_84a A, CASE_ASSIGN B
   where A.FKCLIENT_T eq B.FKCHLD_CLT and
         B.START_DT ne . and
         B.START_DT <= yyq(&SYEAR,&SQTR) <= B.END_DT
   order by a.fkclient_t, B.START_DT, B.END_DT, END_TM
   ;
quit;

/*proc sort data = assign_84b; by fkclient_t START_DT END_DT END_TM; run;*/

*** Where more than 1 CASE_ID, use the most recent. ***;

data assign_84c ;
   set assign_84b ;
   by fkclient_t ;
   if last.fkclient_t then output ;
   drop START_DT END_DT END_TM;
run ;

*** Join the CASE_ID back to the original file ***;

proc sql;

   create table assign_84 as
   select x.*,
          y.CASEID
   from assign_84a as x left join assign_84c as y
   on x.fkclient_t = y.fkclient_t
     ;
quit;


/*
*** commented out because revised UCB_FC table does not include incoming ICPC;
*** Check for ICPC Instances, make sure to keep the unique value for your
    dataset (here it is THIRD_ID).
    ICPC instances come from the intervention reason table, the intervention
    Reason table only links to the CASE table.
***;*/
/*	Delete any placements where case intervention reason is incoming ICPC
	These placements are not California's responsibioity and would be reported 
	by the sending state*/
/*	assign values to COUNTY based on UCB_OFFICE data*/

data assign_cty (drop = x_cws_off_no  x_fkcws_offt x_cnty) ;
  /* merge assign_84(in=a)
         icpc;
   by fkclient_t;
   if a;
   if intvrsnc=1226 then delete;*/
	set assign_84;
  
   if TIME ge 0 and TIME lt 12 then TIME_IN = 1;
   else if TIME ge 12 and TIME lt 24 then TIME_IN = 2;
   else if TIME ge 24 and TIME lt 36 then TIME_IN = 3;
   else if TIME ge 36 and TIME lt 48 then TIME_IN = 4;
   else if TIME ge 48 and TIME lt 60 then TIME_IN = 5;
   else if TIME ge 60 then TIME_IN = 6;
   else if TIME lt 0 then TIME_IN = 99;

   drop office_s_dt office_e_dt type; * intvrsnc;

PERIOD_DT=yyq(&SYEAR,&SQTR) ;

if AGE lt 0 or AGE gt 21 then delete ; *AGE = 99 ;

	   x_cws_off_no = cws_off_no;
	   x_fkcws_offt = fkcws_offt;
	   x_cnty = cnty_spfcd;
       
		*%include "/pool01/home/joemagruder/Los_Angeles/county.sas";
		%include "/dss/SAS/PROGRAM/CWS_CMS/SAS_MACRO/county.sas";

        *if cnty_spfcd = 19 then do;
                     *if cws_off_no in("S3253") then COUNTY = 1901; /*Belvedere*/
                *else if cws_off_no in("S1277") then COUNTY = 1902; /*Compton*/
                *else if cws_off_no in("S1280") then COUNTY = 1903; /*El Monte*/
                *else if cws_off_no in("S1254") then COUNTY = 1904; /*Glendora*/

                *else if cws_off_no in("S2218") then COUNTY = 1905; /* Hawthorne old Wateridge South*/

                *else if cws_off_no in("S8234") then COUNTY = 1906; /*Lancaster*/
                *else if cws_off_no in("S3239") then COUNTY = 1907; /*Metro North*/
                *else if cws_off_no in("S8236") then COUNTY = 1908; /*Palmdale*/
                *else if cws_off_no in("S5252") then COUNTY = 1909; /*Pasadena*/
                *else if cws_off_no in("S1255") then COUNTY = 1910; /*Pomona*/

                *else if cws_off_no in("S8251") then COUNTY = 1911; /*Santa Clarita*/
                *else if cws_off_no in("S4261") then COUNTY = 1912; /*Santa Fe Springs*/
                *else if cws_off_no in("S7207") then COUNTY = 1913; /*South County*/
                *else if cws_off_no in("S2213") then COUNTY = 1914; /*Torrance*/
                *else if cws_off_no in("S5211") then COUNTY = 1915; /*Van Nuys*/

                *else if cws_off_no in("S6219") then COUNTY = 1916; /*Vermont Corridor*/
                *else if cws_off_no in("S2217") then COUNTY = 1917; /*Wateridge old Wateridge North*/

               **else if cws_off_no in("S2218") then COUNTY = 1917; /*Wateridge South*/

                *else if cws_off_no in("S6260") then COUNTY = 1918; /*West Los Angeles*/
                *else if cws_off_no in("S5212") then COUNTY = 1919; /*West SF Valley*/

                *else if cws_off_no in("S0216") then COUNTY = 1920; /*Adoptions*/
                *else if cws_off_no in("S0222") then COUNTY = 1924; /*Medical & ASFA*/
                *else if cws_off_no in("S1251") then COUNTY = 1931; /*American Indian*/
                *else if cws_off_no in("S1250") then COUNTY = 1932; /*Asian Pacific Project*/
                *else if cws_off_no in("S0249") then COUNTY = 1933; /*Deaf Services*/
                *else if cws_off_no in("S0237") then COUNTY = 1934; /*Pomona Family First*/
                *else if cws_off_no in("S1288") then COUNTY = 1935; /*CSEC*/
                *else if /*cws_off_no in("S1278") and */
                     fkcws_offt = "1iyrzAg196" then COUNTY = 1940; /*ROU*/
                *else if cws_off_no in("S0224") then COUNTY = 1941; /*MART*/
                *else if /*cws_off_no in("S1278") and */
                     fkcws_offt in("PHmifFE197", "QxMGV6D197") 
                                               then COUNTY = 1942; /* ER Command/Hotline */

                *else if cws_off_no in("S1286") then COUNTY = 1943; /* Juvenile Court Services */
                *else if cws_off_no in("S0246", "S0247", "S9056",
                             "S4261", "S0394") then COUNTY = 1945; /* Administration */
                *else if cws_off_no in("S0299") then COUNTY = 1950; /* Probation */
                *else if cws_off_no in("S9072") then COUNTY = 1960; /* KinGAP */
                *else                                COUNTY = 1999; /* Missing */
        *end;

        *else if cnty_spfcd = 01 then COUNTY = 0100;
        *else if cnty_spfcd = 02 then COUNTY = 0200;
        *else if cnty_spfcd = 03 then COUNTY = 0300;
        *else if cnty_spfcd = 04 then COUNTY = 0400;
        *else if cnty_spfcd = 05 then COUNTY = 0500;
        *else if cnty_spfcd = 06 then COUNTY = 0600;
        *else if cnty_spfcd = 07 then COUNTY = 0700;
        *else if cnty_spfcd = 08 then COUNTY = 0800;
        *else if cnty_spfcd = 09 then COUNTY = 0900;
        *else if cnty_spfcd = 10 then COUNTY = 1000;
        *else if cnty_spfcd = 11 then COUNTY = 1100;
        *else if cnty_spfcd = 12 then COUNTY = 1200;
        *else if cnty_spfcd = 13 then COUNTY = 1300;
        *else if cnty_spfcd = 14 then COUNTY = 1400;
        *else if cnty_spfcd = 15 then COUNTY = 1500;
        *else if cnty_spfcd = 16 then COUNTY = 1600;
        *else if cnty_spfcd = 17 then COUNTY = 1700;
        *else if cnty_spfcd = 18 then COUNTY = 1800;
       **else if cnty_spfcd = 19 then COUNTY = 1900;
        *else if cnty_spfcd = 20 then COUNTY = 2000;
        *else if cnty_spfcd = 21 then COUNTY = 2100;
        *else if cnty_spfcd = 22 then COUNTY = 2200;
        *else if cnty_spfcd = 23 then COUNTY = 2300;
        *else if cnty_spfcd = 24 then COUNTY = 2400;
        *else if cnty_spfcd = 25 then COUNTY = 2500;
        *else if cnty_spfcd = 26 then COUNTY = 2600;
        *else if cnty_spfcd = 27 then COUNTY = 2700;
        *else if cnty_spfcd = 28 then COUNTY = 2800;
        *else if cnty_spfcd = 29 then COUNTY = 2900;
        *else if cnty_spfcd = 30 then COUNTY = 3000;
        *else if cnty_spfcd = 31 then COUNTY = 3100;
        *else if cnty_spfcd = 32 then COUNTY = 3200;
        *else if cnty_spfcd = 33 then COUNTY = 3300;
        *else if cnty_spfcd = 34 then COUNTY = 3400;
        *else if cnty_spfcd = 35 then COUNTY = 3500;
        *else if cnty_spfcd = 36 then COUNTY = 3600;
        *else if cnty_spfcd = 37 then COUNTY = 3700;
        *else if cnty_spfcd = 38 then COUNTY = 3800;
        *else if cnty_spfcd = 39 then COUNTY = 3900;
        *else if cnty_spfcd = 40 then COUNTY = 4000;
        *else if cnty_spfcd = 41 then COUNTY = 4100;
        *else if cnty_spfcd = 42 then COUNTY = 4200;
        *else if cnty_spfcd = 43 then COUNTY = 4300;
        *else if cnty_spfcd = 44 then COUNTY = 4400;
        *else if cnty_spfcd = 45 then COUNTY = 4500;
        *else if cnty_spfcd = 46 then COUNTY = 4600;
        *else if cnty_spfcd = 47 then COUNTY = 4700;
        *else if cnty_spfcd = 48 then COUNTY = 4800;
        *else if cnty_spfcd = 49 then COUNTY = 4900;
        *else if cnty_spfcd = 50 then COUNTY = 5000;
        *else if cnty_spfcd = 51 then COUNTY = 5100;
        *else if cnty_spfcd = 52 then COUNTY = 5200;
        *else if cnty_spfcd = 53 then COUNTY = 5300;
        *else if cnty_spfcd = 54 then COUNTY = 5400;
        *else if cnty_spfcd = 55 then COUNTY = 5500;
        *else if cnty_spfcd = 56 then COUNTY = 5600;
        *else if cnty_spfcd = 57 then COUNTY = 5700;
        *else if cnty_spfcd = 58 then COUNTY = 5800;
        *else if cnty_spfcd in (59, 98, 99, .) then COUNTY = 9800;


    run;


/* Voluntary Status/Court Dependency */

/* Find voluntary status at point in time date */

proc sql;
	create table vol_01 as select
	assign_cty.*,
	vol_status.vlntry_ind,
	vol_status.vs_s_dt
	from assign_cty left join vol_status
	on assign_cty.fkclient_t = vol_status.fkclient_t
		and vol_status.vs_s_dt <= yyq(&SYEAR,&SQTR) and 
  		(vol_status.vs_e_dt = . or vol_status.vs_e_dt > yyq(&SYEAR,&SQTR))
	order by fkclient_t, vs_s_dt;
	quit;

/* Eliminate duplicates by taking later date status started */

data vol_02;
length COUNTY AGENCY COURT_IND AGE GENDER /*ETHNIC*/ CENS_ETHNIC HISP_CDX TIME_IN /*FED*/ 3;
length PERIOD_DT 4;
set vol_01;

by FKCLIENT_T VS_S_DT;
if last.FKCLIENT_T;

if VLNTRY_IND = 'N' then COURT_IND = 1;
else if VLNTRY_IND = 'Y' then COURT_IND = 2;
else COURT_IND = 99;

run;

/* Find service program in effect at point in time date */

proc sql;

create table vol_03 as
select x.*, y.EFFECTV_DT as SRV_S_DT, y.SRV_CMPC
from vol_02 as x left join cws.CS_SVCMT as y
on x.CASEID = y.FKCASE_T and 
   y.EFFECTV_DT <= yyq(&SYEAR,&SQTR) and 
  (y.END_DT = . or y.END_DT > yyq(&SYEAR,&SQTR))
order by FKCLIENT_T, SRV_S_DT;

quit;

/* Eliminate duplicates by taking last service program start before point in time date */

data vol_04;
set vol_03;

by FKCLIENT_T SRV_S_DT;
if last.FKCLIENT_T;
run;

/* Find placement episode legal authority */
/*need to do this here in spite of placement authority at start of episode being on
  UCB_FC because PIT legal authority may not be same as episode start legal authority*/

proc sql;
        
create table lg_auth_01 as
select x.*, y.EFFCTV_DT as LG_AUTH_S_DT, y.PLC_ATHC
from vol_04 as x left join cws.lg_autht as y
on x.FKCLIENT_T = y.FKPLC_EPST and 
   x.PLCEP_ID = y.FKPLC_EPS0 and 
   y.effctv_dt <= yyq(&SYEAR,&SQTR)
order by FKCLIENT_T, LG_AUTH_S_DT;

quit;

/* Take last legal authority before point-in-time date */

data lg_auth_02;
set lg_auth_01;

by FKCLIENT_T LG_AUTH_S_DT;
if last.FKCLIENT_T;

run;

data lg_auth_03 (drop = VS_S_DT SRV_S_DT LG_AUTH_S_DT);
set lg_auth_02;

if FED = 14 and PLC_ATHC in (1409, 1410, 1411, 1412, 1413, 6536, 6537, 6538) then FED = 15;   
	/* Guardian - Dependent */
else if FED = 14 then FED = 16;                                 /* Guardian - Other */

if FED = . then FED = 99;

/* Removal Reason */

if RMV_RSNC in (1609, 1615, 1619) then REMREAS = 1;   /* Neglect */
else if RMV_RSNC = 1617 then REMREAS = 2;             /* Physical */
else if RMV_RSNC = 1620 then REMREAS = 3;             /* Sexual */
else if RMV_RSNC = 6548 then REMREAS = 4;             /* Voluntary Reentry */
else if RMV_RSNC in (1611, .) Then REMREAS = 99;      /* Missing */
else REMREAS = 9;                                     /* Other */

run;







/*  Youth Parents in Care*/
/*  identify all youth parents where their child was born on or before point-in-time
	date and where the child has not been adopted or died at the point-in-time
	date.*/
data pit_parent_01;
	set offspring;
	if (ch_dob le yyq(&SYEAR,&SQTR) and (ch_censor_dt = . or ch_censor_dt > yyq(&SYEAR,&SQTR))
		or ch_dob = .);
	run;

proc sort data = pit_parent_01;
	by fkclient_t ch_dob;
	run;


/*	create counter of the children that the youths in care have, excluding children
	who have been adopted or who have died
	Note: this counter is problematic because many reported "twins" appear,
	on closer inspection, to be duplicate data entries.*/
data pit_parent_03;
	set pit_parent_01;
	by fkclient_t ch_dob;
	retain CHILD_CTX;
	if first.fkclient_t then CHILD_CTX = 1;
		else child_ctx = CHILD_CTX+1;
	run;

proc sort data = pit_parent_03;
	by fkclient_t child_ctx;
	run;
	

/*	take one record per child parent, using the last record to show the number of 
	children the child parent appeared to have on the point-in-time date*/
data pit_parent_05;
	set pit_parent_03;
	by fkclient_t child_ctx;
	if last.fkclient_t;
	YOUTH_PARENT = 1;
	run;


/*add the count data to point-in-time file*/
proc sql;
	create table pit_parent_07 as select
	lg_auth_03.*,
	pit_parent_05.CHILD_CTX,
	pit_parent_05.YOUTH_PARENT
	from lg_auth_03 left join pit_parent_05
	on lg_auth_03.fkclient_t = pit_parent_05.fkclient_t
	order by fkclient_t;
	quit;




**********************************************************************************************************;

data point1;
	set pit_parent_07;
	if youth_parent = . then youth_parent = 0;
	if child_ctx = . then child_ctx = 0;
	/*add in post_RFA facility types*/
		if fed = 2 and fac_type = 6914.1 then PIT_PLC_RFA = 5;        /* RFH - Relative */
		else if fed = 2 and fac_type = 6915.1 then PIT_PLC_RFA = 7;   /* RFH FFA - Relative */
		else if fed = 3 and fac_type = 6914.2 then PIT_PLC_RFA = 6;   /* RFH - Non Relative */
		else if fed = 4 and fac_type = 6915.2 then PIT_PLC_RFA = 8;   /* RFH FFA - Non Relative */
		else if fed = 10 and fac_type = 6916 then PIT_PLC_RFA = 12;   /* STRTP */
		else PIT_PLC_RFA = fed;
	

/*recalculate OH_LOC_PIT to represent relationship between supervising county and county
  of physical presence on point-in-time date j magruder 2024.07.05*/

	if oh_cnty = '70' then rescty = '59'; /*other state*/
		else if oh_cnty = '80' then rescty = '60'; /*other country*/
		else if oh_cnty = '90' or fed = 17 then rescty = '61'; /*runaway*/
		else if oh_cnty in('98', '99') then rescty = '62';  /*missing*/
		else rescty = oh_cnty;;
	/*delete oh_cnty when preponderance of evidence (out-of-state ZIP Code and
	  identified other state) says child not placed in California*/
	*if oh_zip_state ne "CA" and oh_state_cd not in(0,1828) then oh_cnty = ' ';
	oh_cntyn = input(oh_cnty, 3.);
	rsp_cntyn  = input(cnty_spfcd, 3.);
	if fed = 17 then OH_LOC_PIT = 9; /*runaway*/
	*if plc_fclc in(7164, 7181) then OH_LOC_PIT = 9; /*missing if in runaway status*/
	else if oh_state_cd ne 1828 and
		oh_state_cd ge 1823 and oh_state_cd le 1880 and oh_state_cd ne . then OH_LOC_PIT = 4; /*placed out of state*/
	else if oh_cntyn > 58 or oh_cntyn = . or rsp_cntyn > 58 or rsp_cntyn = . then OH_LOC_PIT = 99;
	else if oh_cntyn = rsp_cntyn then OH_LOC_PIT = 1; /*placed in supervising county*/
	else if rsp_cntyn = 1 then do;
		if oh_cntyn in (1, 7, 38, 39, 41, 43, 50) then OH_LOC_PIT = 2; /*placed in adjacent county*/
		else OH_LOC_PIT = 3; /*placed in non-adjacent county*/
		end;
	else if rsp_cntyn = 2 then do;
		if oh_cntyn in (2,3,5,9,26,55) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 3 then do;
		if oh_cntyn in (2,3,5,9,34,39) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 4 then do;
		if oh_cntyn in (4, 6, 11, 32, 51, 52,58) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 5 then do;
		if oh_cntyn in (2,3,5,39,50,55) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 6 then do;
		if oh_cntyn in (4,6,11,17,51,57) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 7 then do;
		if oh_cntyn in (1,7,21,34,38,39,48,49) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 8 then do;
		if oh_cntyn in (8,12,47) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 9 then do;
		if oh_cntyn in (2,3,9,31,34) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 10 then do;
		if oh_cntyn in (10,14,16,20,24,26,27,35,54) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 11 then do;
		if oh_cntyn in (4,6,11,17,23,52) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 12 then do;
		if oh_cntyn in (8,12,23,47,53) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 13 then do;
		if oh_cntyn in (13,33,37) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 14 then do;
		if oh_cntyn in (10,14,15,26,36,54) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 15 then do;
		if oh_cntyn in (14,15,16,36,40,42,54,56) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 16 then do;
		if oh_cntyn in (10,15,16,27,40,54) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 17 then do;
		if oh_cntyn in (6,11,17,23,28,49,57) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 18 then do;
		if oh_cntyn in (18,25,32,45,46) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 19 then do;
		if oh_cntyn in (15,19,30,36,56) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 20 then do;
		if oh_cntyn in (10,20,22,24,26,55) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 21 then do;
		if oh_cntyn in (7,21,38,48,49) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 22 then do;
		if oh_cntyn in (20,22,24,50,55) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 23 then do;
		if oh_cntyn in (11,12,17,23,49,52,53) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 24 then do;
		if oh_cntyn in (10,20,22,24,35,43,50) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 25 then do;
		if oh_cntyn in (18,25,45,47) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 26 then do;
		if oh_cntyn in (2,10,14,20,26,55) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 27 then do;
		if oh_cntyn in (10,16,27,35,40,44) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 28 then do;
		if oh_cntyn in (17,28,48,49,57) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 29 then do;
		if oh_cntyn in (29,31,46,58) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 30 then do;
		if oh_cntyn in (19,30,33,36,37) then OH_LOC_PIT = 2;
		else  OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 31 then do;
		if oh_cntyn in (9,29,31,34,51,58) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 32 then do;
		if oh_cntyn in (4,18,2,45,46,52,58) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 33 then do;
		if oh_cntyn in (13,30,33,36,37) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 34 then do;
		if oh_cntyn in (3,7,9,31,34,39,48,51,57) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 35 then do;
		if oh_cntyn in (10,24,27,35,43,44) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 36 then do;
		if oh_cntyn in (14,15,19,30,33,36) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 37 then do;
		if oh_cntyn in (13,	30,	33,	37) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 38 then do;
		if oh_cntyn in (1,7,21,38,41) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 39 then do;
		if oh_cntyn in (1,3,5,7,34,39,50) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 40 then do;
		if oh_cntyn in (15,16,27,40,42) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 41 then do;
		if oh_cntyn in (1,38,41,43,44) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 42 then do;
		if oh_cntyn in (15,40,42,56) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 43 then do;
		if oh_cntyn in (1,24,35,41,43,44,50) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 44 then do;
		if oh_cntyn in (27,35,41,43,44) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 45 then do;
		if oh_cntyn in (18,25,32,45,47,52,53) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 46 then do;
		if oh_cntyn in (18,29,32,46,58) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 47 then do;
		if oh_cntyn in (8,12,25,45,47,53) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 48 then do;
		if oh_cntyn in (7,21,28,34,48,49,57) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 49 then do;
		if oh_cntyn in (7,17,21,23,28,48,49) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 50 then do;
		if oh_cntyn in (1,5,22,24,39,43,50,55) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 51 then do;
		if oh_cntyn in (4,6,31,34,51,57,58) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 52 then do;
		if oh_cntyn in (4,11,23,32,45,52,53) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 53 then do;
		if oh_cntyn in (12,23,45,47,52,53) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 54 then do;
		if oh_cntyn in (10,14,15,16,54) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 55 then do;
		if oh_cntyn in (2,5,20,22,26,50,55) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 56 then do;
		if oh_cntyn in (15,19,42,56) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 57 then do;
		if oh_cntyn in (6,17,28,34,48,51,57) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	else if rsp_cntyn = 58 then do;
		if oh_cntyn in (4,29,31,32,46,51,58) then OH_LOC_PIT = 2;
		else OH_LOC_PIT = 3;
		end;
	label OH_LOC_PIT = "PIT Placement Location";
run;


/*modification for uncompressed data*/


/*modified to retain service component*/
/*modified to incude county, state, and country placement located in j magruder 2020.06.25*/
/*modified to include plc_athc in effect on period date j magruder 2023.08.25*/
proc sql;

create table pit_cnty_&SYEAR.Q&SQTR as
select fkclient_t, OHMPL_ID, cnty_spfcd, COUNTY length=4, AGENCY, COURT_IND, AGE, GENDER as GENDER_CD, ETHNIC, CENS_RC, 
       CENS_ETHNIC, HISP_CDX, TIME_IN, FED as PIT_PLC, PIT_PLC_RFA, PIT_SCPR, REMREAS, CHILD_CTX, YOUTH_PARENT, 
	  SRV_CMPC, PERIOD_DT, OH_CNTY, OH_STATE_CD, oh_cntry_c, nfc_only, plc_fclc, 
	  ICWA_elgcd, indn_stc, idn_trbcx, plc_athc, afcars, OH_LOC_PIT, OH_LOC  
from point1;
quit;

proc print data = pit_cnty_&SYEAR.Q&SQTR (obs = 30);
	run;

proc append base=county data=pit_cnty_&SYEAR.Q&SQTR;



/* 
	*following would be in the macro for extracting a particular period's data;
data point1a;
set point1;

AGENCY = 4;

run;

data point2;
set point1 point1a;
run;

proc sql;

create table pit_cnty_&SYEAR.Q&SQTR as
select cnty_spfcd, COUNTY length=4, AGENCY, COURT_IND, AGE, GENDER as GENDER_CD, ETHNIC, CENS_RC, 
       CENS_ETHNIC, HISP_CDX, TIME_IN, FED as PIT_PLC, PIT_SCPR, REMREAS, plc_athc, 
	  PERIOD_DT, count(*) as COUNT length=4 format=comma12.
from point2
group by cnty_spfcd, COUNTY, AGENCY, COURT_IND, AGE, GENDER_CD, ETHNIC, CENS_RC, CENS_ETHNIC, 
       HISP_CDX, TIME_IN, PIT_PLC, PIT_SCPR, REMREAS, plc_athc, PERIOD_DT;
quit;

data point3a;
set point2;

COUNTY = 0;

run;

data point3b;
set point2;

where cnty_spfcd = '19'; 
COUNTY = 1900;

run;

data point3;
set point3a point3b;
run;

proc sql;
create table pit_state_&SYEAR.Q&SQTR as
select cnty_spfcd, COUNTY length=4, AGENCY, COURT_IND, AGE, GENDER as GENDER_CD, ETHNIC, CENS_RC, 
       CENS_ETHNIC, HISP_CDX, TIME_IN, FED as PIT_PLC, PIT_SCPR, REMREAS, plc_athc, 
       PERIOD_DT, count(*) as COUNT length=4 format=comma12.
from point3
group by cnty_spfcd, COUNTY, AGENCY, COURT_IND, AGE, GENDER_CD, ETHNIC, CENS_RC, CENS_ETHNIC, 
       HISP_CDX, TIME_IN, PIT_PLC, PIT_SCPR, REMREAS, plc_athc, PERIOD_DT;
quit;

proc append base=county data=pit_cnty_&SYEAR.Q&SQTR;
proc append base=state data=pit_state_&SYEAR.Q&SQTR;
*/
%end ;
%end ;
%mend PIT ;

proc datasets;
	delete county;
	run;

%PIT(1,1998,1,2025) ;

proc contents data = county varnum;
run;

data ucb_pit;
	set county;
	run;

/*remove NDLG placements in guardian homes from UCB_PIT file*/

data dwh.ucb_pit; 
set ucb_pit;

/* if plc_athc in(1404, 6539) and pit_plc = 16 then delete; */

/* 2024.04.15 Change to exclude runaways who have a guardian authority for placement */
if plc_athc in(1404, 6539) and pit_plc in(16,17) then delete;
label oh_loc_pit = "PIT Placement Location";

run;