Introduction

This concept provides information about using the Cadham Provincial Laboratory (CPL) Laboratory Information Management System (LIMS) database, which replaced the previous database system used by CPL. Much of this concept is based on speaking with Penny Klassen, a Manitoba Health analyst working at the Cadham lab, in January 2025.

The purpose of this concept is to provide analysts and researchers with information about what is contained in the LIMS data, a description of the structure of the LIMS data, what is contained in each of the available datasets, and how to link them together, and explanations of several of the most important variables needed to work with the LIMS data.

Additional information on LIMS can be found in the:

• Cadham Provincial Laboratory (CPL) - Laboratory Information Management System (LIMS) data description and the
  
• LIMS entry in the MCHP Metadata Repository system (internal access only).

For information on the previous CPL database see:

• the concept dictionary entry CADHAM Provincial Laboratory (CPL) - Overview of Services and Data and
  
• the Cadham Provincial Laboratory (CPL) data description.

Scope of CPL Services

• Province-wide requisition and public health laboratory test information for Microbiology, Newborn Screening, Serology, Parasitology, and Virus Detection. Other clinical laboratories in the province refer specimens to CPL as a reference lab. Isolates may be forwarded for further characterization, especially if a notifiable disease.
  
• All serology for the province, except for CBS/Red Cross products, is done at CPL. All Clinical Virology is done at CPL, except for a small volume of rapid testing done outside CPL since approximately 2007. Some testing was also done in other laboratories during the Influenza H1N1 outbreak.
  
• CPL does all reference enteric bacteriology (except for C difficile toxin typing), all of the chlamydiology from 2000 onwards, a large portion of the enteric parasitology, and a minor portion of the bacteriology and mycology.
  
• The data contains a small percentage of records for testing related to non-Manitoba residents and non-Manitoba practitioners.
  
  Note: The LIMS data does not contain any information on tuberculosis (TB) testing. Cadham stopped conducting these tests in 2001/02. Tuberculosis testing results can be found in the Diagnostic Services of Manitoba (DSM) and Westman Labs data.

The Laboratory Information Management System (LIMS) database

The LIMS database covers the period from August 2009 to present, while the earlier CPL database covered the period from 1992/93 to July 2010. While the types of lab tests and services done at Cadham did not change, the data structure, variables, and names of tests and analyses vary significantly between these two databases. Code created for the earlier system will not work with the more recent LIMS data.

There are four main tables containing LIMS data:


• Patient table – containing a scrambled PHIN and basic demographic information. As the sample records also contain a scrambled PHIN this table may not be necessary. Caution: there can be multiple records (revisions) for a single individual as records are updated when their information changes. Best practice would be to use the registry to obtain this information.
  
• Sample table – containing scrambled PHIN, sample number, dates for when the sample was collected and received as well which section of the lab will be analyzing the specimen. The four main sections (identified using the x_sample_category variable) in the sample data) are:
  
  • Chemistry – Perinatal screening tests for a variety of metabolic and endocrine disorders. Chemistry tests account for less than 5% of all testing done at Cadham. The bulk of diagnostic chemistry tests are done by Diagnostic Services of Manitoba (DSM) or private labs.
    
  • Microbiology – Diagnostic testing for bacterial and fungal infections including chlamydia, gonorrhea, and clostridium difficile. Culture and antibiotic sensitivity analysis for bacterial infections.
    
  • Serology – Diagnostic tests using blood and plasma samples for infectious disease including viral hepatitis (A, B, & C), Epstein Barr (EBV), syphilis, HIV, Lyme disease, malaria, measles, etc.
    
  • Virology – Polymerase Chain Reaction (PCR) tests on adenovirus, influenza, coronavirus, CMV, Epstein Barr (EBV), measles, mumps, and the herpes family of virus.
    
    Note: There is some overlap in the types of organisms tested for by the microbiology, serology and virology sections depending on the types of tests used and specimen source. In general, tests for virus or bacteria antibodies or antigens (e.g. hep C IGM, EBV IGG etc.) are found under serology. PCR testing for viruses are done by virology while PCR testing for bacteria is in done by microbiology. Most blood-based tests are done by serology.
  
  
• Test table – Contains information on tests conducted on each sample. Contains sample number which can be used to link to the Sample table by sample number. One sample can have multiple tests done on it, each with have a unique test number in this table. For many tests with binary results (e.g., positive/negative) the test data also contains the overall results. The Test data does not contain any patient identifiers so it must be used in conjunction with the Sample data.
  
• Results table – Contains detailed results for each test. Contains test and sample number for linking to those tables. Multiple results may be available for a single test, each with a unique result number.

Methods

1. The first step in using the LIMS data is to select observations from the SAMPLE dataset. If you have a cohort you can subset the sample data to only include those individuals using a PHIN format. Note that the patient identifier in LIMS is FILE_PHIN.
   
   1. Exclude samples that have been cancelled using the STATUS variable (cancelled tests have STATUS= “X”;)
      
   2. Use the X_SAMPLE_CATEGORY to select the general area of the tests you are interested in. Categories to choose from include:
      
      1. CHEMISTRY
         
      2. MICRO
         
      3. SEROLOGY
         
      4. VIROLOGY
   3. The sample date-time variable (SAMPLED_DTTM) can be used as a proxy for date of diagnosis. Some observations will have missing sample dates. To handle this you can use the earliest of sampled, received, or login date-time values.
      dx_date= (DATEPART(MIN(SAMPLE_DTTM, RECD_DTTM, LOGIN_DTTM))
   
   
2. The second step is to link samples to the TEST data using SAMPLE_NUMBER. Creating a sample_number format is a simple way to select the tests for your samples. A single sample_number can be linked to multiple tests (a one-to-many linkage). Additionally you should:
   
   1. Exclude tests that have been cancelled using the STATUS variable (STATUS NE “X”) the same was as with the SAMPLE data.
      
   2. Select the tests you are interested in using the ANALYSIS variable. There are hundreds of different values for this variable and determining the relevant ones can be difficult. These values are generally abbreviated and require some domain specific knowledge to interpret. Using PROC FREQ to look through the different types of analysis, knowing the various names (and abbreviations) of the organisms you are looking for, and Googling (or using the search engine of your choice) the different test names is often useful.
      
      1. Often there are multiple values of ANALYSIS that sound like the are all tests for the same thing. For example, there are >10 ANALYSIS values that start with “HIV”. Don’t worry, some of these refer to different steps/tests done on one sample as part of the testing procedure, so there can be multiple observations for a single HIV test, and each will have their own test number (but all the same sample number). The main test observation will be linked to these “sub tests” through the PARENT_NUMBER variable. The main observation will have PARENT_TEST= “0”, while the “sub tests” have PARENT_TEST values equal to the TEST_NUMBER of the main test observation. We typically only need the overall result to determine a positive/negative test result.
         
      2. To select the main test observation use:
         
         1. PARENT_TEST= “0” and
            
         2. X_REPORTABLE= “T”. An observation being reportable means that it appears on final report sent to a physician. If an observation has X_REPORTABLE= “F” it means it does not appear on the final report and should not be interpreted by us as it may have been overridden or had an ambiguous outcome so another test was done on it, etc.
   3. Using the main test observation, we can often determine what the final determination is without having to link to the RESULTS table. There are two variables in the TEST data that come from the RESULT data, these are IN_SPEC and IN_CONTROL, both of which contain a Boolean T/F value.
      
      1. IN_CONTROL refers to whether that control part of the test worked. If IN_CONTROL= “F” then the test did not work properly, while IN_CONTROL= “T” means the control was successful
         
      2. IN_SPEC refers to whether the test on the sample was within the specification for a negative result (IN_SPEC= “T” is a negative test), while IN_SPEC= “F” indicates a positive diagnosis.
         
      3. A positive test results will thus have IN_CONTROL= “T” and IN_SPEC = “F”
   
   
3. Sometimes we need to look at the RESULT table, for example to determine a HIV positive patients viral load, which is a numeric result. TESTS can be linked to RESULTS by using the TEST_NUMBER. One test is often linked to several results.
   
   1. To interpret the results of a test with a positive vs negative outcome we can IN_SPEC/IN_CONTROL variables as before.
      
   2. Alternatively, or when numeric or more complex results are required, we can use the FORMATTED_ENTRY variable which contains text. Using FORMATTED_ENTRY can be complicated as a positive test could be reported as “POS”, “POSITIVE”, “POSITIVE/POSITIVE”, or “NEGATIVE/POSITIVE.
      
   3. To make it more fun, not all the results will be what people typically think of as a lab test. For example, for HIV, some observations contain information on whether an individual has had previous HIV tests in the LIMS data in the past or their current and past HIV status. The REPORTED_NAME variable contains information on what the result data is for. For example, a syphilis test result can have “components” of “History”, “Current-History”, “Syphilis” (which is a final, overall result), and “RPR Reading” (which refers to a type of antibody test).
   
   
4. For analysts with internal access to the Programmers website on the UM SharePoint site, there is example code available on using LIMS data for HIV, syphilis, and chlamydia/gonorrhea (see the Data Analysts Presentations available in the Links section below - internal access only.)