/* code version 2011.11.04 */
/****************************************************************************
Program: Psych_Meds_5F.sas, test revision from program "Psych Meds_Only After
Placement- Revision 10-17-07". 
Purpose: CWS Outcomes measure 5F: Psychotropic Drug Authorization.   
Revisions: 
VLS- 5/28/08, updated file/libnames for testing. Removed unnecessary 
confidential data from placement selection step. Added steps to undup place-
ment and case assignment. Added exclusions for NDLG cases, incoming ICPC,
age 19 and older, and LA offices. Added step for finding responsible county
when no current assignment. Final population with psychnum numerator saved 
in psych5f file.
VLS- 7/17/08, Added revised code for tables by summary, placement, age, 
ethnicity, and gender.
KGZ- 02/2009, updated code to Release 6.1. (See modification of meds table below.) 
Lois VanBeers - June 2009, converted to dynamic.  
	Added placement type to dynamic. Added non-fc, pre-adoptive,
	runaway, trial home visits, and 'other' placements.
	Deleted legal non-dep guardians first rather than at the end.
	Changed exclusion of LA offices to more standard code.  
	Used episode county instead of state id county as the secondary choice.
	(Assignment table county is first choice.)
VLS- 2/25/11, replaced LA office exclusion with case responsible agency code = 'C' (Child Welfare supervised). 	
Lois VanBeers - Oct 11, 2011, added agency as summation variable and 
	deleted selection for  welfare-only placements.
	Deleted non-foster care.
2012.05.18 M. Armijo. Made correction to mapping of P_ETHNCTYC to derived var ETHNIC.
*****************************************************************************/

/* Modified by M. Armijo for running in UCB production environment.
 */

%macro CDSS_ENV ;
/*LOGON TO SERVER AND ESTABLISH LIBRARIES USED IN ANALYSIS*/;
%let dapb=162.2.111.31;
 options comamid=tcp remote=dapb;
 signon noscript;
libname R_WORK slibref=WORK server=DAPB;

/* submit locally and remotely */
%let q = 2011\q2;
%let reportstrt = '01jan2011'd;  
%let reportend = '30jun2011'd;

libname cws    "\\Cdssapp05\DSS\RAD\CWSCMS_Data\ucb_source\&q";
libname pstore '\\cdssapp05\dss\RAD\Pstore\pstore_lvanbeer\measure 5F';
libname Summ '\\cdssapp05\dss\RAD\Pstore\pstore_lvanbeer\measure 5F\SQLCounts';
%mend CDSS_ENV ;


/*** NEED TO CHANGE ***/
%let reportstrt = '01JAN1998'd;  
%let reportend = '31DEC2024'd;
libname cws "/wss1/SAS/SASDATA/CWS_CMS/Q4_2024/SOURCE" ;
libname dwh "/ssa4/SAS/SASDATA/CWS_CMS/Q4_2024" ;

/*** END CHANGE SECTION ***/



libname pstore "." ;
libname Summ "." ;


/*revised to 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*/
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 > &reportstrt;
    quit;

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


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)  
		/*	AND
		case.rsp_agy_cd = 'C'  */
;





/* Get all psychotropic records. */ 
proc sql;
	create table pstore.meds as
	select	a.fkclient_t as ClientCondID,
			b.END_DT as RxEndDate,  
			b.start_DT as RxStartDate, 
			c.CONSENT_DT  /* parental or court consent date */
	from cws.cl_condt as a,
		cws.medicat as b,
		cws.medchist as c   /* MEDICATION_CONSENT_HISTORY  */
	where a.identifier = b.fkcl_condt and
		b.identifier = c.fkmedicat and
		b.PSYTRP_IND = 'Y' 	/* psychotropic meds indicator is yes */	
;

/* drop the old tables so the new one can replace it and be added to */

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

/*	get last ohp and last county assignment for the quarter for each child.  
	Drop children 19 or older by end of quarter.      */
data QtrPlacements2 (drop = PlaceChangeReason ) ;
	set QtrPlacements (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;
	
   /* Find those children with an Open Episode but closed OHP  (limbo)*/
	if ohpEnd NE . and ohpEnd LT &BeginQrtr 
		then do;
			if PlaceChangeReason = 1431	then PlaceType = 1200 ; /* runaway */
			else if PlaceChangeReason = 1440	then PlaceType = 1300 ; /* trial home visit */
			else PlaceType = 1600 ;    /* other */
		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.);
	
   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;   
      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(1200,7181) PLACEMENT_TYPE = 17 ; 	/* runaway */
		when(1300) PLACEMENT_TYPE = 18 ; 	/* trial home visit */
		when(0,.) PLACEMENT_TYPE = 99 ; 	/* Missing */
               	       otherwise PLACEMENT_TYPE = 29;   /* Other */
	  end ;   
	
	if TransHousing = 'Y' then  PLACEMENT_TYPE = 20;  /* transitional housing */
	
	
	drop birth_dt agency gender_cd ethnic_cd AssignCounty EpiCounty;
run;

/* Now get the numerators for the quarter.
	Methodology states that client is to be counted as on Psychotropics if the 
	Rx start and end dates overlap the quarter, not the placement.*/
proc sql;
	create table QtrPlacementMeds as
		select * 
		from QtrPlacements2,
			pstore.Meds as meds
		where QtrPlacements2.ClientID = meds.ClientCondID and
			CONSENT_DT LE &EndQrtr and
			(	RxStartDate LE &EndQrtr or 
				RxStartDate = . ) and
			(  RxEndDate GE &BeginQrtr or
			   RxEndDate = . )
		/* Rx start and end dates are optional in CWS/CMS  */
order by clientID, OhpStart, CONSENT_DT, RxEndDate; 
quit;

data QtrPlacementMeds2  ;
	set QtrPlacementMeds (drop = ClientCondID);
		by clientID ;
		if last.clientID;
run;

/* Create 1 ds with all placements for the quarter with
	a Meds indicator (1 - Psychotropic Rx ; 0 - no Psychotropic RX ) */
data QtrPlacementsWInd ;
	merge QtrPlacementMeds2  (in = inMeds)
		QtrPlacements2 (in = inPlace)   ;
			by clientID ;
			if inPlace and inMeds  then Meds = 1;
			if inplace and not inMeds then Meds = 0 ;
run;


/********************************************************************************
/* Create group counts */
/* First the state counts */
proc sql;
   Create table statecounts as
   select  
      	 PLACEMENT_TYPE,
		 Meds,
         Age,
         ethnic,
		 agency_cd,
         gender,   
         count(*) as count  
   from QtrPlacementsWInd
   group by  PLACEMENT_TYPE, Meds, Age, agency_cd, ethnic, 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,
		 Meds,
         Age,
		 agency_cd,
         ethnic,
         gender,   

         count(*) as count  
   from QtrPlacementsWInd
   group by County, PLACEMENT_TYPE, Meds, 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 QtrPlacementsWInd countycounts counties;
run;

%if &SYEAR=&STOP and &SQTR=&EQTR %then %goto finished;
%end ;
%end ;
%finished: %mend Quarter;
/*** NEED TO CHANGE ***/
%Quarter(1998,2024,4) ;
/*** END CHANGE SECTION ***/


/* create one data set with county and state sums */
data CountyState;
	set summ.counties
		summ.state;
	FiveF_D = count;
	if meds = 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,
		 	Meds,
         	Age,
			agency_cd,
         	ethnic,
         	gender_cd
	from CountyState
	group by period_dt,	County, Placement_Type, Meds, Age, agency_cd, ethnic, gender_cd
;

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


/* 5F_idx.sas */
/* Purpose:
 * Generate the production report database with indexing on 
 * appropriate columns.
 * Programmer: M. Armijo
 * Revision History:
 * 2010.06.01 changed encoding of County to conform to standard for CSSR report data.
 * 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 summary2 ;
set TEMP.summary2 ;
if county eq '99' then county='98' ;
run ;

proc sql;
create table CFSR.cdss_5F 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 MEDS_N length=4 label="PlacementsWMeds"
,FREQ_D as MEDS_D length=4 label="Placements"
from summary2
order by PERIOD_DT, AGENCY_CD, CNTY_SPFCD
;

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

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

data Test.CDSS_5F;
set CFSR.CDSS_5F;
run;

proc sql;

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