A Microcomputer-Based System for Distributed Statistical Analysis of Mortality Data Using a CD-ROM Database Sabbatini, RME (2); Lobo da Costa Jr, M (1); Becker, RA (3) and Tardelli, A (4) (1) Department of Statistics and Epidemiology, School of Public Health, University of S<176>o Paulo, S<176>o Paulo; (2) Center for Biomedical Informatics, State University of Campinas, Campinas; (3) National Division of Epidemiology, Ministry of Health, Brasilia; (4) Latin-American and Caribbean Center for Information in Health Sciences, Pan-American Health Organization, S<176>o Paulo, Brazil ABSTRACT The analysis of mortality data using country-wide data is very important for many epidemiological and public health projects. However, the costs involved in having a large number of original records available in a local computer installation usually represent a serious obstacle to achieve this. This paper relates a novel approach in distributing mortality databases to end-users, by providing a very fast, simple-to-use microcomputer software together with local, regional or national databases comprising eight years (with a total of more than six million original and complete death certificate records), using magnetic diskettes and a CD-ROM entirely produced in Brazil. 1. Introduction The analysis of mortality data using country-wide data is very important for many epidemiological and public health projects at all levels of the health care and prevention system, particularly in developing countries, where data on morbidity are usually scarce. Not only international (e.g., WHO) and national (e.g., the Ministry of Health) organizations are permanently interested into collecting, sorting, retrieving and analyzing the demographic, personal and nosological data available through the official certificates usually emitted after a death; but local organizations as well. Thus, medical researchers, province- and district-level health authorities and other persons and organizations have a need to access and to analyze aggregate as well as original mortality data which originated in their own areas, as well as to compare them with data originating in other levels and areas. Taking Brazil as a typical example of many developing countries, we have a relatively good country-wide Mortality Information System, which is comprised by the following elements [1,2]: - Death certificates (DCs) are filled out and signed by the physician who attended to the death. The recorded data are quite comprehensive and complete, including not only the deceased's basic demographic and personal data, but also the immediate, antecedent and accessory causes of death (see Table 1). - One copy of the DC is archived at the local public notary; while the other is sent out to the state-level institution responsible for health statistics. There, they are collected, coded, transferred to spreadsheets, compiled, and finally dispatched to the Federal Ministry of Health in Brasilia, the capital district, by means of paper records or magnetic tapes (in some states). - The DC data are subsequently verified and keyed in/transferred to a large computer mainframe at the Ministry of Health premises, where they remain stored in magnetic tapes and disks. From this large database, the Ministry's Division of Epidemiology produces annual bound volumes containing hundreds of standard mortality tables, which are distributed freely to end-users. It is also available for performing further, more refined or customized statistical analyses, using the own Ministry's computing resources, upon demand. Given this last possibility, the Ministry of Health is queried many times a year by regional and district investigators and authorities about more detailed mortality statistics. Unfortunately, this approach has many problems. Firstly, the Ministry computing resources are not geared to this kind of enquiry and do not offer on-line access or dedicated, user-friendly software for this purpose. The coding of customized searches and analyses take a great deal of effort, time, and personnel, which usually are not available, due to budgetary constraints. Geographic distances (which in Brazil are of a truly continental dimension) add to these hindrances. Furthermore, end-users are most of the time interested in analyzing only a much smaller subset of the original database; therefore an on-line solution would be a great waste of time and computing resources, with a resulting low benefit/cost ratio. After analyzing these aspects, and aiming at a different concept of database access by remote end-users, the National Division of Epidemiology of the Brazilian Federal Ministry of Health started a project, in collaboration with the Center of Biomedical Informatics of the State University of Campinas, State of S<176>o Paulo, the School of Public Health of the University of S<176>o Paulo, and the Latin-American and Caribbean Center for Information in Health Sciences, Pan-American Health Organization, S<176>o Paulo, to develop and distribute the following: - Computer-readable copies of the national mortality database, using a standard format, either in its entirety (by means of a CD-ROM (Compact Disk Read Only Memory) optical disk) or in logically-divided subsets (by means of magnetic 5 1/4" diskettes); both forms being readable by standard IBM-PC-compatible microcomputers; - A user-friendly software package for microcomputers, provided with the capability of simple statistical analysis of the distributed mortality database, such as the production of cross-tables, time series and graphs. A second specification for this software is that it should be fast enough to allow for interactive "playing with data" by users. The original concept was to provide database subsets selected at state-level to all the State Secretaries of Health in the country. Regional, district or county (municipality) levels would be provided on demand by interested parties, by a central distribution facility, located at the Ministry of Health. These subsets would not have all the variables recorded into the DC, but only the most important ones (see Table 1). The production of microcomputer-readable databases and diskettes was under the responsibility of the National Division of Epidemiology, through its Mortality Information System subdivision. The production of CD-ROMs with the full database was realized by the Computer Division of the Latin-American and Caribbean Center for Information in Health Sciences, which already had the necessary resources and extensive experience in producing CD-ROMs of medical literature databases (such as LILACS, a Latin-American subset of MEDLARS). The design, coding and testing of the microcomputer analysis software was under the responsibility of the Center for Biomedical Informatics of the State University of Campinas. Funding for development, training and distribution was provided by the Pan-American Health Organization, the Brazilian National Research Council (Ministry of Science and Technology), the State University of Campinas, and the Brazilian Ministry of Health. 2. The Mortality Database The mortality database was produced by downloading the mainframe- based records to a IBM-PC microcomputer connected to a micro-to- mainframe terminal emulator card. It comprised all the original DC records from 1979 to 1986, in a excess of six million records, and with a total size of almost 550 Mbytes. Two databases were produced: one, which was called Reduced Set, had the 14 most important variables recorded onto the DC. The other database, called Complete Set, had the full set of 39 variables (see Table 1). The recording format was a card-image, fixed-format numerically coded ASCII string (see Figure 1), each record separated from the following by an ASCII 13 (carriage return) 10 (newline) sequence, so that it could be read by the most common microcomputer software packages (SPSS, SAS, dBASE III, EPI-INFO, etc.). The database was subdivided into distinct files, each file holding all the records for a given year. The Reduced database was delivered to end-users without any kind of index files, while the Complete database included some externally-resident index files, by state and year. Furthermore, two auxiliary databases were produced using the same format: - a sequentially indexed file of all national 7-digit codes for place of death/place of living (coding region, state and municipality), with names in extenso. - a sequentially indexed file of 3-digit International Classification of Diseases (ICD, release 9) codes, with names in extenso. 3. The Microcomputer Software The microcomputer software was written in the compiled Turbo BASIC language (Borland International, Inc.), for a minimal hardware platform consisting of a MS-DOS-based 16-bit microcomputer, PC-XT compatible, with 640 Kbyte of RAM, a 5 1/4" 360 Kbyte diskette drive, a 20 Mbyte or larger hard disk, CGA video graphics and a graphical 80-column impact matrix printer. This configuration has been chosen because is still the most prevalent one in the health area, in Brazil, since it is the most inexpensive. However, the program can be run on more potent microcomputers, without modification. There is a separate version of the program for microcomputers fitted with a mathematical coprocessor. The two major required characteristics of the sofware were accomplished in the following way: - User friendliness was achieved through a simple, point-and-choose, menu-based text interface. All on-screen prompts are accompanied by brief explanation messages, so that no lengthy external manual is required. Furthermore, the code files for ICD and municipalities are useful to help the user locate or interpret some codes recorded into the database; - Speed was achieved through several programming techniques, such as non-indexed sequential text files, extensive record-read buffering, unformatted read, etc. Maximum performance was measured at ca. 130.000 records/minute in the simplest analytical mode (counting, Reduced database), with a 33 MHz PC 80386, mathematical coprocessor (30387) and data files loaded into virtual memory; what is actually quite good for a microcomputer-based application. This means, for exemple, that the entire national database for a recent year (about 1,2 million records) can be analyzed in less than 10 seconds. 3.1 Software Modules The main menu gives access to 8 functional modules: - Definition of the file set - Selective listing of the database - Simple frequency statistics and histogram - Cross tables analysis - Time series analysis and graphs - Review of previous analyses - Production of database subsets - Access to auxiliary code databases Each analysis session begins by selecting a set of data files. All files pertaining to a given geopolitical division have the same prefix (for example, SP19 means the 19th health region of the state of S<176>o Paulo) plus a variable suffix of two digits, denoting the year (in the same example, file SP1982 means the DC records for 1982). The user selects the desired suffix and a range of years. The list of variables is presented twice in each analysis module: the first to define the target variable(s) to list or analyze, and the second to define the selection criteria; which are specified by means of ON/OR combinations of ranges. The resulting graphs and tables may be sent, on user's choice, either to the screen, printer or disk file. In the last case, they can be easily incorporated into word processing files. The last analysis done can be reviewed in the screen, without the need to repeat them. The scheme of the database is stored into a separate ASCII configuration file. It contains the name of the fields, its length, admissible codes and ranges, etc. The same happens with a file containing error, warning and prompt messages used by the program. Thus, it is quite easy to customize the software for other idioms or mortality databases with different layouts. The program has also an option to produce other data files containing a subset of the distributed database, by means of selection criteria (e.g., a data file containing only infants of a given age bracket), which is useful to speed data analysis or to distribute data subsets. 4. Results The diskette-based subsets of the national mortality databases were distributed, as planned, to all 26 Brazilian State Secretaries of Health, where they are currently being employed for many purposes, but mainly for epidemiological investigations comprising the regions of interest of the Unified Health System offices. Moreover, an additional 80 copies of several customized subsets were distributed on demand to investigators and health authorities located in many cities, states, and even different countries. The database is usually distributed in compressed format (which a compression ratio of about 80 %), in several diskettes. The program files are distributed in another, separate diskete which also contains an installation program, very easy to use. This program copies program and data files to the hard disk, decompresses them and concatenates them together into yearly files. Except for the user prompting to insert diskettes sequentially, it is fully automatic. The CD-ROM-based full set of mortality records were distributed also to state authorities, medical and public health schools, as well as to investigators of other countries. Of the more than 200 copies produced, only about 30 were distributed, mainly because CD-ROM drives are not much disseminated in Brazil, and must be imported at higher costs than those usually found in developed countries. A new project is underway to fund a wider distribution of CD-ROMs, to update it with data on years 1987 to 1989, and to donate CD-ROM drivers to the user institutions. The place where the databases are most intensely used is in the School of Public Health of the University of S<176>o Paulo, where several theses, dissertations and research project were or are being made with the help of the mortality databases. 5. Results and Conclusions The present initiative was greeted with great enthusiasm on the part of the users of mortality data in our country. This can be ascertained by the unexpected large number of persons and institutions which asked for the database. Not only they were made independent of the delays and vagaries involved in getting statistical analyses from the central computing facility of the Ministry of Health, but they were also able to "play" freely with the data, performing as many exploratory data analyses as desired, in an interactive manner. The fast analyses times contributed to that. Investigations that were not previously pursued due to the difficulties in gaining access to data, suddenly became possible. Moreover, the long-term computing demand by external users on the Ministry was considerably reduced. Hardware requirements were kept simple and inexpensive, so that practically all users were able to find the proper resources. The easeness of operating the program, even by people without formal training in computing, contributed greatly to its popularity and real use. Training requirements were kept to a minimum. Since access time to the CD-ROM is quite large compared to that of a hard disk, the most common form of usage is to copy a subset from the CD-ROM into the microcomputer's disk, using one of the options embedded in the software. Another trend we have detected is that several users import easily the data to EPI-INFO, statistical packages, electronic spreadsheet and database management programs, in order to perform analyses not available in the software distributed by us. A formal evaluation of this project is underway. Meanwhile, it is clear to us that the experience was very valuable and that other countries could benefit from it, since it was able to demonstrate the viability of using microcomputers to analyze large bodies of mortality data, provided that a correct combination of distribution media and software performance is achieved. 6. Acknowledgments The authors acknowledge gratefully the support and encouragement provided by the following persons: Profs. Edmundo Juarez and Edmur Pastorello (School of Public Health, USP, formely at the Ministry of Health) and Abel Packer (Latin-American and Caribbean Center for Information in Health Sciences, Computing Division). 7. References [1] Proceedings of the Meeting on National Health Information Systems. Brasilia, DF, Brazil: Ministry of Health (in Portuguese), 1975. [2] Health Information Systems. Mortality Information Subsystem. Manual of Operational Procedures. Brasilia, DF, Brazil: Ministry of Health (in Portuguese), 1976. Table 1 Set of variables recorded in the Brazilian mortality database (* variable present in the reduced set) ----------------------------------------------------------- Number of the Death Certificate* Informant state Type of death* Month of recording Month of occurrence* Year of occurrence* Marriage status Sex* Age at time of death* Place of occurrence Region of occurrence* State of occurrence* Municipality of occurrence* Region of residence* State of residence* Municipality of residence* Occupation/profession Country of birth Degree of instruction Occupation of the father Degree of instruction of the father Occupation of the mother Degree of instruction of the mother Age of the mother Number of children born alive (for women) Number of children born dead (for women) Weeks of gestation (for perinatal deaths) Kind of pregnancy (for perinatal deaths) Parturition (for perinatal deaths) Weight at birth (for perinatal deaths) Medical assistance* Physician Auxiliary diagnostic procedures Surgery Necropsy Basic cause of death (ICD-9)* Type of violent death Work accident ------------------------------------------------------------ Published in: Proceed. 11th International Congress of the European Federation of Medical Informatics. London, Freund Publ., p. 518-522, 1993. (c) 1993 by Freund Publ. Co. Ltd., London