*TAY_ep_18_in_care_jm_17_qtr_v07.sas;

/*2022.08.16 S Cuccaro-Alamin, J Magruder*/
 
/* Copyright (c) 2022 CCWIP, University of California at Berkeley. All rights reserved. */
/* TAY EMPLOYMENT OUTCOMES - AGE 18 IN CARE */

*change xqtr and YEAR;
%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. ;
%include "/dss/SAS/PROGRAM/CWS_CMS/&QTR._&YEAR/autoexec.sas" ;

%let quarter=Q4_2022;
%let dataq=2022 Quarter 4;
*%let sdate='01JAN1998'D; * 18th bday start date;
*%let edate='31DEC2011'D; * 18th bday end date;

libname dwh     "/ssa4/SAS/SASDATA/CWS_CMS/&quarter.";
libname cws  	"/wss1/SAS/SASDATA/CWS_CMS/&quarter./SOURCE";
libname edd     "/pool01/vol05/SAS/SASDATA/EDD_II/2022Q4";



proc format;
	value yes_no
	1 = "Yes"
	2 = "No"
	0 = "No"
	. = "Missing";
	run;

	
proc format;
    value newfed
        1 = "Pre-adopt"
        2 = "Kin"
        3 = "Foster"
        4 = "FFA"
		5 = "RFH-Rel"
		6 = "RFH-NR"
		7 = "RFH-FFA-Rel"
		8 = "RFH-FFA-NR"
        9 = "Court Specified"
	   9.1 = "Tribe Specified"
	   9.2 = "Tribally Approved"
	   9.5 = "Tribe Specified/Approved"
        10 = "Group"
        11 = "Shelter"
		12 = "STRTP"
        13 = "Non-FC" 
        14 = "Guardian" 
        15 = "Guardian-Dependent"
        16 = "Guardian - Non Dependent"
        17 = "Runaway"
        18 = "Trial HV"
        19 = "SILP"
        20 = "Transitional Housing"
        29 = "Other"
	   30 = "Mixed" 
        99 = "Missing";
    run;	
/***************************************************************/
/* FOSTER CARE FILE */


/*find children in a placement at anytime between 16th and 18th birthdays*/
data universe_01 
		(keep = fkclient_t agy_rspc birth_dt pe_s_dt pe_s_age pe_e_dt gender_cd
			oh_s_dt oh_e_dt cens_rc cnty_rem ethnic fac_type fkplc_hm_t
			hisp_cd hisp_cdx ive ohmpl_id oh_cnty plcg_rnc plcmnt scp_rltc 
			plc_fclc p_ethnctyc scp_rltc rsp_cnty spell age_16 age_17 age_18 
			term_ty_c oh_flag age_18_yr time_in plcep_id tpe_e_dt flag_8 pmt_order);
	set dwh.ucb_fc;
	/* Exclude AGY_RSPC 36=Private Adoption Agency, 5605=LA - 
			Private Adoption Agency, 6133=Mental Health, 6134=Kin-GAP */
	if AGY_RSPC in (0036, 5605, 6133, 6134) then delete;
	if birth_dt = . then delete;
	age_16 = intnx('year', BIRTH_DT, 16, 's');
	age_17 = intnx('year', BIRTH_DT, 17, 's');
	age_18 = intnx('year', BIRTH_DT, 18, 's');
	format age_16 age_17 age_18 mmddyy10.;
	age_18_yr = year(age_18);
	if year(age_18) < 2000 or year(age_18) ge &year. then delete;
	/*select placements open between 16th and 18th birthdays*/
	if age_18 > oh_s_dt
		and (oh_e_dt = . or oh_e_dt ge age_16);
	if pe_e_dt ne . and pe_e_dt < age_16 then delete;
		/*there are problems with some placements with no end dates in spite
		  of being in placement episodes with end dates before age 16*/
	if age_18 > oh_s_dt
		and (oh_e_dt = . or oh_e_dt ge age_18) then oh_flag = 1; 
	/*limit to placements starting before 18th birthday*/
	if oh_s_dt > age_18 then delete;
	if pe_s_dt - (birth_dt-10) > 0 then do;
	pe_s_age = floor ((intck('month',birth_dt,pe_s_dt)
  		- (day(pe_s_dt) < day(birth_dt))) / 12);
	if pe_s_age < 0 then pe_s_age = .;
	end;

	/*find placement episodes less than or equal to 8 days in length*/
	if PE_E_DT = . or PE_E_DT > age_18 then TPE_E_DT = age_18;
		else TPE_E_DT = PE_E_DT;

	if TPE_E_DT - PE_S_DT >= 8 then FLAG_8 = 1;
		else if TPE_E_DT - PE_S_DT < 8 then FLAG_8 = 2;

	/*find placement episode length - ending with first of pe_e_dt or age_18
		uses same method as point in time program - calculates by month*/
	TIME=INT((TPE_e_dt - PE_S_DT)/30.438);
	
   		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;
	label time_in = "Time in Current Episode Before Age 18";
	run;

proc sort data = universe_01;
	by fkclient_t pmt_order;
	run;

/*add date of death*/
proc sql;
	create table universe_02 as select
	universe_01.*,
	client_t.death_dt format mmddyy10.
	from universe_01 left join cws.client_t
	on universe_01.fkclient_t = client_t.identifier
	order by fkclient_t, pmt_order;
	quit;

/*find placement on day before 18th birthday or, if not in placement on day before 18th birthday
  find last placement in episode before 18th birthday*/
/*delete records if child died before age 18*/

data universe_03 (rename = (plc_fclc = last_age18_ohp));
	set universe_02;
	by fkclient_t pmt_order;
	if last.fkclient_t;
	if death_dt < age_18 and death_dt ne . then delete;
	run;


/*find office assignment and thus county at later of last day of placement episode
  or 18th birthday*/
	/*office assignment*/	


/*Use assignment from UCB_OFFICE table. If no assignment on that table,
	use ST_ID table. If no assignment on that table, use rsp_cnty which */

proc sql;
	create table assign_81 as
		select u3.*,
			ucb_office.*
			from universe_03 as u3 left join dwh.ucb_office
		on ucb_office.fkchld_clt = u3.fkclient_t
			and office_s_dt le tpe_e_dt
			and (office_e_dt = . or office_e_dt ge tpe_e_dt)
		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 tpe_e_dt
			and (st_id.office_e_dt = . or st_id.office_e_dt ge tpe_e_dt))
		order by fkclient_t, office_s_dt;
		;
	quit;

/* Put two files back together - i.e., add state ID county derived 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 rsp_cnty
	This is calculating county at the end of the episode, or age 18 which is not necessrily
	the county calculated for rsp_cnty which may be county at another pint in episode*/

data universe_04;
	set assign_82a assign_82b1;
	   x_cws_off_no = cws_off_no;
	   x_fkcws_offt = fkcws_offt;
	   x_cnty = cnty_spfcd;
       
		%include "/dss/SAS/PROGRAM/CWS_CMS/SAS_MACRO/county.sas";
	if cnty_spfcd in(' ', '99') then cnty_spfcd = rsp_cnty;
	run;

proc sort data = universe_04;
	by fkclient_t pmt_order;
	run;

data universe_05;
set universe_04 (drop = x_cws_off_no x_fkcws_offt x_cnty type office_s_dt office_e_dt
		fkchld_clt rsp_cnty);
by fkclient_t pmt_order;
/* Agency Type */

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


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


/* additional anchor dates */
*age_17 = intnx('year', BIRTH_DT, 17, 's');
*age_18 = intnx('year', BIRTH_DT, 18, 's');
age_19 = intnx('year', BIRTH_DT, 19, 's');
age_20 = intnx('year', BIRTH_DT, 20, 's');
age_21 = intnx('year', BIRTH_DT, 21, 's');
age_22 = intnx('year', BIRTH_DT, 22, 's');
age_23 = intnx('year', BIRTH_DT, 23, 's');

format age_19 age_20 age_21 age_22 age_23 MMDDYY10.;

yrqtr_16=yyq(year(age_16),qtr(age_16));
yrqtr_17=yyq(year(age_17),qtr(age_17));
yrqtr_18=yyq(year(age_18),qtr(age_18));
yrqtr_19=yyq(year(age_19),qtr(age_19));
yrqtr_20=yyq(year(age_20),qtr(age_20));
yrqtr_21=yyq(year(age_21),qtr(age_21));
yrqtr_22=yyq(year(age_22),qtr(age_22));
yrqtr_23=yyq(year(age_23),qtr(age_23));
format yrqtr_16 yrqtr_17 yrqtr_18 yrqtr_19 yrqtr_20 yrqtr_21 yrqtr_22 yrqtr_23 YYQ6.;


/* special episode date for those still in care */
if PE_E_DT = . then SPE_E_DT = (age_23+30); 
else SPE_E_DT = PE_E_DT;
format SPE_E_DT MMDDYY10.;
if death_dt ne . or term_ty_c in( 5432, 5515) then known_death = 1;
run;

proc sort data = universe_05;
	by FKCLIENT_T pmt_order; 
	run;

*proc contents data = universe_05; run;
*proc contents data = dwh.ucb_fc2;
	run;


/*create a sub sample of ucb_fc file with all placements for those in universe*/
proc sql;
	create table status_01 as select
	u.fkclient_t,
	u.plcep_id as index_plcep_id,
	u.birth_dt,
	u.death_dt,
	u.spe_e_dt,
	u.age_16,
	u.age_17,
	u.age_18,
	u.age_19,
	u.age_20,
	u.age_21,
	u.age_22,
	u.age_23,
	u.time_in,
	u.known_death,
	ucb_fc.plcep_id,
	ucb_fc.pe_s_dt,
	ucb_fc.pe_e_dt,
	ucb_fc.oh_s_dt,
	ucb_fc.oh_e_dt,
	ucb_fc.term_ty_c,
	ucb_fc.plc_fclc,
	ucb_fc.fac_type,
	ucb_fc.scp_rltc,
	ucb_fc.trnhsg_fac,
	ucb_fc.plcg_rnc,
	ucb_fc.spell,
	ucb_fc.pmt_order
	from universe_05 as u left join dwh.ucb_fc2 as ucb_fc
	on u.fkclient_t = ucb_fc.fkclient_t
		and ucb_fc.pe_s_dt ge (ucb_fc.birth_dt-10)
	order by fkclient_t, pmt_order;
	quit;
 
/*find age at start of first episode*/
data first_episode_01 (keep = fkclient_t pe1_s_age);
	set status_01;
	by fkclient_t pmt_order;
	if first.fkclient_t;
	pe1_s_age = floor ((intck('month',birth_dt,pe_s_dt)
  		- (day(pe_s_dt) < day(birth_dt))) / 12);
	if pe1_s_age < 0 then pe1_s_age = 0;
	run;

/*find age at end of last episode*/
data last_episode_01 (keep = fkclient_t last_pe_e_age);
	set status_01;
	by fkclient_t pmt_order;
	if last.fkclient_t;
	last_pe_e_age = floor ((intck('month',birth_dt,pe_e_dt)
  		- (day(pe_e_dt) < day(birth_dt))) / 12);
	*if last_pe_e_age > 23 then last_pe_e_age = 23;
	run;

proc freq data = last_episode_01;
	table last_pe_e_age/missing;
	run;




/*predominant facility type*/

/*identify placements in the age 18 episode*/
proc sql;
	create table predom_001 as select
	status_01.*
	from status_01
	where status_01.plcep_id in (select plcep_id from universe_05) /*limits to age 18 epsode*/
	order by status_01.fkclient_t, status_01.oh_s_dt;
	run;

/*set temporary placement end date to first of age 18 or actual pacement end*/
data predom_003;
	set predom_001;
	by fkclient_t oh_s_dt;
	if oh_s_dt ge age_18 then delete; 
		/*only take placements starting befodre 18th birthday*/
	if oh_e_dt ge age_18 or oh_e_dt = . then toh_e_dt = (age_18-1);
		else toh_e_dt = oh_e_dt;
		/*based on days in care prior to 18th birthday*/
	run;

/*predominant placement type from UCB_FC creation file*/
data predom_01 ;
set predom_003 ; /*episode between age 17 and 18*/

keep
fkclient_t
FT
plcep_id        /* Placement-Episode-ID */
OH_S_DT         /* OHMPL-Start-Date */
tOH_E_DT         /* temporary OHMPL-End-Date */
;

/* If OHMPL-End-Date is missing, use the date of the extract. */
*if OH_E_DT eq . or oh_e_dt > &cutoff. then OH_E_DT= &DODEX ;

if PLC_FCLC = 2222 then FT = 1;                  /* Pre_Adopt */
else if PLC_FCLC in (1421,1422) then FT = 2;     /* Kin */
/* else if  SCP_RLTC in (1639) then FT = 2; */   /* Kin-rel-non-guardian */
else if PLC_FCLC in (1415,1416) then FT = 3;     /* Foster */
else if PLC_FCLC in (1414,2200) then FT = 4;     /* FFA */
else if PLC_FCLC = 1419 then FT = 9;             /* Court Specified Home */
else if PLC_FCLC in (7208) then FT = 9.2;        /* Tribally Approved Homes  2022.01.11 j magruder*/
else if PLC_FCLC = 1417 then FT = 10;            /* Group */
else if PLC_FCLC in (1418,7027) then FT = 11;    /* Shelter */
else if PLC_FCLC in ('1420', '6362', '6363', 
       '6364', '6365', '6366', '6367', '6368', 
       '6369', '6370', '6371') then FT = 13;     /* Non-FC added 2021.04.12 j magruder*/
else if PLC_FCLC = 5411 then FT = 14;            /* Guardian */
else if PLC_FCLC = 6716 then FT = 19;            /* SILP */

/* else if PLC_FCLC = 6914.1 then FT = 5; */     /* RFA - Non Relative */
/* else if PLC_FCLC = 6914.2 then FT = 6; */     /* RFA - Relative */
/* else if PLC_FCLC = 6915.1 then FT = 7; */     /* RFA FFA - Non Relative */
/* else if PLC_FCLC = 6915.2 then FT = 8; */     /* RFA FFA - Relative */
/* else if PLC_FCLC = 6916 then FT = 12;  */     /* STRTP */

else if PLC_FCLC not in (.,0) then FT = 29;      /* Other */
else if PLC_FCLC in (.,0) then FT = 99;          /* Missing */

run ;

/* Sum the days spent in each facilitye type (FT) */
proc sql ;
create table FT_SUMM as
select
FT ,
sum(tOH_E_DT - OH_S_DT) as DAYS ,
plcep_id,
fkclient_t
from predom_01
group by plcep_id, FT
;
quit;

/* Compute total days for each spell by summing days in each placement*/
proc sql;
create table TOTAL as
select
fkclient_t,
plcep_id,
sum(tOH_E_DT - OH_S_DT) as DAYS
from predom_01
group by plcep_id
;

/* Compute the proportion of spell duration spent in each type */
proc sql;
create table PTIME as
select
FT_SUMM.DAYS/TOTAL.DAYS as PTIME,
FT,
TOTAL.plcep_id,
TOTAL.fkclient_t
from FT_SUMM, TOTAL
where FT_SUMM.plcep_id = TOTAL.plcep_id
order by plcep_id, PTIME desc
;

/* Assign PREDOM_FT if any, otherwise assign value "MIXED" */
data PREDOM_FT (keep = fkclient_t plcep_id predom_ft);
set PTIME ;
by plcep_id ;
if first.plcep_id then do ;
   select ;
      when(PTIME eq .) PREDOM_FT=99 ;
      when(PTIME ge 0.5) PREDOM_FT=FT ;
      otherwise PREDOM_FT=30 ;                     /* MIXED */
   end;
end;
else delete ;
label predom_ft = "Pre Age 18 Predominant Facility Type";
run ;

proc sort data = predom_ft;
	by fkclient_t;
	run;


proc format;
	value status
		20 = "Before Care"
		21 = "In Care"
		22 = "Permanency"
		23 = "Age of Majority"
		24 = "NMD, etc. Eligible for Reentry"
		25 = "NMD age limit"
		26 = "Incarcerated/WIC601/WIC602"
		27 = "Runaway"
		28 = "Other"
		29 = "Unknown";
		run;

/* find exit status at age 16, 17, 18, 19, 20, 21, 22, 23*/

/*detrmine youth's status at each birthday - 16 to 23 - this mixes placement type and 
  episode termination type*/
%macro status(agegp, ageyr);
/*do for each age group*/
/*first delete placements and episodes starting after test age*/
data status_&agegp._03;
	set status_01;
	by fkclient_t oh_s_dt;
	if pe_s_dt ge &ageyr. or oh_s_dt ge &ageyr. then delete;
	if oh_e_dt = . then toh_e_dt = (age_23-1);
		else toh_e_dt = oh_e_dt;
run;; 

proc sort data = status_&agegp._03;
	by fkclient_t oh_s_dt toh_e_dt;
	run;
/*then take last record before test age - that shows status at test age
  but child may have left episode before that date - in which status is termination type
  code. If child still in episode, then status is placement type, unless placement ended
  before test date in which case placement type is nil*/

data status_&agegp._05 (keep = fkclient_t &ageyr. &agegp._term_ty_c &agegp._plc_fclc 
		&agegp._statusd &agegp._status plcg_rnc);
	set  status_&agegp._03;
	by fkclient_t oh_s_dt toh_e_dt;
	if last.fkclient_t;
	if pe_e_dt ne . and pe_e_dt < &ageyr. then &agegp._term_ty_c = term_ty_c;
		else &agegp._term_ty_c = 0;

	if &agegp._term_ty_c = 0 then do;
		if oh_e_dt = . and oh_s_dt = . then do;
			&agegp._plc_fclc = 0;
			&agegp._fac_type = 0;
			end;
		else
		if oh_e_dt = . or oh_e_dt ge &ageyr. then do;
			&agegp._plc_fclc = plc_fclc;
			&agegp._fac_type = fac_type;
			&agegp._trnhsg_fac = trnhsg_fac;
		end;
		else do;
			&agegp._plc_fclc = 0;
			&agegp._fac_type = 0;
			end;
		end;
	if &agegp._plc_fclc > 0 then &agegp._statusd = &agegp._plc_fclc;
		else &agegp._statusd = &agegp._term_ty_c;

	if &agegp._plc_fclc in(6364,6365) then &agegp._status = 26; 
		/*incarcerated - Juvenile Hall or Camp/Ranch placement type*/
	else if (&agegp._plc_fclc in (7164, 7181)or &agegp._term_ty_c in ( 5430, 5511)) then &agegp._status = 27; 
		/*Runaway*/
	else if &agegp._term_ty_c in(5427, 5433, 5506, 5510, 6805, 6806, 6814, 6815) then &agegp._status = 23; 
		/*Age of majority/emancipation*/
	else if &agegp._term_ty_c in(5426, 5434, 5439, 5440, 5505, 5513, 5516, 6530, 6531) then &agegp._status = 22; 
		/*Permanency*/
	else if &agegp._term_ty_c in(6803,6812,6804,6813) then &agegp._status = 24;
		/*NMD etc. eligible for reentry*/
	else if &agegp._term_ty_c in(5632, 6309, 6310) then &agegp._status = 26; 
		/*Incarcerated/WIC601/WIC602 Termination Type codes*/
	else if &agegp._term_ty_c > 0 then &agegp._status = 28; 
		/*other*/
	else if &agegp._plc_fclc > 0 then &agegp._status = 21; 
		/*in care*/
	else if plcg_rnc = 1436 then &agegp._status = 26; 
		/*incarcerated placement change reason and no termination type*/
	else &agegp._status = 29; 
		/*unknown*/

	run;
/*
proc freq data = status_&agegp._05;
	table &agegp._status; * &agegp._statusd;
	format &agegp._status status.;
	format &agegp._statusd syscode.;
	run;
*/
%mend status;

%status(age16, age_16);
%status(age17, age_17);
%status(age18, age_18);
%status(age19, age_19);
%status(age20, age_20);
%status(age21, age_21);
%status(age22, age_22);
%status(age23, age_23);

data cohort;
	merge universe_05 first_episode_01 last_episode_01 PREDOM_FT status_age16_05 
		status_age17_05 status_age18_05 
		status_age19_05 status_age20_05 status_age21_05 status_age22_05 status_age23_05
		(drop = plcg_rnc);
	by fkclient_t;
	/*if child had no care in any episode prior to or on status date,, 
	  then set to 'before care'*/
	if age16_status = . then age16_status = 20;
	if age17_status = . then age17_status = 20;
	if age18_status = . then age18_status = 20;
	if age19_status = . then age19_status = 20;
	if predom_ft = . then predom_ft = 99;
	run;

*proc contents data = cohort varnum; run;



/***************************************************************/
/* EDD DATA */
/* get edd wage data for cohort for follow-up period */
proc sql; 
create table fc_edd_01 as select * from edd.edd_fc_2022q4
where edd_fc_2022q4.cws_id in (select FKCLIENT_T from cohort)
order by cws_id, qtr, wagesum;
quit;

/*proc freq; tables qtr; run;*/

proc contents data = fc_edd_01; run;

/*eliminate duplicate records for the same quarter, taking one with higher
  reported wages although wages in duplicate entries seem to be the same*/
data fc_edd_03;
	set fc_edd_01;
	by cws_id qtr wagesum;
	if last.qtr;
run; 

/* merge on age 18 and date_f variables*/
proc sql; 
create table fc_edd_10 as select 
	x.*, 
	y.*
from fc_edd_03 as x left join cohort as y 
	on x.cws_id=y.FKCLIENT_T
	order by fkclient_t, x.qtr;
	quit;

/*
proc contents data = fc_edd_10 varnum;
	run;

proc print data = fc_edd_10 (obs = 90);
	var fkclient_t yrqtr_18 yrqtr_23 qtr wagesum;
	where qtr gt yrqtr_23 ;
	run;
*/

/*proc print (obs = 40); var cws_id qtr wagesum yrqtr_f; run; */
/* delete observations after qtr where youth turns 23 makes the file smaller */
data fc_edd_20;
set fc_edd_10;
if qtr gt yrqtr_23 then delete;
run;
/*proc print (obs = 40); var cws_id qtr wagesum yrqtr_f; run; */
/* merging edd information -  use cws_id for sorting */
/* unduplicate to deal with duplication issues */
proc sort; by fkclient_t qtr wagesum; run;

data fc_edd_30;
	set fc_edd_20;
	by fkclient_t qtr wagesum;
	if last.qtr;
run;


/* create date indicators for wages */
data fc_edd_40;
set fc_edd_30;
year_edd=year(QTR);
qtr_edd=qtr(QTR);
run;


/* identifying quarters with employment */
data fc_edd_60;	
	set fc_edd_40;
	if qtr le yrqtr_16 then earn_yr = 15;
	else if qtr le yrqtr_17 then earn_yr = 16;
	else if qtr le yrqtr_18 then earn_yr = 17;
	else if qtr le yrqtr_19 then earn_yr = 18;
	else if qtr le yrqtr_20 then earn_yr = 19;
	else if qtr le yrqtr_21 then earn_yr = 20;
	else if qtr le yrqtr_22 then earn_yr = 21;
	else if qtr le yrqtr_23 then earn_yr = 22;
	else earn_yr = 23;
	run;


/*count quarters worked in age year*/
proc sql;
create table qtrs_work_01 as select
fc_edd_60.fkclient_t,
fc_edd_60.earn_yr,
count(*) as qtrs_emp label "quarterss worked in age year",
sum(wagesum) as year_wage label "earinings in age year"
from fc_edd_60
group by fkclient_t, earn_yr;  /*count rows for each fkclient_t for each age */
quit;

/*problem is that if the youth had no earnings in a year there
  is no record. So this creates record for each year for each
  youth with a zero months employed. Nn the end this record only 
  retained if the child had no reported earnings in the year*/
data blank_yrs (keep = fkclient_t earn_yr qtrs_emp year_wage);
	set qtrs_work_01;
	by fkclient_t earn_yr;
	if first.fkclient_t then do;
		earn_yr = 15;
		qtrs_emp = 0;
		year_wage = 0;
		output blank_yrs;
		earn_yr = 16;
		qtrs_emp = 0;
		year_wage = 0;
		output blank_yrs;
		earn_yr = 17;
		qtrs_emp = 0;
		year_wage = 0;
		output blank_yrs;
		earn_yr = 18;
		qtrs_emp = 0;
		year_wage = 0;
		output blank_yrs;
		earn_yr = 19;
		qtrs_emp = 0;
		year_wage = 0;
		output blank_yrs;
		earn_yr = 20;
		qtrs_emp = 0;
		year_wage = 0;
		output blank_yrs;
		earn_yr = 21;
		qtrs_emp = 0;
		year_wage = 0;
		output blank_yrs;
		earn_yr = 22;
		qtrs_emp = 0;
		year_wage = 0;
		output blank_yrs;
		earn_yr = 23;
		qtrs_emp = 0;
		year_wage = 0;
		output blank_yrs;
	end;

	run;
	
data qtrs_work_03;
	set qtrs_work_01 blank_yrs;
	run;

proc sort data = qtrs_work_03;
	by fkclient_t earn_yr qtrs_emp;
	run;

/*By retaining the last record for each youth earning year the retained
  record is the record showing employment if the youth had any employment,
  otherwise the retained record shows no emloyment (the record from the
  blank_yrs table).*/
data qtrs_work_05;
	set qtrs_work_03;
	by fkclient_t earn_yr qtrs_emp;
	if last.earn_yr;
	run;

proc tabulate data = qtrs_work_05 missing format = comma7.0;
	class qtrs_emp earn_yr;
	table all qtrs_emp all, earn_yr;
	*where earn_yr = 16; * and qtrs_emp > 4;

	run;
	
/*reformat data to add to cohort file with one variable per year*/
data earn_15 (keep = fkclient_t earn_yr_15 earn_amt_15)
	earn_16 (keep = fkclient_t earn_yr_16 earn_amt_16)	
	earn_17 (keep = fkclient_t earn_yr_17 earn_amt_17)
	earn_18 (keep = fkclient_t earn_yr_18 earn_amt_18)
	earn_19 (keep = fkclient_t earn_yr_19 earn_amt_19)
	earn_20 (keep = fkclient_t earn_yr_20 earn_amt_20)
	earn_21 (keep = fkclient_t earn_yr_21 earn_amt_21)
	earn_22 (keep = fkclient_t earn_yr_22 earn_amt_22)
	earn_23 (keep = fkclient_t earn_yr_23 earn_amt_23);
	set qtrs_work_05;
	by fkclient_t earn_yr qtrs_emp;
	if earn_yr = 15 then do;
		earn_yr_15 = qtrs_emp;
		earn_amt_15 = year_wage;
		output earn_15;
		end;
	if earn_yr = 16 then do;
		earn_yr_16 = qtrs_emp;
		earn_amt_16 = year_wage;
		output earn_16;
		end;
	if earn_yr = 17 then do;
		earn_yr_17 = qtrs_emp;
		earn_amt_17 = year_wage;
		output earn_17;
		end;
	if earn_yr = 18 then do;
		earn_yr_18 = qtrs_emp;
		earn_amt_18 = year_wage;
		output earn_18;
		end;
	if earn_yr = 19 then do;
		earn_yr_19 = qtrs_emp;
		earn_amt_19 = year_wage;
		output earn_19;
		end;
	if earn_yr = 20 then do;
		earn_yr_20 = qtrs_emp;
		earn_amt_20 = year_wage;
		output earn_20;
		end;
	if earn_yr = 21 then do;
		earn_yr_21 = qtrs_emp;
		earn_amt_21 = year_wage;
		output earn_21;
		end;
	if earn_yr = 22 then do;
		earn_yr_22 = qtrs_emp;
		earn_amt_22 = year_wage;
		output earn_22;
		end;
	if earn_yr = 23 then do;
		earn_yr_23 = qtrs_emp;
		earn_amt_23 = year_wage;
		output earn_23;
		end;
	run;

data earn_sum;
	merge earn_15 earn_16 earn_17 earn_18 earn_19 earn_20 earn_21 earn_22 earn_23;
	by fkclient_t;
	pre_age_18_qtrs = sum(earn_yr_16, earn_yr_17);
	post_age_18_qtrs = sum(earn_yr_18, earn_yr_19, earn_yr_20, earn_yr_21, earn_yr_22);
	if earn_yr_16 > 0 or earn_yr_17 > 0 then any_16to18 = 1; else any_16to18 = 0;
	if earn_yr_18 > 0 then any_18to19 = 1; else any_18to19 = 0;
	if sum(earn_yr_18, earn_yr_19) > 0 then any_18to20 = 1; else any_18to20 = 0;
	if sum(earn_yr_18, earn_yr_19, earn_yr_20) > 0 then any_18to21 = 1; else any_18to21 = 0;
	if sum(earn_yr_18, earn_yr_19, earn_yr_20, earn_yr_21) > 0 then any_18to22 = 1; else any_18to22 = 0;
	if any_18to21 = 1 or earn_yr_22 > 0 then any_18to23 = 1; else any_18to23 = 0;
	any_earn = 1;
	wage_16to18 = earn_amt_17 + earn_amt_16;
	wage_18to19 = earn_amt_18;
	wage_18to20 = wage_18to19 + earn_amt_19;
	wage_18to21 = wage_18to20 + earn_amt_20;
	wage_18to22 = wage_18to21 + earn_amt_21;
	wage_18to23 = wage_18to22 + earn_amt_22;

	run;

data tay.TAY_EDD;
	merge cohort earn_sum;
	by fkclient_t;
	if any_16to18 = . then any_16to18 = 0;
	if any_18to19 = . then any_18to19 = 0;
	if any_18to20 = . then any_18to20 = 0;
	if any_18to21 = . then any_18to21 = 0;
	if any_18to22 = . then any_18to22 = 0;
	if any_18to23 = . then any_18to23 = 0;
	if any_earn = . then any_earn = 0;
	/*start here - set wage sums to 0 if missing*/
	if earn_amt_16 = . then earn_amt_16 = 0;
	if earn_amt_17 = . then earn_amt_17 = 0;
	if earn_amt_18 = . then earn_amt_18 = 0;
	if earn_amt_19 = . then earn_amt_19 = 0;
	if earn_amt_20 = . then earn_amt_20 = 0;
	if earn_amt_21 = . then earn_amt_21 = 0;
	if earn_amt_22 = . then earn_amt_22 = 0;

	if wage_16to18 = . then wage_16to18 = 0;
	if wage_18to19 = . then wage_18to19 = 0;
	if wage_18to20 = . then wage_18to20 = 0;
	if wage_18to21 = . then wage_18to21 = 0;
	if wage_18to22 = . then wage_18to22 = 0;
	if wage_18to23 = . then wage_18to23 = 0;
	/*yearly earnings with zero set to missing to allow calculation of mean and
	  meadian earnings only for those with earnings*/
	if earn_amt_16 = 0 then earn_amt_16x = .;
		else earn_amt_16x = earn_amt_16;
	if earn_amt_17 = 0 then earn_amt_17x = .;
		else earn_amt_17x = earn_amt_17;
	if earn_amt_18 = 0 then earn_amt_18x = .;
		else earn_amt_18x = earn_amt_18;
	if earn_amt_19 = 0 then earn_amt_19x = .;
		else earn_amt_19x = earn_amt_19;
	if earn_amt_20 = 0 then earn_amt_20x = .;
		else earn_amt_20x = earn_amt_20;
	if earn_amt_21 = 0 then earn_amt_21x = .;
		else earn_amt_21x = earn_amt_21;
	if earn_amt_22 = 0 then earn_amt_22x = .;
		else earn_amt_22x = earn_amt_22;

	
	label predom_ft = "Pre Age 18 Predominant Facility Type";
	run;

proc contents data = tay.TAY_EDD varnum;
	run;





%let age_cohort = 2015;
%let out_year = 22;

proc tabulate data = TAY.TAY_EDD missing format = comma7.0 noseps;
	class age_18_yr ethnic gender_cd oh_flag pe1_s_age last_pe_e_age county cnty_spfcd flag_8
		time_in agency cens_ethnic predom_ft scp_rltc last_age18_ohp any_16to18
		age18_status age19_status age20_status age21_status age22_status age23_status;
	class any_18to19 any_18to20 any_18to21 any_18to22 any_18to23/ descending;	
	table  any_18to&out_year.=' ' all, age&out_year._status*n=' ' all*n=' '
		/box = "Any Reportable Earnings between Age 18 and Age &out_year.";
	format age&out_year._status status.;
	format any_18to&out_year. yes_no.; 
	where age_18_yr = &age_cohort.;
	title3 "Youth Turning 18 in &age_cohort.";
	label  age&out_year._status = "Last Known Status at age &out_year.";
	run;