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

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

/*Code for identifying a baby's birth delivery mode (caesarean, assisted vaginal, unassisted vaginal);

Delivery mode is not recorded against a baby, so we need to:
 - identify the baby's mother
 - find hospital admissions for births for the mother 
 - identify the hospital admission for birth record that is close to the baby's birth date 
- get the delivery mode from that record

This code can also be used to identify hospital admissions for birth for a cohort of mothers

We start with a list of children that we want birth delivery mode for (my_cohort)
*/

*Access required to run this code:
MoH B4SC 
DIA;


*User input required for the next line;

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

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


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





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

*Get dates of birth for children;
*Can skip this step if you rfile already has a birth date variable called birth_date in ddmmyy10. format;
*We will make birthdate the 15th of the month as we do not know day of birth;
proc sql;
	create table cohort_bd as
	select a.*, input(cat('15-', b.snz_birth_month_nbr, '-', b.snz_birth_year_nbr), anydtdte10.) as birth_date format ddmmyy10. 
	from my_cohort as a left join data.personal_detail as b on a.snz_uid=b.snz_uid;
quit;

*Next step is to get the snz_uids for the child's birth parent (ie the parent who gave birth to them);

	*We need to subset the births table because permissions are denied on some variables;
	proc sql;
		connect to odbc(dsn="idi_clean_&refresh._srvprd");
			create table birth_subset as 
			select * from connection to odbc
			(select snz_uid, parent1_snz_uid
			from dia_clean.births);
		disconnect from odbc;
	quit;

	*Birth parent (we will call this parent 'mother' from here on) is recorded as parent 1 on birth records;
	proc sql;
		create table parent_uid as 
		select a.*, b.parent1_snz_uid as mother_snz_uid
		from cohort_bd as a left join birth_subset as b
		on a.snz_uid=b.snz_uid;
	quit;

 *Get all hospital events for the mothers;
	proc sql;
		create table parent_hosp_all as
		select distinct a.snz_uid, a.birth_date, b.moh_evt_evst_date as event_st_date format ddmmyy10., b.moh_evt_even_date as event_end_date format ddmmyy10., 
		b.moh_evt_event_id_nbr, a.mother_snz_uid
		from parent_uid as a left join moh.pub_fund_hosp_discharges_event as b
		on a.mother_snz_uid=b.snz_uid;
	quit;

	*Merge with diagnosis data to get diagnostic codes for the mothers' visits;
	*Can take a long time if cohort is large;
	proc sql;
		create table parent_hosp_diag as 
		select a.*, b.moh_dia_clinical_code, b.moh_dia_diagnosis_type_code, b.moh_dia_clinical_sys_code
		from parent_hosp_all as a left join moh.pub_fund_hosp_discharges_diag as b 
		on a.moh_evt_event_id_nbr=b.moh_dia_event_id_nbr;
	quit;

	*Now each child has all of their mother's hospital events recorded against the child's snz_uid; 

*Mothers may have admissions for several births. We need to identify the admission that was for the child's birth (not for their sibling's birth);

	*Birth admissions could start some time before birth so we will find all events that have birth ICD codes and have
	an admission date less than 3 months prior to the child's birth month;
	*This will differentiate children form siblings but not from twins- there is not much we can do to differentiate twins (see below);
	data  birth_event;	
		set parent_hosp_diag;
				
		*Calculate time between child's birth date and start of hospital admission;
		diff_start=intck('day',event_st_date,birth_date);

		*Flag the events that had codes indicating it was a birth;

		*Flagging birth events ICD-10;
			if event_st_date>="01JAN2000"d and substr(moh_dia_clinical_code,1,3) in ('Z37', 'O80', 'O81', 'O82') and moh_dia_clinical_sys_code>='09' 
				and (moh_dia_Diagnosis_type_code="A" or moh_dia_Diagnosis_type_code="B") then birth_event_m=1;

			*Flagging birth events ICD-9;
			if moh_dia_diagnosis_type_code="O" and moh_dia_clinical_sys_code>='09' and moh_dia_clinical_code in ('9046700', '9046800', '9046801', '9046802', '9046803', 
				'9046804', '9046805', '9046900', '9046901', '9047000', '9047001', '9047002', '9047003', '9047004', '1652000', '1652001', '1652002', '1652003') 
				then birth_event_m=1;

			if event_st_date>="01JAN2000"d and moh_dia_clinical_sys_code>='09' 
				and (moh_dia_Diagnosis_type_code="A" or moh_dia_Diagnosis_type_code="B") then do;
				if substr(moh_dia_clinical_code,1,3) in ('O80') then delivery_mode="Unassisted";
				if substr(moh_dia_clinical_code,1,3) in ('O81') then delivery_mode="Assisted";
				if substr(moh_dia_clinical_code,1,3) in ('O82') then delivery_mode="Caesarean";
			end;
			
			if moh_dia_diagnosis_type_code="O" and moh_dia_clinical_sys_code>='09' then do;
				if moh_dia_clinical_code in ('9046700', '9047000') then delivery_mode="Unassisted";
				if moh_dia_clinical_code in ('9046800', '9046801', '9046802', '9046803', '9046804', '9046900', '9047001', '9047002', '9047003', '9047004') 
					then delivery_mode="Assisted";
				if moh_dia_clinical_code in ('1652000', '1652001', '1652002', '1652003') then delivery_mode="Caesarean";
			end;
			
			keep snz_uid mother_snz_uid delivery_mode birth_event_m moh_evt_event_id_nbr diff_start;

		*Keep only the events that are identified as births and where the hospital admission date is 
		between 20 days after and 3 months before the child's birth date (to make sure it is not a birth of a sibling);
		*Note this will not distinguish twin births as both twins are born close in time to each other;
		if birth_event_m=1 and -20 < diff_start < 92;
	run;

	proc sort data=birth_event (where=(delivery_mode ne ''))nodup out=birth_event_nodup;
		by snz_uid delivery_mode;
	run;

	*Some children have multiple delivery modes;
	*This can happen due to data error and also for twins where delivery modes were different;
	*We need a single delivery mode per child so we will sort and take the greater intervention one (caesarean > assisted > unassisted);
	*To do this we need to create a numeric variable with the order to be sorted;
	*if twins were born via different delivery modes both will be coded with the maximum intervention mode as there is no way to distinguish between the two twins;

	data birth_event_mode;
		set birth_event_nodup;
		delivery_mod_num=.;
		if delivery_mode="Caesarean" then delivery_mod_num=1;
		if delivery_mode="Assisted" then delivery_mod_num=2;
		if delivery_mode="Unassisted" then delivery_mod_num=3;
	run;

	proc sort data=birth_event_mode;
		by snz_uid delivery_mod_num;
	run;

	*Getting file back to one row per child, selecting the 'highest' delivery mode;
	data delivery_mode_single;
		set birth_event_mode;
		by snz_uid;
		if first.snz_uid;
	run;

	*merge delivery modes back on to original file;
	proc sql;
		create table delivery_mode_final as
		select a.*, b.delivery_mode, b.mother_snz_uid
		from my_cohort as a left join delivery_mode_single as b on a.snz_uid=b.snz_uid;
	quit;


*Note there are still quite a few children that we could not find a delivery mode for;
*These will include non-hospital births and also linking errors etc;
*May be possible to get additional infomration from maternity collection but we have not tried this;
proc freq; tables delivery_mode; run;