/* md_case_svc_comp.sas */
/* Construct the multi-dimensional report data for case-service-components,
 * for the specified points-in-time. The beginining year, ending year and ending
 * quarter are provided to the macro call statement at the end of file.
 * Dimesions:
 *    Agency (RSP_AGY_CD)   
 *    Point-in-time
 *    County
 *    Age
 *    Gender
 *    Ethnic group
 *    Service component type
 *    Voluntary Status
 *    TBD (Post-Placement Status)
 * Required Input:
 * CWSCMS source tables, UCB_CASE_SVC_COMP and UCB_ASSIGNMENT_CASE derived tables.
 * Programmer: M. Armijo 
 * Revision History: 
 * 2008.02.13 First Revision.
 * 2008.03.20 Revised unduplication method for open case components:
 * where multiple service components are open for a single point-in-time,
 * select based on the service type priority (PP=1, FR=2, FM=3, ER=4) alone,
 * rather than on START_DT followed by service type priority.
 * 2008.04.04 Expanded list of office numbers to exclude from LA County to include:
 * S0246 S0397 S0266. Also deleting records where office number is null for LA county only.
 * 2008.04.22 Changed the unduplication from case-level to person-level.
 * Discovered that a small number of clients had multiple cases open during a given
 * point-in-time. These clients are now counted once only.
 * 2009.01.06 Changed the age at point-in-time computation to the new standard.
 * Does NOT use the AGE macro (in-line).
 * Changed the encoding for MISSING for CNTY_SPFCD from '99' to '98'.
 * 2010.09.21 Added a dimension for RSP_AGY_CD.
 * With the addition of RSP_AGY_CD, we no longer have to delete certain 
 * office numbers, used in LA county to track non-CW cases.
 * 2012.04.16 Modified for REL 6.6 change: new SERVICE COMPONENT TYPE 'ST'
 * (Supportive Transition). 
 * 2013.12.03 modified to conform to mofified UCB_CASE_SVC_COMP file j magruder;
 * 2014.01.07 modified sort order to take last case type on pit date j magruder;
 * 2022.12.10 modified to include ICWA and tribal status j magruder;
 * 2023.05.01 modified to use CSC_ORDER to correctly order when 2 CSCs start
   on same date (1 usually ends on same date) j magruder;
 */

options MPRINT SYMBOLGEN NOSOURCE ;

/* Make sure that we are not appending to an existing DB */
proc sql ;
drop table local.MD_CASE_SVC_COMP
;

/* create views to improve code organization (among other things). */
data CSC_VIEW /view=CSC_VIEW ;
set DWH.UCB_CASE_SVC_COMP(rename=(GENDER_CD=GTEMP ETHNIC=ETEMP))  ;
*CSC_END_DT=END_DT ;
*CSC_START_DT=EFFECTV_DT ;
if CSC_E_DT eq . then CSC_E_DT='31dec3000'd ;
select(SRV_CMPT) ;
   when('ST') SRV_CMP=5 ;
   when('ER') SRV_CMP=4 ;
   when('FM') SRV_CMP=3 ;
   when('FR') SRV_CMP=2 ;
   when('PP') SRV_CMP=1 ;
   otherwise ;
end;
run ;

data ASG_VIEW /view=ASG_VIEW ;
set DWH.UCB_ASSIGNMENT_CASE ;
if END_DT eq . then END_DT='31dec3000'd ;
run ;

data CSVOL_VIEW /view=CSVOL_VIEW ;
set CWS.CSVOL_ST ;
if END_DT = . then END_DT='31dec3000'd ;
run ;

/********************************/
/* macro definition begins here */
/********************************/
%macro ETL(STARTYEAR, ENDYEAR, ENDQTR) ;
%do SYEAR = &STARTYEAR %to &ENDYEAR ; 
%do SQTR = 1 %to 4 ; 

/* The point-in-time is the first day of the specified YYQ.
 * We are able to compute one additional PIT for the last
 * YYQ (end of extract YYQ plus one day, e.g. YYQ+1).
 */
data _null_ ;
call symput('PIT',yyq(&SYEAR,&SQTR)) ;
run ;

/* Restrict UCB_CASE_SVC_COMP to the specified point-in-time. */
/*modified to sort on last case service component based on end date j magruder 2013.12.03*/
/*modified to exclude case service components associated with closed cases and to sort on 
  last case service component based on start date rather than end date j magruder 2015.01.19*/

proc sql ;
create table PIT_CSC1 as
select *
from CSC_VIEW
where &PIT between CSC_S_DT and CSC_E_DT
    and (&PIT le CASE_E_DT or CASE_E_DT = .) 
order by FKCASE_T, CSC_ORDER  
/*modify to use CSC_ORDER to order CSC spells starting on same date j magruder 2023.05.01*/
;
quit ;

/* case-level unduplication */
/* if more than one service program opened on a specific date, take the
   last service program opened*/
data PIT_CSC2 ;
set PIT_CSC1 ;
by FKCASE_T CSC_ORDER ; 
if last.FKCASE_T then output ;
run ;

/* Restrict UCB_ASSIGNMENT_CASE to the specified point-in-time. */
/* Note - the assignment case file includes a large number of assignments on 
   cases that are no longer open - > 110,000 for cases open in 2013 j magruder 2013.12.03*/

proc sql ;
create table PIT_ASG1 as
select *
from ASG_VIEW
where &PIT between START_DT and END_DT
order by FKCASE_T, START_DT ;
;
quit ;

/* case-level unduplication */
data PIT_ASG2 ;
set PIT_ASG1 ;
by FKCASE_T START_DT ;
if last.FKCASE_T then output ;
run ;

/* Retrieve County and Office Number from assignment-level table.
 * Drop NON-CW at the case level.
 */
proc sql ;
create table CASE_SVC_COMP1 as
select
   A.*
  ,B.CNTY_SPFCD
from PIT_CSC2 A, PIT_ASG2 B
where A.FKCASE_T=B.FKCASE_T    
;


/*********************************************************/
/* Add voluntary status to the service component record. */
/*********************************************************/

/* Restrict CASE_VOLUNTARY_STATUS to the specified point-in-time. */
proc sql ;
create table PIT_CSVOL1 as
select *
from CSVOL_VIEW
where &PIT between START_DT and END_DT
order by FKCASE_T, START_DT
;

/* case-level unduplication */
data PIT_CSVOL2 ;
set PIT_CSVOL1 ;
by FKCASE_T START_DT ;
if last.FKCASE_T then output ;
run ;

proc sql ;
drop table PIT_CSVOL1 ;

/* Retrieve VOLUNTARY_IND for the point-in-time */
create table CASE_SVC_COMP2 as
select
   A.*
  ,B.VLNTRY_IND
from CASE_SVC_COMP1 A left join PIT_CSVOL2 B
on A.FKCASE_T=B.FKCASE_T
;
drop table PIT_CSVOL2 ;

/*************************************************************************/
/* Partition service component type of FM into 2 subtypes:               */
/*   Post-Placement (prior serice periods of PP or FR for the case)      */
/*   Pre-Placement (no prior service periods of PP or FR for the case)   */
/*************************************************************************/

/* Retrieve prior service component history for cases which have
 * a service component type FM for the current point in time. 
 */

proc sql ;
create table FM_CASEHIST as 
select UCB_CASE_SVC_COMP.*
from DWH.UCB_CASE_SVC_COMP
where FKCASE_T in(
   select FKCASE_T
   from CASE_SVC_COMP2     
   where SRV_CMPT eq 'FM'
)
and
CSC_S_DT le &PIT
order by FKCASE_T, CSC_S_DT descending 
;

/* Assign a new component type:
 * AP for Post-Placement (previous case-level FR or PP)
 * BP for Pre-Placement (no previous case-level FR or PP)
 */

data FM_CLASSIFY(keep=FKCASE_T SRV_CMPT) ; 
retain FKCASE_T S1-S20 ;
array SCOMP(*) $ S1-S20 ;
set FM_CASEHIST ;
by FKCASE_T ;
if first.FKCASE_T then do ;
   i=1 ;
   do j=1 to 20 ;
      SCOMP(j)=' ' ;
   end ;
end ;
SCOMP(i)=SRV_CMPT ;
if last.FKCASE_T then do ;
   do j=2 to 20 ;
      if SCOMP(j) in('FR','PP') then do ; 
         /* cassify as post-placement */
         SRV_CMPT='AP' ;
         goto endloop ;
      end ;
   end ;
   /* no prior FR or PP. classify as pre-placement */
   SRV_CMPT='BP' ;
   endloop: 
   output ;
end ;
i+1 ;
run ;

/* update database with new component type value. */
data CASE_SVC_COMP3 ;
   update CASE_SVC_COMP2 FM_CLASSIFY ;
   by FKCASE_T ;
run ; 
 
/********************************************************/
/* Age at point-in-time and data encoding changes here. */
/********************************************************/
/*need to add ICWA and Tribal membership dummy codes at this point*/
data CASE_SVC_COMP4 ;
set CASE_SVC_COMP3 ;

/* compute age in years */
/* (superceded) AGE=int( (&PIT-BIRTH_DT)/365.25) ; */
/* New method */

AGE=floor((intck('month',BIRTH_DT,&PIT)-(day(&PIT) lt day(BIRTH_DT)))/12); 
if AGE eq .  then AGE=99 ;
if AGE lt 0  then AGE=99 ;
if AGE ge 22 then AGE=99 ;

select(GTEMP) ;
   when('F') GENDER_CD=1 ;
   when('M') GENDER_CD=2 ;
   when('I') GENDER_CD=3 ;
   otherwise GENDER_CD=99 ;
end ;

select(ETEMP) ;
   when('1') ETHNIC=1 ;
   when('2') ETHNIC=2 ;
   when('3') ETHNIC=3 ;
   when('4') ETHNIC=4 ;
   when('5') ETHNIC=5 ;
   otherwise ETHNIC=99 ;
end ;

select(VLNTRY_IND) ;
   when('N') VOLUNTARY_STATUS=1 ;
   when('Y') VOLUNTARY_STATUS=2 ;
   otherwise VOLUNTARY_STATUS=99 ;
end ;

select(SRV_CMPT) ;
   when('ER') SRV_CMP=1 ;
   when('BP') SRV_CMP=2 ;
   when('AP') SRV_CMP=3 ;
   when('FR') SRV_CMP=4 ;
   when('PP') SRV_CMP=5 ;
   when('ST') SRV_CMP=6 ;
   otherwise SRV_CMP=99 ;
end ;

/*Add ICWA and Tribal status codes 2022.12.12 j magruder*/
select (ICWA_ELGCD) ;
   when ("Y") ICWA = 1;  /*ICWA Eligible*/
   when ("N") ICWA = 2;	/*ICWA Not Eligible*/
   when ("P") ICWA = 3;  /*ICWA Status Pending*/
   otherwise ICWA = 99; /*ICWA Not asked, unknown, mssing*/
end;

select (INDN_STC) ;
   when ("1212") TRIBAL_STC = 1; /*Tribal Member of at least one tribe*/
   when ("1211") TRIBAL_STC = 2; /*Eligible for Tribal Membership but not member of any tribe*/
   when ("1214") TRIBAL_STC = 3; /*Neither of above but Pending Verification*/
   when ("1210") TRIBAL_STC = 4; /*None of above but Claims Tribal membership*/
   when ("1213") TRIBAL_STC = 5; /*none of above and 1+ tribes found ineligible for membership*/
   when ("6532") TRIBAL_STC = 6; /*None of above and no tribe has responded after 60 days*/ 
   otherwise TRIBAL_STC = 99;     /*No Tribal Membership data available*/
end;

if CNTY_SPFCD eq '99' then CNTY_SPFCD='98' ;
run;
/*
proc format;
	value icwa
	1 = "ICWA Eligible"
	2 = "ICWA Not Eligible"
	3 = "ICWA Status Pending"
	99 = "No ICWA Status Data";
	value Tribal
	1 = "Tribal Member"
	2 = "Eligible for Tribal Membership"
	3 = "Pending verification of Tribal Status"
	4 = "Claims Tribal Membership"
	5 = "Not Eligible for Tribal Membership"
	6 = "No Tribal Response after 60 days"
	99 = "No Tribal Status Data";
	run;
*/

/***********************************/
/* Generate the state total counts */ 
/***********************************/
proc sql ;
drop table CASE_SVC_COMP2 ;
drop table CASE_SVC_COMP3 ;
create table MD_STATE1 as
select
   count(*) as COUNT
  ,AGE
  ,GENDER_CD
  ,ETHNIC
  ,SRV_CMP 
  ,VOLUNTARY_STATUS
  ,RSP_AGY_CD
  ,ICWA
  ,TRIBAL_STC
from CASE_SVC_COMP4
group by
   AGE
  ,GENDER_CD
  ,ETHNIC
  ,SRV_CMP 
  ,VOLUNTARY_STATUS
  ,RSP_AGY_CD
  ,ICWA
  ,TRIBAL_STC
;

/* Add values unique to this pass */
data MD_STATE2 ; 
length 
RSP_AGY_CD $1 CNTY_SPFCD $2
AGE GENDER_CD ETHNIC PERIOD_DT COUNT 4 ;
set MD_STATE1 ;
PERIOD_DT=&PIT ;
CNTY_SPFCD='00' ;
run ;
 
/* Add single PIT database to all-PIT database */
proc datasets NOLIST ;
append base=local.MD_CASE_SVC_COMP data=MD_STATE2 ;
delete MD_STATE1 MD_STATE2 ;
run ;

/************************************/
/* Generate the county total counts */ 
/************************************/
proc sql ;
create table MD_CNTY1 as
select
   count(*) as COUNT
  ,CNTY_SPFCD
  ,AGE
  ,GENDER_CD
  ,ETHNIC
  ,SRV_CMP
  ,VOLUNTARY_STATUS
  ,RSP_AGY_CD
  ,ICWA
  ,TRIBAL_STC
from CASE_SVC_COMP4
group by
   CNTY_SPFCD
  ,AGE
  ,GENDER_CD
  ,ETHNIC
  ,SRV_CMP
  ,VOLUNTARY_STATUS
  ,RSP_AGY_CD
  ,ICWA
  ,TRIBAL_STC
;

/* Add values unique to this pass */
data MD_CNTY2 ;  
length 
RSP_AGY_CD $1 CNTY_SPFCD $2
AGE GENDER_CD ETHNIC PERIOD_DT COUNT 4 ;
set MD_CNTY1 ;
PERIOD_DT=&PIT ;   
run ;

/* Add single PIT database to master database */
proc datasets NOLIST ;
append base=local.MD_CASE_SVC_COMP data=MD_CNTY2 ;
delete MD_CNTY1 MD_CNTY2 CASE_SVC_COMP1 ;
run ;

%if &SYEAR=&ENDYEAR and &SQTR=&ENDQTR %then %goto finished;
%end ;
%end ;
%finished: %mend ETL ;
/*******************************/
/* macro definition ends here. */
/*******************************/
/* note that we can process Q+1 for the current extract Q */
%ETL(1998,2025,1) ;