*****************************************************************************************;
** 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                        **;
**                                                                                     **;
** 4C.sas                                                                              **;
**                                                                                     **;
** Ths report includes all children who were in a congregate care placement on the     **;
** report point-in-time date (PERIOD_DT) and calculaes whether they had been in care   **;
** for at least 365 of the 400 preceding days. (This calculation of having been in     **;
** care for at least a year was made to give leeway for ruaway episodes.)              **;
**                                                                                     **;
** 2024.01.25 modified to use UCB_PIT rather than UCB_FC to create base cohort         **;
**            j magruder                                                               **;
*****************************************************************************************;

%let days_obs = 400;
%let days_gh = 365;

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

**now move to specific periods as a macro;

%macro CORE(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 */

proc sql;
create table test0 /* test_01 */ as
select fkclient_t, COUNTY as CNTY length=3, AGENCY, COURT_IND, AGE, GENDER_CD,
                ETHNIC, CENS_RC, CENS_ETHNIC, HISP_CDX, TIME_IN, PIT_PLC, PIT_SCPR, REMREAS,
                PERIOD_DT,
	case ICWA_ELGCD
		when "Y" then 1  /*ICWA Eligible*/
   		when "N" then 2	/*ICWA Not Eligible*/
   		when "P" then 3  /*ICWA Status Pending*/
   		else 99 /*ICWA Not asked, unknown, mssing*/
		end as ICWA,
	case INDN_STC
   		when 1212 then 1 /*Tribal Member of at least one tribe*/
   		when 1211 then 2 /*Eligible for Tribal Membership but not member of any tribe*/
   		when 1214 then 3 /*Neither of above but Pending Verification*/
   		when 1210 then 4 /*None of above but Claims Tribal membership*/
  		when 1213 then 5 /*none of above and 1+ tribes found ineligible for membership*/
   		when 6532 then 6 /*None of above and no tribe has responded after 60 days*/ 
	    else 99     /*No Tribal Membership data available*/
		end as TRIBAL_STC

from dwh.ucb_pit
where pit_plc in(10,11) and PERIOD_DT = yyq(&SYEAR.,&SQTR.) ; 
quit;

data test_01;
set test0;
if ETHNIC not in (1,2,3,4,5) then ETHNIC = 99;
run;

/* find all placements in year ending with pit in gh settings;
   include both group home and shelter placements; */
proc sql;
    create table gh_year_01 as
        select test_01.*,
            ucb_fc_afcars.oh_s_dt,
            ucb_fc_afcars.oh_e_dt,
            ucb_fc_afcars.plc_fclc,
            ucb_fc_afcars.plcmnt,
            ucb_fc_afcars.spell

        from test_01 left join dwh.ucb_fc_afcars
        on test_01.fkclient_t = ucb_fc_afcars.fkclient_t
            and ucb_fc_afcars.oh_s_dt le yyq(&SYEAR,&SQTR) 
            and (ucb_fc_afcars.oh_e_dt = .
                or ucb_fc_afcars.oh_e_dt ge yyq(&SYEAR,&SQTR) - &days_obs.)
            and ucb_fc_afcars.plc_fclc in(1417,1418)
            and ucb_fc_afcars.oh_s_dt ne ucb_fc_afcars.oh_e_dt
        order by fkclient_t, spell, plcmnt, oh_s_dt;
    quit;

/*find length of that portion of each placement that occurred in study period in days,
  and adjust length when there are overlapping placements.*/

data gh_year_02;
    set gh_year_01;
    by fkclient_t spell plcmnt oh_s_dt ;
    retain fkclient_t_x oh_e_dt_y;
    if oh_s_dt < yyq(&SYEAR,&SQTR) - &days_obs.
        then oh_s_dt_x = yyq(&SYEAR,&SQTR) - &days_obs.;
            else oh_s_dt_x = oh_s_dt;
    if oh_e_dt = . or oh_e_dt > yyq(&SYEAR,&SQTR) then oh_e_dt_x = yyq(&SYEAR,&SQTR);
        else oh_e_dt_x = oh_e_dt;
    format oh_s_dt_x oh_e_dt_x mmddyy10.;
    if fkclient_t = fkclient_t_x and oh_e_dt_y > oh_s_dt_x
        then oh_s_dt_z = oh_e_dt_y;
        else oh_s_dt_z = oh_s_dt_x;
    gh_days = oh_e_dt_x - oh_s_dt_z;
    if gh_days le 0 then gh_days = 0;
    fkclient_t_x = fkclient_t;
    oh_e_dt_y = oh_e_dt_x;
    format oh_e_dt_y oh_s_dt_z mmddyy10.;
    run;

/*calculate total number of group home days in study period*/

data gh_year_03 (drop = fkclient_t_x);
    set gh_year_02;
    by fkclient_t spell plcmnt oh_s_dt;
    retain fkclient_t_y gh_days_x;
    if fkclient_t ne fkclient_t_y then gh_days_x = gh_days;
        else gh_days_x = gh_days_x + gh_days;
    fkclient_t_y = fkclient_t;
    run;

/*group time in care data*/

data gh_year_04 (drop = fkclient_t_y);
    set gh_year_03;
    by fkclient_t spell plcmnt;
    if last.fkclient_t;
    if gh_days_x ge &days_gh. then most_year = 1;
        else most_year = 0;
    mo_in_gh = floor(gh_days_x/365.25*12);

    run;

data gh_year_05;
set gh_year_04;

AGENCY = 4;

run;

data point2;
set gh_year_04 gh_year_05;

rename
CNTY = COUNTY;
run;

data point2a;
set point2;

CNTY = COUNTY/100;

run;

data point3;
set point2a;

if 1901 <= COUNTY <= 1999 then CNTY = 19;
ETHNIC = ETHNIC * 1;

run;

proc sql;
create table pit_cnty_&SYEAR.Q&SQTR as
select CNTY, AGENCY, COURT_IND, AGE, GENDER_CD, ETHNIC, REMREAS, PERIOD_DT, PIT_PLC,
    /* CENS_RC, CENS_ETHNIC, HISP_CDX, TIME_IN, PIT_SCPR, */
	ICWA, TRIBAL_STC, 
           most_year, count(*) as COUNT length=4 format=comma12.
from point3
group by CNTY, AGENCY, COURT_IND, AGE, GENDER_CD, ETHNIC, REMREAS, most_year, PERIOD_DT, PIT_PLC,
     /* ,CENS_RC, CENS_ETHNIC, HISP_CDX, TIME_IN, PIT_SCPR */ 
	ICWA, TRIBAL_STC;
quit;

data point4;
set point3;

CNTY = 0;

run;

proc sql;
create table pit_state_&SYEAR.Q&SQTR as
select CNTY, AGENCY, COURT_IND, AGE, GENDER_CD, ETHNIC, REMREAS, PERIOD_DT, PIT_PLC,
    /* CENS_RC, CENS_ETHNIC, HISP_CDX, TIME_IN, PIT_SCPR, */
	ICWA, TRIBAL_STC, 
       most_year, count(*) as COUNT length=4 format=comma12.
from point4
group by CNTY, AGENCY, COURT_IND, AGE, GENDER_CD, ETHNIC, REMREAS, most_year, PERIOD_DT, PIT_PLC,
     /* ,CENS_RC, CENS_ETHNIC, HISP_CDX, TIME_IN, PIT_SCPR */ 
	ICWA, TRIBAL_STC ;
quit;

proc append base=county data=pit_cnty_&SYEAR.Q&SQTR;
proc append base=state data=pit_state_&SYEAR.Q&SQTR;

%end ;
%end ;
%mend CORE ;
%CORE(1,1998,1,2025) ;

data dvlp.cdss_4c;
set county state;
run;

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

data test.cdss_4c;
set dvlp.cdss_4c;
run;