/* code version 2011.11.04 */
/***********************************************************************************/
/*                      MEASURE 6B: IEP SERVICES REPORT                            */
/***********************************************************************************/
/*
/ 
/Programmer: Jim Pecorella
/October 2009 - LVB rewrote as dynamic and added pre-adoptive placements and
/the ability to summarize by placement type.
/
/INCLUDES:
/
/	Placement episodes open 31 days or more with an open out-of-home placement.
/	Responsible agency code is "34" (County Welfare Department)
/   County assigned to the case
/		
/EXCLUDES:
/ Non Child Welfare supervised cases. 
/	Incoming ICPC cases
/	Placement episodes of less than 31 days  
/	Children placed outside of California
/	Non-foster care placements (NFC_PLCT table)
/   Children >= 19 years old 
/
************************************************************************************/
/* Revision History:
 * Modified by M. Armijo for running in UCB production environment.
 * VLS- 2/28/11, replaced LA office exclusion with case responsible agency code = 'C'
 * (Child Welfare supervised). 
 * Lois VanBeers. October 12, 2011. Modified code to get children from all 
 * agencies, not just welfare-supervised.  Added agency as a summary variable.  
 * Deleted non-fc children.
 * 2012.05.18 M. Armijo. Made correction to mapping of P_ETHNCTYC to derived var ETHNIC. 
 * 2015.11.23 J. Magruder - modify to eliminate problem of children being excluded because
 * of lack of authority for placement record
 */
	
%macro CDSS_ENV ;
/* CDSS environment-specific code contained in macro (not called) */
*CDSS FORMAT TO RUN THE REPORT;

*Submit locally;
%let dapb=162.2.111.31;
 options comamid=tcp remote=dapb;
 signon noscript;
libname R_WORK slibref=WORK server=DAPB;
* End local submit;

%let q=2011\Q2;
%let ReportStart = '01jan1998'd;

libname cws    "\\Cdssapp05\DSS\RAD\CWSCMS_Data\ucb_source\&q";
libname pstore '\\Cdssapp05\dss\RAD\Pstore\pstore_lvanbeer\Measure 6B';
libname Summ '\\cdssapp05\dss\RAD\Pstore\pstore_lvanbeer\measure 6B\SQLCounts';

%mend CDSS_ENV ;

%let ReportStart = '01jan1998'd;

/*** NEED TO CHANGE ***/
libname cws "/wss1/SAS/SASDATA/CWS_CMS/Q4_2024/SOURCE" ;
libname dwh "/ssa4/SAS/SASDATA/CWS_CMS/Q4_2024" ;
libname pstore "." ;
libname Summ "." ;


/* Revision 2015.11.23 - Use UCB_FC_AFCARS which excludes non-dependent guardians, out-of-state
 * ICPC placements, and non-foster care placements. Uses original CDSS program field
 * names. Changes adoption code to 1000 to conform to original CDSS program coding.
 * This is to correct problem identified by Santa Clara county when the county stopped
 * recording the authority for placement.
 */
proc sql;
    create table placements_01 as
        select u.p_ethnctyc as ethnic_cd,
            u.hisp_cd,
            u.gender_cd,
            u.birth_dt,
            u.fkclient_t as clientid,
            u.pe_s_dt as EpiStart,
            u.pe_e_dt as EpiEnd,
            u.gvr_entc as EpiCounty,
            u.agy_rspc as Epi_agency,
            u.oh_s_dt as ohpstart,
            u.oh_e_dt as ohpend,
            u.fkplc_hm_t as placeID,
            u.plcg_rnc as PlaceChangeReason,
            u.plc_fclc,
            case u.plc_fclc
                when 2222 then 1000
                else u.plc_fclc
                end as PlaceType
        from dwh.ucb_fc_afcars as u
        where u.pe_e_dt is null or pe_e_dt > &reportstart
            and (u.pe_e_dt = . or pe_e_dt - pe_s_dt ge 31);
    quit;

proc sql;
    create table placements_02a as
        select placements_01.*,
            plchm.trnhsg_fac as TransHousing,
            plchm.f_state_c
        from placements_01 left join cws.plc_hm_t as plchm
        on placements_01.placeID = plchm.identifier
        order by clientid, ohpstart;
    quit;

/*exclude out of state pacements*/
data placements_02;
    set placements_02a;
    if f_state_c ne 1828 then delete;
    run;


/*end of modification to correct for placement authority problem*/

proc sql;
  create table pstore.placements as 
	select 
		placements_02.*,
		case.RSP_AGY_CD as Agency,
 		assign.cnty_spfcd   as AssignCounty,
    assign.start_dt as AssignStart,
   	assign.fkcase_ldt
   		
   	from placements_02,
  		cws.case_t as case,
  		cws.asgnm_t as assign
  where
  		placements_02.ClientID = case.fkchld_clt and
		case.IDENTIFIER = assign.ESTBLSH_ID and
		assign.ASGNMNT_CD = 'P' and 		/*returns primary assigned cases only, not secondary, 
											hence no in-coming ICPC's.*/
		assign.ESTBLSH_CD = 'C' and 		/*case assignments, not referral*/
		(case.start_dt < ohpend or ohpend is null) AND
		(case.end_dt is NULL or case.end_dt >= ohpstart)  and 
		/*ensures case corresponds to placement*/
		(assign.start_dt < ohpend or ohpend is null) AND
		(assign.end_dt = . or assign.end_dt >= ohpstart)  
	order by ClientID;
;

/* Get IEPs for all CWS clients. */
 create table IEPs as
 select 
   a.identifier as ClientID
 From
   cws.client_t as a,
   cws.ed_enrlt as b,
   cws.edgr_det as c,
   cws.edc_rect as d
 Where
   a.identifier=b.fkchld_clt and
   b.identifier=c.fked_enrlt and
   c.fked_enrlt=d.fkedgr_det and
   c.third_id=d.fkedgr_de0 and
   d.ED_REC_C=741
 order by ClientID
;
quit;

/* Eliminate duplicates  */
proc sort data=IEPs out = pstore.IEPs  nodupkey;
	by ClientID ;
run;

/* Add IEP indicators to the placements. 
	Methodology states that client is to be counted as having
	an IEP regardless of its timing with the placement.*/
	
data pstore.PlacementsWInd ;
	merge pstore.Placements (in = inPlace)   
			pstore.IEPs  (in = inIEPs)	;
			by clientID ;
			if inPlace and inIEPs  then do;
				IEP = 1;  output;
			end;

			if inplace and not inIEPs then do;
				IEP = 0 ;  output;
			end;
run;

proc sql;
   drop table summ.state, summ.counties ;
quit;

/*************  Macro to generate summary counts for each quarter ****************************************/
options obs=MAX macrogen symbolgen ;

%macro Quarter(START, STOP, EQTR) ;
%do SYEAR = &START %to &STOP ;
%do SQTR = 1 %to 4 ; 

data _null_ ;
/* define start of analysis period */
call symput('BeginQrtr',yyq(&SYEAR,&SQTR)) ;

/* define end of analysis period, (use quarter/year rollover handling) */
%if &SQTR^=4 %then %do; call symput('EndQrtr',yyq(&SYEAR,%eval(&SQTR+1))-1) ; %end;
%if &SQTR=4  %then %do; call symput('EndQrtr',yyq(%eval(&SYEAR+1),1)-1)   ; %end;
run ;

/* first get all the episodes in the quarter and sort the placements and assignments */
 /* Select the last county assignment for each placement.   */
proc sql ;
	create table QtrPlacements as
	select * 
	from pstore.PlacementsWInd as placements
	where 
		EpiStart LE &EndQrtr        AND
     	( EpiEnd eq . OR EpiEnd GE &BeginQrtr)	and
		ohpstart LE &EndQrtr	and
		( ohpEnd eq . or ohpEnd GE &BeginQrtr) and
		AssignStart LE 	&EndQrtr
	order by clientID, EpiStart, OhpStart, AssignStart ; 

/* delete all the too-short episodes from the analysis quarter.  */
data QtrPlacements1;
  set QtrPlacements;
	if datdif(EpiStart,&EndQrtr,'act/act') LT 30 then delete; 
run;

/*	get last ohp and last county assignment for the quarter for each child.  
	Drop children 19 or older by end of quarter.      */
		/* the number of obs is the denominator */
data QtrPlacements2  ;
	set QtrPlacements1 (drop =  fkcase_ldt  AssignStart) ;
		by clientID ;
		if last.clientID ;

	AGE = floor ( (intck('month',BIRTH_DT,&EndQrtr)-(day(&EndQrtr) 
			lt day(BIRTH_DT) ) ) / 12); 
   if AGE GE 19 then delete;
   
  /* Assign a county, either from (1) the assignment table or
	 (2) the episode county   */	
   	County = AssignCounty;
	if AssignCounty = '99' and
    		 1068 <= EpiCounty <= 1125 
				then County = put((EpiCounty - 1067),z2.);
		
   if AGE eq .  then AGE=99 ;
   if AGE lt 0  then AGE=99 ;
   if AGE GE 19  then AGE=99 ;
  
   select(gender_cd) ;
      when('F') gender = 1;                    
      when('M') gender = 2;   
      when('I') gender = 3;   
      otherwise gender = 99;                  
   end ;

   select(Agency) ;  /* 9.27.2011. LVB added Select */
   		when('A') agency_cd=1 ; /* Private Adoption Agency */
   		when('C') agency_cd=2 ; /* County Welfare Department */
   		when('I') agency_cd=3 ; /* Indian Child Welfare */
  		 when('K') agency_cd=4 ; /* Kin-Gap */
  		 when('M') agency_cd=5 ; /* Mental Health */
  		 when('O') agency_cd=6 ; /* Out of State Agency */
  		 when('P') agency_cd=7 ; /* Probation */
  		 when('S') agency_cd=8 ; /* State Adoption District Office */
  		 otherwise agency_cd=99 ; /* unknown */
	end ;

/* 11.2.2011 LVB added the following to look at the 
		agency code in the episode table because case agency 
		above is sometimes missing */
	if Agency_cd = 99 and EpiAgency NE . then do;

	  select(EpiAgency) ;
   		when(36,5605) agency_cd=1 ; /* Private Adoption Agency */
   		when(34) agency_cd=2 ; /* County Welfare Department */
   		when(5602,5607) agency_cd=3 ; /* Indian Child Welfare */
  		 when(6134) agency_cd=4 ; /* Kin-Gap */
  		 when(6133) agency_cd=5 ; /* Mental Health */
  		 when(35,5604) agency_cd=6 ; /* Out of State Agency */
  		 when(33,5603) agency_cd=7 ; /* Probation */
  		 when(37,5606) agency_cd=8 ; /* State Adoption District Office */
  		 otherwise agency_cd=99 ; /* unknown */
	  end ;
	end;

   select(ETHNIC_cd) ;
      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(822,824,825,827,828,829,831,832,833,834,835,836,837,838,5922,5923) ETHNIC=4 ; /* Asian */
      when(820,821) ETHNIC=5 ;                 /* Native Am */
      otherwise ETHNIC=9 ;                     /* UNKNOWN VALUE */               
   end ;

      if HISP_CD eq 'Y' then ETHNIC = 3 ;        /* Hispanic */


	select(PlaceType) ;
		when (1000) PLACEMENT_TYPE = 1 ; 	/* pre-adopt */
		when(1421,1422) PLACEMENT_TYPE = 2 ;    /* Relative/NREFM*/           
		when(1415,1416) PLACEMENT_TYPE = 3 ;  	/* Foster */
		when(1414,2200) PLACEMENT_TYPE = 4 ;	/* FFA */
		when(1419) PLACEMENT_TYPE = 9 ; 	/* Court Specified */
		when(7208) PLACEMENT_TYPE = 9.2 ; 	/* Tribally Approved Home*/
		when(1417) PLACEMENT_TYPE = 10; 	/* Group */
		when(1418,7027) PLACEMENT_TYPE = 11 ; 	/* Shelter */
		when(5411) PLACEMENT_TYPE = 15 ; 	/* Guardian-dependent */
		when(0,.) PLACEMENT_TYPE = 99 ; 	/* Missing */
               	       otherwise PLACEMENT_TYPE = 29;   /* Other */
	  end ;   
	
	if TransHousing = 'Y' then  PLACEMENT_TYPE = 20;  /* transitional housing */
	
	drop birth_dt  gender_cd agency ethnic_cd AssignCounty EpiCounty;
run;


/********************************************************************************
/* Create group counts */
/* First the state counts */
proc sql;
   Create table statecounts as
   select  
      	 PLACEMENT_TYPE,
        IEP,
        Age,
        ethnic,
	 agency_cd,
        gender,   
        count(*) as count  
   from QtrPlacements2
   group by  PLACEMENT_TYPE, IEP, Age, ethnic, agency_cd, gender 
   ;

/* Add quarter and year of referral */
data state;
   set statecounts;
   period_dt = yyq(&SYEAR,&SQTR) ;
   County = '00';
run;
 
/* Store each quarter's counts.  */
proc datasets NOLIST ;
   append base = summ.state  data = state;
   delete  statecounts state;
run;

/* Now the county counts */
proc sql;
   Create table countyCounts as
   select County,   
      	 PLACEMENT_TYPE,
	 IEP,
        Age,
	 agency_cd,
        ethnic,
        gender,   
	 count(*) as count  
   from QtrPlacements2 
   group by County, PLACEMENT_TYPE, IEP, Age, agency_cd, ethnic, gender
;

/* Add quarter and year of referral */
data counties;
   set countyCounts;
   PERIOD_DT=yyq(&SYEAR,&SQTR) ;
run;

/* Store each quarter's counts.  */
proc datasets NOLIST ;
   append base = summ.counties  data = counties;
   delete  countycounts counties;
run;

%if &SYEAR=&STOP and &SQTR=&EQTR %then %goto finished;
%end ;
%end ;
%finished: %mend Quarter;

/*** NEED TO CHANGE ***/
%Quarter(1998,2024,4) ;

data CountyState;
	set summ.counties
		summ.state;
	FiveF_D = count;
	if IEP = 1 then FiveF_N = count;
	rename gender = gender_cd ;
run;

proc sql;
	create table Summary1 as
	select sum(FiveF_N) as Freq_N,
		sum(FiveF_D) as Freq_D,
		period_dt,
		County,   
      		PLACEMENT_TYPE,
		IEP,
         	Age,
		agency_cd,
         	ethnic,
         	gender_cd
	from CountyState
	group by period_dt,	County, PLACEMENT_TYPE, IEP, Age, ethnic, agency_cd, gender_cd
;

data summ.summary2;
	set Summary1;
		if Freq_N = . then Freq_N = 0;
		if Freq_D = . then Freq_D = 0;
run;
/* 6B_idx.sas */
/* Purpose:
 * Generate the production report database with indexing on 
 * the appropriate columns
 * Programmer: M. Armijo
 * Revision History:
 * 2010.07.15 Recoded county='99' as county='98' to conform to standard for
 * dynamic reports.
 * 2011.10.12. Lois VanBeers added agency_cd.
 */

%let DATA_VERSA=2024Q4Dvlp ;
%let DATA_VERSB=2024Q4Test ;

libname CFSR "/ssa3/SAS/REPORT_DATA/&DATA_VERSA/CFSR" ;
libname Test "/ssa3/SAS/REPORT_DATA/&DATA_VERSB/CFSR" ;
libname TEMP   "." ;

data CDSS_6B ;
set TEMP.SUMMARY2 ;
if county eq '99' then county='98' ;
run ;

proc sql;
create table CFSR.cdss_6B as
select    
 period_dt as PERIOD_DT           length=4 label="Period"  
,agency_cd as AGENCY_CD 		  length=4 label="Agency"
,county as CNTY_SPFCD             length=2 label="SupervisingCounty" 
,AGE as AGE                       length=4 label="Age"
,ethnic as ETHNIC                 length=4 label="Ethnicity"
,gender_cd as GENDER_CD           length=4 label="Gender"
,PLACEMENT_TYPE as PLACEMENT_TYPE label = "PlacementType"
,FREQ_N as IEP_N length=4 label="PlacementsWIEP"
,FREQ_D as IEP_D length=4 label="Placements"
from CDSS_6B
order by PERIOD_DT, AGENCY_CD, CNTY_SPFCD
;

create index PERIOD_DT on CFSR.CDSS_6B(PERIOD_DT) ;
/* create index AGENCY_CD on CFSR.CDSS_6B(AGENCY_CD) ; */
create index CNTY_SPFCD on CFSR.CDSS_6B(CNTY_SPFCD) ;

data Test.CDSS_6B;
set CFSR.CDSS_6B;
run;

proc sql;

create index PERIOD_DT on Test.CDSS_6B(PERIOD_DT) ;
create index CNTY_SPFCD on Test.CDSS_6B(CNTY_SPFCD) ;

/* clean up intermediate files */
drop table TEMP.summary2 ;
drop table TEMP.counties ;
drop table TEMP.ieps ;
drop table TEMP.office ;
drop table TEMP.placements ;
drop table TEMP.placementswind ;
drop table TEMP.state ;