***************************************************************************;
**                                                                       **;
** Copyright (c) 2006 Center for Social Services Research,               **;
** University of California at Berkeley. All rights reserved.            **;
** Center for Social Services Research - Univ of California at Berkeley  **;
** CWS/CMS Reporting:  Allegations, Substantiated Allegations,           **;
**    Entries, and In Care Rates                                  	 **;
**                                                                       **;
**  BY: Sean Lee                                                         **;
**                                                                       **;
**                                                                       **;
***************************************************************************;

options nodate nonumber macrogen mprint missing='.';

data fe oe entries(drop=undup); 
   set ent.entries (keep= agency period_dt cnty county ethnic age gender_cd count2 entry flag_8 undup rename=(count2=count)) ;
   where UNDUP = 1 and qtr(period_dt)=1 and 
         0 <= age <=17 and
         2000 le year(period_dt) le 2024 and
         (COUNTY <= 1900 or COUNTY >= 2000);

   if age = 0.5 then age = 0;
   if flag_8=1 then tic_8plus=count;
   else if flag_8=2 then tic_lt8=count ;
   if entry=1 then output fe;
   else if entry=2 then output oe;
   if ethnic = 99 then ethnic = 7;
   output entries ;
run;

data referrals(drop=county)
     invrefs(drop=county)
     subrefs(drop=county); 
   set ref.referrals (keep= period_dt cnty county ethnic age gender_cd count alg_dspc investigate rsp_agy_cd) ;
   where qtr(period_dt)=1 and 
         2000 le year(period_dt) le 2024  and 
         (COUNTY <= 1900 or COUNTY >= 2000) and
         0 <= age <= 17 and
         RSP_AGY_CD = 'C';
   agency=4;
   if alg_dspc=1 then output subrefs;
   if investigate=1 then output invrefs;
   if ethnic = 99 then ethnic = 7;
   output referrals;
run;

data subrefs;
set subrefs;

if ethnic = 99 then ethnic = 7;
run;

data invrefs;
set invrefs;

if ethnic = 99 then ethnic = 7;
run;

data pit; 
   set pit.pit     (keep= period_dt county ethnic age gender_cd count agency) ;
   where qtr(period_dt)=3 and 
         2000 le year(period_dt) le 2024 and
        (COUNTY le 1900 or COUNTY ge 2000);
   period_dt=yyq(year(period_dt),1);
   if ethnic = 99 then ethnic = 7;
CNTY = COUNTY/100;
run;

*** Summarize the datasets ***;

proc summary data=fe nway;
   class period_dt agency cnty ethnic age gender_cd ;
   var count tic_8plus tic_lt8 ;
   output out=fe2 sum=; 
run;

proc summary data=oe nway;
   class period_dt agency cnty ethnic age gender_cd ;
   var count tic_8plus tic_lt8 ;
   output out=oe2 sum=;
run;

proc summary data=entries nway;
   class period_dt agency cnty ethnic age gender_cd ;
   var count tic_8plus tic_lt8 ;
   output out=entries2 sum=; 
run;

proc summary data=referrals nway;
   class period_dt agency cnty ethnic age gender_cd ;
   var count ;
   output out=ref2 sum=; 
run;

proc summary data=invrefs nway;
   class period_dt agency cnty ethnic age gender_cd ;
   var count ;
   output out=invrefs2 sum=;
run;

proc summary data=subrefs nway;
   class period_dt agency cnty ethnic age gender_cd ;
   var count ;
   output out=subrefs2 sum=; 
run;

proc summary data=pit nway;
   class period_dt agency cnty ethnic age gender_cd ;
   var count ;
   output out=pit2 sum=; 
run;

%macro summdof ;

%do year=2000 %to 2024;

proc sort data=dof.upd_fin_&year. out=dof&year.; 
   where 1 le county le 58; by county age gender; 
run;

data dof&year.;
   set dof&year.;
   if 0 <= age <=17;
run;

proc summary data=dof&year. nway;
   class county age gender ;
   var black white hispanic asian pac_isl native total;
   output out=summ&year. sum=;
run;

data vert_dof&year.(rename=(county=cnty race=ethnic ));
   set summ&year.;
   year=&year.;

   if county = 59 then delete;

   if gender=:'F' then gender_cd=1;
   else if gender=:'M' then gender_cd=2;

   period_dt=yyq(year,1) ;

   total_dof=total;
   if black    not in (.,0) then do; race=1; dof=black;    alloth=total_dof-black    ; output; end;
   if white    not in (.,0) then do; race=2; dof=white;    alloth=total_dof-white    ; output; end;
   if hispanic not in (.,0) then do; race=3; dof=hispanic; alloth=total_dof-hispanic ; output; end;
   if asian    not in (.,0) then do; race=4; dof=asian;    alloth=total_dof-asian    ; output; end;
   if pac_isl  not in (.,0) then do; race=4; dof=pac_isl;  alloth=total_dof-pac_isl  ; output; end;
   if native   not in (.,0) then do; race=5; dof=native;   alloth=total_dof-native   ; output; end;
   if total-sum(black,white, native, asian, pac_isl, hispanic) ne 0 then do;
                                     race=7; dof=total - sum(black,white, native, asian, pac_isl, hispanic);
                                     alloth=total_dof-sum(black,white, native, asian, pac_isl, hispanic) ;
                                     output;
   end;

   keep year period_dt county gender_cd race dof alloth age;
run;

data temp&year ;
set vert_dof&year (drop=CNTY);

CNTY=0;

run;

data temp1&year;
set temp&year vert_dof&year;
run;

proc sql;

create table vert_dofn&year as
select year, period_dt, cnty, gender_cd, ethnic, sum(dof) as dof, sum(alloth) as alloth, age
from temp1&year
group by year, period_dt, cnty, gender_cd, ethnic, age;
quit;

%end;

%mend summdof;

%summdof;

data vert_dof;
   set vert_dofn2000
       vert_dofn2001
       vert_dofn2002
       vert_dofn2003
       vert_dofn2004
       vert_dofn2005
       vert_dofn2006
       vert_dofn2007
       vert_dofn2008
       vert_dofn2009
       vert_dofn2010
       vert_dofn2011
       vert_dofn2012
       vert_dofn2013
       vert_dofn2014
       vert_dofn2015
       vert_dofn2016
       vert_dofn2017
       vert_dofn2018
       vert_dofn2019
       vert_dofn2020
       vert_dofn2021
       vert_dofn2022
       vert_dofn2023
       vert_dofn2024
; 
   uniqval=_n_;
run;

proc sort data=vert_dof; by period_dt cnty age ethnic gender_cd; run;
proc sort data=fe2;      by period_dt agency cnty age ethnic gender_cd; run;
proc sort data=oe2;      by period_dt agency cnty age ethnic gender_cd; run;
proc sort data=entries2; by period_dt agency cnty age ethnic gender_cd; run;
proc sort data=ref2;     by period_dt agency cnty age ethnic gender_cd; run;
proc sort data=invrefs2; by period_dt agency cnty age ethnic gender_cd; run;
proc sort data=subrefs2; by period_dt agency cnty age ethnic gender_cd; run;
proc sort data=pit2;     by period_dt agency cnty age ethnic gender_cd; run;

data template1;
   do year=2000 to 2024;
   do cnty=0 to 58 ;
      do agency = 1 to 4;
	     do age = 0 to 21 ;
		    do gender_cd= 1 to 2;
  		       do ethnic=1 to 6 ;
			      period_dt=yyq(year,1);
			/*	  if cnty=59 then cnty=98; */
			  /*    if age=21 then age=99; */
				  if ethnic=6 then ethnic=7;
				  output;
			   end;
			end;
		 end;
	  end;
   end;
   end;
run;

proc sort data=template1; by period_dt agency cnty age ethnic gender_cd; run;
proc sort data=template1 out=template2; by period_dt agency cnty age ethnic gender_cd; where agency=4; run;

%macro loopy(ds,tlvl);

data &ds.3;
   merge &ds.2 (in=a)
         template&tlvl.(in=b);
   by period_dt agency cnty age ethnic gender_cd; 
run;

proc sql;
   create table &ds.4 as
   select a.*,
          b.dof,
		  b.alloth,
		  b.uniqval
   from &ds.3 as a left join vert_dof as b
   on a.period_dt=b.period_dt and
      a.gender_cd=b.gender_cd and
	  a.ethnic=b.ethnic and
      a.cnty=b.cnty and
	  a.age=b.age 
	  ;

quit;

data &ds.5; 
   set &ds.4;
   if count=. and dof=. then delete;
run;

%mend loopy;

%loopy(fe,1);
%loopy(oe,1);
%loopy(entries,1);
%loopy(ref,2);
%loopy(invrefs,2);
%loopy(subrefs,2);
%loopy(pit,1);

data fullset(drop=_freq_); 
   set fe5(in=a)
       oe5(in=b)
       entries5(in=c)
       ref5(in=d)
       subrefs5(in=e)
       pit5(in=f) 
       invrefs5(in=g);
   if a then level=1;
   if b then level=2;
   if c then level=3;
   if d then level=4;
   if e then level=5;
   if f then level=6;
   if g then level=7;

   if count=. then count=0;
   if dof=. then dof=0;
   if tic_8plus=. then tic_8plus=0;
   if tic_lt8=. then tic_lt8=0;
run;

proc sort data=fullset out=rates1; by level agency ethnic age gender_cd; run;

data rates2;
set rates1;

if AGE = 0 then AGE_GRP = 0;
else if AGE in (1,2) then AGE_GRP = 1;
else if AGE in (3,4,5) then AGE_GRP = 2;
else if AGE in (6,7,8,9,10) then AGE_GRP = 3;
else if AGE in (11,12,13,14,15) then AGE_GRP = 4;
else if AGE in (16,17) then AGE_GRP = 5;
else if AGE = 100 then AGE_GRP = 9;
else AGE_GRP=6;

run;

data dvlp.dri_rri;
set rates2;

run;

proc sort data = dvlp.dri_rri;
by LEVEL CNTY PERIOD_DT;
run;

data test.dri_rri;
set dvlp.dri_rri;
run;


proc sql;

create index INDX1 on dvlp.dri_rri(LEVEL, CNTY, PERIOD_DT);
create index INDX1 on test.dri_rri(LEVEL, CNTY, PERIOD_DT);

quit;