*FC_children_as_parents/youth_as_parent.sas;

*2018.07.19 j magruder;
/*	This program identifies children who appear to have given birth or fathered one or more
	children while in foster care*/
*2018.07.27 j magruder modified to included alleged parents per CDSS instruction;
*2018.10.11 j magruder modified to exclude childen once they are adopted or die;


/*
[CDSS Methodology]
Denominator: 
The denominator includes youth in foster care meeting the following criteria:
•	Who have an open placement episode at the point-in-time
•	Who are less than 21 years old at the point-in-time
•	Who are at least 10 years old at the point-in-time
•	In Child Welfare or Probation Department supervised care
The denominator excludes children and youth: 
•	In incoming Interstate Compact for the Placement of Children (ICPC) placements.
•	In non-dependent legal guardian placements.

Numerator: 
Youth who are identified as parents through at least one of the following:
•	Minor/NMD Parent Indicator (MNRMOM_IND) in the child client table 
	(CHLD_CLT) has the code value “Y.”
•	One of the following client relationship codes (CLNTRELC) in the client 
	relationship table (CLN_RELT) is associated with the youth through either 
	the focus client identifier (FKCLIENT_T) or the secondary client identifier 
	(FKCLIENT_0) and the possible child is at least 10 years younger than the 
	youth parent:
 
o	Mother/Daughter (Birth) – 247
o	Mother/Daughter (Presumed) – 5620 
o	Mother/Daughter (Alleged) – 246
o	Mother/Son (Birth) – 252
o	Mother/Son (Presumed) – 6361 
o	Mother/Son (Alleged) – 251
o	Father/Daughter (Birth) – 205
o	Father/Daughter (Presumed) – 207 
o	Father/Daughter (Alleged) – 204
o	Father/Son (Birth) – 211
o	Father/Son (Presumed) – 213
o	Father/Son (Alleged) – 210 
o	Daughter/Mother (Birth) – 196
o	Daughter/Mother (Presumed) – 198 
o	Daughter/Mother (Alleged) – 195 
o	Son/Mother (Birth) – 291
o	Son/Mother (Presumed) – 6360 
o	Son/Mother (Alleged) – 290 
o	Daughter/Father (Birth) – 190
o	Daughter/Father (Presumed) – 192
o	Daughter/Father (Alleged) – 189 
o	Son/Father (Birth) – 285
o	Son/Father (Presumed) – 287
o	Son/Father (Alleged) – 284 

 The numerator excludes children and youth:
•	Whose children have had a finalized adoption
•	Whose children are deceased

*/
/*Note: "Youth" = child in care who is a parent
	"Child/Children" = the child of a "Youth"*/

/*PART ONE - Identify children born to youth with a history of being in care*/
/*births limited to those to youth-parents between the ages of 10 and 20, inclusive*/
/*CHANGE QUARTERLY: xqtr, YEAR, ext_dt*/
%let xqtr=4;
%let YEAR=2022 ;
%let extract = Q&xqtr._&year.;
%let Ext=%str(&year. Quarter &xqtr. extract);
%let Yr=%str(&year.);
%let QTR=Q&xqtr. ;
%let ext_dt = '31DEC2022'D;
%let start = '01JAN2017'D;
%let end = '01JAN2018'D;

/**************************************************************/
*%include "/dss/SAS/PROGRAM/CWS_CMS/&QTR._&YEAR/autoexec.sas" ;

/*CHANGE QUARTERLY*/
%include "/pool01/vol01/SAS/PROGRAM/CWS_CMS/Q4_2022/autoexec.sas";

libname data8b "/pool01/TEMP/CWS_CMS/8B";

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

*this step just to make date on reports more readable;
data _null_;
    call symput('pitx',trim(left(put(&start., worddate.))));
    run;

OPTIONS FORMCHAR="|----|+|---+=|-/\<>*";


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

/*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_fc;
	if pe_e_dt = . then tpe_e_dt = &ext_dt.;
		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;
/*
proc freq data = unique_08;
	table mnrmom_ind/missing;
	run;
*/


**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;

data data8b.offspring_05;
  set offspring_05;
run;

*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='0' ;          /* 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='9' ;                            /* 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;

data data8b.offspring_07;
  set offspring_07;
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;

/*add counter - number of children the youth parent had prior to leaving care, as reported in 
	CWS/CMS - because this will vary by point-in-time all are retained
	This will need adjustment when child dropped due to death or adoption*/
data offspring;
	set offspring_09;
	by fkclient_t parent_age_at_birth;
	retain child_ct;
	if first.fkclient_t then child_ct = 1;
		else child_ct = child_ct+1;
	run;
/*end of youth with child routine*/


data data8b.offspring_08;
  set offspring_08;
run;

data data8b.offspring_09;
  set offspring_09;
run;

data data8b.offspring (keep = FKCLIENT_T ch_id ch_dob ch_dod ch_ad_final_dt ch_censor_dt child_ct);
  set offspring;
run;

data data8b.offspring_all;
  set offspring;
run;


*****************************************************************************************;
** 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     **; 
**                                                                                     **;
*****************************************************************************************;

************CHANGE NEEDED HERE;
%include "/pool01/vol01/SAS/PROGRAM/CWS_CMS/Q4_2022/autoexec.sas";

libname data8b "/pool01/TEMP/CWS_CMS/8B";



/* 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;
set DWH.UCB_FC(drop = ETHNIC);

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;
set test1;

by FKCLIENT_T;
if first.FKCLIENT_T;

run;

data test3;
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 = .));

run;

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

data test4;
set test3;

by FKCLIENT_T;
if last.FKCLIENT_T;

run;

/* LIMBO */

data test5a;
set test4;

LIMBO = '0';

run;

proc sql;

create table test4a as
select *
from test2
where FKCLIENT_T not in (select FKCLIENT_T from test4);

create table test4b as
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;
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;
set test4d test4f;

LIMBO = '1';

run;

data test5(drop = GENDER_CD);
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 AGECLS = 1;
else if AGE in (1, 2) then AGECLS = 2;
else if AGE in (3, 4, 5) then AGECLS = 3;
else if AGE in (6, 7, 8, 9, 10) then AGECLS = 4;
else if AGE in (11, 12, 13, 14, 15) then AGECLS = 5;
else if AGE in (16, 17, 18) then AGECLS = 6;
else if AGE in (19, 20, 21) then AGECLS = 7;
else if AGE lt 0 or AGE gt 21 then AGECLS = 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 */

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 test6;

if LIMBO = '0' then do;
select(PLC_FCLC);
  when('2222') FED1 = 1;            /* Pre-Adopt */
  when('1421','1422') FED1 = 2;     /* Relative/NREFM*/           
  when('1415','1416') FED1 = 3;     /* Foster */
  when('1414', '2200') FED1 = 4  ;  /* FFA */
  when('1419') FED1 = 9;            /* Court Specified Home */
  when('7208') FED1 = 9.2 ; 	    /* Tribally Approved*/
  when('1417') FED1 = 10;           /* Group */
  when('1418','7027') FED1 = 11;    /* Shelter */
  when('1420') FED1 = 13;           /* Non-FC */
  when('5411') FED1 = 14;           /* Guardian */
  when('7181') FED1 = 17;           /* Runaway */
  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(0,.) FED1 = 99 ;             /* Missing */
   otherwise FED1 = 29;             /* Other */

end;
end;
 
if LIMBO = '0' and (TRNHSG_IND = 'Y' or 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;

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;
set test9c;

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;

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;
else if FED3 = 19 then FED = 19;
else if FED3 = 29 then FED = 29;


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;

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

data assign_83;
	set assign_82a assign_82b1;
	run;

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


/*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 = cnty_rem;
	run;

proc contents data = assign_84a varnum; run;

/* Adding Case ID */

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;


/*
*** 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.
***;*/

proc sql;

   create table icpc1 as
   select identifier as caseid,
          fkchld_clt
   from cws.case_t
   where fkchld_clt in (select fkclient_t
                        from assign_84) and
                 (start_dt ne . and start_dt le yyq(&SYEAR,&SQTR)) and
                 (end_dt=. or end_dt ge yyq(&SYEAR,&SQTR))
                 ;

quit;


proc sql;
sql;
   create table icpc2 as
   select a.fkchld_clt as fkclient_t,
          b.intvrsnc
   from icpc1 as a, cws.intv_rnt as b
   where a.caseid=b.fkcase_t and
         b.fkcase_t ne ' ' and
         b.intvrsnc=1226
      ;
quit;

*** There are no dates associated with the Intervention Reason Table
    so we only need to unduplicate by our unique variable and rejoin.
***;

proc sort data=icpc2 nodupkey out=icpc; by fkclient_t; run;
proc sort data=assign_84; by fkclient_t; run;

/*	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;
   merge assign_84(in=a)
         icpc;
   by fkclient_t;
   if a;
   if intvrsnc=1226 then delete;

  
   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 ;

        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("S8234") then COUNTY = 1905; /*Lancaster*/
                else if cws_off_no in("S3239") then COUNTY = 1906; /*Metro North*/
                else if cws_off_no in("S8236") then COUNTY = 1907; /*Palmdale*/
                else if cws_off_no in("S5252") then COUNTY = 1908; /*Pasadena*/
                else if cws_off_no in("S1255") then COUNTY = 1909; /*Pomona*/

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

                else if cws_off_no in("S6219") then COUNTY = 1915; /*Vermont Corridor*/
                else if cws_off_no in("S2217") then COUNTY = 1916; /*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("S1278") and */
                     fkcws_offt = "1iyrzAg196" then COUNTY = 1940; /*ROU/CSEC*/
                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 x.*, y.VLNTRY_IND, y.START_DT as VS_S_DT, y.END_DT as VS_E_DT
from assign_cty as x left join cws.CSVOL_ST as y
on x.CASEID = y.FKCASE_T and 
   y.START_DT <= yyq(&SYEAR,&SQTR) and 
  (y.END_DT = . or y.END_DT > yyq(&SYEAR,&SQTR))
order by FKCLIENT_T, y.START_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 */

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;

proc append base=data8b.lg_auth_01 data=lg_auth_01 force;
run;

/* 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;

****COMMENTED OUT BY GYS 2/10/22***;
if FED = 14 and PLC_ATHC in (1409, 1410, 1411) then FED = 15;   /* Guardian - Dependent */
else if FED = 14 then FED = 16;                                 /* Guardian - Other */

if FED = . then FED = 99;
****COMMENTED OUT BY GYS 2/10/22***;

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

call symput('PIT',period_dt);

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 data8b.offspring_all;
	if (ch_dob le &pit. and (ch_censor_dt = . or ch_censor_dt > &pit.))
		or ch_dob = .;
	run;

proc sort data = pit_parent_01;
	by fkclient_t child_ct;
	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 child_ct;
	retain child_ctx;
	if first.fkclient_t then child_ctx = 1;
		else child_ctx = child_ctx+1;
	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_ct;
	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;
	run;


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, CHILD_CTX, YOUTH_PARENT,
		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, CHILD_CTX, YOUTH_PARENT, REMREAS, 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, CHILD_CTX, YOUTH_PARENT,
		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, CHILD_CTX, YOUTH_PARENT, 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 ;



*************NEED TO CHANGE;
%PIT(1,2014,1,2023) ;

data pit_la1;
set county state;

if PIT_PLC = . then PIT_PLC = 99;

run;

data data8b.pit;
set pit_la1; 

if cnty_spfcd = '19' and COUNTY = . then COUNTY = 1999;
else if COUNTY = . then COUNTY = 9800;

run;

proc sort data = data8b.pit;
by AGENCY COUNTY PERIOD_DT;
run;

libname data8b "/pool01/TEMP/CWS_CMS/8B";


data data8b.pit_disaggr;
    set data8b.pit;
    do i=1 to count;
        output;
    end;
run;