/****************************************************************************************
** Copyright (c) 2014 Center for Social Services Research,                             **
** University of California at Berkeley. All rights reserved.                          **
**                                                                                     **
** This program produces data for Case Openings Per Year Reports                       **
**                                                                                     **
** Openings                                                                            **
**                                                                                     **
**                                                                                     **

**    2014.03.25 modify case end program for case start data                           ** 
**                                                                                     ** 
****************************************************************************************/

data data1a;
    set dwh.UCB_CASE_SVC_COMP;
    where CASE_S_DT ne . and CASE_S_DT <= &CUTOFF and RSP_AGY_CD in ("C" "P")
        and case_s_dt ge (birth_dt-30) and year(case_s_dt) > 1996;
    if CSC_E_DT = . then SORT_DT = &DODEX; /* Date of data Extract */
        else SORT_DT = CSC_E_DT;
        format birth_dt sort_dt mmddyy10.;
    run;

proc sort data = data1a;
    by fkchld_clt case_s_dt fkcase_t csc_s_dt sort_dt;
    run;
/*
proc print data = data1a (obs = 30);
    var fkchld_clt fkcase_t case_s_dt case_e_dt csc_s_dt csc_e_dt srv_cmpt case_s_cnty sort_dt;
    where year(case_s_dt) = 2013;
    run;
*/
*Delete record for case if ER and there are subsequent non-ER case types;
*This allows reporting of first non-ER case unless the only case type was ER;
data data1b;
    set data1a(drop=ETHNIC);
    by fkchld_clt case_s_dt fkcase_t csc_s_dt sort_dt;
    if last.fkchld_clt = 0 and srv_cmpt = "ER" then delete;
    run;

*keep first record for each case - i.e., showing county and case service component (type)
 at the time of case opening after deleting initial ER case type if subsequent other
 case type - and make other usual transformations;


data data1(drop = GENDER_CD);
set data1b; 

by fkchld_clt case_s_dt fkcase_t csc_s_dt sort_dt;
if first.fkcase_t;
    

where RSP_AGY_CD in ("C", "P");

if CASE_E_DT = . or CASE_E_DT - CASE_S_DT >= 8 then FLAG_8 = 1;
else if CASE_E_DT - CASE_S_DT < 8 then FLAG_8 = 2;

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

TEMP_ID = FKCHLD_CLT || compress(put(CASE_CT,Z2.));

/* Agency Type */

select(RSP_AGY_CD);
   when("C") AGENCY = 1;         /* Child Welfare */
   when("P") 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/PI */
   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 */

AGE = int((intck('month',birth_dt,CASE_S_DT)-(day(CASE_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 <= CASE_S_DT - BIRTH_DT < 29 then AGE = 0;


label age = "Age at Case Opening";

select(SRV_CMPT) ;
   when('ER') SRV_CMP=1 ;
   when('FM') SRV_CMP=2 ;
   when('AP') SRV_CMP=2 ;
   when('FR') SRV_CMP=4 ;
   when('PP') SRV_CMP=5 ;
   when('ST') SRV_CMP=6 ;
   otherwise SRV_CMP=99 ;
end ;

run;

/*
proc tabulate data = data1 missing format = comma8.0 noseps;
    options pageno = 1;
    class age srv_cmpt;
	table age=' ' all='Total', 
		srv_cmpt*(N*(f=comma7.0) pctn<srv_cmpt all>='%'*(f=5.1))
		all='Total'*(N*(f=comma7.0))
		/rts = 9 box = "Case Opening Age";
    where case_ct = 1 and year(case_s_dt) = 2009;
    label srv_cmpt = "First Service Component";
    run;
*/

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

%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. ;
/*
*test periods;
%let syear = 2013;
%let sqtr = 1;
*/

data entry5;
*set entry4;
set data1;

where CASE_S_DT ne . and CASE_S_DT between yyq(&SYEAR,&SQTR) and yyq(&SYEAR+1,&SQTR)-1;

*CNTY_ID = FKCHLD_CLT || compress(put(CASE_E_CNTY,Z2.));
cnty_id = fkchld_clt || case_s_cnty;

*if entry = . then entry = 2;

PERIOD_DT=yyq(&SYEAR,&SQTR);

run;

/* Add Unduplcate by county level */

proc sort data = entry5;
by CNTY_ID CASE_S_DT SORT_DT;
run;

data entry5a;
set entry5;

by CNTY_ID;
if last.CNTY_ID;

UNDUP = 1;

run;

/* Add UNDUP back to main data */

proc sql;

create table entry6 as
select x.*, y.UNDUP length=3
from entry5 as x left join entry5a as y
on x.TEMP_ID = y.TEMP_ID;

quit;

data entry6a;
set entry6(drop=AGENCY);

AGENCY = 4;

run;

data entry7;
set entry6 entry6a;

CNTY = CASE_S_CNTY * 1;

where AGE ne 99;

run;

proc sql;

create table entry_cnty_1 as
select CNTY length=3, FLAG_8 length=3, AGE length=3,
       GENDER as GENDER_CD length=3, ETHNIC length=3, CENS_RC length=3, 
       CENS_ETHNIC length=3, HISP_CDX length=3, UNDUP length=3,
       PERIOD_DT length=4, AGENCY length=3, SRV_CMP length = 3, CASE_CT length = 3,
       count(*) as COUNT length=4 format=comma12.
from entry7
group by CNTY, FLAG_8, AGENCY, AGE, GENDER_CD, ETHNIC, CENS_RC, 
        CENS_ETHNIC, HISP_CDX, UNDUP, PERIOD_DT, SRV_CMP, CASE_CT;

quit;

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

COUNT2 = COUNT;

run;


data entry8;
set entry7(drop=CNTY);

CNTY=0;

run;

proc sort data=entry6 out=entry8a;
by FKCHLD_CLT CASE_S_DT SORT_DT;
run;

data entry8b;
set entry8a;

by FKCHLD_CLT;
if last.FKCHLD_CLT;

UNDUP=1;
CNTY=0;

run;

data entry8c;
set entry8b;

AGENCY = 4;

run;

data entry8d;
set entry8b entry8c;

where AGE ne 99;

if UNDUP = . then UNDUP = 0;

run;

proc sql;

create table entry_state_1 as
select CNTY length=3, FLAG_8 length=3, AGE length=3,
       GENDER as GENDER_CD length=3, ETHNIC length=3, CENS_RC length=3, 
       CENS_ETHNIC length=3, HISP_CDX length=3, UNDUP length=3,
       PERIOD_DT length=4, AGENCY length=3, SRV_CMP length = 3, CASE_CT length = 3,
       count(*) as COUNT2 length=4 format=comma12.
from entry8d
group by CNTY, FLAG_8, AGENCY, AGE, GENDER_CD, ETHNIC, CENS_RC, 
        CENS_ETHNIC, HISP_CDX, UNDUP, PERIOD_DT, SRV_CMP, CASE_CT;


create table entry_state_2 as
select CNTY length=3, FLAG_8 length=3, AGE length=3,
       GENDER as GENDER_CD length=3, ETHNIC length=3, CENS_RC length=3,
       CENS_ETHNIC length=3, HISP_CDX length=3, UNDUP length=3,
       PERIOD_DT length=4, AGENCY length=3, SRV_CMP length = 3, CASE_CT length = 3,
       count(*) as COUNT length=4 format=comma12.
from entry8
group by CNTY, FLAG_8, AGENCY, AGE, GENDER_CD, ETHNIC, CENS_RC, 
        CENS_ETHNIC, HISP_CDX, UNDUP, PERIOD_DT, SRV_CMP, CASE_CT;

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.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.CENS_ETHNIC = y.CENS_ETHNIC and
   x.HISP_CDX    = y.HISP_CDX and
   x.PERIOD_DT   = y.PERIOD_DT and
   x.AGENCY      = y.AGENCY and
   x.SRV_CMP     = y.SRV_CMP  and
   x.CASE_CT     = y.CASE_CT

group by x.CNTY, x.FLAG_8, x.AGENCY, x.AGE, x.GENDER_CD, x.ETHNIC, 
        x.CENS_RC, x.CENS_ETHNIC, x.HISP_CDX, x.UNDUP, x.PERIOD_DT,
        x.SRV_CMP;

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 dvlp.case_OPEN;
set county state;

if cnty = 99 then cnty = 98;

run;

proc sort data = dvlp.case_OPEN;
by FLAG_8 AGENCY PERIOD_DT CNTY;
run;

data test.case_OPEN;
set dvlp.case_OPEN;
run;