Baseline data for parallel statistics calculation

Baseline data for parallel statistics calculation

FAERS reports from Q1 2004 - Q4 2024 (this is actually a quarter behind as of 06252025 but we will hold off on Q1 2025 for now)

• all reports from FDA loaded into the postgres tables

  • demo and demo legacy

  • indi and indi_legacy

  • outc and outc_legacy

  • reac and reac_legacy

  • rpsr and rpsr_legacy

  • ther and ther_legacy

  • drug and drug_legacy

• staging vocabulary - latest vocabulary with MeDDRA and RxNorm that corresponds date-wise with the FAERS data (currently true in cem_development)

  • concept -- NOTE: has NP vocabulary that was validated in prior work. We will use this for now.

    • integrating the NaPDI NP concepts and mappings to RxNorm NP products

    • NOTE: multi-ingredient drug products are a known issue to be fixed later (meaning that all quarterly calculations are not correct for multi-ingredient drugs)

  • concept_relationship

    • integrating the NaPDI NP relationships

• Deduplication (part II) specific

  • vigibase tables used for vigimatch - date corresponding with FAERS -- needs be checked (Current Vigibase is up through June 2025 -- Brian to confirm that the schema is on vigibase_development))

    • umcdersafetyreportid

    • suspectedduplicate

  • country_code -- NOTE: permission to skip and go with prior version

  • combined_drug_mapping (after the workflow for creating the table has been ran for the most recent FAERS data)

  • standard_case_outcome (after the workflow for creating the table has been ran for the most recent FAERS data)

  • standard_case_outcome_drilldown (after the workflow for creating the table has been ran for the most recent FAERS data)

  • standard_case_drug (after the workflow for creating the table has been ran for the most recent FAERS data)

  • lit_ref (preferably b/c of the need to make calls to pubmed when build, but we need to consider the implication of cumulative calculations and possibly manage so that there is not time leakage)

Known TODOs form the slurm job (https://github.com/rkboyce/NaPDI-pv/blob/crc-time-injection/job.slurm)

• listing staging tables

• dropping records from all FAERS report-related tables after the chosen year/quarter

• fix the issue where the reaction (ADR, MeDRA-coded) is put in a column incorrectly named outcome_concept_id - happens in https://github.com/rkboyce/NaPDI-pv/blob/crc-time-injection/derive_standard_drug_outcome_count.sql and should be named adr_concept_id

• once the fix above happens, be sure to port the other SQL to use adr_concept_id wherever outcome_concept_id was before (e.g., https://github.com/rkboyce/NaPDI-pv/blob/crc-time-injection/derive_standard_drug_outcome_contingency_table.sql)

• add Xiaotong's drug-drug-adr calculations (specified here) - and code here -- focus on porting the SQL for drug-drug-ADR and the other is drug-drug-SMQ (ADR as a Standardizd MedDRA Query)

  • NOTE: this depends on a correct standard_drug_adr_table (note name change from standard_np_drug_outcome) which you can see a predecessor created here