*****************************************************************************************************************;
*****************************************************************************************************************;

*Author: Sheree Gibb, A Better Start National Science Challenge;
*Date: This version May 2021;

*This code selects all 4 year olds living in NZ in a given fiscal year and determines whether they have ever completed a B4SC check;
*Completion of B4SC checks is split into three categories following conversations with MoH;
*Tests within these categories are often administered together;
*The categories are:
Vision and hearing (VHT)
Nurse checks
SDQ-Teacher;

*Access required to run this code:
MoH B4SC 
DIA


*User input required for the next two lines;


%let refresh = 20210420; ** Specify IDI refresh to use for extractions;

%let year = 2015; ** Specify end of fiscal year of interest for the denominator population;


*End of user input, do not change anything below this line;


*****************************************************************************************************************;
*****************************************************************************************************************;






%let prevyear = %eval(&year. - 1);
%let nextyear = %eval(&year. + 1);

libname data ODBC dsn=idi_clean_&refresh._srvprd schema=data;
libname moh ODBC dsn=idi_clean_&refresh._srvprd schema=moh_clean;

*Denominator population of 4 year olds;


*We are calculating B4SC coverage for fiscal years so we can use the respop table to get the denominator population;
*Initially select all respop, we will restrict to 4 year olds later;
data totalpop;
set data.snz_res_pop;
where srp_ref_date="30JUN&year"d;
keep snz_uid;
run;

*Add birth dates to the respop;
proc sql;
create table finalpop2 as
select a.*, b.snz_birth_year_nbr, b.snz_birth_month_nbr
from totalpop as a left join data.personal_detail as b on a.snz_uid=b.snz_uid;
quit;

*We will flag 4 year olds who died before age 5 so that we have the option of removing them from the denominator population;
*Get a list of all deaths recorded in DIA data;
*Only extract deaths after 1990 as file size with all deaths was too large (we are looking at 4yo children so all deaths will be post 1990);
proc sql;
connect to odbc(dsn="idi_clean_&refresh._srvprd");
   create table dia_deaths as
   select * from connection to ODBC
   (select distinct snz_uid, dia_dth_death_year_nbr, dia_dth_death_month_nbr
   from dia_clean.deaths
   where (dia_dth_death_year_nbr <= &year. and dia_dth_death_year_nbr >= 1990) 
   order by snz_uid);
quit;

*Merge deaths with main file;
proc sql;
create table finalpop3 as
select a.*, b.dia_dth_death_year_nbr, b.dia_dth_death_month_nbr
from finalpop2 as a left join dia_deaths as b 
on a.snz_uid=b.snz_uid;
quit;


** Calculate age at end of fiscal year;
*** flag children who died age 4 but keep them in for now, users have the option to remove them (only a very small number of children);
*** remove people who are not aged 4;
data finalpop_res;
   set finalpop3;

   *Calculate age at end of fiscal year;
   age1 = &year. - snz_birth_year_nbr;
   if snz_birth_month_nbr >= 7 then age=age1-1;
   else if snz_birth_month_nbr <=6 then age=age1;
  
   *Only keep people who were aged 4 at the end of the fiscal year;
   if age ne 4 then delete;

   *Flag people who died during year so that we can remove later if we want to;
   *Note this only affects a very small number of 4 year olds so removing won't make much difference;
   if dia_dth_death_year_nbr=&year. then died_&year.=1;
   else died_&year.=0;

    *Create reference date for next step where we calculate overseas time;	
	*Reference date is the first day of the month of 4th birthday;
	ref_date_char=cat(snz_birth_year_nbr,'-',snz_birth_month_nbr,'-01');

	format ref_date_temp ddmmyy10.;
	ref_date_temp=input(ref_date_char, anydtdte10.);
	format ref_date_start ref_date_end ddmmyy10.;
	ref_date_start=intnx('year',ref_date_temp,4,'sameday');

	ref_date_end_temp=intnx('year',ref_date_temp,5,'sameday');
	ref_date_end=intnx('day',ref_date_end_temp,-1,'sameday');

   keep snz_uid age snz_birth_year_nbr snz_birth_month_nbr died_&year. ref_date_start ref_date_end;

run;


**You may wish to add code to remove children from the population if they spent a lot of time overseas during their 4th year and therefore were not present to receive a B4SC;
**Code not included here;

*Get b4sc completion information;

*Join denominator pop to B4SC data;
proc sql;
create table pop_b4sc as
select a.*, b.moh_bsc_general_date as general_date format ddmmyy10., b.moh_bsc_check_status_text, 
case 
	when moh_bsc_general_date=. then 0 else 1
end as general_complete,
b.moh_bsc_vision_date as vision_date format ddmmyy10., b.moh_bsc_vision_outcome_text, 
case 
	when moh_bsc_vision_date=. then 0 else 1
end as vision_complete,
b.moh_bsc_hearing_date as hearing_date format ddmmyy10., b.moh_bsc_hearing_outcome_text, 
case 
	when moh_bsc_hearing_date=. then 0 else 1
end as hearing_complete,
b.moh_bsc_growth_date as growth_date format ddmmyy10., b.moh_bsc_growth_outcome_text,
case 
	when moh_bsc_growth_date=. then 0 else 1
end as growth_complete, 
b.moh_bsc_dental_date as dental_date format ddmmyy10., b.moh_bsc_dental_outcome_text, 
case 
	when moh_bsc_dental_date=. then 0 else 1
end as dental_complete,
b.moh_bsc_imms_date as imms_date format ddmmyy10., b.moh_bsc_imms_outcome_text, 
case 
	when moh_bsc_imms_date=. then 0 else 1
end as imms_complete,
b.moh_bsc_peds_date as peds_date format ddmmyy10., b.moh_bsc_peds_outcome_text, 
case 
	when moh_bsc_peds_date=. then 0 else 1
end as peds_complete,
b.moh_bsc_sdqp_date as sdqp_date format ddmmyy10., b.moh_bsc_sdqp_outcome_text, 
case 
	when moh_bsc_sdqp_date=. then 0 else 1
end as sdqp_complete,
b.moh_bsc_sdqt_date as sdqt_date format ddmmyy10., b.moh_bsc_sdqt_outcome_text,
case 
	when moh_bsc_sdqt_date=. then 0 else 1
end as sdqt_complete
from finalpop_res as a left join moh.b4sc as b
on a.snz_uid=b.snz_uid;
quit;

data b4sc_final;
set pop_b4sc;

if hearing_complete=1 and vision_complete=1 then vht_complete=1;
else vht_complete=0;

if (growth_complete=1 and imms_complete=1 and dental_complete=1 and peds_complete=1 and sdqp_complete=1) then nurse_complete=1;
else nurse_complete=0;

run;