/* 2B_idx.sas */
/* Progammer: M. Armijo
 * 
 * Purpose: Using the output generated by program 2B, transform the data
 * so that there are 4 explicit frequency variables:
 * IMMED_RESPONSE_PLANNED
 * IMMED_RESPONSE_ACTUAL
 * DAY10_RESPONSE_PLANNED
 * DAY10_RESPONSE_ACTUAL
 * rather than a single frequency variable, with 4 type-indicator variables.
 * The resulting database is more compact and easier to code queries for.
 * Revision 2008.09.04 First revision.
 * Revision 2008.10.03 changed null count values to zero.
 * Revision 2011.07.12 Convert instances of '99' to '98' for county.
 * Revision 2011.09.28 Lois VanBeers added agency_cd and an index for 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" ;


data CDSS_2B ;
set LOCAL.STATE LOCAL.COUNTIES ;
/* Convert instances of '99' to '98' for county. */
if supcounty = '99' then supcounty='98' ;
run ;

/* transform data structure for report program... */
%let DIMENSIONS=agency_cd,period_dt,supcounty,age,ethnic,gender_cd ;

proc sql ;
create table IMMED_RESPONSE_PLANNED as
select sum(COUNT) as IMMED_RESPONSE_PLANNED,
&DIMENSIONS
from CDSS_2B
where PIM eq 1
group by &DIMENSIONS
;
create table IMMED_RESPONSE_ACTUAL as
select sum(COUNT) as IMMED_RESPONSE_ACTUAL,
&DIMENSIONS
from CDSS_2B
where IMCNT eq 1
group by &DIMENSIONS
;
create table DAY10_RESPONSE_PLANNED as
select sum(COUNT) as DAY10_RESPONSE_PLANNED,
&DIMENSIONS
from CDSS_2B
where PTEN eq 1
group by &DIMENSIONS
;
create table DAY10_RESPONSE_ACTUAL as
select sum(COUNT) as DAY10_RESPONSE_ACTUAL,
&DIMENSIONS
from CDSS_2B
where TENCNT eq 1
group by &DIMENSIONS
;

data CDSS_2BX ;
merge
   IMMED_RESPONSE_PLANNED
   IMMED_RESPONSE_ACTUAL
   DAY10_RESPONSE_PLANNED
   DAY10_RESPONSE_ACTUAL ;
by agency_cd period_dt supcounty age ethnic gender_cd ;

run ;

/* misc data "fixes" */
data CDSS_2BX ;
set CDSS_2BX ; 
/* Any missing counts generated in the previous step are set to zero. */
if IMMED_RESPONSE_PLANNED = . then IMMED_RESPONSE_PLANNED=0 ;
if IMMED_RESPONSE_ACTUAL  = . then IMMED_RESPONSE_ACTUAL=0 ;
if DAY10_RESPONSE_PLANNED = . then DAY10_RESPONSE_PLANNED=0 ;
if DAY10_RESPONSE_ACTUAL  = . then DAY10_RESPONSE_ACTUAL=0 ;

run ;

proc sql ;
create table CFSR.CDSS_2BX as
select
 agency_cd as AGENCY_CD length=4 label="Responsible Agency"
,period_dt as PERIOD_DT length=4 label="Period"  
,supcounty 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"
,IMMED_RESPONSE_PLANNED length=4 label="ImmediateResponse(Planned)"
,IMMED_RESPONSE_ACTUAL  length=4 label="ImmediateResponse(Timely)"
,DAY10_RESPONSE_PLANNED length=4 label="10-DayResponse(Planned)"
,DAY10_RESPONSE_ACTUAL  length=4 label="10-DayResponse(Timely)"
from CDSS_2BX
order by PERIOD_DT, CNTY_SPFCD
;
create index PERIOD_DT on CFSR.CDSS_2BX(PERIOD_DT) ;
create index CNTY_SPFCD on CFSR.CDSS_2BX(CNTY_SPFCD) ;

drop table local.state ;
drop table local.counties ;

data Test.CDSS_2BX;
set CFSR.CDSS_2BX;
run;

proc sql;

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