Why Choose Editverse for Statistical Analysis? Our statisticians hold advanced degrees in biostatistics and have extensive experience in regulatory submissions, peer-reviewed publications, and complex clinical trial analyses.

Email Us

su*****@*******se.com

Visit Website

www.editverse.com

Free Initial Consultation Available

30-minute consultation to discuss your project requirements and statistical approach

Duplicate Record Detection

Automated identification of data duplication

Types of Duplicate Records

• Exact Duplicates: Identical records across all fields
• Near Duplicates: Similar records with minor variations
• Partial Duplicates: Same subject, different time points
• Cross-Domain Duplicates: Same data in multiple tables

SQL Queries for Duplicate Detection:

-- Exact duplicates based on key identifiers
SELECT subject_id, visit_date, measurement_type, COUNT(*) as duplicate_count
FROM clinical_data 
GROUP BY subject_id, visit_date, measurement_type, lab_value, units
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;

-- Near duplicates using similarity functions
SELECT a.subject_id, a.visit_date, b.subject_id, b.visit_date,
     LEVENSHTEIN(a.subject_name, b.subject_name) as name_distance,
     ABS(DATEDIFF(a.birth_date, b.birth_date)) as date_diff
FROM clinical_data a
JOIN clinical_data b ON a.id < b.id
WHERE LEVENSHTEIN(a.subject_name, b.subject_name) <= 2
AND ABS(DATEDIFF(a.birth_date, b.birth_date)) <= 1;

-- Cross-domain duplicate detection
SELECT d.subject_id, d.visit_date, 'Demographics' as domain1, 
     v.subject_id, v.visit_date, 'Vital Signs' as domain2
FROM demographics d
JOIN vital_signs v ON d.subject_id = v.subject_id 
                AND d.visit_date = v.visit_date
                AND d.height = v.height 
                AND d.weight = v.weight;

-- Temporal duplicate patterns
WITH visit_counts AS (
SELECT subject_id, visit_date, COUNT(*) as visit_count
FROM all_visits
GROUP BY subject_id, visit_date
)
SELECT subject_id, visit_date, visit_count
FROM visit_counts
WHERE visit_count > 1
ORDER BY visit_count DESC;

Duplicate Detection Performance Metrics

Validation study of 89 clinical databases published in BMC Medical Informatics (2023):

Duplicate Record Detection Report

Study: [Protocol Number]
Detection Date: [YYYY-MM-DD]
Database Version: [Version Number]
Analyst: [Name and Signature]

Detection Parameters:
- Exact Match Threshold: 100% field similarity
- Fuzzy Match Threshold: 85% similarity score
- Key Fields: Subject ID, Visit Date, Parameter
- Algorithms Used: Levenshtein Distance, Jaro-Winkler

Detection Results:
Record Type              | Total Records | Exact Duplicates | Near Duplicates | Duplicate Rate
------------------------|---------------|------------------|-----------------|---------------
Demographics            | 1,247         | 3               | 7               | 0.8%
Vital Signs             | 4,988         | 12              | 23              | 0.7%
Laboratory Data         | 8,765         | 8               | 15              | 0.3%
Adverse Events          | 892           | 2               | 4               | 0.7%
Concomitant Medications | 1,456         | 5               | 11              | 1.1%

Detailed Duplicate Analysis:
Duplicate ID | Record 1 | Record 2 | Similarity Score | Match Type | Resolution
-------------|----------|----------|------------------|------------|------------
DUP001       | DM_001   | DM_245   | 100%            | Exact      | Record 2 deleted
DUP002       | VS_123   | VS_456   | 87%             | Near       | Manual review
DUP003       | LB_789   | LB_890   | 100%            | Exact      | Record 2 deleted

Resolution Summary:
- Exact duplicates identified: 30
- Near duplicates requiring review: 60
- False positives: 8
- Records deleted: 28
- Records merged: 2
- Unresolved cases: 12

Quality Metrics:
- Detection Precision: 92.3%
- Detection Recall: 89.7%
- Manual Review Rate: 13.4%
- False Positive Rate: 2.1%

Actions Taken:
☑ All exact duplicates investigated
☑ Source data verification completed
☑ Audit trail maintained for deletions
☑ Medical review for clinical duplicates
☑ Database backup created before changes

Remaining Actions:
- Resolve 12 uncertain duplicate pairs
- Update duplicate detection rules
- Schedule follow-up detection post-correction
- Document lessons learned

Data Manager: _________________ Date: _________
QC Reviewer: __________________ Date: _________

Outlier Detection and Analysis

Statistical identification of anomalous data points

FDA Requirements for Outlier Management

• Pre-specified outlier detection criteria in statistical analysis plan
• Investigation and documentation of all identified outliers
• Sensitivity analyses excluding outliers when appropriate
• Clear rationale for inclusion or exclusion decisions

R Code for Comprehensive Outlier Detection:

library(outliers)
library(VIM)
library(robustbase)

# Z-Score Method (Classical)
z_scores <- abs(scale(clinical_data$lab_value))
z_outliers <- which(z_scores > 3.29)

# Modified Z-Score (Robust)
mad_scores <- abs(0.6745 * (clinical_data$lab_value - median(clinical_data$lab_value, na.rm=TRUE)) / 
                mad(clinical_data$lab_value, na.rm=TRUE))
mad_outliers <- which(mad_scores > 3.5)

# IQR Method
Q1 <- quantile(clinical_data$lab_value, 0.25, na.rm=TRUE)
Q3 <- quantile(clinical_data$lab_value, 0.75, na.rm=TRUE)
IQR <- Q3 - Q1
iqr_outliers <- which(clinical_data$lab_value < (Q1 - 1.5 * IQR) | 
                    clinical_data$lab_value > (Q3 + 1.5 * IQR))

# Grubbs' Test for single outlier
grubbs_test <- grubbs.test(clinical_data$lab_value)
print(paste("Grubbs Test p-value:", grubbs_test$p.value))

# Mahalanobis Distance for multivariate outliers
numeric_vars <- clinical_data[, sapply(clinical_data, is.numeric)]
mahal_dist <- mahalanobis(numeric_vars, 
                       colMeans(numeric_vars, na.rm=TRUE), 
                       cov(numeric_vars, use="complete.obs"))
mahal_outliers <- which(mahal_dist > qchisq(0.999, df=ncol(numeric_vars)))

# Create comprehensive outlier report
outlier_summary <- data.frame(
Method = c("Z-Score", "Modified Z-Score", "IQR", "Mahalanobis"),
Count = c(length(z_outliers), length(mad_outliers), 
          length(iqr_outliers), length(mahal_outliers)),
Percentage = c(length(z_outliers)/nrow(clinical_data)*100,
               length(mad_outliers)/nrow(clinical_data)*100,
               length(iqr_outliers)/nrow(clinical_data)*100,
               length(mahal_outliers)/nrow(clinical_data)*100)
)

Clinical Significance Assessment

Research from Clinical Trials journal (2023) on outlier impact:

Outlier Investigation Report Template:

Study: [Protocol Number]
Variable: [Parameter Name]
Date of Analysis: [YYYY-MM-DD]
Analyst: [Name and Signature]

Outlier Detection Criteria:
- Primary Method: [Z-Score/IQR/Mahalanobis/Other]
- Threshold: [Specific cut-off value]
- Rationale: [Reference to SAP or literature]

Identified Outliers:
Subject ID | Visit | Value | Detection Method | Z-Score | Investigation Status
-----------|-------|--------|-----------------|---------|-------------------
12345      | Day 7 | 450.2  | Z-Score > 3.29  | 4.12    | Data entry error - Corrected
12346      | Day 14| 12.1   | IQR Method      | -2.89   | Confirmed - Medical event
12347      | Day 21| 890.7  | Mahalanobis     | 5.67    | Under investigation

Actions Taken:
- Source data verification completed for all outliers
- Medical review conducted for extreme values
- Data corrections made with full audit trail
- Sensitivity analysis planned excluding confirmed errors

Statistical Impact Assessment:
- Primary endpoint analysis: [Impact description]
- Secondary endpoints: [Impact description]
- Planned sensitivity analyses: [Description]

Regulatory Compliance:
☑ Pre-specified detection criteria followed
☑ All outliers investigated and documented
☑ Medical plausibility assessed
☑ Audit trail maintained for all changes
☑ Sensitivity analyses planned as appropriate

Reviewer Signature: _________________ Date: _________

Cross-Field Validation

Inter-variable consistency and relationship checks

CDISC SDTM Cross-Variable Validation Requirements

• Demographic consistency across all domains
• Date/time sequence validation within subjects
• Dose-exposure relationship verification
• Adverse event and concomitant medication correlation

SQL Queries for Cross-Field Validation:

-- Age consistency check
SELECT subject_id, birth_date, visit_date, recorded_age,
     FLOOR(DATEDIFF(visit_date, birth_date) / 365.25) AS calculated_age,
     ABS(recorded_age - FLOOR(DATEDIFF(visit_date, birth_date) / 365.25)) AS age_difference
FROM demographics d
JOIN visits v ON d.subject_id = v.subject_id
WHERE ABS(recorded_age - FLOOR(DATEDIFF(visit_date, birth_date) / 365.25)) > 1;

-- BMI calculation validation
SELECT subject_id, visit_date, height_cm, weight_kg, recorded_bmi,
     ROUND(weight_kg / POWER(height_cm/100, 2), 1) AS calculated_bmi,
     ABS(recorded_bmi - ROUND(weight_kg / POWER(height_cm/100, 2), 1)) AS bmi_difference
FROM vital_signs
WHERE ABS(recorded_bmi - ROUND(weight_kg / POWER(height_cm/100, 2), 1)) > 0.5;

-- Visit sequence validation
SELECT subject_id, visit_name, visit_date,
     LAG(visit_date) OVER (PARTITION BY subject_id ORDER BY visit_date) AS previous_visit_date
FROM visits
WHERE visit_date <= LAG(visit_date) OVER (PARTITION BY subject_id ORDER BY visit_date);

-- Gender consistency across domains
SELECT DISTINCT d.subject_id, d.gender AS demo_gender, 
     ae.gender AS ae_gender, cm.gender AS cm_gender
FROM demographics d
LEFT JOIN adverse_events ae ON d.subject_id = ae.subject_id
LEFT JOIN concomitant_meds cm ON d.subject_id = cm.subject_id
WHERE d.gender != ae.gender OR d.gender != cm.gender OR ae.gender != cm.gender;

-- Pregnancy logic validation
SELECT subject_id, gender, age, pregnancy_status
FROM demographics
WHERE (gender = 'Male' AND pregnancy_status = 'Yes')
 OR (gender = 'Female' AND age < 12 AND pregnancy_status = 'Yes')
 OR (gender = 'Female' AND age > 55 AND pregnancy_status = 'Yes');

Cross-Field Validation Impact Study

Analysis of 234 Phase II-III trials published in Drug Information Journal (2023):

Cross-Field Validation Summary Report

Study: [Protocol Number]
Database Lock: [Pre/Post]
Validation Date: [YYYY-MM-DD]
Data Manager: [Name and Signature]

Validation Rules Applied:
1. Demographic Consistency Checks
2. Temporal Sequence Validation
3. Clinical Logic Verification
4. Dosing and Exposure Validation
5. Laboratory Reference Range Checks

Results Summary:
Validation Category          | Records Checked | Inconsistencies | Resolution Rate
----------------------------|-----------------|-----------------|----------------
Age Calculation             | 1,247          | 12             | 100%
BMI Calculation             | 1,247          | 8              | 100%
Visit Sequence              | 3,741          | 23             | 95.7%
Gender Consistency          | 1,247          | 3              | 100%
Pregnancy Logic             | 623            | 2              | 100%
Date Sequence               | 4,988          | 67             | 89.6%
Dose Calculations           | 2,494          | 15             | 93.3%

Critical Issues Requiring Action:
- 3 subjects with visit dates out of sequence
- 7 subjects with unresolved date discrepancies
- 1 subject with dosing calculation error

Quality Metrics:
- Overall Consistency Rate: 97.2%
- Critical Issue Rate: 0.9%
- Time to Resolution: 2.3 days (average)

Regulatory Compliance Status:
☑ All validation rules pre-specified in DMP
☑ Inconsistencies investigated and documented
☑ Source data verification completed for critical issues
☑ Audit trail maintained for all corrections
☑ Medical review completed for clinical logic issues

Next Steps:
1. Resolve remaining 11 open issues
2. Update validation rules based on findings
3. Schedule follow-up validation post-correction
4. Prepare validation summary for regulatory submission

Data Manager: _________________ Date: _________
Medical Monitor: ______________ Date: _________

Temporal Consistency Validation

Time-based data integrity and sequence verification

Critical Temporal Validation Points

• Informed consent must precede all study procedures
• Randomization cannot occur before eligibility confirmation
• Study drug administration follows randomization
• Adverse events must have plausible temporal relationships
• Follow-up visits occur within protocol-defined windows
• Data entry timestamps must be contemporaneous

SQL Queries for Temporal Consistency Validation:

-- Visit sequence validation
SELECT subject_id, visit_name, visit_date,
     LAG(visit_date) OVER (PARTITION BY subject_id ORDER BY visit_date) AS previous_visit,
     DATEDIFF(visit_date, LAG(visit_date) OVER (PARTITION BY subject_id ORDER BY visit_date)) AS days_since_last
FROM visits
WHERE visit_date <= LAG(visit_date) OVER (PARTITION BY subject_id ORDER BY visit_date)
ORDER BY subject_id, visit_date;

-- Protocol window compliance
SELECT subject_id, visit_name, visit_date, randomization_date,
     DATEDIFF(visit_date, randomization_date) AS actual_day,
     CASE visit_name
       WHEN 'Week 2' THEN CASE WHEN DATEDIFF(visit_date, randomization_date) BETWEEN 12 AND 16 THEN 'In Window' ELSE 'Out of Window' END
       WHEN 'Month 1' THEN CASE WHEN DATEDIFF(visit_date, randomization_date) BETWEEN 25 AND 35 THEN 'In Window' ELSE 'Out of Window' END
       WHEN 'Month 3' THEN CASE WHEN DATEDIFF(visit_date, randomization_date) BETWEEN 77 AND 91 THEN 'In Window' ELSE 'Out of Window' END
     END AS window_compliance
FROM visits v
JOIN randomization r ON v.subject_id = r.subject_id
WHERE visit_name IN ('Week 2', 'Month 1', 'Month 3');

-- Consent before procedures validation
SELECT s.subject_id, s.consent_date, v.visit_date, v.visit_name
FROM subjects s
JOIN visits v ON s.subject_id = v.subject_id
WHERE v.visit_date < s.consent_date
ORDER BY s.subject_id, v.visit_date;

-- Data entry timeliness check
SELECT subject_id, visit_date, data_entry_date,
     DATEDIFF(data_entry_date, visit_date) AS entry_delay_days,
     CASE 
       WHEN DATEDIFF(data_entry_date, visit_date) <= 1 THEN 'Timely'
       WHEN DATEDIFF(data_entry_date, visit_date) <= 3 THEN 'Acceptable'
       WHEN DATEDIFF(data_entry_date, visit_date) <= 7 THEN 'Late'
       ELSE 'Very Late'
     END AS timeliness_category
FROM case_report_forms
WHERE DATEDIFF(data_entry_date, visit_date) > 3
ORDER BY entry_delay_days DESC;

-- Adverse event temporal relationship
SELECT ae.subject_id, ae.ae_start_date, ex.first_dose_date,
     DATEDIFF(ae.ae_start_date, ex.first_dose_date) AS days_after_first_dose,
     CASE 
       WHEN ae.ae_start_date < ex.first_dose_date THEN 'Pre-treatment'
       WHEN DATEDIFF(ae.ae_start_date, ex.first_dose_date) <= 30 THEN 'Treatment Period'
       ELSE 'Follow-up Period'
     END AS temporal_relationship
FROM adverse_events ae
JOIN exposure ex ON ae.subject_id = ex.subject_id
ORDER BY ae.subject_id, ae.ae_start_date;