************************************************************************************;
/*****MEASURE 5B2: Dental EXAM REPORT*****/
************************************************************************************;
/* Run 5B Medical first */
/* Kreg Zimmerman did original programming to create the static reports
	on the UCB website.
	Spring 2010 - Lois VanBeers converted to dynamic. 

	Tests for dental examinations for those children in welfare-supervised
	placement episodes.  As of the last day of the quarter the episodes 
	must be open and of at least 30 days in length and the child
	must be at least 3-years-old.

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

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

	There are requirements for periodic dental 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 dental exam. 

	We modified that rule for 3-year-olds.  Safe Measures does not count
	cases for 3-yr-olds as out of compliance until the child reaches the
	4th b'day without a dental exam.  The original programming for the
	static report counted cases with 3-yr-olds as out of compliance as
	soon as they turned 3 for every quarter in which they were 3 and didn't
	have a dental exam. After consulting with Debbie Williams
	and the case compliance review procedures (which are still used as a 
	de facto guide) we decided to use a middle ground.  This program gives
	120 days after the child's 3rd birthday to get a dental check-up.  
*/

/* Revision History
 * 2010.08.24 M. Armijo. Extended ages to include 19 & 20.
 * 2011.10.5 Lois VanBeers added summation by agency
 * 2012.05.18 M. Armijo. Made correction to mapping of P_ETHNCTYC to derived var ETHNIC.
 * 2015.12.01 J. Magruder. Modified to accomodate revisions to companion program 5B_medical.sas.
 * 2017.04.11 J. Magruder. Modified to adjust to new EPSDT requirement of referral for dental
				exams at 6 month intervals - see ACL 17-22.
 * 2017.06.09 J. Magruder. Modified to adjust to corrected EPSDT requirement of referarl for
                dental exams at 1-year intervals.
 */

%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\Dental SQLCounts';
libname pstore '\\Cdssapp05\dss\RAD\Pstore\pstore_lvanbeer\Measure 5B';
libname cws   "\\Cdssapp05\dss\rad\CWSCMS_Data\UCB_Source\&q." ;

/* Submit remotely */
%mend CDSS_ENV ;

options mprint nosource ;
%let ReportStart = '01jan1998'd;

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


/* Get all dental services.
	We in CWDAB decided to use the Delivered Service Table via
	the Case Delivered Service Table because we wanted the
	services for the child in the case.  Safe Measures has
	a higher compliance rate because they use the Individual 
	Delivered Service table.  We decided not to use that method
	because additional services are found for a child that 
	way when the child changes cases.  Each child should, however,
	when starting a new case, be given a new dental and 
	medical check-up.  Our method would screen for those new
	check-ups; the methods of Safe Measures wouldn't.  */        
proc sql;
 create table pstore.DentalServices 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 = 1768 or SVC_CNTC = 3239 ) and
    DelService.status_cd = 'C' 
 order by fkcase_t, ServiceDate
;                       

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 ;

 
/* get the placements that are open at the end of each quarter	*/
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
/* Revision of 12.01.15 */
           /* and LegalDate LE &EndQrtr */
	 order by clientID, EpiStart, OhpStart, AssignStart /*, LegalDate */ ; 
/* End revision */
quit;



/* Get the last placement for each child.
	Select the last county assignment and last legal authority for each placement. 
	 Delete non-dependent legal guardians.
	 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;

/* Revision of 12.01.15 */

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

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

/* Get dental 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.DentalServices services
 on
	QtrPlacements1.CaseID = services.fkcase_t and
	ServiceDate LE &EndQrtr 
 order by ClientID, ServiceDate ;
quit;


/* 	Get last  dental exam for each child. */
	/*revised 2017.06.08 to conform to requirement that dental exams be
	  done once a year by asking: "If the child is (e.g.) 7 and has been in
	  care, did she have an exam when 6 or 7?" This is consistent with the
	  way the measure has been done in the past. A tighter reading would be to
	  ask if the child had had a dental exam in the year before the end of 
	  quarter date. Note - initial revision was to 6-month intervals, but
      CDSS learned that 6 months was only recommendation and yearly was
      actual requirement*/
Data QrtrService  ;
	set AddServices;
		by ClientID ServiceDate ;
		if last.ClientID ;
/* LVB  - I changed the calculation of the age to the standard the 
	Bureau is using.  But I didn't change the calculations for
	the dental categories and dental ages because I didn't want to 
	change the programming that much when it had been vetted by
	all the county reps.                    */
	AGE = floor ( (intck('month',BIRTH_DT,&EndQrtr)-(day(&EndQrtr) 
			lt day(BIRTH_DT) ) ) / 12);  
	if Age GE 1;   /* Count only those children age 1 or older */
  	DAGE = floor ( (intck('month',BIRTH_DT,Servicedate)-(day(servicedate) 
			lt day(BIRTH_DT) ) ) / 12); 	
 DentalCompliance = 0;
 /*dental exams required within the first 30 days of placement, thus
   if in care less than 30 days at the end of the quarter, child with
   no exam is in compliance - JM 2017.06.09*/
 If &endqrtr - epistart le 30 then dentalcompliance = 1;
 else if servicedate = . then do;
/* the following statement allows 120 days from the child's 1st
		birthday (365 + 120 = 485) for a dental exam before the 
		case is counted as out of compliance. [Source of this rule is
        not clear - not in ACL - but retained - JM 2017.06.09]*/
	if ( (&EndQrtr - birth_dt) LE 485 ) then DentalCompliance = 1;
	/*otherwise, out of compliance*/
	else dentalcompliance = 0;
	end;
 else if ServiceDate NE . and (age - dage le 1) then dentalcompliance = 1;
 /*default leaves dentalcompliance = 0*/

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

  /* Assign UCB-defined categories */
   if AGE eq .  then AGE=99 ;
   if AGE lt 0  then AGE=99 ;
   if AGE GE 21  then AGE=99 ;  
 
 * 2011.10.5 Lois VanBeers added agency code below;
      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 */

   format PLACEMENT_TYPE 4.1; /*ADDED 02-02-22 BY GYS*/

	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  ;  
run;




/* Create group counts */
/* First the state counts */
 * 2011.10.5 Lois VanBeers added agency code to the SQL below;
proc sql;
   Create table statecounts as
   select  
      	 PLACEMENT_TYPE,
		 DentalCompliance,
         Age,
         ethnic,
		 agency_cd,
         gender,   
         count(*) as count  
   from QrtrService
   group by  PLACEMENT_TYPE, DentalCompliance, Age, agency_cd, ethnic, gender 
   ;
quit;

/* 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 */
 * 2011.10.5 Lois VanBeers added agency code to the SQL below;
proc sql;
   Create table countyCounts as
   select County,   
      	 PLACEMENT_TYPE,
		 DentalCompliance,
         Age,
         ethnic,
		 agency_cd,
         gender,   

         count(*) as count  
   from QrtrService 
   group by County, PLACEMENT_TYPE, DentalCompliance, Age, ethnic, 
			agency_cd, gender ;
quit;

/* 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;
	FiveBDental_D = count;
	if Dentalcompliance = 1 then FiveBDental_N = count;
	rename 	gender = gender_cd ;
run;

 * 2011.10.5 Lois VanBeers added agency_cd to the SQL below;
proc sql;
	create table Summary1 as
	select sum(FiveBDental_N) as Freq_N,
			sum(FiveBDental_D) as Freq_D,
			period_dt,
			County,   
      		Placement_Type,
		 	DentalCompliance,
         	Age,
         	ethnic,
			agency_cd,
         	gender_cd
	from CountyState
	group by period_dt,	County, Placement_Type, DentalCompliance, Age, 
				 ethnic, agency_cd, gender_cd ;
quit;

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

 * 2011.10.5 Lois VanBeers added agency_cd to the SQL below;
proc sql;
	create table summ.cdss_5BDental 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 DentalCOMP_N length=4 label="DentalCompliance" ,
		FREQ_D as DentalCOMP_D length=4 label="Placements"
	from summary2
	order by PERIOD_DT, CNTY_SPFCD
;
create index PERIOD_DT on summ.cdss_5BDental(PERIOD_DT) ;
create index CNTY_SPFCD on summ.cdss_5BDental(CNTY_SPFCD) ;