- Open Access
- Open Peer Review
Data preparation techniques for a perinatal psychiatric study based on linked data
BMC Medical Research Methodology volume 12, Article number: 71 (2012)
In recent years there has been an increase in the use of population-based linked data. However, there is little literature that describes the method of linked data preparation. This paper describes the method for merging data, calculating the statistical variable (SV), recoding psychiatric diagnoses and summarizing hospital admissions for a perinatal psychiatric study.
The data preparation techniques described in this paper are based on linked birth data from the New South Wales (NSW) Midwives Data Collection (MDC), the Register of Congenital Conditions (RCC), the Admitted Patient Data Collection (APDC) and the Pharmaceutical Drugs of Addiction System (PHDAS).
The master dataset is the meaningfully linked data which include all or major study data collections. The master dataset can be used to improve the data quality, calculate the SV and can be tailored for different analyses. To identify hospital admissions in the periods before pregnancy, during pregnancy and after birth, a statistical variable of time interval (SVTI) needs to be calculated. The methods and SPSS syntax for building a master dataset, calculating the SVTI, recoding the principal diagnoses of mental illness and summarizing hospital admissions are described.
Linked data preparation, including building the master dataset and calculating the SV, can improve data quality and enhance data function.
Existing population and hospital data are valuable sources of surveillance and research. Data linkage provides a tangible route to make the data sources more powerful for research [1, 2]. In recent years, there has been a marked increase in the use of registry data for births, deaths and diseases such as psychiatric disorders [3, 4]. Hospital admission data has become one of the key data collections for medical research especially for population-based cohort studies. In New South Wales (NSW), the Midwives Data Collection (MDC), the Registry of Births, Deaths and Marriages (RBDM) registration data, the Admitted Patient Data Collection (APDC) and the Cancer Registry (CR) are commonly used by medical researchers for their studies [6–9]. However, there is little literature that describes the method of linked data preparation. Research articles using linked data did not describe data preparation details in method section because of the word limit. Method papers about linked data mainly focused on strategies rather than on techniques [2, 5, 10]. However, the data preparation techniques including syntax are often inquired by researchers.
This paper aims to answer four frequently asked questions in data preparation using linked birth and hospital data. The first question is how are data collections merged? The second is how are hospital admissions distributed into the periods before pregnancy, during pregnancy and after birth? The third is how are psychiatric diagnoses recoded and grouped? The fourth is how are hospital admissions summarised and ordered in sequence?
Data sources used in this paper as examples are: The NSW Midwives Data Collection (MDC) which includes mothers’ and babies’ records, the NSW Register of Congenital Conditions (RCC), the NSW Admitted Patient Data Collection (APDC) and the NSW Pharmaceutical Drugs of Addiction System (PHDAS). The MDC is a population-based data collection of all births in NSW. It covers all births of at least 20 weeks gestation or at least 400 grams birthweight in public and private hospitals and homebirths. It includes information on maternal characteristics, pregnancy, labour, delivery and neonatal outcomes. The NSW Department of Health has managed the MDC since 1987. Personal identifiers were included in the data collection after 1992 and there was a revision to the MDC form in 1993. It is recommended that record linkage studies be carried out on data collected since 1994. The RCC is a population-based surveillance system and monitors birth defects detected during pregnancy, at birth or up to one year after birth. The data is available for a rolling five-year period. It covers structural birth defects but not functional problems. The RCC was established in 1990. As the RCC was voluntary up to 1997, the birth defects records were incomplete especially for terminations of pregnancy. Since 1998, the reporting of terminations of pregnancy has improved and doctors, hospitals and laboratories have been required to notify the register of all birth defects. The APDC is a routinely collected census of all hospital separations. It includes all patient hospitalisations from NSW public, private and repatriation hospitals, private day procedures centres and public nursing homes. The data include patient demographics, diagnoses and clinical procedures. The APDC is collected on a financial year basis. A limitation of APDC data is that there were no names in the APDC prior to 1 July 2000. Since 1 July 2000, the APDC has included names for patients admitted to public hospitals but does not have names for patients admitted to private hospitals. Other information such as sex, date of birth, medical record number, hospital code and address can be used to assist in linkage. The PHDAS is an administrative database used by the Pharmaceutical Services Unit of the NSW Ministry of Health to facilitate the authorisation of medical practitioners to prescribe drugs of addiction. It consists of the Methadone Subsystem implemented in 1985, the Non-Methadone Subsystem implemented in 1985 and the Stimulant Notification Subsystem implemented in 1999. This study used the NSW Opioid Treatment Program (OTP) in the Methadone Subsystem and included treatment information such as program type, start and end date, reason for ending program and other drugs of concern.
Data linkage was performed by The Centre for Health Record Linkage (CHeReL) of the NSW Department of Health using probabilistic record linkage methods and choicemaker software (http://www.cherel.org.au). Each record was assigned a record identification number, the Project Person Number, which allows the records for the same individual to be identified, extracted and linked. The CHeReL Checked a random sample of 1,000 persons, the false positive rate of the linkage was 0.3% and false negative <0.5%.
The study was approved by the NSW Population & Health Services Research Ethics Committee and the Human Research Ethics Committee of the University of New South Wales.
The aims of this study are: to investigate the pattern and rate of hospital admission for maternal psychiatric disorders and substance use before and after birth; explore the factors associated with these problems; and compare the perinatal outcomes of babies whose mothers were admitted or not admitted to hospital for mental illness before birth.
This study was based on linked data which include the MDC, RCC, APDC and PHDAS. The study population included all mothers aged from 18 to 44 years who gave birth between 1 January 2003 and 31 December 2004 in NSW, and their babies. Birth records from 2003 to 2004 in the MDC were linked with the RCC in the same period and with APDC and PHDAS records between 1 January 2001 and 31 December 2006. This enabled hospital admissions for the mothers to be traced back to at least two years before birth and followed up for at least two years after birth. Each mother was followed up from the year before pregnancy to the end of the 24th month after birth.
The key variables included in the study included Project Person Number for mother and baby; mother’s age in days at birth; mother’s age in days at hospital admission and discharge; diagnoses for psychiatric disorders, substance use and birth defects; maternal age; mother’s country of birth; maternal diabetes mellitus and hypertension; pregnancy complications (pre-eclampsia, gestational diabetes); smoking status during pregnancy; remoteness of living area and a socioeconomic indicator (i.e. the Index of Relative Socio-economic Disadvantage Quintile); delivery method; infant gender; birthweight; gestational age; admission to a neonatal intensive care unit (NICU) or special care nursery (SCN); and fetal/neonatal death type (stillbirth, neonatal death, post-neonatal death). The reason for using mother’s age in days at birth, at hospital admission and discharge rather than date of birth, hospital admission and discharge is to maintain privacy and decrease the risk of inadvertent identification of individuals from the data.
The main measurements for mothers were hospital admission rate of maternal psychiatric disorders and substance use before pregnancy, during pregnancy and after birth; and factors associated with maternal psychiatric disorders and substance use. The main pregnancy outcomes included birthweight, preterm birth, birth defects and admission to an NICU or SCN. The outcomes were compared between babies whose mothers were admitted and not admitted to hospital with the diagnoses of mental illness.
The diagnoses in hospital data were classified using ICD-10-AM (International Statistical Classification of Diseases and Related Health Problems Tenth Revision, Australian Modification). The diagnoses included principal, stay and all diagnosis. The principal diagnosis, coded ‘icd10d1’, referred to a medical condition that was chiefly responsible for the hospital admission . An additional diagnosis, coded from ‘icd10d2’ to ‘icd10d55’, was a condition or a complaint either coexisting with the principal diagnosis or arising during the hospitalization. Stay diagnosis was an additional diagnosis coded ‘icd10d2’ and referred to the diagnosis that most influenced the length of stay in hospital. The stay diagnosis was frequently the same as the principal diagnosis; however, it may be different .
For studies based on linked data collections, there are generally two ways to merge the data. One is to link all or the majority of study datasets to build a master dataset. Then the master dataset can be used for variable preparation and tailored into sub data for different study purposes. There are two advantages of building a master dataset. One is that it provides a platform to improve the data quality. The consistency of values for variables that repeatedly appear in the master dataset can be checked and completeness improved . For example, the variable mother’s country of birth (Cob) appeared in each birth record of the MDC; if the mother had two births, her Cob could be checked for consistency and the missing value could be minimised. Maternal Indigenous status was under-reported in the NSW births[14, 15]. Maternal Aboriginal status was under-ascertained in both the MDC and RBDM. By linking the MDC and RBDM and constructing an SV of Aboriginality, the number of missing values of Aboriginality was significantly decreased and the under-estimation was significantly improved . Another advantage is that the master dataset provides an opportunity to calculate an SV which identifies hospital admissions in the periods before pregnancy, during pregnancy and after birth (such as Admmonth in Table1). The SV of time interval (SVTI) can also be used to select study populations. For example, the mothers who admitted to hospital with a diagnosis of mental illness in the period from 12 months before pregnancy to 24 months after birth could be selected using the SVTI for the analysis of rates before and after birth (see Figure1). Another way to merge data is to select study records from different data collections according to study topic and merge these to build the study data for the specific analysis. The advantage of this method is that the merged data size is relatively small which makes the merge easier.
An example of merging a master dataset for a maternal mental illness study is shown in Figure2. Firstly, the birth records in the MDC were merged with the records in the RCC for the same birth. Secondly, the birth records of one mother were merged by birth order of the mother into a single record. Finally, the mother’s linked birth data were merged with the APDC and PHDAS (see Figure2) by mother’s Project Person Number. The master dataset included all available information for the study and could be tailored for different analyses.
Prepare the variable which distributes hospital admissions over the periods before and after birth
In order to describe the trend of hospitalisations before and after birth (see Figure1), an SVTI needs to be created. The SVTI can identify hospital admissions over different time intervals such as a week, month or year, before and after birth. It also shows the time of admission before and during pregnancy. The steps and SPSS syntax for calculating and recoding an SVTI variable, Admmonth, which identifies hospital admissions during the months before and after birth, are shown in Table1. The Admmonth is calculated by three variables: maternal age in days at birth in the MDC (AgeBirthMum); mother’s age in days at admission in the APDC (AGEAdmMum); and gestational age in days (Gage). Gestational age was provided from the MDC as the number of completed weeks since the start of the last menstrual period. As a result, it needs to be converted into days to keep the unit consistent with the previous two variables. Because the Gage is reported in completed weeks, the accuracy of hospital admission time before birth is also in weeks rather than in days. For interval recoding, each month is defined as 30 days and each year as 360 days.
The hospital admissions in other time intervals such as year (Admyear) can be calculated the same way. The only difference is the length of the interval recoded. For example, each value of Admyear covers 360 days while Admmonth covers 30 days.
Classify diagnoses of mental illness
Mental illnesses are generally classified into diagnoses groups according to study purpose, psychiatric definition and sample size. As a result, the International Classification of Diseases (ICD) code in each category may vary across research papers [16–19]. Table2 shows the diagnostic categories or groupings used for our study and SPSS syntax which allows recoding of the principal diagnoses of mental illness into these groups. The most prevalent diagnoses found in this study are those represented in the table: unipolar depression (including major depression F32 codes and those depressions identified as arising in the first six weeks after birth F53.0 code); bipolar disorder; acute or brief psychotic episodes; adjustment and anxiety disorders; and schizophrenia and schizophrenia-like disorder. In addition we are interested in substance use disorders — there is a category for less common remaining disorders (e.g. somatoform disorders) as well as an overall mental illness category where all diagnoses are grouped together.
The principal diagnosis in Table2 refers to the diagnosis which is chiefly responsible for the hospital admission . ICD-10-AM refers to International Statistical Classification of Diseases and Related Health Problems, Tenth Revision, Australian Modification . In NSW APDC data, the variable ‘icd10d1’ refers to principal diagnosis; ‘icd10d2’ refers to stay diagnosis and ‘icd10d3’ and following refer to other diagnoses. If stay and other diagnoses are recoded into one diagnosis, the command of ‘DO REPEAT’ and ‘END REPEAT’ should be added to the syntax. For example, the variable which includes one-stay diagnosis and 50 other diagnoses for schizophrenia and schizophrenia-like disorder (Other1) is recoded by the syntax: COMPUTE Other1 = 0. DO REPEAT icd10d = icd10d2 TO icd10d52. If (icd10d GE ‘F20’ AND icd10d LE ‘F22.9’) Other1 = 1. If (icd10d GE ‘F24’ AND icd10d LE ‘F29’) Other1 = 1. END REPEAT. EXECUTE. VARIABLE LABELS Other1' stay and other diagnoses of schizophrenia and schizophrenia-like disorder F20-22, F24-29'.
Aggregate hospital admissions
Hospital admissions need to be summarised into different perinatal periods for some analyses. The following questions are frequently asked: how many hospital admissions in pregnancy or in the first year after birth? how many first hospital admissions during the study period? and how many days of hospital stay in total between pregnancy and the first year postpartum? To answer these questions, some aggregated SVs need to be created. Table3 show the steps and SPSS syntax for summarizing and sequentially ordering the hospital admissions for principal diagnoses of mental illness during pregnancy.
For the analysis of total length of hospital stay during pregnancy, the duration of each hospital stay during the period is calculated firstly by using maternal age in days at discharge minus maternal age in days at admission. Then the durations of hospital stay during pregnancy are added together by using the function of aggregate data in SPSS (see Table3).
Register-based and routinely collected data are important sources of disease surveillance and epidemiological studies [21, 22]. The data sources have been widely utilized in the Nordic countries, Scotland, United Kingdom, United States, Canada and Australia [5, 21]. For rare conditions such as birth defects, spinal surgery and arthroplasty, the data provide an effective means for monitoring the rates [23, 24]. By compiling more similar data sources from different countries or regions, the study population for the rare diseases increased significantly and the study results became more reliable [24, 25]. On the other hand, data linkage provides a way to extend the study field to broader areas and improve the quality of the linked data [2, 10, 26]. By linking different data sources according to study objectives, study variables can be increased and the completeness of the variable values can be improved significantly [10, 26].
A common limitation of registered or routinely collected data is loss of registration or under-reporting. Favourable health was generally more frequent among the registered than the non-registered, and non-registration may lead to bias in analyses of health inequalities [10, 27]. In NSW, Aboriginal mothers were less likely to register their births . The magnitude of under-estimation can be estimated by the capture-recapture method . The master dataset provides a platform for creating an SV which can improve the under-estimation . The SV is created from linked data and adds value to the data.
Building a master dataset is essential for linked data analysis. The master dataset is useful for data quality improvement. The more data to be linked when building the master dataset, including internal and external datasets, the greater the chance of improving data quality (including consistency and completeness), and the larger the amount of information made available for research. For a mother’s perinatal psychiatric study, the master dataset will be more useful if it includes, in addition to mothers’ and babies’ information, fathers’ data and other data collections such as the national health insurance data (Medicare), RBDM and the hospital- based Emergency Department Data Collection (EDDC). An optimal linked master dataset should cover all life events and health conditions of a study population in the long term.
The techniques for building a master dataset were derived from the current study data which were relatively simple. For very complicated data, such as the Western Australian Data Linkage System (WADLS), which was instigated in 1995 to link up to 40 years of data from over 30 collections for an historical population of 3.7 million, more linking methods were applied . For example, firstly the study records and variables for specific topics were selected from different data sources and then the data were linked .
It should be borne in mind when describing rates or risks before birth that gestational age in MDC refers to the time interval from the first day of women’s last menstrual period (LMP) to her baby’s date of birth rather than the interval between conception and date of birth. The conception date is about 14 days after the first day of women’s LMP. Furthermore, gestational age data is provided in completed weeks rather than days. As a result, the shortest time interval before birth should be expressed in weeks.
For more complicated data linkage, building a variable dictionary, including the SV, is very helpful when checking and analysing data.
To provide a comprehensive and representative picture of maternal mental illness before and after birth, some other limitations of linked data should also be considered when interpreting and disseminating the results [28, 29]. Patients’ access to health care impacts hospital admission rates. For psychiatric disorders, severity needs to be considered because only mild and severe patients admitted to hospital.
Linked data preparation including building a master dataset and calculating the SV can improve data quality and enhance data function.
Admitted Patient Data Collection
The Centre for Health Record Linkage
Emergency Department Data Collection
International Classification of Diseases
International Statistical Classification of Diseases and Related Health Problems Tenth Revision Australian Modification
Last menstrual period
Midwives Data Collection
New South Wales
Pharmaceutical Drugs of Addiction System
Register of Congenital Conditions
Statistical Package for the Social Sciences
statistical variable of time interval.
Burns L, Mattick RP, Cooke M: Use of record linkage to examine alcohol use in pregnancy. Alcohol Clin Exp Res. 2006, 30 (4): 642-8. 10.1111/j.1530-0277.2006.00075.x.
Morgan VA, Jablensky AV: From inventory to benchmark: quality of psychiatric case registers in research. Br J Psychiatry. 2010, 197 (1): 8-10. 10.1192/bjp.bp.109.076588.
Mai Q, et al: Mental illness related disparities in diabetes prevalence, quality of care and outcomes: a population-based longitudinal study. BMC Med. 2011, 9: 118-10.1186/1741-7015-9-118.
Munk-Olsen T, Laursen TM, et al: Psychiatric Disorders with Postpartum Onset: Possible Early Manifestations of Bipolar Affective Disorders. Arch Gen Psychiatry. 2011, 69 (4): 428-34.
Holman CD, et al: A decade of data linkage in Western Australia: strategic design, applications and benefits of the WA data linkage system. Aust Health Rev. 2008, 32 (4): 766-77. 10.1071/AH080766.
Goldsbury DE, et al: Using linked routinely collected health data to describe prostate cancer treatment in New South Wales.Australia: A validation study. BMC Health Serv Res. 2011, 11: 253-10.1186/1472-6963-11-253.
Yu XQ, et al: A population-based study from New South Wales, Australia 1996–2001: area variation in survival from colorectal cancer. Eur J Cancer. 2005, 41 (17): 2715-21. 10.1016/j.ejca.2005.05.018.
Roberts CL, Tracy S, Peat B: Rates for obstetric intervention among private and public patients in Australia: population based descriptive study. BMJ. 2000, 321 (7254): 137-41. 10.1136/bmj.321.7254.137.
Algert CS, et al: Regional block versus general anaesthesia for caesarean section and neonatal outcomes: a population-based study. BMC Med. 2009, 7: 20-10.1186/1741-7015-7-20.
Xu F, et al: Improvement of maternal Aboriginality in NSW birth data. BMC Med Res Methodol. 2012, 12 (1): 1-8. 10.1186/1471-2288-12-1.
Australian Institute of Health and Welfare: Australian hospital statistics 2008–09. Health services series no. 17. Cat. no. HSE 84. 2010, AIHW, Canberra
Centre for Epidemiology and Research: Diabetes as a principal diagnosis and as a comorbidity:hospitalizations by sex, NSW 1991–92 to 2010–2011. 2012, http://www.healthstats.nsw.gov.au/Indicator/dia_pcohos,
Xu F, et al: Improvement of maternal Aboriginality in NSW birth data. BMC Med Res Methodol. 2012, 12 (1): 8-10.1186/1471-2288-12-8.
Leeds KL, et al: Indigenous mothers and their babies, Australia 2001–2004. cat. no. PER 38. 2007, AIHW, Canberra
Li Z, et al: Australia’s mothers and babies 2009. 2011, AIHW National Perinatal Epidemiology and Statistics Unit, Sydney, 1-122.
Lawrence D, et al: Mortality in Western Australian psychiatric patients. Soc Psychiatry Psychiatr Epidemiol. 2000, 35 (8): 341-7. 10.1007/s001270050248.
Lawrence D, et al: Excess cancer mortality in Western Australian psychiatric patients due to higher case fatality rates. Acta Psychiatr Scand. 2000, 101 (5): 382-8. 10.1034/j.1600-0447.2000.101005382.x.
Munk-Olsen T, et al: Perinatal mental disorders in native Danes and immigrant women. Archives of Women's Mental Health. 2010, 13 (4): 319-326. 10.1007/s00737-009-0131-0.
Mai Q, et al: Do users of mental health services lack access to general practitioner services?. Medical Journal of Australia. 2010, 192 (9): 501-506.
Page A, Ambrose S, HDGlover J, Atlas of Avoidable Hospitalisations in Australia: Atlas of Avoidable Hospitalisations in Australia: ambulatory care-sensitive conditions. 2007, PHIDU, University of Adelaide, Adelaide
Miettunen J, et al: Use of Register Data for Psychiatric Epidemiology in the Nordic Countries. Textbook in Psychiatric Epidemiology. Edited by: John Wiley. 2011, 117, 131
Munk-Olsen T, et al: New parents and mental disorders: a population-based register study. JAMA. 2006, 296 (21): 2582-9. 10.1001/jama.296.21.2582.
Surman G, da Silva AA, Kurinczuk JJ: Cerebral palsy registers and high-quality data: an evaluation of completeness of the 4Child register using capture-recapture techniques. Child Care Health Dev. 2012, 38 (1): 98-107. 10.1111/j.1365-2214.2011.01280.x.
Ohrn A, et al: Adverse events in spine surgery in Sweden: a comparison of patient claims data and national quality register (Swespine) data. Acta Orthop. 2011, 82 (6): 727-31. 10.3109/17453674.2011.636673.
Labek G, et al: Organisation, data evaluation, interpretation and effect of arthroplasty register data on the outcome in terms of revision rate in total hip arthroplasty. Int Orthop. 2011, 35 (2): 157-63. 10.1007/s00264-010-1131-4.
Mai Q, et al: Mental illness related disparities in diabetes prevalence, quality of care and outcomes: a population-based longitudinal study. BMC Medicine. 2011, 9 (1): 118-10.1186/1741-7015-9-118.
Nummela O, et al: Register-based data indicated nonparticipation bias in a health study among aging people. J Clin Epidemiol. 2011, 64 (12): 1418-25. 10.1016/j.jclinepi.2011.04.003.
Schuh R, et al: Validity of published outcome data concerning Anatomic Graduated Component total knee arthroplasty: a structured literature review including arthroplasty register data. Int Orthop. 2012, 36 (1): 51-6. 10.1007/s00264-011-1255-1.
Kallen B, Nilsson E, Olausson PO: Antidepressant use during pregnancy: comparison of data obtained from a prescription register and from antenatal care records. Eur J Clin Pharmacol. 2011, 67 (8): 839-45. 10.1007/s00228-011-1021-8.
The pre-publication history for this paper can be accessed here:http://www.biomedcentral.com/1471-2288/12/71/prepub
We would like to thank data custodians Lee Taylor, Kim Lim and Tony Dunn of the NSW Department of Health, Hilarie Tardif and Glenda Lawrence of the Centre for Health Record Linkage (CheReL) and John Lumby and Pia Salmelainen of the Pharmaceutical Services Branch of NSW Health for providing the data, undertaking the linkage and providing expert technical advice. We acknowledge Nicole Reilly for helping with diagnoses grouping and D’Arcy J Holman for providing training courses on the analysis of linked health data. We acknowledge the families who have contributed their data and professional staff involved in the data collection and management of this study.
Funding for this study was provided by Australia’s National Health and Medical Research Council (NHMRC) Training Fellowship.
The authors declare that they have no competing interests.
FX participated in the study design, data analysis and paper writing. LH participated in the study design. MPA participated in diagnoses grouping. EAS participated in the study design and coordinated and supervised the study. All authors read, revised and approved the final version of the manuscript.
About this article
Cite this article
Xu, F., Hilder, L., Austin, M. et al. Data preparation techniques for a perinatal psychiatric study based on linked data. BMC Med Res Methodol 12, 71 (2012) doi:10.1186/1471-2288-12-71
- Data preparation
- Psychiatric study