************************************************************************************;
/*****MEASURE 5B1: CHDP EXAM REPORT*****/
************************************************************************************;

/* Tests for medical examinations for those children in welfare-supervised
	placement episodes.  The episodes must be open as of the end of each reporting 
	quarter and be of at least 30 days in length by the last day of the quarter.

	Children who are in non-dependent legal guardianships or in incoming ICPC
	cases, or who are out-of-state are excluded.

	Those children fulfilling the above criteria without a medical exam are 
	counted as out-of-compliance.  (Medical exams are required for each child
	during the first 30 days in out-of-home placement.)  

	There are requirements for periodic medical exams for each child depending
	on the child's age.  Children are counted as out of compliance if they leave
	an age category without the required medical exam. 

Kreg Zimmerman did the original programming.
	Winter 2010 - Lois VanBeers added pre-adoptive placements and converted to dynamic.
        Modified by M. Armijo for running in UCB production environment.
 */

/* Revision History
 * 2010.08.24 M. Armijo. Extended ages to include 19 & 20.
 * 2011.02.25 V. Simon.  Replaced LA office exclusion with case responsible agency code = 'C' (Child Welfare supervised).  
 * 2011.10.04 L. VanBeers made modifications to include all agency types, not just welfare.
 * Also deleted non-fc placements.
 * 2012.05.18 M. Armijo. Made correction to mapping of P_ETHNCTYC to derived var ETHNIC.
 * 2015.11.23 J. Magruder.
 * 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.
 * Correct problem identified by Santa Clara county when the county stopped recording
 * the authority for placement - modify to eliminate problem of children being excluded because
 * of lack of authority for placement record;
 * 2017.04.13 j magruder change to new EPSDT exam periods - see ACL 17-22.;
 */

%macro CDSS_ENV ;
/* CDSS environment-specific code contained in macro (not called) */
*SUBMIT LOCALLY;
%let dapb=162.2.111.31;
 options comamid=tcp remote=dapb;
 signon noscript;

*SUBMIT LOCALLY;
libname R_WORK slibref=WORK server=DAPB;

*Submit  locally and remotely;
%let q=2011\Q2; 
%let ReportStart='01jan2011'd;

libname SUMM '\\Cdssapp05\dss\RAD\Pstore\pstore_lvanbeer\Measure 5B\Med SQLCounts';
libname pstore '\\Cdssapp05\dss\RAD\Pstore\pstore_lvanbeer\Measure 5B';
libname cws   "\\Cdssapp05\dss\rad\CWSCMS_Data\UCB_Source\&q." ;
%mend CDSS_ENV ;

options mprint nosource ;
%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 J. Magruder */
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_02 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_03;
    set placements_02;
    if f_state_c ne 1828 then delete;
    run;

/* Get assignment county and exclude incoming ICPC. 
	10.5.2011.  Lois VanBeers deleted the selection by
	case responsible agency = Child Welfare. */

proc sql;
  create table pstore.placements as 
	select 
		placements_03.*,
		case.Identifier as CaseID,
		case.RSP_AGY_CD as Agency,
 		assign.cnty_spfcd   as AssignCounty,
    	assign.start_dt as AssignStart,
   		assign.fkcase_ldt
  from	placements_03,
  	 	cws.case_t as case,  
  		cws.asgnm_t as assign
  where	 placements_03.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 
		(assign.start_dt < ohpend or ohpend is null) AND
		(assign.end_dt = . or assign.end_dt >= ohpstart);
/* Revision 2011.10.4.2011 L. VanBeers Removed the test for welfare cases only. */
		/*		and    case.rsp_agy_cd = 'C'  */
;
    quit;

/* Get all medical services */  
proc sql; 
 create table pstore.MedServices as
 select 
 	CDS.fkcase_t,
	DelService.start_dt as ServiceDate
 From
 	cws.csdlSvct as CDS,
   cws.dl_Svc_t as DelService
 Where
   CDS.fkdl_Svc_t = DelService.Identifier and
   (SVC_CNTC = 1746 or SVC_CNTC = 1769 or 
	SVC_CNTC = 3236 or SVC_CNTC = 3238 ) and
    DelService.status_cd = 'C' 
 order by fkcase_t, ServiceDate
;                       

proc sql;
/*	drop table pstore.visit_comp2;   /* LVB commented out and added next line.*/
drop table summ.state  , summ.counties ;		

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

 
/* get the placements that are open at the end of each quarter	*/
/* Select the last county assignment and last legal authority for each placement.   */
proc sql ;
	create table QtrPlacements as
	select * 
	from pstore.Placements as placements
	where /*  include only placements open at Q's end 
		Must include test for episodes too.  Otherwise you can get
		placements that show a null end date but the episode has closed.
		(especially in pre-adopts).*/
		EpiStart LE &EndQrtr	and
			( EpiEnd eq . or EpiEnd GE &EndQrtr) and
		ohpstart LE &EndQrtr	and
			( ohpEnd eq . or ohpEnd GE &EndQrtr) and
		AssignStart LE 	&EndQrtr /*and
		LegalDate LE &EndQrtr*/
	 order by clientID, EpiStart, OhpStart, AssignStart/*, LegalDate*/ ; 

/* Get the last placement for each child.
	 Delete NDLG.
	 Keep only those placements in episodes open at least 30 days by the end of each quarter.  */
data QtrPlacements1 ;
  set QtrPlacements;
  	by clientID;
  	if last.clientID;

	/*if (plc_athc not in (1409,1410,1411,1412,1413))  
			and (PlaceType = 5411) 
			then  delete;*/

	if (datdif(EpiStart,&EndQrtr,'act/act')) GE 30;
			/*keeps placement episodes 31 days or longer.*/
  drop  plc_athc ;  
run;

/* Get medical exams for all children with placements in the 
	analysis quarter*/
proc sql;
 create table AddServices as
 select 
 	QtrPlacements1.*,
	services.ServiceDate 
 From
 	QtrPlacements1 left join 
	pstore.MedServices services
 on
	QtrPlacements1.CaseID = services.fkcase_t and
	ServiceDate LE &EndQrtr 
 order by ClientID, ServiceDate
;


/* 	Get last  medical exam for each child. */
Data QrtrService  ;
	set AddServices;
		by ClientID ServiceDate ;
		if last.ClientID ;

	AGE = floor ( (intck('month',BIRTH_DT,&EndQrtr)-(day(&EndQrtr) 
			lt day(BIRTH_DT) ) ) / 12); 
 
	If ServiceDate = . then medcompliance = 0; 
		/*if no exam, then not in compliance. 
	      Note only children in care 30 or more days in file*/
	else if birth_dt = . then medcompliance = .;
		/*Compliance cannot be calculated without age and thus birth date*/
If (ServiceDate NE . and birth_dt ne .) then do;
 	 length ageEoq MedAge 8.;	
   		AgeEoq = (&EndQrtr - birth_dt);      /*Age at the end of our time period.*/
   		MedAge = (ServiceDate - birth_dt);   /*Age at medical exam.*/
	/*child age group at end of study quarter*/
	if ageEoq < 3 then medEoqcat = 1; /*under 3 days*/
	else if ageEoq le 5 then medEoqcat = 2; /*3 to 5 days*/
	else if ageEoq le 30 then medEoqcat = 3; /*by 1 month*/
	else if ageEoq le 61 then medEoqcat = 4; /*2 months*/
	else if ageEoq le 122 then medEoqcat = 5; /*4 months*/
	else if ageEoq le 182 then medEoqcat = 6; /*6 months*/
	else if ageEoq le 273 then medEoqcat = 7; /*9 months*/
	else if ageEoq le 365 then medEoqcat = 8; /*12 months*/
	else if ageEoq le 456 then medEoqcat = 9; /*15 months*/
	else if ageEoq le 547 then medEoqcat = 10; /*18 months*/
	else if ageEoq le 730 then medEoqcat = 11; /*24 months*/
	else if ageEoq le 913 then medEoqcat = 12; /*30 months*/
	else if ageEoq le 1095 then medEoqcat = 13; /*3 years*/
	else if ageEoq ge 1096 then medEoqcat = 14+(floor((ageEoq-1095)/365.25)); 
	/*Yearly intervals for those over age 3*/
	if ageEoq > 7670 then medEoqcat = 32; /*over age 21*/

	/*child's age group at time of last EPSDT exam*/
	if MedAge < 3 then MedCat = 1; /*under 3 days*/
	else if MedAge le 5 then MedCat = 2; /*3 to 5 days*/
	else if MedAge le 30 then MedCat = 3; /*by 1 month*/
	else if MedAge le 61 then MedCat = 4; /*2 months*/
	else if MedAge le 122 then MedCat = 5; /*4 months*/
	else if MedAge le 182 then MedCat = 6; /*6 months*/
	else if MedAge le 273 then MedCat = 7; /*9 months*/
	else if MedAge le 365 then MedCat = 8; /*12 months*/
	else if MedAge le 456 then MedCat = 9; /*15 months*/
	else if MedAge le 547 then MedCat = 10; /*18 months*/
	else if MedAge le 730 then MedCat = 11; /*24 months*/
	else if MedAge le 913 then MedCat = 12; /*30 months*/
	else if MedAge le 1095 then MedCat = 13; /*3 years*/
	else if medAge ge 1096 then medcat = 14+(floor((medAge-1095)/365.25));  
	/*Yearly intervals for those over age 3*/ 
	if medAge > 7670 then medcat = 32; /*over age 21*/ 

	medcompliance = 0;   
   if (medEoqcat - medcat LE 1) then medcompliance = 1; 
  end;


  /* 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.);
/* capture persons age 19 & 20 */		
   if AGE eq .  then AGE=99 ;
   if AGE lt 0  then AGE=99 ;
   if AGE GE 21  then AGE=99 ;  
  
      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(gender_cd) ;
      when('F') gender = 1;                    
      when('M') gender = 2;   
      when('I') gender = 3;   
      otherwise gender = 99;                  
   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  agency gender_cd ethnic_cd AssignCounty EpiCounty
	 	birth_dt  medEoqcat medcat  ;  
run;


***********************************REMOVE*****;
proc freq data=AddServices;
  tables placetype;
run;
***********************************REMOVE*****;


/* Create group counts */
/* First the state counts */
/* 10.5.2011. Lois VanBeers added agency_cd */
proc sql;
   Create table statecounts as
   select  
      	 PLACEMENT_TYPE,
		 MedCompliance,
         Age,
         ethnic,
		 agency_cd,
         gender,   
         count(*) as count  
   from QrtrService
   group by  PLACEMENT_TYPE, MedCompliance, 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 */
/* 10.5.2011 Lois VanBeers added agency_cd */
proc sql;
   Create table countyCounts as
   select County,   
      	 PLACEMENT_TYPE,
		 MedCompliance,
         Age,
         ethnic,
		 agency_cd,
         gender,   

         count(*) as count  
   from QrtrService 
   group by County, PLACEMENT_TYPE, MedCompliance, 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;
	FiveBMed_D = count;
	if medcompliance = 1 then FiveBMed_N = count;
	rename 	gender = gender_cd ;
run;

/* 10.5.2011 Lois VanBeers added agency_cd */
proc sql;
	create table Summary1 as
	select sum(FiveBMed_N) as Freq_N,
			sum(FiveBMed_D) as Freq_D,
			period_dt,
			County,   
      		Placement_Type,
		 	medcompliance,
         	Age,
         	ethnic,
			agency_cd,
         	gender_cd
	from CountyState
	group by period_dt,	County, Placement_Type, medcompliance, 
		Age, ethnic, agency_cd, gender_cd
;

data summary2;
	set Summary1;
		if Freq_N = . then Freq_N = 0;
		if Freq_D = . then Freq_D = 0;
run;

/* 10.5.2011 Lois VanBeers added agency_cd */
proc sql;
	create table summ.cdss_5BMed as
	select    
		period_dt as PERIOD_DT length=4 label="Period"  ,
		county as CNTY_SPFCD   length=2 label="SupervisingCounty" ,
		AGE as AGE             length=4 label="Age", 
		ethnic as ETHNIC       length=4 label="Ethnicity",
		agency_cd  as AGENCY_CD length=4 label = "Agency", 
		gender_cd as GENDER_CD length=4 label="Gender",
		Placement_Type as PLACEMENT_TYPE label = "PlacementType"    ,
		FREQ_N as MEDCOMP_N length=4 label="MedicalCompliance" ,
		FREQ_D as MEDCOMP_D length=4 label="Placements"
	from summary2
	order by PERIOD_DT, CNTY_SPFCD
;
create index PERIOD_DT on summ.cdss_5BMed(PERIOD_DT) ;
create index CNTY_SPFCD on summ.cdss_5BMed(CNTY_SPFCD) ;