wiki:Hicdep_1.80Printable

Version 1 (modified by admin, 4 years ago) (diff)

Copy from Hicdep 1.70

This is a printer-friendly version of the HICDEP 1.70 specification available under http://www.hicdep.org/

HICDEP 1.70

This article describes the HICDEP version 1.70 which was released on the 8th of November 2013. For a more detailed version history, please refer to the ChangeLog.

General data format

The table pages referenced in the overview describe the specific tables' structure in detail and present a list of suggested codes, both standard and human readable.

All codes apart from trivial no, yes or unknown codes are presented as lookup tables, the usage of these are described in the the article Considerations for using the format to create a database.

Along with the basic structure described in each “Core fields” section, additional fields containing additional or more specific data are described in the “Additional fields” sections. These fields were taken from several cohort collaborations but with the required changes that were needed for the specific data structures. This is presented to the reader to show that the core structure is not a fixed proposal but rather a basic structure, which can be altered by adding fields.

Issues regarding duplicates are discussed in Considerations For Data Management.

Overview of data tables

Table Content
tblCEP holds type and date of clinical events and procedures including serious non-AIDS conditions. Former known as tblAE (adverse event).
tblART holds type of antiretroviral drug, start and stop dates and reason for stopping
tblBAS holds basic information such as demographics, basic clinical information and date of AIDS diagnosis
tblDELIVERY_CHILD holds delivery information related to the child
tblDELIVERY_MUM holds delivery information related to the mother
tblDIS holds type and date of CDC-C diseases.
tblLAB holds type, date, value and unit of laboratory tests.
tblLAB_BP holds date, diastolic and systolic values and unit of blood pressure measurements.
tblLAB_CD4 holds date and value of CD4 measurements.
tblLAB_RNA holds date, value, detection limit and type of viral assay.
tblLAB_RES holds background information on the resistance test, laboratory, library, kit, software and type of test
tblLAB_RES_LVL_1 holds nucleoside sequence for the PRO and RT sequences
tblLAB_RES_LVL_2 holds mutations and positions of these.
tblLAB_RES_LVL_3 holds resistance result in relation to antiretroviral drug.
tblLAB_VIRO holds test results for viro-/serological tests (hepatitis etc.)
tblLTFU holds data on death and drop-out
tblMED holds type, start and stop dates for other medication/treatments.
tblNEWBORN holds information related to newborns
tblNEWBORN_ABNORM holds information related to abnormalities of newborns
tblOVERLAP holds information on the patient's participation in other cohorts
tblPREG holds general pregnancy-related information
tblPREG_OBS holds information on obstetrical problems during pregnancy
tblPREG_OUT describes the pregnancy outcome
tblREFILL holds information on prescription refills
tblSAMPLES holds information on the storage of blood, urine and other biological samples
tblVIS holds visit related information such as weight, wasting, smoking, occupational status etc.

Diagram

Hicdep ER Top
Hicdep ER Middle
Hicdep ER Bottom

Structure of data

From flat files towards a normalized structure

The data collected in HIV collaborations is presented on the following pages in a set of data files/tables. Typically data would be put into one data file that would hold one line/record per patient where each field is represented as a separate column in that dataset. Often a dataset could contain more than 3000 columns of data.

The implication of going from thousands of fields to fewer fields means that data is in fact transposed from the flat format into the normalised format.

Example of a flat file structure:

PATIENT ALAT_D ALAT_V ALAT_U ASAT_D ASAT_V ASAT_U
999999 01-01-2000 15 U/l 01-01-2000 12 U/l

The normalised structure would then be like this:

PATIENT TYPE_ID LAB_DATE LAB_VAL LAB_UNIT
999999 1 01-01-2000 15 U/l
999999 2 01-01-2000 12 U/l

The type of measurement is identified through the TYPE_ID field. Here 1 codes for ALAT and 2 codes for ASAT:

Code Description
1 ALAT - Alanin-Aminotransferase
2 ASAT - Aspartat aminotransferase

Technical considerations

To enable a normalised structure that minimises the number of columns dramatically, the one file solution must be broken into several minor tables. These breakdowns are driven by the different data characteristics.

Each table has a basic structure that includes the patient identifier, a code that represents e.g. drug, adverse event or laboratory test performed. Along with this combination values like date, result, unit etc are present for each record.

A record for a laboratory measurement would include:

  • Patient identifier
  • Measurement type identifier
  • Measured value
  • Unit of value
  • Date of determination

A record for usage of an antiretroviral drug would include:

  • Patient identifier
  • Drug identifier
  • Start date for usage
  • End date for usage
  • Reason for discontinuation

These issues imply that a set of distinct tables must be generated based on the “nature” of the data. Since laboratory, medication and event data both cannot and should not be mixed at least 3 tables must be designed. Additionally there are other types of information that need their own domains: background information on the patient (height, birth date etc.), visit related data (weight, blood pressure, wasting etc.), and resistance testing (the latter requires more consideration due to the diversity of data present).

In this protocol further separation of data into different tables are presented. These separations are not only based on the rules for the relational model and normalisation, but they are “culturally” related.

For example: antiretroviral treatment medication is kept in one table and other medication in another table; CD4 cell measurements and HIV-RNA measurements are put into separate tables, that are also different from the general laboratory table. These separations are done simply because data in these tables are of distinct importance in analysis and often are gathered more frequently and with more attention than other variables.

Coding Conventions

Date codes

Although it is best to have precise dates in the format of YEAR-MONTH-DAY ISO standard, it might be that some cohorts are limited to representing date data at the level of the month only, or information kept on the patient in the charts only defines dates to the month and in some cases only to the year. To solve this a set of date codes are presented here.

Day unknown

In this case the date should be coded as the 15th of the month – so that 1999-12-?? becomes 1999-12-15. This enables the date to be no more than 15 days away from the actual date.

Month and day unknown

Best approach to this is to apply something similar, as with unknown dates, this would then mean that 1999-??-?? becomes 1999-07-01.

Year unknown

If the year is unknown but the presence of the date value is needed as in case of opportunistic infections or adverse events (see later in this document) a fictive date should be used that couldn’t be mistaken with an actual date. An unknown year should be coded as 1911-11-11.

Specification of precision

An alternative to the above is to apply an additional field to each date field for which it is known that there might be issues regarding the precision of the dates. The field is then used to specify at which degree of the day, month or year the date is precise:

Code Precision of date
< Before this date
D Exact to the date
M Exact to the month
Y Exact to the year
> After this date
U Unknown

ICD-10 codes

The coding system is the official standard for coding of diseases, however there is a wide set of “homebrew” codes used within the HIV field in data coding in general, often it’s a 3 or 4 letter codes that is an abbreviation for the AIDS defining disease. ICD-10 doesn’t have single codes that represent all single CDC-C events and as a consequence of this a list of 3 to 4 letter codes is the recommended way of coding for all CDC stage C events

ICD-10 codes are however the recommended for codes AE’s since it would become impossible for this protocol to maintain a complete list of all possible AE’s. ICD-10 is also recommended for causes of death.

ATC codes

ATC is a hierarchical structure for coding medication. The structure and hierarchy are best explained with an example of how a drug code is defined. Here it is on Indinavir:

J
ANTIINFECTIVES FOR SYSTEMIC USE (1st level, anatomical main group)
J05
ANTIVIRALS FOR SYSTEMIC USE (2nd level, therapeutic subgroup)
J05A
DIRECT ACTING ANTIVIRALS (3rd level, pharmacological subgroup)
J05AE
Protease inhibitors (4th level, chemical subgroup)
J05AE02
Indinavir (5th level, chemical substance)

This hierarchy has some benefits as will be explained later, but one of its limitations is that it’s impossible to “read” the code compared to the widely used 3 letter mnemonic codes for antiretroviral drugs.

Example:

Drug Code ATC code
Indinavir IDV J05AE02

The difference is that the IDV code is easily readable, where the ATC code is not; going from a flat file structure to a normalised structure the human readable aspect becomes increasingly important. In the flat file format the column names and the possibility of labels makes data more or less readable; in the normalised format only the coding can help. Because of this the 3 letter codes are being presented in this document. However it must be stressed that usage of the ATC coding should be used to diminish the risk of several homebrew and non-compatible coding schemes.

Currently however, the ATC scheme does not provide sufficient detail on the specific drugs, there is e.g. no official way to code Saquinavir as hard or soft gel. Thus a slight alteration to the set of codes will be presented in the sections of the ART and MED tables. The alterations are designed to extend the existing structure of ATC.

One of the benefits is that the structure of ATC allows easier statistics on e.g. drug classes

  • J05AE Protease inhibitors
    • J05AE01 Saquinavir
    • J05AE02 Indinavir
    • J05AE03 Ritonavir
    • J05AE04 Nelfinavir
    • J05AE05 Amprenavir
    • J05AE06 Lopinavir
  • J05AF Nucleoside and nucleotide reverse transcriptase inhibitors
    • J05AF01 Zidovudine
    • J05AF02 Didanosine
    • J05AF03 Zalcitabine
    • J05AF04 Stavudine
    • J05AF05 Lamivudine
    • J05AF06 Abacavir
    • J05AF07 Tenofovir disoproxil
    • J05AF30 Combinations1
  • J05AG Non-nucleoside reverse transcriptase inhibitors
    • J05AG01 Nevirapine
    • J05AG02 Delavirdine
    • J05AG03 Efavirenz

Although the codes might be harder to read they provide grouping mechanisms in the way they are coded. Interested readers should go to the ATC Website to learn about the structure of ATC. A fully updated database of ATC codes and DDD (Defined Daily Dosage) is available for querying.

Other codes

It is often necessary to code for values like “Yes”, “No” and “Unknown”, this document suggests that the following codes should be used:

Code Description
0 No
1 Yes
9 Unknown

Unknown should be used to identify the difference between a value that has not yet been collected (Empty) and a value that cannot be collected (Unknown). Empty values should be required where Unknown values make little sense to keep querying for a value.

Example – weight:

Depending on the unit in which weight is measured, a different value for Unknown should be applied. In the case of kg the “Unknown” code should be 999 and not just 9 or 99, the last two could be actual values.

Blank values, for SAS users also known as "." and for database programmers known as NULL, should be used wherever specified in this protocol. However, sometimes it might be more correct just to omit the record if no value has been recorded, test has not been performed etc.

Quality Assurance

In order to verify the consistency and correctness of the data, QA checks are made before the data is used. The QA checks applying to a given table are listed at the bottom of its article. Additionally, a list of all QA checks, including checks which do not directly apply to the HICDEP tables themselves, is available here.

tblBAS - Basic clinical, background and demographic information

holds basic information such as demographics, basic clinical information, date of AIDS diagnosis, death and drop-out information

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

CENTER

character

Code for Clinic/Centre/Hospital where patient is seen.

BIRTH_D

yyyy-mm-dd

Birth date

FRSVIS_D

yyyy-mm-dd

First seen at clinic

ENROL_D

yyyy-mm-dd

Date of enrolment into the cohort

GENDER

numeric:

  • 1 = Male
  • 2 = Female
  • 9 = Unknown

Gender/sex

HEIGH

numeric (metric): 999 = Unknown

Height of patient at visit/most current

MODE

numeric. see coding table for valid codings.

Mode of infection

ORIGIN

character (1-3 letter/numeric codes). see coding table for valid codings.

Nationality or region of origin of patient

ETHNIC

numeric. see coding table for valid codings.

Ethnicity of patient. Please take the additional notes into consideration when using this field.

EDU_LVL

numeric. see coding table for valid codings.

Last completed education Level. ISCED97 refers to the 1997 International Standard Classification of Education

HIV_POS_D

yyyy-mm-dd

Date of first positive HIV test

SEROCO_D

yyyy-mm-dd

Date of seroconversion

RECART_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Has the patient received antiretroviral treatment?

RECART_D

yyyy-mm-dd

Date ART started

LTART_D

yyyy-mm-dd

Date last assessed for ART. If started ART, last date known to be on ART, or if not on ART, last date ART free.

AIDS_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Has patient been given an AIDS diagnosis?

AIDS_D

yyyy-mm-dd

IF YES, date of AIDS diagnosis

Additional fields

For mode of infection and origin a set of other fields are often used to capture what cannot be coded. These fields are represented here as optional fields as it is the intention that the suggested codes applied to the MODE and ORIGIN should be able to cover all possible values.

Field name

Format

Description

MODE_OTH

character

Mode of infection OTHER

ORI_OTH

character

Origin of patient OTHER

CENS_D

yyyy-mm-dd

The date of administrative censoring; the last date the database has been updated for this patient

SEROHOW

numeric:

  • 1 = Midpoint between last neg/first pos test
  • 2 = Lab evidence of seroconversion
  • 3 = Seroconversion illness
  • 4 = Other
  • 9 = Unknown

For Seroconverters only: How was the seroconversion date determined?

NAIVE_Y

numeric:

  • 0 = No
  • 1 = Yes
  • 9 = Unknown

Is the patient ART-naïve upon enrollment?

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblBASWithinTableBW001AIDS date < SEROCO_DYES
tblBASWithinTableBW002Duplicate patientsYES
tblBASWithinTableBW003First 3 chars of PATIENT don't form valid cohort codeCascadeOnlyYES
tblBASWithinTableBW004Missing PATIENTYES
tblBASWithinTableBW005Missing CENTERYES
tblBASWithinTableBW006Missing BIRTH_DYES
tblBASWithinTableBW007Missing FRSVIS_DYES
tblBASWithinTableBW008Missing ENROL_DYES
tblBASWithinTableBW009Missing GENDERYES
tblBASWithinTableBW010Missing HEIGHYES
tblBASWithinTableBW011Missing MODEYES
tblBASWithinTableBW012Missing MODE_OTH if MODE=90YES
tblBASWithinTableBW013Missing ORIGINYES
tblBASWithinTableBW014Missing ETHNICYES
tblBASWithinTableBW015Missing SEROCO_DYES
tblBASWithinTableBW016Missing RECART_YYES
tblBASWithinTableBW017Missing AIDS_YYES
tblBASWithinTableBW018Missing AIDS_D if AIDS_Y=1YES
tblBASWithinTableBW019BIRTH_D out of range (15-85 yrs)CascadeOnlyYES
tblBASWithinTableBW020BIRTH_D out of range (<18)PENTAYES
tblBASCrossTableBC001RECART_Y=1 but no records in tblART YES
tblBASCrossTableBC002AIDS_Y=0, but AIDS-defining records in tblDISYES
tblBASCrossTableBC003AIDS_Y=1 but no AIDS-defining records in tblDIS tableYES

MODE field

containing table
tblBAS
explanation of variable
Mode of infection
format of data
numeric. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeMode of infection
1homo/bisexual
2injecting drug user
3(1+2)
4haemophiliac
5transfusion, non-haemophilia related
6heterosexual contact
7(6+2)
8Perinatal
90other, (specify)
99unknown

ORIGIN field

containing table
tblBAS
explanation of variable
Nationality or region of origin of patient
format of data
character (1-3 letter/numeric codes). see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

Region codes & country codes

Region Codes

UN Region Codes - Composition of macro geographical (continental) regions, geographical sub-regions, and selected economic and other groupings

save Download this table as CSV filesave Download this table as XML file
CodeRegion
001World
002Africa
014 - Eastern Africa
017 - Middle Africa
015 - Northern Africa
018 - Southern Africa
011 - Western Africa
019Americas
419 - Latin America and the Caribbean
029 - - Caribbean
013 - - Central America
005 - - South America
021 - Northern America
142Asia
143 - Central Asia
030 - Eastern Asia
034 - Southern Asia
035 - South-Eastern Asia
145 - Western Asia
150Europe
151 - Eastern Europe
154 - Northern Europe
039 - Southern Europe
155 - Western Europe
009Oceania
053 - Australia and New Zealand
054 - Melanesia
057 - Micronesia
061 - Polynesia

Country Codes

UN Country Codes - Countries or areas, codes and abbreviations, including translation from ISO ALPHA-3 codes.

save Download this table as CSV filesave Download this table as XML file
CodeCountryISO ALPHA-3 code
4AfghanistanAFG
248Åland IslandsALA
8AlbaniaALB
12AlgeriaDZA
16American SamoaASM
20AndorraAND
24AngolaAGO
660AnguillaAIA
28Antigua and BarbudaATG
32ArgentinaARG
51ArmeniaARM
533ArubaABW
36AustraliaAUS
40AustriaAUT
31AzerbaijanAZE
44BahamasBHS
48BahrainBHR
50BangladeshBGD
52BarbadosBRB
112BelarusBLR
56BelgiumBEL
84BelizeBLZ
204BeninBEN
60BermudaBMU
64BhutanBTN
68Bolivia (Plurinational State of)BOL
535Bonaire, Saint Eustatius and SabaBES
70Bosnia and HerzegovinaBIH
72BotswanaBWA
76BrazilBRA
92British Virgin IslandsVGB
96Brunei DarussalamBRN
100BulgariaBGR
854Burkina FasoBFA
108BurundiBDI
116CambodiaKHM
120CameroonCMR
124CanadaCAN
132Cape VerdeCPV
136Cayman IslandsCYM
140Central African RepublicCAF
148ChadTCD
830Channel Islands
152ChileCHL
156ChinaCHN
344China, Hong Kong Special Administrative RegionHKG
446China, Macao Special Administrative RegionMAC
170ColombiaCOL
174ComorosCOM
178CongoCOG
184Cook IslandsCOK
188Costa RicaCRI
384Côte d'IvoireCIV
191CroatiaHRV
192CubaCUB
531CuraçaoCUW
196CyprusCYP
203Czech RepublicCZE
408Democratic People's Republic of KoreaPRK
180Democratic Republic of the CongoCOD
208DenmarkDNK
262DjiboutiDJI
212DominicaDMA
214Dominican RepublicDOM
218EcuadorECU
818EgyptEGY
222El SalvadorSLV
226Equatorial GuineaGNQ
232EritreaERI
233EstoniaEST
231EthiopiaETH
234Faeroe IslandsFRO
238Falkland Islands (Malvinas)FLK
242FijiFJI
246FinlandFIN
250FranceFRA
254French GuianaGUF
258French PolynesiaPYF
266GabonGAB
270GambiaGMB
268GeorgiaGEO
276GermanyDEU
288GhanaGHA
292GibraltarGIB
300GreeceGRC
304GreenlandGRL
308GrenadaGRD
312GuadeloupeGLP
316GuamGUM
320GuatemalaGTM
831GuernseyGGY
324GuineaGIN
624Guinea-BissauGNB
328GuyanaGUY
332HaitiHTI
336Holy SeeVAT
340HondurasHND
348HungaryHUN
352IcelandISL
356IndiaIND
360IndonesiaIDN
364Iran (Islamic Republic of)IRN
368IraqIRQ
372IrelandIRL
833Isle of ManIMN
376IsraelISR
380ItalyITA
388JamaicaJAM
392JapanJPN
832JerseyJEY
400JordanJOR
398KazakhstanKAZ
404KenyaKEN
296KiribatiKIR
414KuwaitKWT
417KyrgyzstanKGZ
418Lao People's Democratic RepublicLAO
428LatviaLVA
422LebanonLBN
426LesothoLSO
430LiberiaLBR
434LibyaLBY
438LiechtensteinLIE
440LithuaniaLTU
442LuxembourgLUX
450MadagascarMDG
454MalawiMWI
458MalaysiaMYS
462MaldivesMDV
466MaliMLI
470MaltaMLT
584Marshall IslandsMHL
474MartiniqueMTQ
478MauritaniaMRT
480MauritiusMUS
175MayotteMYT
484MexicoMEX
583Micronesia (Federated States of)FSM
492MonacoMCO
496MongoliaMNG
499MontenegroMNE
500MontserratMSR
504MoroccoMAR
508MozambiqueMOZ
104MyanmarMMR
516NamibiaNAM
520NauruNRU
524NepalNPL
528NetherlandsNLD
540New CaledoniaNCL
554New ZealandNZL
558NicaraguaNIC
562NigerNER
566NigeriaNGA
570NiueNIU
574Norfolk IslandNFK
580Northern Mariana IslandsMNP
578NorwayNOR
275Occupied Palestinian TerritoryPSE
512OmanOMN
586PakistanPAK
585PalauPLW
591PanamaPAN
598Papua New GuineaPNG
600ParaguayPRY
604PeruPER
608PhilippinesPHL
612PitcairnPCN
616PolandPOL
620PortugalPRT
630Puerto RicoPRI
634QatarQAT
410Republic of KoreaKOR
498Republic of MoldovaMDA
638RéunionREU
642RomaniaROU
643Russian FederationRUS
646RwandaRWA
652Saint-BarthélemyBLM
654Saint HelenaSHN
659Saint Kitts and NevisKNA
662Saint LuciaLCA
663Saint-Martin (French part)MAF
666Saint Pierre and MiquelonSPM
670Saint Vincent and the GrenadinesVCT
882SamoaWSM
674San MarinoSMR
678Sao Tome and PrincipeSTP
680Sark
682Saudi ArabiaSAU
686SenegalSEN
688SerbiaSRB
690SeychellesSYC
694Sierra LeoneSLE
702SingaporeSGP
534Sint Maarten (Dutch part)SXM
703SlovakiaSVK
705SloveniaSVN
90Solomon IslandsSLB
706SomaliaSOM
710South AfricaZAF
728South SudanSSD
724SpainESP
144Sri LankaLKA
729SudanSDN
740SurinameSUR
744Svalbard and Jan Mayen IslandsSJM
748SwazilandSWZ
752SwedenSWE
756SwitzerlandCHE
760Syrian Arab RepublicSYR
762TajikistanTJK
764ThailandTHA
807The former Yugoslav Republic of MacedoniaMKD
626Timor-LesteTLS
768TogoTGO
772TokelauTKL
776TongaTON
780Trinidad and TobagoTTO
788TunisiaTUN
792TurkeyTUR
795TurkmenistanTKM
796Turks and Caicos IslandsTCA
798TuvaluTUV
800UgandaUGA
804UkraineUKR
784United Arab EmiratesARE
826United Kingdom of Great Britain and Northern IrelandGBR
834United Republic of TanzaniaTZA
840United States of AmericaUSA
850United States Virgin IslandsVIR
858UruguayURY
860UzbekistanUZB
548VanuatuVUT
862Venezuela (Bolivarian Republic of)VEN
704Viet NamVNM
876Wallis and Futuna IslandsWLF
732Western SaharaESH
887YemenYEM
894ZambiaZMB
716ZimbabweZWE

ETHNIC field

containing table
tblBAS
explanation of variable
Ethnicity of patient
format of data
numeric. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeEthnicity of patient
10White
20Black
21Black African
22Black Caribbean
30Hispanic
40Asian
50Americas
60Indigenous
10201+2
10401+4
20302+3
30403+4
98Prohibited
99Unknown

Limitations

The definition of ethnicity is complex and there is no ideal definition for all countries and for all times. As described by many authors, ethnicity is a fluid and imprecise concept heavily influenced by societal views. If definition of ethnicity is complex, inevitably its categorization will be complex too. The definition and categorization used in HICDEP acknowledges these limitations and aims by no means to solve the intense international debate of this issue. We suggest users to ask themselves “why is this variable necessary to answer my research question?” to avoid some of the common mistakes highlighted in the publications below.

  1. Ahdieh L, Hahn RA. Use of the terms ‘race’, ‘ethnicity’, and ‘national origins’: a review of articles in the American Journal of Public Health, 1980–1989. Ethnicity and Health 1996; 1:95–8
  2. Bhopal R. J. Epidemiol. Community Health 2004; 58:441–445
  3. Cooper RS, Kaufman JS, Ward R. Race and Genomics. N Engl J Med 2003; 348; 12: 1166-1170
  4. European Centre for Disease Prevention and Control. Improving HIV data comparability in migrant populations and ethnic minorities in EU/EEA/EFTA countries: findings from a literature review and expert panel. Stockholm: ECDC; 2011.www.ecdc.europa.eu

tblLTFU - Death and drop-out

holds data in death and drop-out

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

DROP_Y

numeric:

  • 1 = Yes
  • 0 = No

Has the patient DROPPED OUT?

DROP_D

yyyy-mm-dd

IF YES, Date of Last Visit

DROP_RS

numeric. see coding table for valid codings.

IF YES, Reason for DROP

DEATH_Y

numeric:

  • 1 = Yes
  • 0 = No

Has the patient died?

DEATH_D

yyyy-mm-dd

Date of Death

SUD_DEATH_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Sudden Death?

EXP_DEATH_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Expected Death?

AUTOP_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Was an autopsy Performed?

DEATH_R1

character. see coding table for valid codings.

Cause of death

DEATH_RC1

character with codes:

  • I = Immediate cause
  • U = Underlying cause/condition
  • C = Contributing cause
  • N = Not available

Coding of causal relation of the code given in DEATH_R1 to the death

DEATH_R2

character. see coding table for valid codings.

Cause of death

DEATH_RC2

character with codes:

  • I = Immediate cause
  • U = Underlying cause/condition
  • C = Contributing cause
  • N = Not available

Coding of causal relation of the code given in DEATH_R2 to the death

DEATH_R3

character. see coding table for valid codings.

Cause of death

DEATH_RC3

character with codes:

  • I = Immediate cause
  • U = Underlying cause/condition
  • C = Contributing cause
  • N = Not available

Coding of causal relation of the code given in DEATH_R3 to the death

DEATH_SOURCE

character

Source of information for coding of death (e.g. CoDe within own cohort, CoDe from D:A:D, CoDe from ART-CC, etc.)

List of DEATH_R# and DEATH_RC# should be continued for as many reasons that are recorded.

The DEATH_RC# fields should enable cohorts to transfer data in accordance with the Coding of Death project (CoDe). You are welcome to contact the CoDe group for electronic sample forms for detailed collection of data used for the CoDe review process.

CoDe defines 1 immediate, 2 contributing and 1 underlying cause of death.

Additional fields

Field name

Format

Description

ICD10_1

character

Cause of death as ICD-10 if available

ICD10_2

character

Cause of death as ICD-10 if available

ICD10_31

character

Cause of death as ICD-10 if available

ICD9_1?

character

Cause of death as ICD-9 if available

ICD9_2

character

Cause of death as ICD-9 if available

ICD9_31

character

Cause of death as ICD-9 if available

DEATH_OT

character

Reason for death – other - description

L_ALIVE

yyyy-mm-dd

Last date known to be alive

1: List of ICD10_# and ICD9_#inplace of or together with DEATH_R# and together DEATH_RC# and should be continued for as many reasons that are recorded.

CoDe defines 1 immediate, 2 contributing and 1 underlying cause of death.

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblLTFUWithinTableLFW001DROP_Y and DEATH_Y both non-nullYES
tblLTFUWithinTableLFW002DEATH_Y and DROP_RS both non-null YES
tblLTFUWithinTableLFW003Any of DEATH_Rx or DEATH_RCx non-null but DEATH_Y=0YES
tblLTFUWithinTableLFW004R2/RC2 non-null but R1/RC1 nullYES
tblLTFUWithinTableLFW005R3/RC3 non-null but R2/RC2 nullYES
tblLTFUWithinTableLFW006Duplicate patientsYES
tblLTFUWithinTableLFW007Missing DROP_YYES
tblLTFUWithinTableLFW008Missing DROP_D if DROP_Y=1YES
tblLTFUWithinTableLFW009Missing DROP_RS if DROP_Y=1YES
tblLTFUWithinTableLFW010Missing DEATH_YYES
tblLTFUWithinTableLFW011Missing DEATH_D if DEATH_Y=1YES
tblLTFUWithinTableLFW012DEATH_D non null but DEATH_Y=0YES
tblLTFUCrossTableLFC001Patient not found in tblBASYES
tblLTFUCrossTableLFC002patient in tblBAS hasn't got a record in tblLTFUYES
tblLTFUCrossTableLFC003tblBAS has AIDS=0 but DEATHRx =8.1 YES

DROP_RS field

containing table
tblLTFU
explanation of variable
IF DROP_Y = 1, Reason for DROP
format of data
numeric. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeReason for Drop Out
0Patient was not infected (mainly for children)
1Patient lost to follow-up / not known to be dead
2Patient has not had visit within required amount of time
2.1Patient did not respond to several invitations
3Patient moved away
3.1Patient moved to another country
4Patient moved and is followed by another centre
4.1Paediatric patient transferred to adult care
5Patients decision
5.1Patients caretaker wanted to discontinue (for children)
6Consent withdrawn
7Incarceration/jail
8Institutionalisation (drug treatment, psychological …etc.)
9Other

Note: If consent is withdrawn, all patient data except for the patient id and reason for drop out may have to be deleted.

DEATH_R1 field

containing table
tblLTFU
explanation of variable
Cause of death
format of data
character. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

The following table is adapted from page 23 of the Coding of Death (CoDe) specification at http://www.cphiv.dk/Portals/_default/pdf_folder/code_protocol_ver_1.0.pdf.

The codes 31 to 33.1, 04.xx and 92.1 are not present in CoDe.

The codes 04.* (non AIDS-defining malignancies) are tentative and subject to change for the next release.

Use the most specific coding available whenever possible.

save Download this table as CSV filesave Download this table as XML file
CodeCause of Death
01AIDS (ongoing active disease)
01.1Infection
01.2Malignancy
02Infection (other than 01.1)
02.1Bacterial
02.1.1Bacterial with sepsis
02.2Others
02.2.1Others with sepsis
02.3Unknown aetiology
02.3.1Unknown with sepsis
03Chronic viral hepatitis (progression of/complication to)
03.1HCV
03.1.1HCV with cirrhosis
03.1.2HCV with liver failure
03.2HBV
03.2.1HBV with cirrhosis
03.2.2HBV with liver failure
04Malignancy (other than 01.2 and 03, 03.1, 03.2)
04.03ANUS - Anal cancer
04.04BLAD - Bladder cancer
04.05BONE - Bone cancer
04.06BRAC - Brain cancer
04.07BRCA - Breast cancer
04.10.1ALL - Leukaemia: Acute lymphoid
04.10.2AML - Leukaemia: Acute myeloid
04.10.3CLL - Leukaemia: Chronic lymphoid
04.10.4CML - Leukaemia: Chronic myeloid
04.10.9LEUK - Leukaemia: unspecified
04.18COLO - Colon cancer
04.11COTC - Connective tissue cancer
04.12ESOP - Esophagus cancer
04.13GALL - Gallbladder cancer
04.14GYCA - Gynaecologic cancer
04.15HDL - Hodgkin lymphoma
04.16HENE - Head and neck (incl. face) cancers
04.17KIDN - Kidney cancer
04.19LIPC - Lip cancer
04.20LIVR - Liver cancer
04.21LUNG - Lung cancer
04.22MALM - Malignant melanoma
04.27MULM - Multiple myeloma
04.29PANC - Pancreas cancer
04.31PENC - Penile cancer
04.32PROS - Prostate cancer
04.33RECT - Rectum cancer
04.34STOM - Stomach cancer
04.35TESE - Testicular seminoma
04.36UTER - Uterus cancer
04.40.1MEAC - Metastasis: of adenocarcinoma
04.40.2MEOC - Metastasis: of other cancer type
04.40.3MESC - Metastasis: of squamuos cell carcinoma
04.40.9META - Metastasis: unspecified
04.90OTH - Other Malignancy Type
04.99UNKP - Unknown Malignancy Type
05Diabetes Mellitus (complication to)
06Pancreatitis
07Lactic acidosis
08MI or other ischemic heart disease
08.1AMI
08.1.1Definitive AMI (Dundee 1)
08.1.2Possible AMI (Dundee 2/9)
08.2Other ischemic heart disease
09Stroke
10Gastro-intestinal haemorrhage (if chosen, specify underlying cause)
11Primary pulmonary hypertension
12Lung embolus
13Chronic obstructive lung disease
14Liver failure (other than 03, 03.1, 03.2)
15Renal failure
16Accident or other violent death (not suicide)
17Suicide
18Euthenasia
19Substrance abuse (active)
19.1Chronic Alcohol abuse
19.2Chronic intravenous drug-use
19.3Acute intoxication
20Haematological disease (other causes)
21Endocrine disease (other causes)
22Psychiatric disease (other causes)
23CNS disease (other causes)
24Heart or vascular (other causes)
25Respiratory disease (other causes)
26Digestive system disease (other causes)
27Skin and motor system disease (other causes)
28Urogential disease (other causes)
29Obstetric complications
30Congenital disorders
31Symptoms caused by mitochondrial toxicity (other than 06, 07)
32Bleeding (haemophilia)
33Sudden infant death
33.1Child abuse
90Other causes
91Unclassifiable causes
92Unknown
92.1Unknown, Competing risks

tblOVERLAP - Cross-cohort identification

holds information on the patient's participation in other cohorts

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

COHORT

character

Code/name of the cohort

PAT_OTH

character

Unique patient identifier in other cohorts

COH_OTH

character

Name of the cohort

Patients of an "original"-cohort who also participate in a "super"-cohort should be analysed within the "original"-cohort only. To suppress these patients from the datasets of the "super"-cohorts the identifier used in the "super"-cohort is needed. It is suggested that "original"-cohorts report id's from the "super"-cohorts, since the "super"-cohorts might not even know the other ID’s. Often this information is only available at centre level.

A record should be present for each cohort that the patient is participating in (apart from it's own "original"-cohort).

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblOVERLAPWithinTableOW001Invalid other cohortYES
tblOVERLAPCrossTableOC001PATIENT not found in tblBAS for that cohortYES
tblOVERLAPCrossTableOC002PAT_OTH not found in tblBAS for that overlapping cohortYES

tblVIS - Basic follow-up/visit related data

holds visit related information such as weight, wasting, smoking, occupational status etc.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

VIS_D

yyyy-mm-dd

Date of patient visit

WEIGH

numeric (metric: kg): 999 = Unknown

Weight of patient at visit

GAIN_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Is the patient gaining fat in the abdomen, neck, breast or other defined locations?

LOSS_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Is the patient experiencing loss of fat from extremities, buttocks or face?

Depending on the collaboration this data might be collected in intervals of a year, e.g. from July last to July this year. In that case all visit dates or a fixed number of visit dates for that period should be gathered, if the patient did not have a visit in the defined period, a record with the PATIENT id and empty fields for VIS_D etc. should be included.

Additional fields

Field name

Format

Description

CDC_STAGE

character. see coding table for valid codings.

Clinical CDC stage at time of visit?

WHO_STAGE

numeric.

  • 1
  • 2
  • 3
  • 4
  • 9 = Unknown

Clinical WHO stage at time of visit?

The following fields are meant to be used for adolescents and adults.

Field name

Format

Description

EMPLOY

numeric. see coding table for valid codings.

What is the patient's current situation regarding labour?

CONTRACT

numeric. see coding table for valid codings.

If the patient is an employee, what is the type of the patient's employment contract?

SMOKING_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Is the patient currently a smoker?

PREG_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Is the patient currently pregnant? If possible, provide additional details in tblPREG

The following fields are meant to be used for children and infants.

Field name

Format

Description

HEIGH

numeric (metric). 999 = Unknown

Height/length of patient at visit

LIVEWITH

numeric. see coding table for valid codings.

Child lives with/in

HEALTHY_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Is child healthy?

The following fields are meant to be used for infants:

Field name

Format

Description

HEIGH_P

numeric

Height/length of patient at visit in percentiles

WEIGH_P

numeric

Weight of patient at visit in percentiles

HEADC

numeric

Head circumference measured in millimeters (mm)

HEADC_P

numeric

Head circumference in percentiles

BREASTF_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Currently Breastfeeding?

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblVISWithinTableVW001Duplicate records, same VIS_DYES
tblVISWithinTableVW002Height decreasing over time YES
tblVISWithinTableVW003Height out of acceptable rangeYES
tblVISWithinTableVW004Weight out of acceptable rangeYES
tblVISCrossTableVC001patient has no record in BAS tableYES
tblVISCrossTableVC002No weights within 3 mths of starting FPV/DRVEPPICC YES

tblART - Antiretroviral treatment

holds type of antiretroviral drug, start and stop dates and reason for stopping

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

identifies patient

ART_ID

character. see coding table for valid codings.

represents the antiretroviral treatment

ART_SD

yyyy-mm-dd

date of initiation of treatment

ART_ED

yyyy-mm-dd

date of stopping treatment

ART_RS

character. see coding table for valid codings.

reason for stopping treatment

Additional fields

Depending on the aim of the study it might be needed to gather both the dosage and the frequency of the dosage taken. However many cohorts do not collect this date and thus these fields are optional.

Field name

Format

Description

ART_DO

numeric

Dosage (mg or mL) per intake unless ART_FR=-1

ART_FR

numeric:

  • -1 = Frequency not known. ART_DO contains dosage per day
  • 0.33 = 1 dose every third day
  • 0.5 = 1 dose every second day
  • 1 = 1 daily dose/qd
  • 2 = 2 daily doses/bid
  • 3 = 3 daily doses/tid
  • 4... = code gives number of daily doses

Frequency

It may also be necessary to record the start and end time:

Field name

Format

Description

ART_ST

hh:mm

Start hour and minute of the day

ART_ET

hh:mm

Stop hour and minute of the day

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblARTWithinTableAW001ART_RS not null, but end date NULLYES
tblARTWithinTableAW002ART_RS null but end-date non NULLYES
tblARTWithinTableAW003ART_RS=98 yet ART_OTH is nullEPPICC NO
tblARTWithinTableAW004Duplicate records for same cohort, patient, art_id and art_sdYES
tblARTWithinTableAW005Dose out of range for those dose units and frequencyPaediatricOnlyYES
tblARTWithinTableAW006Missing art_fr PaediatricOnlyYES
tblARTWithinTableAW007Missing art_do PaediatricOnlyYES
tblARTWithinTableAW008Missing patient YES
tblARTWithinTableAW009Missing art_id YES
tblARTWithinTableAW010Missing art_sdYES
tblARTWithinTableAW011Overlapping periods of same drugYES
tblARTWithinTableAW012Double reporting - records reported for both combination drugs and their components YES
tblARTWithinTableAW013Periods of overlap of contra-indicated drugsYES
tblARTWithinTableAW014Restart of same drug without a stop YES
tblARTWithinTableAW015ART_SD greater than or equal to ART_EDYES
tblARTCrossTableAC001Patient has no record in table BASYES
tblARTCrossTableAC002Records exist in tblART yet RECART_Y=0 in tblBASYES

ART_ID field

containing table
tblART
explanation of variable
Code representing the antiretroviral treatment
format of data
character. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

A set of extended ATC codes are being presented here in order to code both more specific on subtypes of the drugs, e.g. saquinavir hard and soft gel, but also to enable coding of drugs that are at their trial stage and have not yet been assigned an ATC code. To do this the drug will be assigned the code elements as far down the levels as possible. Given two examples to illustrate this:

Saquinavir - Hard Gel
J05AE01-SQH
Saquinavir - Soft Gel
J05AE01-SQS
Saquinavir - not specified
J05AE01

This will ensure the fidelity needed to distinguish between hard and soft gel and not specified, but also for analysis easily include all records which coding starts with J05AE01, regardless if the drug is hard or soft gel.

See the ATC Index for the individual codes. The extended ATC codes are listed below along with a subset of ATC codes relevant to ART.

save Download this table as CSV filesave Download this table as XML file
Code (Extended ATC Codes)Anti-Retroviral Drugs
J05AART unspecified
J05A-BEVBeviramat
J05A-PBTParticipant in Blinded Trial
J05AEPI unspecified
J05AE-MOZMozenavir (DMP-450)
J05AE01Saquinavir (gel, not specified)
J05AE01-SQHSaquinavir hard gel (INVIRASE)
J05AE01-SQSSaquinavir soft gel (FORTOVASE)
J05AE02Indinavir (CRIXIVAN)
J05AE03Ritonavir (NORVIR)
J05AE03-HRitonavir high dose (NORVIR)
J05AE03-LRitonavir low dose (NORVIR)
J05AE04Nelfinavir (VIRACEPT)
J05AE05Amprenavir (AGENERASE)
J05AR10Lopinavir/Ritonavir (Kaletra). Former code: J05AE06
J05AE07Fos-amprenavir (Telzir, Lexiva)
J05AE08Atazanavir (Reyataz)
J05AE09Tipranavir (Aptivus)
J05AE10Darunavir (TMC-114, Prezista)
J05AFNRTI unspecified
J05AF-ALOAlovudine
J05AF-AMDAmdoxovir (DADP)
J05AF-FOZFozivudine tidoxi
J05AF-LDNLodenosine (trialdrug)
J05AF-RVTReverset
J05AF01Zidovudine (AZT, RETROVIR)
J05AF02Didanosine (ddI) (VIDEX)
J05AF03Zalcitabine (ddC) (HIVID)
J05AF04Stavudine (d4T) (ZERIT)
J05AF05Lamivudine (3TC, EPIVIR)
J05AF06Abacavir (1592U89) (ZIAGEN)
J05AF07Tenofovir (VIREAD)
J05AF08Adefovir (PREVEON)
J05AF09Emtricitabine
J05AF10Entecavir
J05AF11Telbivudine
J05AGNNRTI unspecified
J05AG04Etravirine (TMC 125)
J05AG05Rilpivirine (TMC-278)
J05AG-CPVCapravirine
J05AG-DPC083DPC 083
J05AG-DPC961DPC 961
J05AG-EMVEmivirine (MKC442)
J05AG-LOVLoviride
J05AG01Nevirapine (VIRAMUN)
J05AG02Delavirdine (U-90152) (RESCRIPTOR)
J05AG03Efavirenz (DMP-266) (STOCRIN, SUSTIVA)
J05AR01Combivir (Zidovudine/Lamivudine)
J05AR02Kivexa (Lamivudine/Abacavir)
J05AR03Truvada (Tenofovir/Emtricabine)
J05AR04Trizivir (Zidovudine/Lamivudine/Abacavir)
J05AR05Douvir-N (Zidovudine/Lamivudine/Nevirapine)
J05AR06Atripla (Emtricitabine/Tenofovir/Efavirenz)
J05AR07Triomune (Stavudine/Lamivudine/Nevirapine)
J05AR08Eviplera/Complera (Emtricitabine/Tenofovir/Rilpivirine)
J05AR09Stribild (Emtricitabine/Tenofovir/Elvitegravir/Cobicistat)
J05AR10Kaletra/Aluvia (Lopinavir/Ritonavir)
J05AX-VICVicriviroc (Schering)
J05AX07Enfuvirtide (Fuzeon, T-20)
J05AX08Raltegravir (Merck)
J05AX09Maraviroc (Pfizer)
J05AX11Elvitegravir
J05AX12Dolutegravir
L01XX05Hydroxyurea/Hydroxycarbamid (Litalir)
V03AX03Cobicistat

ART_RS field

containing table
tblART
explanation of variable
Reason for stopping treatment
format of data
character. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeReason for Stopping Treatment
1Treatment failure (i.e. virological, immunological, and/or clinical failure
1.1Virological failure
1.2Partial virological failure
1.3Immunological failure - CD4 drop
1.4Clinical progression
2Abnormal fat redistribution
3Concern of cardiovascular disease
3.1Dyslipidaemia
3.2Cardiovascular disease
4Hypersensitivity reaction
5Toxicity, predominantly from abdomen/G-I tract
5.1Toxicity - GI tract
5.2Toxicity - Liver
5.3Toxicity - Pancreas
6Toxicity, predominantly from nervous system
6.1Toxicity - peripheral neuropathy
6.2Toxicity - neuropsychiatric
6.3Toxicity - headache
7Toxicity, predominantly from kidneys
8Toxicity, predominantly from endocrine system
8.1Diabetes
9Haematological toxicity (anemia etc.)
10Hyperlactataemie/lactic acidosis
88Death
90Side effects - any of the above but unspecified
90.1Comorbidity
91Toxicity, not mentioned above
91.1Toxicity, unspecified
92Availability of more effective treatment (not specifically failure or side effect related)
92.1Simplified treatment available
92.2Treatment too complex
92.3Drug interaction
92.31Drug interaction - commencing TB/BCG treatment
92.32Drug interaction - ended TB/BCG treatment
92.33Change in eligibility criteria (e.g. child old enough for tablets; refrigerator no longer available)
92.4Protocol change
92.9Change in treatment not due to side-effects, failure, poor adherence or contra-indication
93Structured Treatment Interruption (STI)
93.1Structured Treatment Interruption (STI) - at high CD4
94Patient's wish/decision, not specified above
94.1Non-compliance
94.2Defaulter
95Physician's decision, not specified above
96Pregnancy
96.1Pregnancy intended
96.2Pregnancy ended
97Study treatment
97.1Study treatment commenced
97.2Study treatment completed
97.6Drug not available
98Other causes, not specified above
99Unknown

Although most reasons mentioned above could be coded in ICD-10 codes, it is still certain that many reasons will never be defined in terms of ICD-10 (STI, physicians and patients decision), so to avoid a mixture of two different coding systems it is advised to go with the above list and build upon that for now.

tblMED - Other medication

holds type, start and stop dates for other medication/treatments.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

MED_ID

character. see coding table for valid codings.

Code representing the treatment

MED_SD

yyyy-mm-dd

Date of Initiation of Treatment

MED_ED

yyyy-mm-dd

Date of stopping treatment

MED_RS

character. see coding table for valid codings.

reason for stopping treatment

Additional fields

Please see tblART - Antiretroviral treatment.

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblMEDWithinTableMW001Duplicate recordsYES
tblMEDWithinTableMW002MED_ONG=0 and MED_ED null EPPICC NO
tblMEDWithinTableMW003MED_ONG=1 and MED_ED non-null EPPICC NO
tblMEDWithinTableMW004Missing MED_IDYES
tblMEDWithinTableMW005Missing MED_SDYES
tblMEDWithinTableMW006Misisng MED_EDYES
tblMEDWithinTableMW007Overlapping periods of same drugYES
tblMEDWithinTableMW008MED_ED < MED_SDYES
tblMEDWithinTableMW009MED_RS not null, but end date NULLYES
tblMEDCrossTableMC001Patient has no records in tblBASYES

MED_ID field

containing table
tblMED
explanation of variable
Code representing the treatment
format of data
character. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

See also the notes on the extended ATC-Codes and the ATC Index.

Similar to the drugs listed below you can report any other non-ART medication with it's ATC-Code.

save Download this table as CSV filesave Download this table as XML file
Codes (Extended ATC-Codes)Other medication
A10AInsulin or derivatives hereof
A10BOral antidiabetic agents
A14AAnabolic steroids/appetite stimulants
B01ACAnti-platelets
C-HYPOther anti-hypertensive agents [C02, C03, C04, C07, C08]
C09ACE inhibitors
C10Lipid lowering agents
J01AA08Minocycline (MINOCIN)
J01EA01Trimethoprim (MONOTRIM, NOPIL)
J01EC02Sulfadiazine
J01EECotrimoxazole - Comb. of sulfonamides and trimethoprim (BACTRIM, EUSAPRIM, NOPIL)
J01EE01Sulfamethoxazole and trimethoprim (Bactrim)
J01EE03Sulfametrole and trimethoprim - Cosoltrime (MADERAN)
J01FA09Clarithromycine (KLACID)
J01FA10Azithomycine (ZITHROMAX)
J01FF01Clindamycine (DALACIN)
J01GB06Amikacine (AMIKINE)
J01MA02Ciprofloxacine (CIPROXINE, CILOXAN)
J01MA12Levofloxacin (TAVANIC)
J01MA14Moxifloxacin
J01RA02Cosoltrime (MADERAN)
J02AA01Amphotericin B (FUNGIZON)
J02ABImidazoles (DAKTARIN, NIZORAL, PEVARYL …)
J02AB02Ketoconazole
J02AC01Fluconazole (DIFLUCAN)
J02AC02Itraconazole (SPORANOX)
J02AC03Voriconazole
J02AC04Posaconazole
J02AX01Flucytosine
J04AB02Rifampin (RIMATICIN)
J04AB04Rifabutin (MYCOBUTIN)
J04AC01Isoniazide (RIMIFON)
J04AK01Pyrazinamide (PYRAZINAMID)
J04AK02Ethambutol (EMB, MYAMBUTOL)
J04AM05RIFATER
J04BA01Clofazimine (LAMPREN)
J04BA02Dapsone
J05AB01Aciclovir (ZIVORAX)
J05AB04Ribavirin
J05AB06Ganciclovir (CYMEVENE)
J05AB09Famciclovir
J05AB11Valaciclovir (VALTEX)
J05AB12Cidofovir (VISTIDE)
J05AB15Valganciclovir
J05AD01Foscarnet (FOSCAVIR)
J05AE12Boceprevir (VICTRELIS)
J05AE11Telaprevir (INCIVEK, INCIVO)
L01AA01Cyclophosphamide (ENDOXAN)
L01AD02CCNU (LOMUSTINE)
L01AX04Dacabazine (DTIC - Dome)
L01BA01Methotrexate
L01CA01Vinblastin (VELBE)
L01CA02Oncovin (VINCRISTINE)
L01CB01Etoposide (VEPESIDE, EXITOP 100)
L01DB01Doxorubicine, Adriamycine (DOXIL, CAELYX, ADRIBLASTIN)
L01DC01Bleomycine
L01XB01Procarbazine (NATULAN)
L03AA02G-CSF/Filgastrim (NEUPOGEN)
L03ABInterferons
L03AB-AL2Peginterferon alfa-2a/alfa-2b (PEGINTRON, PEGASYS)
L03AB10Peginterferon alfa-2b (PEGINTRON)
L03AB11Peginterferon alfa-2a (PEGASYS)
L03AC-IL2Interleukin 2 (PROLEUKIN)
P01AX06Atovaquone (WELLVONE, MEPRONE)
P01BA03Primaquine
P01BD01Pyrimethamine (DARAPRIM)
P01BD51Pyrimethamine/Sulfadoxine (FANSIDAR)
P01CX01Pentamidine aerosol (PENTACARNET)
V03AF03Folinate of calcium (LEUCOVORINE)
J01Antibiotics
H02Corticosteroids
G02CATocolysis

MED_RS field

containing table
tblMED
explanation of variable
Reason for stopping treatment
format of data
character. see coding table for valid codings.
exists since HICDEP version
1.70

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeReason for Stopping Treatment
1Treatment failure (i.e. virological, immunological, and/or clinical failure
1.1Virological failure
1.2Partial virological failure
1.3Immunological failure - CD4 drop
1.4Clinical progression
2Abnormal fat redistribution
3Concern of cardiovascular disease
3.1Dyslipidaemia
3.2Cardiovascular disease
4Hypersensitivity reaction
5Toxicity, predominantly from abdomen/G-I tract
5.1Toxicity - GI tract
5.2Toxicity - Liver
5.3Toxicity - Pancreas
6Toxicity, predominantly from nervous system
6.1Toxicity - peripheral neuropathy
6.2Toxicity - neuropsychiatric
6.3Toxicity - headache
7Toxicity, predominantly from kidneys
8Toxicity, predominantly from endocrine system
8.1Diabetes
9Haematological toxicity (anemia etc.)
10Hyperlactataemie/lactic acidosis
88Death
90Side effects - any of the above but unspecified
90.1Comorbidity
91Toxicity, not mentioned above
91.1Toxicity, unspecified
92Availability of more effective treatment (not specifically failure or side effect related)
92.1Simplified treatment available
92.2Treatment too complex
92.3Drug interaction
92.31Drug interaction - commencing TB/BCG treatment
92.32Drug interaction - ended TB/BCG treatment
92.33Change in eligibility criteria (e.g. child old enough for tablets; refrigerator no longer available)
92.4Protocol change
92.9Change in treatment not due to side-effects, failure, poor adherence or contra-indication
93Structured Treatment Interruption (STI)
93.1Structured Treatment Interruption (STI) - at high CD4
94Patient's wish/decision, not specified above
94.1Non-compliance
94.2Defaulter
95Physician's decision, not specified above
96Pregnancy
96.1Pregnancy intended
96.2Pregnancy ended
97Study treatment
97.1Study treatment commenced
97.2Study treatment completed
97.6Drug not available
98Other causes, not specified above
99Unknown

Although most reasons mentioned above could be coded in ICD-10 codes, it is still certain that many reasons will never be defined in terms of ICD-10 (STI, physicians and patients decision), so to avoid a mixture of two different coding systems it is advised to go with the above list and build upon that for now.

tblREFILL - Prescription refill data

holds drug, refill date and supply of prescriptions

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

REFILL_D

yyyy-mm-dd

Date of the prescription refill

DRUG_ID

character. see coding table for valid codings.

Drug which was prescribed

SUPPLY

numeric

How many days supply of the drug was supplied

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblREFILLCrossTablePRC001Patient doesn't have a record in BASYES
tblREFILLWithinTablePRW002Missing PATIENTYES
tblREFILLWithinTablePRW003Missing REFILL_DYES
tblREFILLWithinTablePRW004Missing DRUG_IDYES
tblREFILLWithinTablePRW005Missing SUPPLYYES
tblREFILLWithinTablePRW006SUPPLY < 1YES

DRUG_ID field

containing table
tblREFILL
explanation of variable
Drug which was prescribed
format of data
character. see coding table for valid codings.
exists since HICDEP version
1.70

Coding Table

ATC-Code of drug. See the MED_ID coding table and ART_ID coding table, and the ART_ID notes on extended ATC-Codes.

Wiki page "Hicdep_1.70/TableAe" does not exist

Wiki page "Hicdep_1.70/TableAe/FieldAeId" does not exist

Wiki page "Hicdep_1.70/TableAe/FieldAeId/CaseDefinitions" does not exist

Wiki page "Hicdep_1.70/TableAe/FieldAeSpec" does not exist

tblDIS - Opportunistic infections

holds type and date of CDC-C diseases.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

DIS_ID

character. see coding table for valid codings.

Code to identify event

DIS_D

yyyy-mm-dd

Date of event

DIS_WD

numeric. see coding table for valid codings.

Means of diagnosis

DIS_OTH1

character

Other location, only to be filled out if code alone is not sufficient

1 DIS_OTH might be part of the record's unique identification

Additional fields

Please see tblCEP for specification on optional fields.

Field name

Format

Description

DIS_ED

yyyy-mm-dd

End of opportunistic infection

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblDISWithinTableDW001Duplicate records for same DIS_ID and same DIS_DYES
tblDISWithinTableDW002Miscoded DIS_WD as codes on table definitionYES
tblDISWithinTableDW003Miscoded DIS_ID - as in code list attached to table definitionYES
tblDISWithinTableDW004DIS_D missingYES
tblDISWithinTableDW005DIS_ID missingYES
tblDISWithinTableDW006Same event recorded twice - 2 records, same DIS_ID, DIS_D within 6 monthsYES
tblDISWithinTableDW007DIS_ED present but before DIS_DYES
tblDISCrossTableDC001Patient has no record in BASYES
tblDISCrossTableDC002AIDS-defining records, yet AIDS=0 in tblBASYES
tblDISCrossTableDC003First AIDS-defining DIS_D not equal to AIDS_D in tblBASYES

DIS_ID field

containing table
tblDIS
explanation of variable
Code to identify event
format of data
character. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeSevere Opportunistic Infection/Malignancies
DEMAIDS dementia complex
BCNEBacterial pneumonia, recurrent (>2 episodes within 1 year)
CANOCandidiasis, oesophageal, bronchi, trachea, or lungs
COCCCoccidioidomycosis, disseminated or extrapulmonary
CRCOCryptococcosis, extrapulm.
CRSPCryptosporidiosis (duration > 1 month)
CMVRCytomegalovirus (CMV) chorioretinitis
CMVOCMV – other location
HERPHerpes simplex virus ulcers (duration > 1 month) or pneumonitis/esophagitis
HISTHistoplasmosis, extrapulm.
WASTHIV Wasting Syndrome
ISDIIsosporiasis diarrhoea (duration > 1 month)
MCMycobact. avium complex (MAC) or Kanasii, extrapulm.
MCPMycobact. tuberculosis pulm.
MCXMycobact. tuberculosis extrapulm
MCPOMycobact. pulm., other
MCXOMycobact. extrapulm., other
PCPPneumocystis carinii pneumonia (PCP)
LEUProgressive multifocal leucoencephalopathy
SAMSalmonella bacteriaemia (non-tyhpoid) (recurrent)
TOXToxoplasmosis, brain
FBLSFocal Brain lesion
KSKaposi Sarcoma
NHGNon-Hodgkin Lymphoma -not specified
NHGBNon-Hodgkin Lymphoma – Burkitt (Classical or Atypical)
NHGINon-Hodgkin Lymphoma – Diffuse large B-cell lymphoma (Immunoblastic or Centroblastic)
NHGUNon-Hodgkin Lymphoma - Unknown/other histology
NHGPNon-Hodgkin Lymphoma - Primary Brain Lymphoma
CRVCCervical Cancer

Coding Table: Mother-to-child and paediatric specific

save Download this table as CSV filesave Download this table as XML file
StageCodeCDC disease description
ACA-LYMLymphadenopathy
ACA-HEYHepatomegaly
ACA-SPLSplenomegaly
ACA-DERDermatitis
ACA-PARParotitis
ACA-URIRecurrent or persist. UR infection, sinusitis, or otitis media
BCB-ANEAnemia
BCB-BMPBacterial meningitis, pneumonia, or sepsis
BCB-CANCandidiasis oropharyngeal for >2 months (age>6)
BCB-CMYCardiomyopathy
BCB-CMNCMV onset before 1 month
BCB-DIADiarrhea (recurrent or chronic)
BCB-FEVFever (lasting >1 month)
BCB-HEPHepatitis
BCB-HSSHerpes simplex stomatitis (>2 episodes in 1 year)
BCB-HCVHSV bronchitis, pneumonitis, esophagitis (<1 month)
BCB-HZOHerpes zoster, multidermatomal or relapse
BCB-LEILeiomyosarcoma
BCB-LYMLymphoid interstitial pneumonia or pulmonary lymphoid hyperplasia complex
BCB-NEPNephropathy
BCB-NOCNocardiosis
BCB-TONToxoplasmosis (start before 1 month)
BCB-VARVaricella, disseminated
CCC-EPDPneumocystis disease, extrapulmonary
CCC-TODToxoplasmosis disseminated
CCC-COMCryptococcal meningitis
CCC-GENM. genavense disease
CCC-LOBLymphoma,primary, cerebral
CCC-ICCCarcinoma, cervical, invasive
CCC-ILEIntracerebral lesions, indeterminated

Case definitions

save Download this table as CSV filesave Download this table as XML file
CodeSevere Opportunistic Infection/MalignanciesDefinitive/Autopsy or presumptive?Definition
DEMAIDS dementia complexDDisabling cognitive and/or motor dysfunction, or milestone loss in a child, and no other causes by CSF exam and brain imaging or by autopsy
DEMAIDS dementia complexPSame as above but no CSF and brain imaging performed
BCNEBacterial pneumonia, recurrent (>2 episodes within 1 year)DNew X-ray evidence not present earlier and culture of pathogen that typically causes pneumonia (other than P .carinii or M. tuberculosis
BCNEBacterial pneumonia, recurrent (>2 episodes within 1 year)PAcute radiological findings (new X-ray evidence not present earlier) and acute clinical findings
CANOCandidiasis, oesophageal, bronchi, trachea, or lungsD/AGross inspection by endoscopy/autopsy or by microscopy (histology)
CANOCandidiasis, oesophageal, bronchi, trachea, or lungsPRecent onset retrosternal pain on swallowing and confirmed oral or pharyngeal candidiasis
CRCOCryptococcosis, extrapulm.D/AMicroscopy, culture of, or antigen detection in affected tissue
CRSPCryptosporidiosis (duration > 1 month)D/AMicroscopy. Duration of diarrhoea for more then 1 month
CMVRCytomegalovirus (CMV) chorioretinitisPLoss of vision and characteristic appearance on serial ophtalmoscopy, progressing over serial months
CMVOCMV – other locationD/AMicroscopy (histology or cytology)
HERPHerpes simplex virus ulcers (duration > 1 month) or pneumonitis/esophagitisDMicroscopy, culture of, or antigen detection in affected tissue
HISTHistoplasmosis, extrapulm.D/AMicroscopy, culture of, or antigen detection in affected tissue
WASTHIV Wasting SyndromeDWeight loss (over 10% of baseline) with no other cause, and 30 days or more of either diarrhoea or weakness with fever
ISDIIsosporiasis diarrhoea (duration > 1 month)D/AMicroscopy (histology or cytology). Duration of diarrhoea for more than 1 month
LEISLeishmaniasis, visceralD/AHistology or culture of Leishmania amastigotes in bone marrow or detection of amastigotes in tissue/fluid from affected organ in a patient with symptoms and signs consistent with disseminated Leishmaniasis
MCDIMicrosporidosis diarrhoes (dur. > 1 month)D/AStool microscopy or rectal biopsy in patient with persistent diarrhoea
MCMycobact. avium complex (MAC) or Kanasii, extrapulm.DCulture
MCPMycobact. tuberculosis pulm.DCulture
MCXMycobact. tuberculosis extrapulmDCulture
MCPOMycobact. pulm., otherDCulture (indicate type)
MCPOMycobact. pulm., otherPAcid fast bacteria (species not identified by culture) in sputum
MCXOMycobact. extrapulm., otherDCulture (indicate type)
MCXOMycobact. extrapulm., otherPAcid fast bacteria (species not identified by culture) on microscopy of normally sterile body fluid/tissue
PCPPneumocystis carinii pneumonia (PCP)DMicroscopy (histology or cytology)
PCPPneumocystis carinii pneumonia (PCP)PRecent onset of dyspnoea on exertion or dry cough, and diffuse bilateral infiltrates on chest X-ray and pO2 <70 mmHg and no evidence of bacterial pneumonia
LEUProgressive multifocal leucoencephalopathyD/AMicroscopy (histology or cytology)
LEUProgressive multifocal leucoencephalopathyPProgressive deterioration in neurological function and CT/MR scan evidence
SAMSalmonella bacteriaemia (non-tyhpoid) (recurrent)DCulture
TOXToxoplasmosis, brainDMicroscopy (histology/cytology)
TOXToxoplasmosis, brainPRecent onset focal neurological abnormalities or reduced level of consciousness, and mass effect lesion on scan, and specific therapy response
FBLSFocal Brain lesion?TODO:: To be updated ASAP
KSKaposi SarcomaD/AHistology
KSKaposi SarcomaPCharacteristic erythematous/violaceous plaque-like lesion on skin or mucous membranes
NHGNon-Hodgkin Lymphoma -not specified?TODO:: To be updated ASAP
NHGBNon-Hodgkin Lymphoma – Burkitt (Classical or Atypical)DHistology
NHGINon-Hodgkin Lymphoma – Diffuse large B-cell lymphoma (Immunoblastic or Centroblastic)DHistology
NHGUNon-Hodgkin Lymphoma - Unknown/other histology?TODO:: To be updated ASAP
NHGPNon-Hodgkin Lymphoma - Primary Brain LymphomaDTODO:: To be updated ASAP
NHGPNon-Hodgkin Lymphoma - Primary Brain LymphomaPRecent onset of focal neurological symptoms and signs or reduced level of consciousness, CT/MR scan evidence of a lesion or lesions having mass effect, no response to toxo therapy, no evidence of lymphoma outside the brain
CRVCCervical CancerD/AHistology

DIS_WD field

containing table
tblDIS
explanation of variable
Means of diagnosis
format of data
numeric. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeMeans of diagnosis
1Definitive diagnosis
2Presumptive diagnosis
3Diagnosis from autopsy
4Diagnosis from registry

tblLAB_CD4 - Laboratory values

holds date and value of CD4 measurements.

Note: If needed, a CD8 table (tblLAB_CD8) could be formed from the same structure.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

CD4_D

yyyy-mm-dd

Date of measurement

CD4_V

numeric (per microliter): -1 = undetectable or detection limit as negative value

Value of CD4 measurement

Additional fields

CD4_V is assumed to contain absolute CD4 cell counts per mL as standard. In case CD4 % (with respect to CD45+ lymphocytes as denominator) should be collected as well, please append the following field to the table:

Field name

Format

Description

CD4_U

numeric with codes (or full string):

  • 1 = cells/µl
  • 2 = %

Unit of measurement

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblLAB_CD4WithinTableCW001CD4 value out of rangeYES
tblLAB_CD4WithinTableCW002Duplicate records for same dateYES
tblLAB_CD4WithinTableCW003Missing CD4_DYES
tblLAB_CD4WithinTableCW004Missing CD4_VYES
tblLAB_CD4WithinTableCW005Missing CD4_UYES
tblLAB_CD4WithinTableCW006Miscoded CD4_U as coding list on table definitionYES
tblLAB_CD4WithinTableCW007CD4_U=2 (percentage) and CD4_V>100YES
tblLAB_CD4WithinTableCW008CD4_U=(1 or 3) and CD4_V>3000YES
tblLAB_CD4WithinTableCW009CD4 counts spike up or down suddenly (large change in less than a year)YES
tblLAB_CD4CrossTableCC001Patient has no record in BASYES

tblLAB_RNA - Laboratory values

holds date, value, detection limit and type of viral assay.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

RNA_D

yyyy-mm-dd

Date of Measurement/Sample

RNA_V

numeric: -1 = undetectable or detection limit as negative value

HIV-RNA measurement value

RNA_L

numeric

Lower Limit of HIV-RNA Assay

RNA_T

numeric. see coding table for valid codings.

IF AVAILABLE, What type of VIRAL ASSAY was used for this measurement?

Additional fields

Field name

Format

Description

RNA_UL

numeric

IF AVAILABLE, Upper Limit of assay

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblLAB_RNAWithinTableRW001Duplicate records for same RNA_DYES
tblLAB_RNAWithinTableRW002RNA_V < 0 and RNA_L missingYES
tblLAB_RNAWithinTableRW003RNA_V > 10 000 and (RNA_V modulo 1000) = 1 and RNA_UL missingYES
tblLAB_RNAWithinTableRW004Missing RNA_D YES
tblLAB_RNAWithinTableRW005Missing RNA_V YES
tblLAB_RNAWithinTableRW006RNA_V > 10 millionYES
tblLAB_RNAWithinTableRW007RNA_V < RNA_L and RNA_V >= 0YES
tblLAB_RNAWithinTableRW008RNA_V > RNA_ULYES
tblLAB_RNACrossTableRC001Patient has no record in BASYES

RNA_T field

containing table
tblLAB_RNA
explanation of variable
IF AVAILABLE, What type of VIRAL ASSAY was used for this measurement?
format of data
numeric. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeViral assay used
5Roche TaqMan
10Roche 1.0
15Roche 1.5 ultra-sensitive
19Any Roche (unspecified)
20NASBA
21NASBA ultra-sensitive
29Any NASBA (unspecified)
31Chiron b-DNA 1.0
32Chiron b-DNA 2.0
33Chiron b-DNA 3.0
39Any Chiron (unspecified)
40Abbott ultra-sensitive
41Abbott LCx
42Abbott RealTime HIV-1 m2000
50Monitor 1.0
51Monitor 1.0 ultra-sensitive
55Monitor 1.5
56Monitor 1.5 ultra-sensitive
59Monitor unspecified
65Cobas 1.5
66Cobas 1.5 ultra-sensitive
90Other
99Unknown

tblLAB_VIRO - Laboratory values - viro-/serology

holds test results for viro-/serological tests (hepatitis etc.)

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

VS_ID

character. see coding table

Viral test

VS_D

yyyy-mm-dd

Measurement date

VS_R

numeric:

  • 1 = Positive
  • 0 = Negative
  • 9 = Unknown/borderline

Measurement result

VS_V

numeric

Measurement value (HCV-RNA & HBV-DNA only) (copies/ml)

VS_U

character. see coding table for valid codings.

Measurement unit

Additional fields

Field name

Format

Description

VS_LL

numeric

IF AVAILABLE, Lower limit of assay

VS_UL

numeric

IF AVAILABLE, Upper limit of assay

VS_T

character. see coding table for valid codings.

IF AVAILABLE, type of ASSAY used for this measurement

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblLAB_VIROCrossTableLVC001Patient doesn't have a record in BASYES
tblLAB_VIROWithinTableLVW002Missing patient YES
tblLAB_VIROWithinTableLVW003Missing VS_IDYES
tblLAB_VIROWithinTableLVW004Missing VS_DYES
tblLAB_VIROWithinTableLVW005Missing VS_RYES
tblLAB_VIROWithinTableLVW006Missing VS_VYES
tblLAB_VIROWithinTableLVW007Missing VS_UYES
tblLAB_VIROWithinTableLVW008More or less than exactly 1 positive HIV test per patientYES
tblLAB_VIROWithinTableLVW009More than 1 negative HIV test for a patientYES
tblLAB_VIROWithinTableLVW010Date of negative test after date of positive testYES
tblLAB_VIROWithinTableLVW011Duplicate recordsYES

VS_ID field

containing table
tblLAB_VIRO
explanation of variable
Viral test
format of data
character. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeViral Test
BVABacterial vaginosis unspecified method
BVACBacterial vaginosis - clinical
BVAGBacterial vaginosis - gram stain
CHLAChalmydia
CMVACMV anitbodies
GONOGonorrhoe
HBVMarker for hepatitis B infection (=HBVAC) - test unknown
HBVACHBV antibody (core)
HBVACIGMHBV antibody (core IgM)
HBVACIGGHBG antibody (core IgG)
HBVAEHBV antibody (envelope)
HBVASHBV antibody (surface)
HBVDHBV-dna
HBVGEHBV antigen (envelope)
HBVGSHBV antigen (surface)
HCVMarker for hepatitis C infection - test unknown
HCVAHCV antibody
HCVGHCV antigen
HCVBDHCV b-dna
HCVRHCV-rna
HDVAHepatitis delta antibody
HIV-1HIV-1 test
HIV-2HIV-2 test
HIVAEHIV antibodies ELISA
HIVAWBHIV antibodies Western blot
MYCOMycoplasma
P24AGP24 Ag
RUBRubella
STRStreptococcus, group B
TOXAToxo antibodies
UREPUreaplasma

VS_U field

containing table
tblLAB_VIRO
explanation of variable
Measurement unit
format of data
character. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeTest measurement unit
1copies/mL
2IU/mL
3Geq (millions of genome equivalent)

VS_T field

containing table
tblLAB_VIRO
explanation of variable
IF AVAILABLE, the type of ASSAY used for this measurement
format of data
character. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeViral test used
1Roche qualitative (Amplicor) [HCV and HBV]
2Roche quantitative test for HBV (Cobas Amplicor HBV monitor)
3Bayer Bdna quantitative [HCV]
4Bayer Bdna quantitative [HBV]
5Roche Taqman
6Abbott Real Time [HCV and HBV]
7Siemens VERSANT [HCV and HBV] DNA (bNA)
8Quiagen artus [HCV and HBV] PCR kit
9Other

tblLAB_BP - Laboratory values - Blood pressure

holds date, diastolic and systolic values and unit of blood pressure measurements.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

BP_D

yyyy-mm-dd

Date of Measurement/Sample

BP_SYS

numeric

Systolic Blood Pressure

BP_DIA

numeric

Diastolic Blood Pressure

BP_U

numeric. see coding table for valid codings.

Unit of measurement

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES

BP_U field

containing table
tblLAB_BP
explanation of variable
Unit of measurement
format of data
numeric. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeUnit for blood pressure
1mmHg
2cmHg
3Kpa

tblLAB - Laboratory values

holds type, date, value and unit of laboratory tests.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

LAB_ID

character. see coding table for valid codings.

Code representing the measurement

LAB_D

yyyy-mm-dd

Date of measurement/sample

LAB_V

numeric: -1 = undetectable or detection limit as negative value

for dipstick results:

  • 0 = Negative
  • 9 = Trace
  • 1 = 1+
  • 2 = 2+
  • 3 = 3+
  • 4 = 4+

Value of measurement

LAB_U

numeric. see coding table for valid codings.

Unit of measurement

Additional fields

Other detailed information regarding the patient and the measurement would be relevant, like the proposed fasting information shown below.

Field name

Format

Description

LAB_FA

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Was the blood sample taken while fasting?

LAB_ST

  • WB = Whole Blood
  • P = Plasma
  • S = Serum
  • U24 = 24h Urine
  • U = Urine

Specimen type

Depending on the set of measurements collected and the mandatory fields applicable to these individual measurements, it might be useful to separate the LAB table into several sub tables. This is already shown for the CD4 and RNA measurements: the level of detail needed for CD4 is less than for the LAB variables in general (no unit since it’s always the same), while for RNA the data required is more detailed (assay and detection limit).

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblLABWithinTableLW001Duplicate recordsYES
tblLABWithinTableLW002Missing LAB_DYES
tblLABWithinTableLW003Missing LAB_VYES
tblLABWithinTableLW004Missing LAB_UYES
tblLABWithinTableLW005Missing LAB_IDYES
tblLABWithinTableLW008Missing LAB_FAYES
tblLABWithinTableLW009Missing LAB_STYES
tblLABWithinTableLW010LAB_V out of range for unit LAB_UYES
tblLABCrossTableLC001Patient has no record in BAS tableYES

LAB_ID field

containing table
tblLAB
explanation of variable
Code representing the measurement
format of data
character. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
CodeMeasurement
ALBAlbumine
AFPAlfa Fetoprotein
ALPAlkaline Phosphatase
ALTAlanin-Aminotransferase
AMYAmylase
ASTAspartat aminotransferase
BILTotal Bilirubin
CD3CD3
CD3P% CD3 of leukocytes
CD8CD8
CD8P% CD8 of leukocytes
CHOLTotal Cholesterol
CL-Cl-
CRECreatinine
DIPPDipstick result for protein in Urine
GGTGamma-glutamyltransferase
GLUCGlucose
HAEMHaemoglobin
HDLSerum HDL
HEMAHematocrit
INRQuick/INR
LACTLactate
LEUKLeukocytes
LYMLymphocytes
LYMP% Lymphocytes of leukocytes
MCVMCV
NA+Na+
NEUNeutrophils
PHAPH arterial
PHVPH venous
PPPP factor (II, VII, X)
PTRProthrombin rate
THRThrombocytes
TRIGSerum Triglyceride
URAUric acid

LAB_U field

containing table
tblLAB
explanation of variable
Unit of measurement
format of data
numeric. see coding table for valid codings.
exists since HICDEP version
1.30

Coding Table

save Download this table as CSV filesave Download this table as XML file
Unit CodeUnit String
1mmol/L
2g/L
3g/dL
4mg/dL
5IU/L (u/L)
6µmol/L
7INR
81E+9/L
91E+6/L
10cells/µL
11µkat/L
12%
13µg/L
99no units (e.g. for Dipstick results)

It is recommended to use the string codes from the above table since this makes the data human readable.

tblSAMPLES - Blood Samples

This table contains information on the storage of blood, urine and other samples stored in a laboratory.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

patient cohort identifier

SAMP_LAB_D

yyyy-mm-dd

date when the sample was taken

SAMP_TYPE

character:

  • BS = blood serum
  • BP = blood plasma
  • C = viable cells
  • D = cell pellet (DNA)
  • S = semen
  • OTH:x = other sample type x (none of the above)

type of the sample

SAMP_ID

character

identification symbol allowing the localization of the sample in the laboratory

SAMP_LAB

character

laboratory where the samples are stored

SAMP_FREEZE_D

yyyy-mm-dd

date when the sample was frozen

SAMP_FREEZE_T

hh:mm

time when the sample was frozen

SAMP_ALIQ_NO

numeric

number of aliquots available

SAMP_ALIQ_SIZE

numeric

size of the aliquot:

  • in ml for serum, plasma and cell pellet aliquots
  • in millions of cells for viable cell aliquots

SAMP_ALIQ_U

character:

  • 0 = millions of cells
  • 1 = ml

unit of measurement for the SAMP_ALIQ_SIZE value

Additional fields

Field name

Format

Description

SAMP_LAB_T

hh:mm

time when the sample was taken

SAMP_TEMP

numeric

temperature of the storage unit containing the samples (in °C)

SAMP_DEFROST

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

have the samples already been defrosted?

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES

tblLAB_RES - Resistance testing

holds background information on the resistance test, laboratory, library, kit, software and type of test

Note: This table is tightly linked to tblLAB_RES_LVL_1, tblLAB_RES_LVL_2 and tblLAB_RES_LVL_3.

Resistance should be reported at lowest level of interpretation possible – so if the nucleotide sequence is available this should be reported rather than the list of mutations or resistance scores. However, the resistance test results should be captured if they have been part of the physician’s treatment decisions for the patient.

Non-amplifiable resistance tests should not be reported.

These four tables are designed to capture several possible formats the clinics and cohorts might have recorded resistance test data in. Once this data is gathered it should like all other tables be quality assessed.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

PATIENT

character (or numeric if possible)

Code to identify patient (Cohort Patient ID)

TEST_ID

character (or numeric if possible)

An arbitrary value identifying a resistance test result

SAMPLE_D

yyyy-mm-dd

Date of the actual sample taken (NOT the test date)

SEQ_DT

yyyy-mm-dd hh:mm

Date and time when the sequencing was performed

LAB

character

Name of laboratory where the test was performed

LIBRARY

character

Library/algorithm used to identify resistance mutations

REFSEQ

character

Name/identifier of reference strain used to find mutations

KIT

character

Vendor and version/name of the kit used for the test

SOFTWARE

character

Software and version used to determine resistance

TESTTYPE

numeric:

  • 1 = Genotype
  • 2 = Phenotype
  • 9 = Other

Type of test

VIRUSTYPE

numeric:

  • 1 = HIV
  • 2 = HCV

Type of Virus

SUBTYPE

character

Subtype of HIV- or HCV-RNA

Additional fields

As shown with the core fields, the TEST_ID is the link between the 3 levels of data and the test background information table.

Some prior assessment of the assigned test identifiers has to be done in order to avoid duplicates.

In a running database the duplicate issues are easily resolved by adding a unique auto-generated key as the identifier between 3 levels of data and the test background information table.

Along with the TEST_ID it might be necessary to store the ID assigned to the sample at both the testing laboratory but also the centres laboratory in order to track the sample.

Field name

Format

Description

SAMP_LAB

character (or numeric if possible)

The assigned sample ID at the lab where the resistance test is preformed.

SAMP_INT

character (or numeric if possible)

The assigned sample ID from the centre.

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblLAB_RESWithinTableLRW001Duplicate records for same patient on same dateYES
tblLAB_RESWithinTableLRW002Missing PATIENTYES
tblLAB_RESWithinTableLRW003Missing TEST_IDYES
tblLAB_RESWithinTableLRW004Missing SAMPLE_DYES
tblLAB_RESWithinTableLRW005Missing SEQ_DTYES
tblLAB_RESWithinTableLRW006Missing LABYES
tblLAB_RESWithinTableLRW007Missing LIBRARYYES
tblLAB_RESWithinTableLRW008Missing REFSEQYES
tblLAB_RESWithinTableLRW009Missing KITYES
tblLAB_RESWithinTableLRW010Missing SOFTWAREYES
tblLAB_RESWithinTableLRW011Missing TESTTYPEYES
tblLAB_RESWithinTableLRW012Missing SUBTYPEYES
tblLAB_RESWithinTableLRW013SEQ_DT has no time partYES
tblLAB_RESWithinTableLRW014Missing VIRUSTYPEYES
tblLAB_RESCrossTableLRC001PATIENT has no record in tblBASYES
tblLAB_RESCrossTableLRC002This TEST_ID has both LVL_1 and LVL_2 recordsYES

tblLAB_RES_LVL_1 - Nucleotide sequences (PRO, RT, GP41, GP120)

holds nucleoside sequence for the PRO and RT sequences. No entry is made if the test was a phenotype test.

Note: This table is tightly linked to tblLAB_RES.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

TEST_ID

character (or numeric if possible)

Identifier linking this record to tblLAB_RES

SEQTYPE

character:

  • PRO = PRO sequence
  • RT = RT sequence
  • GP41 = GP41 sequence
  • GP120 = GP120 sequence

Type of nucleotide sequence if available

SEQ_START

numeric

Start position for the sequence

SEQ_STOP

numeric

Stop position for the sequence

SEQ_NUC

character

Nucleotide sequence if available

Additional fields

In cases where the amino acid sequence is collected rather than the nucleotide sequence, the field SEQ_NUC might be replaced with SEQ_AA, which is the nucleotide sequence, expressed in an amino acid sequence:

Field name

Format

Description

SEQ_AA

character

Amino acid sequence if available (empty if test was phenotype)

However using the amino acid sequence does not give the same detail of data as the nucleoside sequence: wobbles in the nucleoside sequence can either complicate the reading and alignment of the amino acid sequence or the wobbles can be lost and silent mutations are lost.

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblLAB_RES_LVL_1WithinTableL1W001Duplicate records per TEST_ID and SEQTYPEYES
tblLAB_RES_LVL_1WithinTableL1W002SEQ_START > SEQ_STOPYES
tblLAB_RES_LVL_1WithinTableL1W003SEQ_NUC contains invalid IUPAC characterYES
tblLAB_RES_LVL_1WithinTableL1W004Missing TEST_IDYES
tblLAB_RES_LVL_1WithinTableL1W005Missing SEQ_STARTYES
tblLAB_RES_LVL_1WithinTableL1W006Missing SEQ_STOPYES
tblLAB_RES_LVL_1WithinTableL1W007Missing SEQ_NUCYES
tblLAB_RES_LVL_1CrossTableL1C001TEST_ID not in tblLAB_RES.TEST_IDYES

tblLAB_RES_LVL_2 - Mutations

holds mutations and positions of PRO and RT sequences.

Note: This table is tightly linked to tblLAB_RES.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

TEST_ID

character (or numeric if possible)

Identifier linking this record to tblLAB_RES

GENE

character:

  • PRO = PRO sequence
  • RT = RT sequence
  • GP41 = GP41 sequence
  • GP120 = GP120 sequence

Type of sequence/gene (PRO, RT, GP41, GP120)

AA_POS

numeric

Position of the mutation in the sequence

AA_POS_SUB

character:

  • a = first
  • b = second
  • etc.

Subposition used to code insertions

AA_FOUND_1

character. empty = Amino acid has been deleted.

Mutation (Amino acid) found in the sequence

AA_FOUND_2

character. empty = Amino acid has been deleted.

Mutation (Amino acid) found in the sequence (if more than 1)

AA_FOUND_3

character. empty = Amino acid has been deleted.

Mutation (Amino acid) found in the sequence (if more than 2)

AA_FOUND_4

character. empty = Amino acid has been deleted.

Mutation (Amino acid) found in the sequence (if more than 3)

AA_FOUND_# could be extended if mixtures with more than 4 amino acids are found.

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblLAB_RES_LVL_2WithinTableL2W001AA_FOUND_x but nothing in AA_FOUND(x-1)YES
tblLAB_RES_LVL_2WithinTableL2W002Duplicate records per TEST_ID, GENE, AA_POS and AA_POS_SUBYES
tblLAB_RES_LVL_2WithinTableL2W003Missing TEST_IDYES
tblLAB_RES_LVL_2WithinTableL2W004Missing GENEYES
tblLAB_RES_LVL_2WithinTableL2W005Missing AA_POSYES
tblLAB_RES_LVL_2WithinTableL2W006Missing AA_POS_SUBYES
tblLAB_RES_LVL_2WithinTableL2W007Missing AA_FOUND_1YES
tblLAB_RES_LVL_2CrossTableL2C001TEST_ID not in tblLAB_RES.TEST_IDYES

tblLAB_RES_LVL_3 - Resistance test result

holds resistance result in relation to antiretroviral drug.

Note: This table is tightly linked to tblLAB_RES.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

TEST_ID

character (or numeric if possible)

Identifier linking this record to tblLAB_RES

ATC_CODE

character

ATC code of the medication

RES_SCOR

character

Score of resistance or recommendation given from the test.

Additional fields

For phenotype test results it will be necessary to extend the table with a field to store the cut-off value:

Field name

Format

Description

RES_CUT

character

Cut-off value for phenotype test result

However using the amino acid sequence does not give the same detail of data as the nucleoside sequence: wobbles in the nucleoside sequence can either complicate the reading and alignment of the amino acid sequence or the wobbles can be lost and silent mutations are lost.

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblLAB_RES_LVL_3WithinTableL3W001Duplicate records for same TEST_ID and ATC_CODEYES
tblLAB_RES_LVL_3WithinTableL3W002Missing TEST_IDYES
tblLAB_RES_LVL_3WithinTableL3W003Missing ATC_CODEYES
tblLAB_RES_LVL_3WithinTableL3W004Missing RES_SCORYES
tblLAB_RES_LVL_3CrossTableL3C001TEST_ID not in tblLAB_RES.TEST_IDYES

Notes on the Pregnancy-Related Tables

The correct and accurate modelling of a pregnancy and its outcomes is rather involved, and some assumptions had to be made. This page illustrates the model used in the pregnancy tables to which these assumptions lead.

Example

Consider the following example:

  1. A female patient (123) gets pregnant at time (a).
  2. At time (b) she has an abortion which causes the dead fetus of CHILD 701.
  3. At time (c), she gets pregnant again.
  4. The delivery of this pregnancy is at time (d), when the twins (CHILD 702 and CHILD 703) are born.
              (a)     (b)             (c)          (d)
MUM 123   -----|-------|---------------|------------|--------------------

CHILD 701              |-|
CHILD 702                                           |--------------------
CHILD 703                                           |--------------------

This story is described in the different pregnancy tables as follows:

There are two records in tblPREG, one for each pregnancy:

MOTHER_ID PREG_SEQ MENS_D ...
123 1 (a) ...
123 2 (c) ...

There are three records in tblPREG_OUT describing the pregnancy outcome for each fetus:

MOTHER_ID PREG_SEQ CHILD_ID OUTCOM ...
123 1 701 21 ...
123 2 702 1 ...
123 2 703 1 ...

There is one record in tblDELIVERY_MUM:

MOTHER_ID PREG_SEQ MEMRUP_D ...
123 2 (d) ...

There are two records in tblDELIVERY_CHILD:

MOTHER_ID MEMRUP_D CHILD_ID ...
123 (d) 702 ...
123 (d) 703 ...

There are two records in tblNEWBORN:

CHILD_ID ...
702 ...
703 ...

Important relations

The following statements are true for a well-encoded data set:

  1. Every record in tblPREG_OUT refers to some existing record in tblPREG.
  2. For every record in tblPREG_OUT that describes a delivery, there is exactly one record in tblDELIVERY_CHILD referring to it.
  3. For every record in tblDELIVERY_MUM, there are 1 or more records in tblDELIVERY_CHILD referring to it.
  4. For every record in tblNEWBORN there is exactly one record in tblDELIVERY_CHILD referring to the same child.
  5. For every record in tblDELIVERY_MUM, there is some record in tblPREG to which it refers.

tblPREG - Pregnancy

Please also read the notes on pregnancy tables.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

MOTHER_ID

Character (or numeric if possible)

Patient ID of mother of the child

PREG_SEQ

numeric

Sequence number of the pregnancy for the specified mother

MENS_D

yyyy-mm-dd

Date of last menstrual period (If date not known exactly please give approximated date)

CONCEPT

character:

  • 1=Natural
  • 2=Infertility treatment unspecified
  • 2.1=IVF (In Vitro Fertilisation)
  • 2.2=ICSI (IntraCytoplasmic Sperm Injection)
  • 2.3=Ovulation induction
  • 3=Artificial insemination
  • 4=Self insemination
  • 9=Unknown

Conception

ANC_D

yyyy-mm-dd

Date of first antenatal care contact

INPREG_Y

character:

  • 1=Yes
  • 2=No: ectopic
  • 3=No: missed abortion
  • 4=No: death in utero (IUFT)

At first gynaecological visit: intact intrauterine pregnancy?

INHIST_Y

numeric:

  • 0=No
  • 1=Yes
  • 9=Unknown

If no in INPREG_Y was a histological investigation of reason made.

INHIST_S

character

If yes, please specify reason

INV_PROC

  • 0=No
  • 1=Yes, chorionic villus sampling (CVS)
  • 2=Cordocentesis
  • 3=Amniocentesis
  • 9=Unknown

Invasive procedure

KARYO_T

  • 0=Not done
  • 1=Normal
  • 2=Abnormal
  • 9=Unknown

Karyotype

KARYO_A

character

If abnormal, please specify

CHORIO

character:

  • 1=Monochorionic
  • 2=Dichorionic
  • 3=Trichorionic
  • etc.
  • 9=Unknown

For multiple pregnancies

ULTR_1

character:

  • 0=No
  • 1=Yes, normal
  • 2=Yes, abnormal
  • 9=Unknown

Ultrasound 1. trimester

ULTR_A_1

character

If abnormal, please specify

ULTR_2

character:

  • 0=No
  • 1=Yes, normal
  • 2=Yes, abnormal
  • 9=Unknown

Ultrasound 2. trimester

ULTR_A_2

character

If abnormal, please specify

PROB_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

did any obstetrical problems occur? (if yes, recorded in tblPREG_OBS)

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblPREGWithinTablePW001ANC_D <MENS_DYES
tblPREGWithinTablePW002INPREG_Y=2,3,4 but INHIST_Y nullYES
tblPREGWithinTablePW003INPREG_Y=1 but INHIST_Y non nullYES
tblPREGWithinTablePW004INHIST_Y=1 but INHIST_S nullYES
tblPREGWithinTablePW004INHIST_Y=0 or 9 but INHIST_S non nullYES
tblPREGWithinTablePW005KARYO_T=2 but KARYO_A nullYES
tblPREGWithinTablePW006KARYO_T<>2 but KARYO_A non nullYES
tblPREGWithinTablePW007ULTRA_x =2, but ULTR_A_x nullYES
tblPREGWithinTablePW008ULTRA_x <>2, but ULTR_A_x non nullYES
tblPREGCrossTablePC001PROB_Y = 1 but no records in tblPREG_OBSYES
tblPREGCrossTablePC002PROB_Y = 0 or 9 but records in tblPREG_OBSYES

tblPREG_OBS - Obstetrical problems during pregnancy

This table describes problems during a pregnancy. Abnormalities in newborns are recorded in tblNEWBORN_ABNORM instead.

Please also read the notes on pregnancy tables.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

MOTHER_ID

Character (or numeric if possible)

patient id of mother

PREG_SEQ

numeric

Sequence number of the pregnancy for the specified mother

PROB_T

character:

  • 1 = Preterm contractions
  • 2 = Shortened cervix
  • 3 = Preterm rupture of membranes
  • 4 = Antepartum bleeding
  • 5 = Intrauterine growth retardation (IUGR)
  • 6 = Preeclampsia/HELLP
  • 7 = Hypertension
  • 8 = Gestational diabetes (unspecified type)
  • 8.1 = Gestational diabetes (Diet)
  • 8.2 = Gestational diabetes (Insulin)
  • 9 = Placental abruption
  • 10 = Placenta praevia
  • 99 = Other, specify in PROB_S

type of obstetrical problem

PROB_S

character.

description of other (99) obstetrical problem

CERVIX_S

numeric (mm)

In case of shortened cervix, the length of the cervix in millimeters.

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblPREG_OBSCrossTablePOC001MOTHER_ID+PREG_SEQ doesn't exist in tblPREGYES
tblPREG_OBSWithinTablePOW001PROB_T=99 but PROB_S nullYES
tblPREG_OBSWithinTablePOW002PROB_T<>99 but PROB_S non nullYES
tblPREG_OBSWithinTablePOW003PROB_T=2 but CERVIX_S nullYES
tblPREG_OBSWithinTablePOW003PROB_T<>2 but CERVIX_S non nullYES

tblPREG_OUT - Pregnancy outcome

This table describes outcomes of pregnancies described in tblPREG.

Please also read the notes on pregnancy tables.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

MOTHER_ID

Character (or numeric if possible)

Patient ID of mother of the child

PREG_SEQ

numeric

Sequence number of the pregnancy for the specified mother

CHILD_ID

Character (or numeric if possible)

Patient ID of the child

OUTCOM

character:

  • 1=Born alive, HIV negative
  • 2=Born alive, HIV positive
  • 3=Born alive, unknown HIV status
  • 10=Stillborn
  • 11=Spontaneous miscarriage
  • 20=Termination: surgical
  • 21=Termination: medication

Pregnancy outcome

OUTCOM_R

  • 1=Fetus with malformation
  • 2=Dead fetus
  • 3=Unwanted pregnancy
  • 8=Other
  • 9=Unknown

Reason for termination

OUTCOM_D

yyyy-mm-dd

Date of birth or termination of pregnancy

B_GAGEW

numeric

Gestational age in complete weeks at birth or termination

B_GAGED

numeric

Gestational age in days in addition to weeks at birth or termination

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblPREG_OUTCrossTablePTC001MOTHER_ID+PREG_SEQ doesn't exist in tblPREGYES
tblPREG_OUTCrossTablePTC002OUTCOM=1,2 or 3 and CHILD_ID doesn't exisit in tblNEWBORN or tblDELIVERYCHILDYES
tblPREG_OUTCrossTablePTC003OUTCOM=10,11,20 or 21 and CHILD_ID exists in tblNEWBORN or tblDELIVERYCHILDYES
tblPREG_OUTWithinTablePTW001OUTCOME=20 or 21 and OUTCOM_R nullYES
tblPREG_OUTWithinTablePTW002OUTCOME not 20 or 21 and OUTCOM_R non nullYES

tblDELIVERY_MUM - Delivery information related to the mother

This table contains information about the delivery specific to the mother. Child-specific information is recorded in tblDELIVERY_CHILD instead.

Please also read the notes on pregnancy tables.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

MOTHER_ID

Character (or numeric if possible)

Patient ID of mother of the child

PREG_SEQ

numeric

Sequence number of the pregnancy for the specified mother

MEMRUP_D

yyyy-mm-dd

Date of rupture of membranes

MEMRUP_T

hh:mm

Time of rupture of membranes

LABOUR

character:

  • 1=Spontanous
  • 2=Induced
  • 3=No labour (elective C-section)

Onset of labor

INTERV

character:

  • 0=No
  • 1=Fetal blood sampling FBS
  • 2=Internal electrodes
  • 3=1+2
  • 90=Other
  • 91=1+Other
  • 92=2+Other
  • 93=1+2+Other

Interventions during delivery

INTERV_O

character

Interventions during delivery - other

TEAR_Y

numeric:

  • 1=Yes
  • 0=No
  • 9=Unknown

Episiotomy/tear

BLDLOSS

numeric (mL)

Estimated blood loss during delivery

CONTREAT

character:

  • 0=No, treatment has been interrupted
  • 1=Yes, at the foreseen intervals
  • 2=Yes, but not at forseen time points
  • 9=Unknown

Did the patient continue the usual antiretroviral therapy?

DISCHA_D

yyyy-mm-dd

Date of discharge from hospital

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblDELIVERY_MUMCrossTableDMC001MOTHER_ID doesn't exist in tblPREGYES
tblDELIVERY_MUMCrossTableDMC002MOTHER_ID doesn't exist in tblDELIVERY_CHILD with same MEMRUP_DYES
tblDELIVERY_MUMWithinTableDMW001PREG_SEQ invalid (PREG_SEQ>1 and PREG_SEQ=x, but PREG_SEQ=x-1 doesn't exist)YES
tblDELIVERY_MUMWithinTableDMW002DISCHA_D < MEMRUP_DYES
tblDELIVERY_MUMWithinTableDMW004INTERV=0-3 but INTERV_O non nullYES
tblDELIVERY_MUMWithinTableDMW003INTERV=90,91, 92 or 93 but INTERV_O nullYES

tblDELIVERY_CHILD - Delivery information related to the child(ren)

This table holds information about a delivery specific to a child. Mother-specific data is kept in tblDELIVERY_MUM.

Please also read the notes on pregnancy tables.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

MOTHER_ID

Character (or numeric if possible)

Patient ID of mother of the child

MEMRUP_D

yyyy-mm-dd

Date of rupture of membranes

CHILD_ID

Character (or numeric if possible)

Patient ID of the child

B_SEQ

numeric

If multiple births, indicate number (1=first born)

DELIV_D

yyyy-mm-dd

Date of delivery

DELIV_T

hh:mm

Time of delivery

DELIV_M

character:

  • 1=Vaginally, spontaneous
  • 2=Vaginally, forceps
  • 3=Vaginally, vacuum
  • 10= Cesarean section, primary/elective (before onset of labour and rupture of membrane)
  • 11=Cesarean section, Secondary

Mode of delivery

LABOUR_P

numeric

Duration of labour in hours (from cervical dilatation (>=3 cm) until delivery)

BREECH_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Was the child born from a breech presentation?

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblDELIVERY_CHILDCrossTableDCC001MOTHER_ID doesn't exist in tblPREGYES
tblDELIVERY_CHILDCrossTableDCC002CHILD_ID doesn't exist in tblPREG_OUTYES
tblDELIVERY_CHILDWithinTableDCW001Duplicate records for same Mother_ID, CHILD_ID YES
tblDELIVERY_CHILDWithinTableDCW002B_SEQ >4YES
tblDELIVERY_CHILDWithinTableDCW003B_SEQ>1, but no record exists for same Mother, Child with B_SEQ=1YES
tblDELIVERY_CHILDWithinTableDCW005DELIV_D < MEMRUP_DYES
tblDELIVERY_CHILDWithinTableDCW004LABOUR_P >72YES
tblDELIVERY_CHILDWithinTableDCW0062 children, same mother, same MEMRUP_D, DELIV_D >DELIV_D , yet B_SEQ< B_SEQYES

tblNEWBORN - Newborn

Please also read the notes on pregnancy tables.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

CHILD_ID

Character (or numeric if possible)

Patient ID of the child

BRFEED_SD

yyyy-mm-dd

Breastfeeding, start date.

BRFEED_ED

yyyy-mm-dd

Breastfeeding, end date.

FAT_ETH

See Coding Table.

Ethnicity of father

APGAR_1

numeric

1st APGAR score

APGARM_1

numeric

minute at which the 1st APGAR test was performed

APGAR_2

numeric

2nd APGAR score

APGARM_2

numeric

minute at which the 2nd APGAR test was performed

APGAR_3

numeric

3rd APGAR score

APGARM_3

numeric

minute at which the 3rd APGAR test was performed

ICU_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

Referral to intensive/intermediate care unit?

ICU_S

character

if yes, specify reason

ICU_D

yyyy-mm-dd

Date of entry to intensive/intermediate care unit

ABNORM_Y

numeric:

  • 1 = Yes
  • 0 = No
  • 9 = Unknown

did any abnormalities occur? (if yes, recorded in tblNEWBORN_ABNORM)

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblNEWBORNCrossTableNC001CHILD_ID doesn't exist in tblDELIVERY_CHILDYES
tblNEWBORNCrossTableNC002ABNORM_Y = 1, yet no records in tblNEWBORN_ABNORMYES
tblNEWBORNCrossTableNC003ABNORM_Y = 0 or 9, yet records in tblNEWBORN_ABNORMYES
tblNEWBORNWithinTableNW001BRFEED_SD>BRFEED_EDYES
tblNEWBORNWithinTableNW002APGARM_x's out of order (e.g APGARM_3 < APGARM_2)YES
tblNEWBORNWithinTableNW003ICU_Y=1, but ICU_S or ICS_D nullYES
tblNEWBORNWithinTableNW004ICU_Y=0 or 9, but ICU_S or ICU_D non nullYES

tblNEWBORN_ABNORM - Abnormalities

Abnormalities in newborns are recorded here, one abnormality per row. The absence of a record is to be interpreted as "unknown whether the abnormality existed" since most cohorts only record positive events.

Please also read the notes on pregnancy tables.

Core fields

Note: Fields marked bold form the unique identifier for a record of the table.

Field name

Format

Description

CHILD_ID

Character (or numeric if possible)

Patient ID of the child

ABNORM_T

character:

  • 1 = Birth defect(s) (detectable in physical examination including skin abnormalities)
  • 2 = Congenital infection(s)
  • 3 = Drug withdrawal syndrome
  • 4.1 = Neurological disorder(s): abnormal reflexes
  • 4.2 = Neurological disorder(s): abnormal motility
  • 4.3 = Neurological disorder(s): abnormal tonus
  • 90 = Other health problems

type of abnormality

ABNORM_S

character

further specification of the abnormality

QA Checks

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
tblNEWBORN_ABNORMCrossTableNAC001CHILD_ID doesn't exist in NEWBORNYES
tblNEWBORN_ABNORMWithinTableNAW001ABNORM_T=90 but ABNORM_S nullYES

Considerations for data management

Uniqueness of PATIENT ID

Each table in this document is shown with the PATIENT field as part of the unique identifier for each record, in many cases it might be necessary to specify both PATIENT and COHORT as identifier since the PATIENT id might not be unique across cohorts, this should however be implemented only if needed.

Another solution, which might be worth considering when building the final dataset for the analysis, is to concatenate the patient id and the cohort id into a single unique new patient id.

Duplicate records

Transposition of the flat format data, where there is one record per patient, into a normalised structure that has multiple rows per patient requires that the combination of PATIENT, TYPE_ID and LAB_DATE is unique for each row in the table.

The normalised structure and the relational model does not allow for duplicate records like in the following table to exist:

PATIENT TYPE_ID LAB_DATE LAB_VAL LAB_UNIT
999999 1 2000-01-01 15 U/l
999999 1 2000-01-01 15 U/l

The typical solution to this is to generate an auto-incremented value – RECORD_ID - for each record in the table:

RECORD_ID PATIENT TYPE_ID LAB_DATE LAB_VAL LAB_UNIT
1 999999 1 2000-01-01 15 U/l
2 999999 1 2000-01-01 15 U/l

But it can, and should be dealt with in the design of the study. The presence of duplicate records like in the example shown above might not make any sense; if they did occur it then would be on account of a recording error. If, however, it was important to keep records that show measurements taken the same day but at different times, the format of the LAB_DATE shouldn’t be date (YYYY-MM-DD) but instead should be date-time (YYYY-MM-DD hh:mm:ss):

PATIENT TYPE_ID LAB_DATE LAB_VAL LAB_UNIT
999999 1 2000-01-01 10:00:00 15 U/l
999999 1 2000-01-01 14:00:00 15 U/l

Different and more sophisticated methods to make records unique will be presented later in this document under the definition of the LAB table.

Considerations for using the format to create a database

Administrative fields

Sometimes it might be needed to have a fixed value that shows from which visit or merger a value originates, this does not only apply to the VIS table but could be applied to all tables. This however does depend on the nature of the database and needs for data management, the field below should be considered an administrative support field for data management.

VISIT
Visit number
Numeric:
0 = Baseline Visit
1 = First follow up visit
2 = Second follow up visit
etc.

Often the above field is used for clinical trials databases where there is a need to associate the data directly with a given week’s follow-up. Codes could then be the week number e.g. 4, 12, 24 etc or –1 for screening/randomisation and 0 for baseline visits.

In some cases it might be useful to have a separate field that defines the correct order of the periods. This becomes important where several dates are incomplete (unknown days, unknown months and possibly unknown years). The ordering by date would then not be correct.

One solution to this is use a PERI_ID field that numbers the periods from the 1st until Nth usage:

PERI_ID
Period of usage (1st, 2nd, 3rd etc.)
Numeric

However this is an optional field that for most cohorts may not be needed. It also requires additional maintenance to keep it updated.

For databases that work with double data entry, such as most clinical databases, it becomes necessary to make each data entry unique and backwards traceable. For this to work a field like the above would have to be part of the primary key of each table that requires double data entry.

ENTRY_ID
Number of data entry
Numeric:
1 = first data entry
2 = second data entry
3 = comparison of 1st and 2nd data entry
4 = final approved record including corrections

With respect to performance, it might also be a good design to have 3 copies of each table, one to hold the data while being entered and compared, one for the two data entries to be archived into once a final record has been approved and a table holding the final and approved values. This way it is avoided that queries will have to work on checking for ENTRY_ID = 4 and to select amongst a table holding 3 times the almost same data.

As part of an audit trail in a database a time stamp field could be added for each record to fix the exact time when the record last was inserted or updated. Along with the time stamp name of the user who entered or altered data can be recorded.

T_STAMP USER_LOG
Date and time of data entry Username of user that last inserted or updated data
yyyy-mm-dd hh:mm:ss character

Often it's necessary to keep a log of user action in a separate table. The above suggestion will only be valid for inserts and updates, and only be valid for the most recent action performed.

To record a complete audit trail a logging facility must be implemented. In most database management systems this is done using triggers on the tables. For any insert, update or delete actions performed on the data, the user, time stamp, old value and new value are recorded in the logging table.

The T_STAMP field could also include information about which time zone is relevant for data entry. Depending on database requirements this might in fact be mandatory if the FDA’s 21 CRF part 11 on electronic records and signatures applies.

Further normalisation

Depending on performance considerations it might be worth looking at how data are queried for data entry and data analysis. A smaller tblBAS table might increase performance: Since processing a smaller table is always faster than processing a larger table, one could put drop-out, death, birthday, date of aids diagnosis, etc. into separate tables and keep the core patient list in a separate master table

But if the database is used e.g. for BMI calculations directly on the running database, performance might be enhanced by keeping the patient list and the height together in the same table so that a query involves 2 tables (tblBAS and tblVIS) rather than perhaps 3 or more.

Another consideration is space. Although it may not be much of an issue, it will be possible to minimise the actual size of the database by putting fields that may be empty for most patients, like death information, into a separate table in a 0-1 to 1 relation to the master table.

Lookup tables

In a running database the #_ID fields could be implemented as a foreign key to a linked lookup table containing all possible codes and their corresponding definitions in a text string.

ART lookup table

This setup not only enables integrity of the data, but also defines the domain1 for the #_ID values and enables data to both become human readable and easily recoded2.

An important note on lookup tables is that the performance on a large database can be slowed significantly by using character based keys to link lookup tables with the primary table as it is presented in this document. A work around is to use numeric value for the codes.

1: Domain is a term in the definition of the relational database model that defines a set of allowed values for a given set of fields (attributes), the mixing of different domains is not allowed in order to preserve the integrity of a relational and normalised model.

2: Easily recoded permanently if the relation is specified as cascade on update or recoded dynamic by selecting a different column from the lookup table when querying the data through SQL

Performance

As already outlined in the above section, there are also performance issues that may have to be considered.

When using the suggested data types presented in this document for a database implementation, it may be worth looking at the actual data at hand when defining the final data types. The aim of this document is to present a format that will work between cohorts with rather different setups.

If it is at all possible in many cases there may be a large performance gain by using numeric instead of character fields. Character fields have been suggested here for, amongst others, the PATIENT field. If the PATIENT id is purely numeric it’s worth using a numeric data type since it always faster for querying than a character field.

Whenever the field has to be character, make sure that only the needed amount of space is assigned for the field length; there is no need to assign 50 characters of memory if the field in fact only stores a 3-letter code.

QA checks

This page lists all QA checks currently defined for HICDEP 1.70. The attached QA.csv file also serves as data source for the QA sections in the articles describing the individual tables.

Checks added in so far have been collated from 3 sources:

  • CASCADE
  • EPPICC FPV/DRV mergers
  • PENTA Eurocoord merger

Checks on a table's additional fields have not been included at this stage

Only a few checks for non-HICDEP tables, non-HICDEP fields in HICDEP tables and Study-specific checks have been included at this stage for demo/testing purposes.

Error Codes

The error codes allow to unambiguously reference a certain problem with a given data set. When merging data from different cohorts, the data manager may report problems for instance as follows:

[ATC006] in tblART/ART_ID: there is no drug named 'XYZ' in the coding list

Note: the use of the error code alone is not sufficient to locate a problem since some of them are fairly generic.

Used like above, error codes can help to clearly communicate issues. This holds especially if the QA requirements are being checked by an automatic system.

QA checks listed nowhere else

These are QA checks which are either not directly applicable to a specific table or to tables which are not (yet) part of HICDEP.

Table Crosstable Error Code Description Study specific HICDEP?
AppendixCrossTableGC001New patients who were not submitted last time we did this mergerEPPICC NO
AppendixCrossTableGC002Patients left out who were submitted last time we did this mergerEPPICC NO
tblAE_NEWCrossTableANC001tblBAS says AE_FPV=1, yet records in tblAE_NEW whilst on FPVEPPICC NO
tblAE_NEWCrossTableANC002AE_DRUG not reported in tblART or tblMED EPPICC NO
tblARTWithinTableAW003ART_RS=98 yet ART_OTH is nullEPPICC NO
tblMEDWithinTableMW002MED_ONG=0 and MED_ED null EPPICC NO
tblMEDWithinTableMW003MED_ONG=1 and MED_ED non-null EPPICC NO
AppendixCrossTableGC003Spot-check dates to ensure no Excel date-zero errorsEPPICC NO
tblAE_NADMWithinTableANDW001Duplicate records for same EVENT_IDEPPICC NO
tblAE_NADMCrossTableANDC001EVENT_ID doesn't exist in tblAE_NEWEPPICC NO
tblAE_NADMWithinTableANDW002Missing PatientEPPICC NO
tblAE_NADMWithinTableANDW003Missing EVENT_IDEPPICC NO
tblAE_NADMWithinTableANDW004Missing CANTYP_TEPPICC NO
tblAE_NADMWithinTableANDW005Missing PATREP_SEPPICC NO
tblAE_NADMWithinTableANDW006DIAOTH_Y=1 and DIA_S is missingEPPICC NO
tblAE_NADMCrossTableANDC002PATIENT doesn't exist in tblBASEPPICC NO
tblAE_NEWCrossTableANC003PATIENT has no record in BASEPPICC NO
tblAE_NEWCrossTableANC004AE happened before that drug was startedEPPICC NO
tblAE_NEWWithinTableANW001More than one record for same patient on same date with same AE_IDEPPICC NO
tblAE_NEWWithinTableANW002More than one record for same patient with same EVENT_IDEPPICC NO
tblAE_NEWWithinTableANW003Missing PATIENTEPPICC NO
tblAE_NEWWithinTableANW004Missing AE_DEPPICC NO
tblAE_NEWWithinTableANW005Missing AE_IDEPPICC NO
tblAE_NEWWithinTableANW006Missing EVENT_IDEPPICC NO
tblAE_NEWWithinTableANW007Missing AE_RESEPPICC NO
tblAE_NEWWithinTableANW008Missing AE_TEXTEPPICC NO
tblAE_NEWWithinTableANW009Missing AE_DRUGEPPICC NO
tblAE_NEWCrossTableANC005AE_ART=1 but doesn't agree with records in ART tableEPPICC NO

All QA checks

A complete list of all QA checks listed in the QA.csv file:

Table Crosstable Error Code Description Study specific HICDEP?
AllTablesCrossTableATC001any date in database after DEATH_D in tblLTFUYES
AllTablesCrossTableATC002any date in database after DROP_D in tblLTFUYES
AllTablesCrossTableATC003any date in database before BIRTH_D in tblBASYES
AllTablesCrossTableATC004any date in database in the futureYES
AllTablesCrossTableATC005patients submitted previously who have been missed outYES
AllTablesCrossTableATC006Any fields not coded as coding lists on table definitionYES
AppendixCrossTableGC001New patients who were not submitted last time we did this mergerEPPICC NO
AppendixCrossTableGC002Patients left out who were submitted last time we did this mergerEPPICC NO
tblAE_NEWCrossTableANC001tblBAS says AE_FPV=1, yet records in tblAE_NEW whilst on FPVEPPICC NO
tblAE_NEWCrossTableANC002AE_DRUG not reported in tblART or tblMED EPPICC NO
tblARTWithinTableAW001ART_RS not null, but end date NULLYES
tblARTWithinTableAW002ART_RS null but end-date non NULLYES
tblARTWithinTableAW003ART_RS=98 yet ART_OTH is nullEPPICC NO
tblARTWithinTableAW004Duplicate records for same cohort, patient, art_id and art_sdYES
tblARTWithinTableAW005Dose out of range for those dose units and frequencyPaediatricOnlyYES
tblARTWithinTableAW006Missing art_fr PaediatricOnlyYES
tblARTWithinTableAW007Missing art_do PaediatricOnlyYES
tblARTWithinTableAW008Missing patient YES
tblARTWithinTableAW009Missing art_id YES
tblARTWithinTableAW010Missing art_sdYES
tblARTWithinTableAW011Overlapping periods of same drugYES
tblARTWithinTableAW012Double reporting - records reported for both combination drugs and their components YES
tblARTWithinTableAW013Periods of overlap of contra-indicated drugsYES
tblARTWithinTableAW014Restart of same drug without a stop YES
tblARTWithinTableAW015ART_SD greater than or equal to ART_EDYES
tblARTCrossTableAC001Patient has no record in table BASYES
tblARTCrossTableAC002Records exist in tblART yet RECART_Y=0 in tblBASYES
tblBASWithinTableBW001AIDS date < SEROCO_DYES
tblBASWithinTableBW002Duplicate patientsYES
tblBASWithinTableBW003First 3 chars of PATIENT don't form valid cohort codeCascadeOnlyYES
tblBASWithinTableBW004Missing PATIENTYES
tblBASWithinTableBW005Missing CENTERYES
tblBASWithinTableBW006Missing BIRTH_DYES
tblBASWithinTableBW007Missing FRSVIS_DYES
tblBASWithinTableBW008Missing ENROL_DYES
tblBASWithinTableBW009Missing GENDERYES
tblBASWithinTableBW010Missing HEIGHYES
tblBASWithinTableBW011Missing MODEYES
tblBASWithinTableBW012Missing MODE_OTH if MODE=90YES
tblBASWithinTableBW013Missing ORIGINYES
tblBASWithinTableBW014Missing ETHNICYES
tblBASWithinTableBW015Missing SEROCO_DYES
tblBASWithinTableBW016Missing RECART_YYES
tblBASWithinTableBW017Missing AIDS_YYES
tblBASWithinTableBW018Missing AIDS_D if AIDS_Y=1YES
tblBASWithinTableBW019BIRTH_D out of range (15-85 yrs)CascadeOnlyYES
tblBASWithinTableBW020BIRTH_D out of range (<18)PENTAYES
tblBASCrossTableBC001RECART_Y=1 but no records in tblART YES
tblBASCrossTableBC002AIDS_Y=0, but AIDS-defining records in tblDISYES
tblBASCrossTableBC003AIDS_Y=1 but no AIDS-defining records in tblDIS tableYES
tblDISWithinTableDW001Duplicate records for same DIS_ID and same DIS_DYES
tblDISWithinTableDW002Miscoded DIS_WD as codes on table definitionYES
tblDISWithinTableDW003Miscoded DIS_ID - as in code list attached to table definitionYES
tblDISWithinTableDW004DIS_D missingYES
tblDISWithinTableDW005DIS_ID missingYES
tblDISWithinTableDW006Same event recorded twice - 2 records, same DIS_ID, DIS_D within 6 monthsYES
tblDISWithinTableDW007DIS_ED present but before DIS_DYES
tblDISCrossTableDC001Patient has no record in BASYES
tblDISCrossTableDC002AIDS-defining records, yet AIDS=0 in tblBASYES
tblDISCrossTableDC003First AIDS-defining DIS_D not equal to AIDS_D in tblBASYES
tblLABWithinTableLW001Duplicate recordsYES
tblLABWithinTableLW002Missing LAB_DYES
tblLABWithinTableLW003Missing LAB_VYES
tblLABWithinTableLW004Missing LAB_UYES
tblLABWithinTableLW005Missing LAB_IDYES
tblLABWithinTableLW008Missing LAB_FAYES
tblLABWithinTableLW009Missing LAB_STYES
tblLABWithinTableLW010LAB_V out of range for unit LAB_UYES
tblLABCrossTableLC001Patient has no record in BAS tableYES
tblLAB_CD4WithinTableCW001CD4 value out of rangeYES
tblLAB_CD4WithinTableCW002Duplicate records for same dateYES
tblLAB_CD4WithinTableCW003Missing CD4_DYES
tblLAB_CD4WithinTableCW004Missing CD4_VYES
tblLAB_CD4WithinTableCW005Missing CD4_UYES
tblLAB_CD4WithinTableCW006Miscoded CD4_U as coding list on table definitionYES
tblLAB_CD4WithinTableCW007CD4_U=2 (percentage) and CD4_V>100YES
tblLAB_CD4WithinTableCW008CD4_U=(1 or 3) and CD4_V>3000YES
tblLAB_CD4WithinTableCW009CD4 counts spike up or down suddenly (large change in less than a year)YES
tblLAB_CD4CrossTableCC001Patient has no record in BASYES
tblLAB_RESWithinTableLRW001Duplicate records for same patient on same dateYES
tblLAB_RESWithinTableLRW002Missing PATIENTYES
tblLAB_RESWithinTableLRW003Missing TEST_IDYES
tblLAB_RESWithinTableLRW004Missing SAMPLE_DYES
tblLAB_RESWithinTableLRW005Missing SEQ_DTYES
tblLAB_RESWithinTableLRW006Missing LABYES
tblLAB_RESWithinTableLRW007Missing LIBRARYYES
tblLAB_RESWithinTableLRW008Missing REFSEQYES
tblLAB_RESWithinTableLRW009Missing KITYES
tblLAB_RESWithinTableLRW010Missing SOFTWAREYES
tblLAB_RESWithinTableLRW011Missing TESTTYPEYES
tblLAB_RESWithinTableLRW012Missing SUBTYPEYES
tblLAB_RESWithinTableLRW013SEQ_DT has no time partYES
tblLAB_RESWithinTableLRW014Missing VIRUSTYPEYES
tblLAB_RESCrossTableLRC001PATIENT has no record in tblBASYES
tblLAB_RESCrossTableLRC002This TEST_ID has both LVL_1 and LVL_2 recordsYES
tblLAB_RES CrossTableLRC003TESTTYPE in (1,9) (i.e. not phenotype) yet no records in LVL_1 or LVL_2YES
tblLAB_RES_LVL_1WithinTableL1W001Duplicate records per TEST_ID and SEQTYPEYES
tblLAB_RES_LVL_1WithinTableL1W002SEQ_START > SEQ_STOPYES
tblLAB_RES_LVL_1WithinTableL1W003SEQ_NUC contains invalid IUPAC characterYES
tblLAB_RES_LVL_1WithinTableL1W004Missing TEST_IDYES
tblLAB_RES_LVL_1WithinTableL1W005Missing SEQ_STARTYES
tblLAB_RES_LVL_1WithinTableL1W006Missing SEQ_STOPYES
tblLAB_RES_LVL_1WithinTableL1W007Missing SEQ_NUCYES
tblLAB_RES_LVL_1CrossTableL1C001TEST_ID not in tblLAB_RES.TEST_IDYES
tblLAB_RES_LVL_2WithinTableL2W001AA_FOUND_x but nothing in AA_FOUND(x-1)YES
tblLAB_RES_LVL_2WithinTableL2W002Duplicate records per TEST_ID, GENE, AA_POS and AA_POS_SUBYES
tblLAB_RES_LVL_2WithinTableL2W003Missing TEST_IDYES
tblLAB_RES_LVL_2WithinTableL2W004Missing GENEYES
tblLAB_RES_LVL_2WithinTableL2W005Missing AA_POSYES
tblLAB_RES_LVL_2WithinTableL2W006Missing AA_POS_SUBYES
tblLAB_RES_LVL_2WithinTableL2W007Missing AA_FOUND_1YES
tblLAB_RES_LVL_2CrossTableL2C001TEST_ID not in tblLAB_RES.TEST_IDYES
tblLAB_RES_LVL_3WithinTableL3W001Duplicate records for same TEST_ID and ATC_CODEYES
tblLAB_RES_LVL_3WithinTableL3W002Missing TEST_IDYES
tblLAB_RES_LVL_3WithinTableL3W003Missing ATC_CODEYES
tblLAB_RES_LVL_3WithinTableL3W004Missing RES_SCORYES
tblLAB_RES_LVL_3CrossTableL3C001TEST_ID not in tblLAB_RES.TEST_IDYES
tblLAB_RNAWithinTableRW001Duplicate records for same RNA_DYES
tblLAB_RNAWithinTableRW002RNA_V < 0 and RNA_L missingYES
tblLAB_RNAWithinTableRW003RNA_V > 10 000 and (RNA_V modulo 1000) = 1 and RNA_UL missingYES
tblLAB_RNAWithinTableRW004Missing RNA_D YES
tblLAB_RNAWithinTableRW005Missing RNA_V YES
tblLAB_RNAWithinTableRW006RNA_V > 10 millionYES
tblLAB_RNAWithinTableRW007RNA_V < RNA_L and RNA_V >= 0YES
tblLAB_RNAWithinTableRW008RNA_V > RNA_ULYES
tblLAB_RNACrossTableRC001Patient has no record in BASYES
tblLAB_VIROCrossTableLVC001Patient doesn't have a record in BASYES
tblLAB_VIROWithinTableLVW002Missing patient YES
tblLAB_VIROWithinTableLVW003Missing VS_IDYES
tblLAB_VIROWithinTableLVW004Missing VS_DYES
tblLAB_VIROWithinTableLVW005Missing VS_RYES
tblLAB_VIROWithinTableLVW006Missing VS_VYES
tblLAB_VIROWithinTableLVW007Missing VS_UYES
tblLAB_VIROWithinTableLVW008More or less than exactly 1 positive HIV test per patientYES
tblLAB_VIROWithinTableLVW009More than 1 negative HIV test for a patientYES
tblLAB_VIROWithinTableLVW010Date of negative test after date of positive testYES
tblLAB_VIROWithinTableLVW011Duplicate recordsYES
tblLTFUWithinTableLFW001DROP_Y and DEATH_Y both non-nullYES
tblLTFUWithinTableLFW002DEATH_Y and DROP_RS both non-null YES
tblLTFUWithinTableLFW003Any of DEATH_Rx or DEATH_RCx non-null but DEATH_Y=0YES
tblLTFUWithinTableLFW004R2/RC2 non-null but R1/RC1 nullYES
tblLTFUWithinTableLFW005R3/RC3 non-null but R2/RC2 nullYES
tblLTFUWithinTableLFW006Duplicate patientsYES
tblLTFUWithinTableLFW007Missing DROP_YYES
tblLTFUWithinTableLFW008Missing DROP_D if DROP_Y=1YES
tblLTFUWithinTableLFW009Missing DROP_RS if DROP_Y=1YES
tblLTFUWithinTableLFW010Missing DEATH_YYES
tblLTFUWithinTableLFW011Missing DEATH_D if DEATH_Y=1YES
tblLTFUWithinTableLFW012DEATH_D non null but DEATH_Y=0YES
tblLTFUCrossTableLFC001Patient not found in tblBASYES
tblLTFUCrossTableLFC002patient in tblBAS hasn't got a record in tblLTFUYES
tblLTFUCrossTableLFC003tblBAS has AIDS=0 but DEATHRx =8.1 YES
tblMEDWithinTableMW001Duplicate recordsYES
tblMEDWithinTableMW002MED_ONG=0 and MED_ED null EPPICC NO
tblMEDWithinTableMW003MED_ONG=1 and MED_ED non-null EPPICC NO
tblMEDWithinTableMW004Missing MED_IDYES
tblMEDWithinTableMW005Missing MED_SDYES
tblMEDWithinTableMW006Misisng MED_EDYES
tblMEDWithinTableMW007Overlapping periods of same drugYES
tblMEDWithinTableMW008MED_ED < MED_SDYES
tblMEDWithinTableMW009MED_RS not null, but end date NULLYES
tblMEDCrossTableMC001Patient has no records in tblBASYES
tblOVERLAPWithinTableOW001Invalid other cohortYES
tblOVERLAPCrossTableOC001PATIENT not found in tblBAS for that cohortYES
tblOVERLAPCrossTableOC002PAT_OTH not found in tblBAS for that overlapping cohortYES
tblVISWithinTableVW001Duplicate records, same VIS_DYES
tblVISWithinTableVW002Height decreasing over time YES
tblVISWithinTableVW003Height out of acceptable rangeYES
tblVISWithinTableVW004Weight out of acceptable rangeYES
tblVISCrossTableVC001patient has no record in BAS tableYES
tblVISCrossTableVC002No weights within 3 mths of starting FPV/DRVEPPICC YES
AppendixCrossTableGC003Spot-check dates to ensure no Excel date-zero errorsEPPICC NO
tblAE_NADMWithinTableANDW001Duplicate records for same EVENT_IDEPPICC NO
tblAE_NADMCrossTableANDC001EVENT_ID doesn't exist in tblAE_NEWEPPICC NO
tblAE_NADMWithinTableANDW002Missing PatientEPPICC NO
tblAE_NADMWithinTableANDW003Missing EVENT_IDEPPICC NO
tblAE_NADMWithinTableANDW004Missing CANTYP_TEPPICC NO
tblAE_NADMWithinTableANDW005Missing PATREP_SEPPICC NO
tblAE_NADMWithinTableANDW006DIAOTH_Y=1 and DIA_S is missingEPPICC NO
tblAE_NADMCrossTableANDC002PATIENT doesn't exist in tblBASEPPICC NO
tblAE_NEWCrossTableANC003PATIENT has no record in BASEPPICC NO
tblAE_NEWCrossTableANC004AE happened before that drug was startedEPPICC NO
tblAE_NEWWithinTableANW001More than one record for same patient on same date with same AE_IDEPPICC NO
tblAE_NEWWithinTableANW002More than one record for same patient with same EVENT_IDEPPICC NO
tblAE_NEWWithinTableANW003Missing PATIENTEPPICC NO
tblAE_NEWWithinTableANW004Missing AE_DEPPICC NO
tblAE_NEWWithinTableANW005Missing AE_IDEPPICC NO
tblAE_NEWWithinTableANW006Missing EVENT_IDEPPICC NO
tblAE_NEWWithinTableANW007Missing AE_RESEPPICC NO
tblAE_NEWWithinTableANW008Missing AE_TEXTEPPICC NO
tblAE_NEWWithinTableANW009Missing AE_DRUGEPPICC NO
tblAE_NEWCrossTableANC005AE_ART=1 but doesn't agree with records in ART tableEPPICC NO
tblDELIVERY_CHILDCrossTableDCC001MOTHER_ID doesn't exist in tblPREGYES
tblDELIVERY_CHILDCrossTableDCC002CHILD_ID doesn't exist in tblPREG_OUTYES
tblDELIVERY_CHILDWithinTableDCW001Duplicate records for same Mother_ID, CHILD_ID YES
tblDELIVERY_CHILDWithinTableDCW002B_SEQ >4YES
tblDELIVERY_CHILDWithinTableDCW003B_SEQ>1, but no record exists for same Mother, Child with B_SEQ=1YES
tblDELIVERY_CHILDWithinTableDCW005DELIV_D < MEMRUP_DYES
tblDELIVERY_CHILDWithinTableDCW004LABOUR_P >72YES
tblDELIVERY_CHILDWithinTableDCW0062 children, same mother, same MEMRUP_D, DELIV_D >DELIV_D , yet B_SEQ< B_SEQYES
tblDELIVERY_MUMCrossTableDMC001MOTHER_ID doesn't exist in tblPREGYES
tblDELIVERY_MUMCrossTableDMC002MOTHER_ID doesn't exist in tblDELIVERY_CHILD with same MEMRUP_DYES
tblDELIVERY_MUMWithinTableDMW001PREG_SEQ invalid (PREG_SEQ>1 and PREG_SEQ=x, but PREG_SEQ=x-1 doesn't exist)YES
tblDELIVERY_MUMWithinTableDMW002DISCHA_D < MEMRUP_DYES
tblDELIVERY_MUMWithinTableDMW004INTERV=0-3 but INTERV_O non nullYES
tblDELIVERY_MUMWithinTableDMW003INTERV=90,91, 92 or 93 but INTERV_O nullYES
tblNEWBORNCrossTableNC001CHILD_ID doesn't exist in tblDELIVERY_CHILDYES
tblNEWBORNCrossTableNC002ABNORM_Y = 1, yet no records in tblNEWBORN_ABNORMYES
tblNEWBORNCrossTableNC003ABNORM_Y = 0 or 9, yet records in tblNEWBORN_ABNORMYES
tblNEWBORNWithinTableNW001BRFEED_SD>BRFEED_EDYES
tblNEWBORNWithinTableNW002APGARM_x's out of order (e.g APGARM_3 < APGARM_2)YES
tblNEWBORNWithinTableNW003ICU_Y=1, but ICU_S or ICS_D nullYES
tblNEWBORNWithinTableNW004ICU_Y=0 or 9, but ICU_S or ICU_D non nullYES
tblNEWBORN_ABNORMCrossTableNAC001CHILD_ID doesn't exist in NEWBORNYES
tblNEWBORN_ABNORMWithinTableNAW001ABNORM_T=90 but ABNORM_S nullYES
tblPREG_OBSCrossTablePOC001MOTHER_ID+PREG_SEQ doesn't exist in tblPREGYES
tblPREG_OBSWithinTablePOW001PROB_T=99 but PROB_S nullYES
tblPREG_OBSWithinTablePOW002PROB_T<>99 but PROB_S non nullYES
tblPREG_OBSWithinTablePOW003PROB_T=2 but CERVIX_S nullYES
tblPREG_OBSWithinTablePOW003PROB_T<>2 but CERVIX_S non nullYES
tblPREG_OUTCrossTablePTC001MOTHER_ID+PREG_SEQ doesn't exist in tblPREGYES
tblPREG_OUTCrossTablePTC002OUTCOM=1,2 or 3 and CHILD_ID doesn't exisit in tblNEWBORN or tblDELIVERYCHILDYES
tblPREG_OUTCrossTablePTC003OUTCOM=10,11,20 or 21 and CHILD_ID exists in tblNEWBORN or tblDELIVERYCHILDYES
tblPREG_OUTWithinTablePTW001OUTCOME=20 or 21 and OUTCOM_R nullYES
tblPREG_OUTWithinTablePTW002OUTCOME not 20 or 21 and OUTCOM_R non nullYES
tblPREGWithinTablePW001ANC_D <MENS_DYES
tblPREGWithinTablePW002INPREG_Y=2,3,4 but INHIST_Y nullYES
tblPREGWithinTablePW003INPREG_Y=1 but INHIST_Y non nullYES
tblPREGWithinTablePW004INHIST_Y=1 but INHIST_S nullYES
tblPREGWithinTablePW004INHIST_Y=0 or 9 but INHIST_S non nullYES
tblPREGWithinTablePW005KARYO_T=2 but KARYO_A nullYES
tblPREGWithinTablePW006KARYO_T<>2 but KARYO_A non nullYES
tblPREGWithinTablePW007ULTRA_x =2, but ULTR_A_x nullYES
tblPREGWithinTablePW008ULTRA_x <>2, but ULTR_A_x non nullYES
tblPREGCrossTablePC001PROB_Y = 1 but no records in tblPREG_OBSYES
tblPREGCrossTablePC002PROB_Y = 0 or 9 but records in tblPREG_OBSYES
tblREFILLCrossTablePRC001Patient doesn't have a record in BASYES
tblREFILLWithinTablePRW002Missing PATIENTYES
tblREFILLWithinTablePRW003Missing REFILL_DYES
tblREFILLWithinTablePRW004Missing DRUG_IDYES
tblREFILLWithinTablePRW005Missing SUPPLYYES
tblREFILLWithinTablePRW006SUPPLY < 1YES

Change log

Draft Version 1.100

Version 1.90

  • tblART
    • added new optional field GENERIC
    • added new optional field ART_RS2
    • added new optional field ART_RS3
    • added new optional field ART_RS4
    • ART_ID
      • Added new single tablet formulations:
        • J05AR15 (Atazanavir and cobicistat)
        • J05AR16 (Lamivudine and raltegravir)
        • J05AR17 (Emtricitabine and tenofovir alafenamide)
        • J05AR18 (Emtricitabine, tenofovir alafenamide, elvitegravir and cobicistat)
        • J05AR19 (Emtricitabine, tenofovir alafenamide and rilpivirine)
      • Deleted (moved to tblMED):
        • J05AF08";"Adefovir (PREVEON)"
        • J05AF10";"Entecavir"
        • J05AF11";"Telbivudine"
    • ART_RS
      • added new (sub-)codes for stopping medication
        • 11 (Bone toxicity)
        • 15 (Social contra-indication)
        • 16 (Contra-indication unspecified)
        • 16.8 (Contra-indication expired)
        • 16.9 (Contra-indication - other)
        • 17 (MTCT regimen completed)
        • 70 (Pregnancy - toxicity concerns (during pregnancy))
        • 75 (Pregnancy - switch to a more appropriate regimen for PMTCT)
        • 92.5 (Regular treatment termination (e.g. DAA's for HCV, antibiotics)
  • tblLTFU
    • DEATH_R1
      • added new subcodes:
        • 03.1.3 (HCV with liver cancer)
        • 03.2.3 (HBV with liver cancer)
  • tblMED
    • MED_ID
      • Added new drug codes:
        • A11CC (vitamin D)
        • G02CA (Tocolysis)
        • H02 (Corticosteroids)
        • J01 (Antibiotics)
        • J01GA01 (streptomycin)
        • J02AC05 (Isavuconazole)
        • J02AX04 (caspofungin)
        • J04AB05 (Rifapentine (Priftin))
        • J05AF12 (Clevudine)
        • J05AX GRAZ-ELB (Grazoprevir/Elbasvir)
        • J05AR-DAAS (Daclatasvir/Asunaprevir)
        • J07BM0 (HPV Vaccine)
        • J07BM01 (HPV Vaccine (types 6, 11, 16, 18))
        • J07BM02 (HPV Vaccine (types 16, 18))
        • J07BM03 (HPV Vaccine (types 6, 11, 16, 18, 31, 33, 45, 52, 58))
        • M05BA (bisphosphonate)
        • N05A (Antipsychotics)
        • N05CD (Benzodiazepine derivatives)
        • N05CF (Benzodiazepine related drugs)
        • N06A (Antidepressant)
        • N07BC (Other drugs used in opioid dependence)
        • N07BC01 (Buprenorphine)
        • N07BC02 (Methadone)
        • N07BC03 (Levacetylmethadol)
        • N07BC04 (Lofexidine)
        • N07BC51 (Buprenorphine, combinations)
        • V03AB15 (Naloxone)
      • Added new drug codes (moved from tblART):
        • J05AF08";"Adefovir (PREVEON)"
        • J05AF10";"Entecavir"
        • J05AF11";"Telbivudine"
      • Changed temporary drug names to ATC codes:
        • J05AR-A450OM (ABT-450/r/Ombitasvir) --> J05AX67 (Ombitasvir, paritaprevir(ABT-450) and ritonavir)
        • J05AR-LESO (Ledipasvir/Sofosbuvir) --> J05AX65 (Ledipasvir/Sofosbuvir)
        • J05AX-DBV (Dasabuvir) --> J05AX16 (Dasabuvir)
    • MED_RS
      • added new (sub-)codes for stopping medication (to keep identical to ART_RS)
        • 11 (Bone toxicity)
        • 15 (Social contra-indication)
        • 16 (Contra-indication unspecified)
        • 16.8 (Contra-indication expired)
        • 16.9 (Contra-indication - other)
        • 17 (MTCT regimen completed)
        • 70 (Pregnancy - toxicity concerns (during pregnancy))
        • 75 (Pregnancy - switch to a more appropriate regimen for PMTCT)
        • 92.5 (Regular treatment termination (e.g. DAA's for HCV, antibiotics)
  • tblVIS
    • Added field for Center CENTER
    • Added field for family history of CVD/Stroke FAM_Y
    • Added field for type of clinic/service CLIN_TYPE
    • Added field for speciality of physician SPEC_TYPE
    • Added field for stage of transition from adolescence to adulthood TRANS_STAGE

Version 1.80

  • tblART
    • ART_ID
      • Added:
        • J05AR11 (Lamivudine, tenofovir disoproxil and efavirenz)
        • J05AR12 (Lamivudine and tenofovir disoproxil)
        • J05AR13 (Lamivudine, abacavir and dolutegravir)
        • J05AR14 (Darunavir and cobicistat)
        • J05AX-CAB (Cabotegravir (GSK-744))
  • tblBAS
    • ETHNIC
      • Revised and added codes for different ethnicities
      • Changed limitations
      • Changed description
    • ORIGIN
      • Changed description to be more specific
  • tblLTFU
    • DEATH_R1
      • Added sub-types for 22 and 23
      • Added additional codes table
  • tblMED
    • MED_ID
      • Added:
        • J05AE11 (Telaprevir (INCIVEK, INCIVO))
        • J05AE13 (Faldaprevir)
        • J05AE14 (Simeprevir)
        • J05AE15 (Asunaprevir)
        • J05AX14 (Daclatasvir)
        • J05AX15 (Sofosbuvir)
        • J05AR-A450OM (ABT-450/r/Ombitasvir)
        • J05AR-DAAS (Daclatasvir/Asunaprevir)
        • J05AR-LESO (Ledipasvir/Sofosbuvir)
        • J05AX-DBV (Dasabuvir)
  • tblLAB
    • LAB_U
      • Added:
        • 14 (mg/24h)
        • 15 (mg/mmol)
        • 16 (fl)
        • 17 (µg/mL = mg/L)
        • 18 (µkat/L)
    • LAB_ID
      • Added:
        • ACRA (Albumin Creatinin Ratio)
        • PCRA (Protein Creatinin Ratio)
        • PROT (Protein)
        • PSA (Prostate-specific antigen)
        • PTH (Parathyroid Hormone)

Version 1.70

  • tblAE has been renamed to tblCEP
  • tblCEP
    • Added field CEP_V
    • CEP_ID
      • Added: HOSP, ICU, JAUN, LIVD, LIVT, USAB, ASP, BART, CHAG, NOCA, PCE, PMAR, REQU.
      • Added: BACT, ENDO, MENI, OSTI, PERI, PNEU, PYEL, LEIS, MCDI (Previously in tblDIS).
      • Removed: ANG, BYP, END as they are already in the field CEP_SPEC.
      • Removed COR as now specified with SUD_DEATH_Y and EXP_DEATH_Y in tblLTFU.
    • CEP_SPEC
      • Changed description of NADM - CERV to contain "grade 2 or higher"
      • Added values: LIVB - XX, HEP - XX, NADM - HENE, NADM - BRAIN
  • tblBAS
    • Added field RECART_D (Date ART started)
    • Added field LTART_D (Date last assessed for ART)
    • ETHNIC: Added Limitations section in description
    • Added field EDU_LVL (Last completed education Level)
    • Added field HIV_POS_D (Date of first positive HIV test)
  • tblDIS
    • DIS_ID: Removed HG, BACT, ENDO, MENI, OSTI, PERI, PNEU, PYEL, LEIS, MCDI (now in tblCEP).
  • tblVIS
    • Added optional field EMPLOY (What is the patient's current situation regarding labour?)
    • Added optional field CONTRACT (If the patient is an employee, what is the type of the patient's employment contract?)
    • Added optional field SMOKING_Y (Is the patient currently a smoker?)
    • Added optional field PREG_Y (Is the patient currently pregnant?)
    • Added optional field CDC_STAGE
    • Added optional field WHO_STAGE
  • tblLTFU
    • Added optional fields for ICD9 cause of death coding (ICD9_#)
    • Added optional field DEATH_SOURCE
    • Added optional fields SUD_DEATH_Y and EXP_DEATH_Y. Replacing COR
    • CoDe
      • Typos in the coding table (HBF -> HBV)
      • New codes for cancer (04.XX)
      • New codes for AMI (08.XX)
      • New code: 92.1 - Unknown, competing risks
  • tblART
    • ART_ID: Coding extended/updated to match current ATC codes.
      • Lopinavir / Ritonavir changed code from "J05AE06" to "J05AR10".
      • Elvitegravir changed code from "J05AX-EVG" to "J05AX11"
      • J05AR07 - Triomune
      • J05AR08 - Eviplera / Complera
      • J05AR09 - Stribild
      • J05AR10 - Kaletra / Aluvia
      • J05AX12 - Dolutegravir
      • V03AX03 - Cobicistat
    • ART_FR: Added 0.33 and 0.5. Added special value -1 for "frequency unknown".
    • ART_RS
      • Added new codes: 6.X, 92.31, 92.32, 92.33, 92.4, 92.9, 94.2, 96.1, 96.2, 97.1, 97.2, 97.6
  • tblLAB
    • LAB_ID
      • Removed redundant "APT" code. Use code "ALP"
      • Removed "GLYCE" (synonymous to Glucose in blood)
      • Added "AFP" (Alfa Fetoprotein) and "DIPP" (Dipstick result for protein in Urine).
    • LAB_U
      • Removed WBC codes (WBC was already removed from LAB_ID)
      • Added generic "13 - µg/L" and "99 - No units" code
    • LAB_ST
      • Added codes U and U24.
  • tblLAB_RNA
    • RNA_T
      • New code: 42 - Abbott RealTime HIV-1 m2000
      • New code: 59 - Monitor unspecified
  • tblMED
    • MED_ID
      • Changed description from "Other HIV-related drugs" to "Other medication"
      • Added codes for Boceprevir and Telaprevir
    • MED_RS
      • Same changes as in tblART ART_RS

Version 1.60

  • tblBAS:
    • The ORIGIN field is now coded using UN region and country codes.
    • Added optional SEROHOW field indicating how the seroconversion date was determined.
    • Added optional CENS_D field holding the last date the database was updated for a patient.
  • tblREFILL created to hold prescription refill data.
  • tblLTFU: The DEATH_R# fields now use CoDe codes for coding cause of death.
  • tblLAB:
    • Added codes for Gamma-glutamyltransferase and Prothrombin rate.
  • tblLAB_VIRO:
    • Changed code for "Other" of field VS_T to value 9.
    • Added "HBVACIGM", "HBVACIGG", "HCVBD" and "HDVA" codes for VS_ID field.
  • tblLAB_RES, tblLAB_RES_LVL_1, tblLAB_RES_LVL_2 and tblLAB_RES_LVL_3:
    • Renamed SAMP_ID to TEST_ID, as it gives a better description of what the values should encode.
  • tblLAB_RES:
  • tblLAB_RES_LVL_3:
    • ATC_CODE replaces ART_ID as HCV medication in tblMED may be encoded.
  • tblDIS:
    • Added optional DIS_ED field for the end date of the disease.
  • tblMED:
    • Added MED_RS describing the reason for stopping the treatment; needed for HCV.
  • tblAE:
    • Added a number of codes for AE_ID: ANG, ASCI, AVN, BYP, CERC, END, FIBS, FRA, HEP, HESY, LAC, LIVB, OESO, PAN and PERI.
  • QA checks have been extended and updated where necessary.

Version 1.50

Version 1.30

  • tblART: Updated list of drugs
  • tblAE:
    • added EVENT_ID as unique identifier and link to detailed tables for each event (see 1) – this replaces the optional AE_NO field.
    • added AE_SPEC to further specify an event by coding
    • a series of basic verification fields have been added to allow for tracking of event status for source documentation availability, verification of documentation (through monitoring) and final approval of the event.
    • in AE_R_Y - Relation to treatment: added more detailed codes.
  • tblLAB: added several codes for various biomarker tests.
  • tblLAB_CD4: added CD4_U as optional fields to discriminate between CD4% and CD4 cell count, so that the tblLAB_CD4 table can hold both types of measurements.
  • tblLAB_VIRO: added several codes for various virology and serology tests.
  • CaseDefinitions updated with end stage renal disease, chronic liver disease and non-AIDS defining malignancies

1:Detailed table definitions for the D:A:D events are available at http://www.cphiv.dk/HICDEP/Documents/tabid/159/Default.aspx

Version 1.25

  • tblART: Updated list of drugs
  • tblMED: Updated list of drugs
  • tblDIS:
    • Changed wording for CANO to ‘Candidiasis, oesophageal, bronchi, trachea, or lungs’
    • Added COCC - Coccidioidomycosis, disseminated or extrapulmonary
  • tblLAB: Added LAB_ST as additional field to code for type of specimen used for the measurement
  • tblLAB_CD4: Added CD4_U as additional field so the table can hold both percentage and absolute CD4 measurements
  • tblLAB_RNA: Added RNA_UL (upper limit of detection) to the list of additional fields.
  • Added more viral assays to the list of RNA_T codes
  • tblLAB_VIRO: Added unit field to tblLAB_VIRO into the general format and VS_LL (lower limit of detection), VS_UL (upper limit of detection) and VS_T (type of test) and list of tests to the list of additional fields.
  • tblLTFU: Added DEATH_RC# to code for causal relation of the DEATH_R# code to the death in order to comply with CoDe and still maintain a format to be used for cohorts not using CoDe. ICD10_# fields have been moved to the list of additional fields.

Version 1.21

Code Coding for Reason of Stopping Treatment
1.1 Virological failure
1.2 Partial virological failure
1.3 Immunological failure – CD4 drop
1.4 Clinical progression
90 Side effects – any of the above but unspecified
90.1 Comorbidity
92.1 Simplified treatment available
92.2 Treatment to complex
92.3 Drug interaction
93.1 Structured Treatment Interruption (STI) – at high CD4
94.1 Non-compliance
96 Pregnancy
97 Study treatment

Version 1.2

Version 1.1

  • tblBAS:
    • The table was split into tblBAS and tblLTFU. tblLTFU holds data on death and drop-out
    • Renamed LOS_Y to LOSS_Y
    • Renamed GAI_Y to GAIN_Y
  • tblLAB_BLP:
    • Renamed table to tblLAB_BP
    • Renamed BLP_D to BP_D
    • Renamed BLP_SYS to BP_SYS
    • Renamed BLP_DIA to BP_DIA
    • Renamed BLP_U to BP_U

Version 1.00

  • tblBas:
    • Renamed BIRTHDAY to BIRTH_D
    • Renamed FIRSTVIS to FRSVIS_D
    • Renamed REC_ART to RECART_Y
  • tblLAB:
    • LAB_U: has been dropped – please use the “unit codes/strings” as that is a safer way to code/represent the units – prefixing all “unit codes/strings” with a numeric value should however make analysis easier.
  • tblLAB_VIRO:
    • New table added to capture mainly hepatitis measurements/tests
  • tblLAB_RES:
    • SEQ_DT was added to capture the time of sequencing in order to facilitate quality assurance of the data for contamination that might have happened during the sequencing.
  • tblLAB_RES_LVL1:
    • Renamed SEQ_ST to SEQ_STAR
    • SEQ_STOP: Added to the table to specify at which position in the sequence the sequencing was terminated
  • tblLAB_RES_LVL2:
    • The table has been optimised for ease of analysis so that the mutation codes have been split into their components of amino acid position, sub position for insertions and 4 our more fields for mixtures of amino acids found in the sample

Version 0.50 and 0.90

First public versions that incorporated comments and corrections received from attendees at the 7th International Workshop on HIV Observational Databases, March 29th-30th 2003, Fiuggi, Italy and Stephen Hart.

Version 0.38

Version presented at 7th International Workshop on HIV Observational Databases, March 29th-30th 2003, Fiuggi, Italy