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

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

*This code extracts all hospital birth events for a given cohort (ie the hospital admission that was for their own birth);
*Most hospital births are recorded twice in the hospital admissions dataset: once against the mother, and once against the baby;
*ICD codes for recording births against a mother are different to those for recording a birth against a baby;
*This code selects births recorded against a baby;
*ICD-10 codes for identifying birth events were obtained from MoH
*The final file (birth_event) contains all hospital events for each person (one row per hospitalisation), and a flag for 
	whether or not each event was a birth event
*You need to start with a list of people that you want to identify births for, in a file in the work directory called 'my_cohort';

/*	Things to note:*/
/*	This selects birth events against the baby's record (not against the mother's record)*/
/*	Birth events against the baby do not contain information about delivery mode- for that you need the mother's record*/
/*	This only selects ICD-10 codes so it will only work for births from 2000 onwards (approx)*/
/*	Some babies have more than one birth event (ie more than one moh_evt_event_id_nbr with a birth ICD code against it)*/
/*		and sometimes these have different start and end dates. We are not sure why this happens.*/


*Access required to run this code:
*MoH publicly funded hospital discharges;

*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;


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



	*Library names;
	libname moh ODBC dsn=idi_clean_&refresh._srvprd schema=moh_clean;

	*Extract all hospitalisations for your cohort;
	proc sql;
		create table all_hosp as
		select distinct a.snz_uid, b.moh_evt_event_id_nbr, b.moh_evt_evst_date as event_st_date,
		b.moh_evt_even_date as event_end_date 
		from my_cohort as a left join moh.pub_fund_hosp_discharges_event as b
		on a.snz_uid=b.snz_uid;
	quit;

	*merge with diagnosis data to get diagnostic codes for the visits;
	*this can take a while if you have a big cohort;
	proc sql;
		create table all_hosp_diag as 
		select a.*, b.moh_dia_clinical_code, b.moh_dia_diagnosis_type_code, b.moh_dia_clinical_sys_code
		from all_hosp 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;

	*Flag birth events for the baby;
		data flagged_events;
			set all_hosp_diag;

			*Flagging birth events ICD-10;
			if substr(moh_dia_clinical_code,1,3) in ('Z38') and moh_dia_clinical_sys_code>='09' 
				and (moh_dia_Diagnosis_type_code="A" or moh_dia_Diagnosis_type_code="B") then birth_event=1;
			else birth_event=0;
		run;

	*Summarise to one row per hospital event with a flag for whether or not it was a birth event;
	proc sql;
	create table birth_event as 
	select snz_uid, moh_evt_event_id_nbr, max(event_st_date) as event_st_date format ddmmyy10., max(event_end_date) as event_end_date format ddmmyy10., 
	max(birth_event) as birth_event
	from flagged_events
	where moh_evt_event_id_nbr is not null
	group by snz_uid, moh_evt_event_id_nbr
	order by snz_uid, event_st_date;
	quit;

	*Note that a small number of babies have more than one birth event. In theory birth should only be recorded against one event;
	*Researchers will have to decide what to do with these;
	*List the babies with more than one birth event;
	proc sql; 
		create table check_multiple as
		select snz_uid, count(distinct moh_evt_event_id_nbr) as n_events
		from birth_event
		where birth_event=1
		group by snz_uid
		order by n_events descending;
	quit;