/****************************************************************************************
** Copyright (c) 2007 Center for Social Services Research,                             **
** University of California at Berkeley. All rights reserved.                          **
**                                                                                     **
** This program produces data for Dynamic Entries Reports                              **
**                                                                                     **
** Entries                                                                             **
**                                                                                     **
** These reports are derived from a longitudinal database and provide information      **
** on all entries to out of home care during the time period specified.                **
**                                                                                     **
** Programmer: S. Sean Lee                                                             **
**                                                                                     **
** Revision History:                                                                   **
** 2007.08.30 add Caregiver Relationship variable P_SCPR                               **
**            stop using SCP_RLTC to assign Kin Relative NonGuardian                   **
**            re-assign Placement Type value by varable convention                     **
** 2007.09.19 Using UCB_CNTY_REM values on CNTY for probation and other                **
** 2007.10.18 Take out missing age group                                               **
** 2009.02.10 Add Child per Entry Indicator                                            **
** 2009.03.25 Assigning PLC_FCLC='1415' to 'Foster'                                    **
** 2009.08.31 Updating "Children Entering" as unduplicate count by county level and    **
**            by state total                                                           **
****************************************************************************************/

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

**now move to specific periods as a macro;

%macro ENTRY(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 data1;
set dwh.UCB_FC_AFCARS(drop=ETHNIC);

where AGY_RSPC not in (0036, 5605, 6133, 6134);

if PE_E_DT = . then TPE_E_DT = &CUTOFF;
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;

if OH_E_DT = . then SORT_DT = &DODEX; /* Date of data Extract */
else SORT_DT = OH_E_DT;

if CNTY_REM ne ' ' then CNTY = input(CNTY_REM, best2.);
else CNTY = 98;

if AGY_RSPC ne 34 and CNTY in (.,0,59,98,99) then CNTY = input(RSP_CNTY, best2.);

if not (1 le CNTY le 58) then CNTY=98;

temp_id = FKCLIENT_T || compress(put(SPELL,Z2.));

run;

/* Unduplcate by child level */

data data1a;
set data1;

where PE_S_DT between yyq(&SYEAR,&SQTR) and yyq(&SYEAR+1,&SQTR)-1 ;

cnty_id = FKCLIENT_T || compress(put(CNTY,Z2.));

run;

proc sort data = data1a;
by CNTY_ID SPELL PE_S_DT OH_S_DT PLCMNT SORT_DT;
run;

data data2a;
set data1a;

by CNTY_ID;
if first.CNTY_ID;

run;

data data3a;
set data2a;

UNDUP = 1;

run;

/* Add UNDUP back to main data */

proc sql;

create table data1b as
select x.*, y.UNDUP length=3
from data1 as x left join data3a as y
on x.TEMP_ID = y.TEMP_ID;

quit;


proc sort data = data1b;
by temp_id PE_S_DT OH_S_DT PLCMNT SORT_DT;
run;

data data2;
set data1b;

by temp_id;
if first.temp_id;

run;

data test1;
set data2;

where PE_S_DT between yyq(&SYEAR,&SQTR) and yyq(&SYEAR+1,&SQTR)-1 ;

if UNDUP = . then UNDUP = 0;

/* Agency Type */

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

/* Assign Gender Class */

select(GENDER_CD);
   when('F') GENDER=1;                                /* Female */
   when('M') GENDER=2;                                /* Male */
   when('I') GENDER=3;                                /* Intersex */
   otherwise GENDER=99;                               /* Unknown/Missing */     
end;

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

AGE = int((intck('month',BIRTH_DT,PE_S_DT)-(day(PE_S_DT) lt day(BIRTH_DT)))/12);

if AGE lt 0 or AGE gt 20 then AGE = 99;
if AGE = 0 then AGE = 0.5;
if 0 <= PE_S_DT - BIRTH_DT < 29 then AGE = 0;

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

/* First Placement Type */

if PLC_FCLC in ('2222') then P_PLC = 1;                 /* Pre-Adopt */
else if PLC_FCLC in ('1421','1422') then P_PLC = 2;     /* Kin */
else if PLC_FCLC in ('1415','1416') then P_PLC = 3;     /* Foster */
else if PLC_FCLC in ('1414','2200') then P_PLC = 4;     /* FFA */
/* else if PLC_FCLC in ('6914.1') then P_PLC = 5; */    /* RFH - Relative */
/* else if PLC_FCLC in ('6914.2') then P_PLC = 6; */    /* RFH - Non Relative */
/* else if PLC_FCLC in ('6915.1') then P_PLC = 7; */    /* RFH FFA - Relative */
/* else if PLC_FCLC in ('6915.2') then P_PLC = 8; */    /* RFH FFA - Non Relative */
else if PLC_FCLC = '1419' then P_PLC = 9;               /* Court Specified Home */
else if PLC_FCLC in ('7208') then P_PLC = 9.2;          /* Tribe Specified/Approved Home */
else if PLC_FCLC = '1417' then P_PLC = 10;              /* Group/STRTP */
else if PLC_FCLC in ('1418','7027') then P_PLC = 11;    /* Shelter */
else if PLC_FCLC = '5411' then P_PLC = 14;              /* Guardian */
else if PLC_FCLC = '6716' then P_PLC = 19;              /* SILP */
else if PLC_FCLC not in (' ','0') then P_PLC = 29;      /* Other */
else if PLC_FCLC in (' ','0') then P_PLC = 99;          /* Missing */

if PREDOM_FT = 13 then PREDOM_FT = 29;

/* Caregiver Relationship */

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

run;


*
CNTY length=3, FLAG_8 length=3, ENTRY length=3, AGE length=3, UNDUP length=3,
       GENDER as GENDER_CD length=3, ETHNIC length=3, REMREAS length=3,
       P_PLC length=3, PREDOM_FT, PERIOD_DT length=4, AGENCY length=3, P_SCPR length=3,
       CENS_RC length=3, HISP_CDX length=3,;




/*logic from CFSR3 P1 program - to find Los Angeles office to associate entry with
  note: Los Angeles only wants placements open to a regional office to be associated with 
  a regional office. Thus reported rate will be lower than actual rate for each region.*/
/* find assignment county and office using file dwh.UCB_OFFICE as test of that file */
/* reduce entries_01 to one record - the last record - per episode */

data entries_01a; * (keep = fkclient_t temp_id birth_dt pe_s_dt pe_e_dt 
		oh_s_dt oh_e_dt spell age undup gender ethnic remreas p_plc
		predom_ft agency p_scpr cens_rc hisp_cdx);
	set test1;
	
	run;

proc sort data = entries_01a;
	by temp_id oh_s_dt;
	run;

data entries_01b; * (rename = (fed = L_PLC scpr = l_scpr));
	set entries_01a;
	by temp_id oh_s_dt;
	if last.temp_id;
	run;

/* Bring in office assignment data */

proc sql;
	create table entries_02 as
		select entries_01b.*,
			ucb_office.*
			from entries_01b left join dwh.ucb_office
		on ucb_office.fkchld_clt = entries_01b.fkclient_t
			and (ucb_office.office_s_dt < pe_e_dt /*should this be =< pe_e_dt?*/
				or pe_e_dt = .)
			and ucb_office.office_e_dt > pe_s_dt /*should this be => pe_s_dt?*/
		order by fkclient_t, temp_id, office_s_dt;
		;
	quit;

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

data entries_02a entries_02b (drop = fkchld_clt cnty_spfcd office_s_dt office_e_dt type);
	set entries_02;
	if office_s_dt ne . then output entries_02a;
		else output entries_02b;
	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 entries_02b1 as
		select st_id.*,
			entries_02b.*
			from entries_02b left join dwh.st_id
		on st_id.fkchld_clt = entries_02b.fkclient_t
			and (st_id.office_s_dt < pe_e_dt /*should this be =< pe_e_dt?*/
				or pe_e_dt = .)
			and st_id.office_e_dt > pe_s_dt /*should this be => pe_s_dt?*/
		order by fkclient_t, temp_id, 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 */
/* At this point there are multiple records for many temp_ids - i.e., multiple
   office (or just county) assignments during a given placement episodes) */

data entries_03;
	set entries_02a entries_02b1;
	run;

proc sort data = entries_03;
	by fkclient_t temp_id office_s_dt;
	run;


/* Delete hotline/emergency response office case assignments in Los Angeles when
   subsequent office assignments exist unless open to that office for > 185 days*/
/* This could be expanded to include similar offices in other counties */
/* Delete referral office if there also is a case office unless it is the last assignment.
   This follows methodology of using county/office assignment of case unless there only is referral data */
/* Modified for 2018Q1 to take 185 days from episode start to office end and to apply to Los Angeles
	hotline office - which also provides CSEC services*/


data entries_04;
    set entries_03;
    by fkclient_t temp_id office_s_dt;
    /*if Los Angeles hotline followed by other office, delete*/
    *if cws_off_no in ("S1278") and last.temp_id = 0 then delete;
    if cws_off_no in ("S1278") and last.temp_id = 0 
		and office_e_dt - pe_s_dt < 185 and office_e_dt ne '31DEC3000'D then delete;
    /*if referral status less than 185 days followed by regular assignment, then delete
      may result in change of county responsible*/
    *if type = "R" and last.temp_id = 0 
        and office_e_dt - office_s_dt < 185 and office_e_dt ne . then delete;
	if type = "R" and last.temp_id = 0 
        and office_e_dt - pe_s_dt < 185 and office_e_dt ne '31DEC3000'D then delete;
    /*if opened at Los Angeles MART office as referral or case and then transferred
       to other office (or county) within 185 days, delete*/
    *if cws_off_no = "S0224" and last.temp_id = 0 
        and office_e_dt - office_s_dt < 185 and office_e_dt ne . then delete;
    if cws_off_no = "S0224" and last.temp_id = 0 
        and office_e_dt - pe_s_dt < 185 and office_e_dt ne '31DEC3000'D then delete;
    if office_e_dt = '31DEC3000'D then office_e_dt = .;
    run;



/* Find first office assignment for each placement
   episode. Note temp_id is unique for each child in each episode. */

/* First office assignment in episode*/
data entries (drop = x_cws_off_no  x_fkcws_offt x_cnty);
	set entries_04;
	by fkclient_t temp_id office_s_dt;
	if first.temp_id;
    if cnty_spfcd = '  ' then 
		cnty_spfcd = put((cnty), Z2.);

	   x_cws_off_no = cws_off_no;
	   x_fkcws_offt = fkcws_offt;
	   x_cnty = cnty;
	%include "/dss/SAS/PROGRAM/CWS_CMS/SAS_MACRO/county.sas";
	*if county ge 1920 and county le 1960 then county = 1999;
	run;

data test2;
set entries(drop=GENDER_CD);

where AGE between 0 and 20;

if SPELL = 1 then ENTRY = 1;
else ENTRY = 2;

PERIOD_DT=yyq(&SYEAR,&SQTR);

run;

data test2a;
set test2;

AGENCY = 4;

run;

data test3;
set test2 test2a;
run;

data test3a;
set test3(drop=COUNTY);

where CNTY = 19;

COUNTY = 1900;

run;

data test3c;
set test3 test3a;

run;


proc sql;


create table entry_cnty_1 as
select CNTY length=3, FLAG_8 length=3, ENTRY length=3, AGE length=3, UNDUP length=3,
       GENDER as GENDER_CD length=3, ETHNIC length=3, REMREAS length=3,
       P_PLC, PREDOM_FT, PERIOD_DT length=4, AGENCY length=3, P_SCPR length=3,
       CENS_RC length=3, COUNTY length=4, HISP_CDX length=3,
       count(*) as COUNT length=4 format=comma12.
from test3c
group by CNTY, FLAG_8, ENTRY, UNDUP, AGENCY, AGE, GENDER_CD, ETHNIC, CENS_RC, HISP_CDX, REMREAS, P_PLC, 
	P_SCPR, PREDOM_FT, COUNTY, PERIOD_DT;

quit;

data entry_cnty_&SYEAR.Q&SQTR;
length COUNT2 3;
set entry_cnty_1;

COUNT2 = COUNT;

run;

data test4;
set test3(drop=CNTY);

*where COUNTY ne 1900;

CNTY = 0;
COUNTY = 0;

run;

data test3a;
set test2;

*where COUNTY ne 1900;

run;

proc sort data=test3a out=test4a;
by FKCLIENT_T SPELL PE_S_DT OH_S_DT PLCMNT SORT_DT;
run;

data test4b;
set test4a;

by FKCLIENT_T;
if first.FKCLIENT_T;

UNDUP = 1;
CNTY = 0;
COUNTY = 0;

run;


data test4c;
set test4b;

AGENCY = 4;

run;

data test4d;
set test4b test4c;

run;

proc sql;

create table entry_state_1 as
select CNTY length=3, FLAG_8 length=3, ENTRY length=3, AGE length=3, UNDUP length=3,
       GENDER as GENDER_CD length=3, ETHNIC length=3, REMREAS length=3,
       P_PLC, PREDOM_FT, PERIOD_DT length=4, AGENCY length=3, P_SCPR length=3,
       CENS_RC length=3, HISP_CDX length=3, COUNTY length=4,
       count(*) as COUNT2 length=4 format=comma12.
from test4d
group by CNTY, FLAG_8, ENTRY, UNDUP, AGENCY, AGE, GENDER_CD, ETHNIC, CENS_RC, HISP_CDX, REMREAS, P_PLC, P_SCPR, 
	PREDOM_FT, COUNTY, PERIOD_DT;

create table entry_state_2 as
select CNTY length=3, FLAG_8 length=3, ENTRY length=3, AGE length=3, UNDUP length=3,
       GENDER as GENDER_CD length=3, ETHNIC length=3, REMREAS length=3,
       P_PLC, PREDOM_FT, PERIOD_DT length=4, AGENCY length=3, P_SCPR length=3,
       CENS_RC length=3, HISP_CDX length=3, COUNTY length=4,
       count(*) as COUNT length=4 format=comma12.
from test4
group by CNTY, FLAG_8, ENTRY, UNDUP, AGENCY, AGE, GENDER_CD, ETHNIC, CENS_RC, HISP_CDX, REMREAS, P_PLC, P_SCPR, 
	PREDOM_FT, COUNTY, PERIOD_DT;

create table entry_state_&SYEAR.Q&SQTR as
select x.*, y.COUNT2
from entry_state_2 as x left join entry_state_1 as y
on x.CNTY = y.CNTY and
   x.FLAG_8 = y.FLAG_8 and
   x.ENTRY = y.ENTRY and
   x.AGE = y.AGE and
   x.UNDUP = y.UNDUP and
   x.GENDER_CD = y.GENDER_CD and
   x.ETHNIC = y.ETHNIC and
   x.CENS_RC = y.CENS_RC and
   x.HISP_CDX = y.HISP_CDX and
   x.REMREAS = y.REMREAS and
   x.P_PLC = y.P_PLC and
   x.PREDOM_FT = y.PREDOM_FT and
   x.PERIOD_DT = y.PERIOD_DT and
   x.AGENCY = y.AGENCY and
   x.P_SCPR = y.P_SCPR and
   x.COUNTY = y.COUNTY
group by x.CNTY, x.FLAG_8, x.ENTRY, x.UNDUP, x.AGENCY, x.AGE, x.GENDER_CD, x.ETHNIC, x.CENS_RC, 
	x.HISP_CDX, x.REMREAS, x.P_PLC, x.P_SCPR, x.PREDOM_FT, x.COUNTY, x.PERIOD_DT;

quit;

proc append base=county data=entry_cnty_&SYEAR.Q&SQTR; 
proc append base=state data=entry_state_&SYEAR.Q&SQTR; 
%end;
%end;
%mend ENTRY;

%ENTRY(1,1998,4,2024);

data entries;
set county state;
run;

/* Adding 4B features */

data test.entries;   
set entries;

if P_PLC = 2 then P_PLC_4B = 1;              /* Relative/NREFM */
else if P_PLC = 3 then P_PLC_4B = 2;         /* Foster */
else if P_PLC = 4 then P_PLC_4B = 3;         /* FFA */
else if P_PLC in (10,11) then P_PLC_4B = 4;  /* Group/STRTP, Shelter */
else if P_PLC = 99 then P_PLC_4B = 99;       /* Missing */
else P_PLC_4B = 5;                           /* Other */


if PREDOM_FT = 2 then PREDOM_FT_4B = 1;              /* Relative/NREFM */
else if PREDOM_FT = 3 then PREDOM_FT_4B = 2;         /* Foster */
else if PREDOM_FT = 4 then PREDOM_FT_4B = 3;         /* FFA */
else if PREDOM_FT in (10,11) then PREDOM_FT_4B = 4;  /* Group/STRTP, Shelter */
else if PREDOM_FT = 99 then PREDOM_FT_4B = 99;       /* Missing */
else PREDOM_FT_4B = 5;                               /* Other */

run;

proc sort data = test.entries;
by AGENCY CNTY ENTRY FLAG_8 PERIOD_DT;
run;

/*
data Test.entries;
set Dvlp.entries;
run;
*/