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

*issues: episodes starting before birth included;

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

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

if PE_E_DT = . then TPE_E_DT = &CUTOFF;
else TPE_E_DT = PE_E_DT;

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

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

PERIOD_DT=yyq(&SYEAR,&SQTR);

run;

proc sort data = data1;
by CNTY_ID SPELL PMT_ORDER;

run;

data data3; /* identify children with entries in more than 1 county in reporting period */
	set data1;
	by cnty_id spell pmt_order;
	if first.cnty_id then UNDUP = 1;
	else UNDUP =0;
/* the purpose of UNDUP is to allow a child to be counted in more than one county's unique
 child count if the child entered care in more than one county in the 12-month reporting
 period while only counting the child once in the statewide count. (Statewide child count
 is less than sum of county child counts.) A child entering care in more than one county
 would have an UNDUP value of 1 for first entry in each county. Second + entry
 in the same county have an undup value of 0. */
	run;

*file with one record per episode entry;
proc sort data = data3;
by fkclient_t temp_id spell;
run;

*file with one record per entry with agency identified as 4 (All);
data test2a;
set data3;
by fkclient_t temp_id spell;

AGENCY = 4;

run;

/* merge together so that final file has two records for each episode start -
 one with agency code of 4 and one with agency code of 1, 2, or [rare] 3 */
data test3;
set data3 test2a;
run;

/* create separate set of records for Los Angeles without the Los Angeles
 office designator (any 19nn) county but 1900. */
data test3a;
set test3(drop=COUNTY);

where CNTY = 19;

COUNTY = 1900;

run;
/* merge together. Except for Los Angeles, each episode entry has two
   records. Los Angeles has has four sets of records for each Los Angeles
   episode entry */

data test3c;
set test3 test3a;

run;



*all entries;
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_CD length=3, ETHNIC length=3, REMREAS length=3,
       F_PLC as P_PLC, PREDOM_FT, PERIOD_DT length=4, AGENCY length=3, F_SCPR as 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;

*state wide entries, 2 records per county, no double records for Los Angeles;
data test4;
set test3(drop=CNTY);

*where COUNTY ne 1900;

CNTY = 0;
COUNTY = 0;

run;
/*
*not needed when using UCB_ENTRIES;
data test3a;
set test2; 

*where COUNTY ne 1900;

run;
*/
*statewide children entering - one record per child;
proc sort data=test3 out=test4a;
by FKCLIENT_T SPELL pmt_order;
run;

data test4b;
set test4a;

by FKCLIENT_T;
if first.FKCLIENT_T;

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

run;

*create all agency record;
data test4c;
set test4b;

AGENCY = 4;
run;

*create 2 records per child statewide entries, 1 all agency, 1 by agencies 1, 2, 3;

data test4d;
set test4b test4c;

run;

proc sql;
* one record per child statewide - duplicates 4 and 1,2,3;
create table entry_state_1 as
select CNTY length=3, FLAG_8 length=3, ENTRY length=3, AGE length=3, UNDUP length=3,
       GENDER_CD length=3, ETHNIC length=3, REMREAS length=3,
       F_PLC as P_PLC, PREDOM_FT, PERIOD_DT length=4, AGENCY length=3, F_SCPR as 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_CD length=3, ETHNIC length=3, REMREAS length=3,
       F_PLC as P_PLC, PREDOM_FT, PERIOD_DT length=4, AGENCY length=3, F_SCPR as 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;
quit;   proc sql;
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,3,2025);

data entries;
set county state;
run;

/* Adding 4B features */

data dvlp.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 = dvlp.entries;
by AGENCY CNTY ENTRY FLAG_8 PERIOD_DT;
run;

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