procedure_concept_id

SUS Outpatient Procedure Occurrence

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

SUS CCMDS Procedure Occurrence

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

SUS APC Procedure Occurrence

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

SUS AE Procedure Occurrence

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Rtds Procedure Occurrence

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Oxford Procedure Occurrence

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 UR Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 UR Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 UR Procedure Occurrence Diagnostic Procedure Snomed Ct

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 UR Procedure Occurrence Diagnostic Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 UR Procedure Occurrence Procedure OPCS

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 UR Procedure Occurrence Primary Procedure OPCS

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 UG Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 UG Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 UG Procedure Occurrence Diagnostic Procedure Snomed Ct

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 UG Procedure Occurrence Diagnostic Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 UG Procedure Occurrence Procedure OPCS

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 UG Procedure Occurrence Primary Procedure OPCS

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 SK Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 SK Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 SK Procedure Occurrence Diagnostic Procedure Snomed Ct

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 SK Procedure Occurrence Diagnostic Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 SK Procedure Occurrence Procedure OPCS

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 SK Procedure Occurrence Primary Procedure OPCS

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 SA Procedure Occurrence Procedure Opcs Procedure Date

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 SA Procedure Occurrence Primary Procedure Opcs Procedure Date

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 SA Procedure Occurrence Procedure OPCS Procedure Date

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 SA Procedure Occurrence Primary Procedure OPCS Procedure Date

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 LV Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 LV Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 LV Procedure Occurrence Procedure OPCS

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 LV Procedure Occurrence Primary Procedure OPCS

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

CosdV9LungProcedureOccurrenceRelapseMethodOfDetection

Source column RelapseMethodOfDetection. Lookup RelapseMethodOfDetection concepts for lung cancer recurrence.

RelapseMethodOfDetection procedure_concept_id notes
1 4303062 Morphology
2 4276031 Flow
3 4233623 Molecular
4 4240345 Clinical Examination
9 0 Other (not listed)

Notes

with LU as (
    select
        Record ->> '$.PrimaryPathway.ReferralAndFirstStageOfPatientPathway.DateFirstSeen' as DateFirstSeen,
        Record ->> '$.PrimaryPathway.ReferralAndFirstStageOfPatientPathway.DateFirstSeenCancerSpecialist' as DateFirstSeenCancerSpecialist,
        Record ->> '$.PrimaryPathway.LinkageDiagnosticDetails.DateOfPrimaryDiagnosisClinicallyAgreed' as DateOfPrimaryDiagnosisClinicallyAgreed,
        Record ->> '$.PrimaryPathway.Staging.StageDateFinalPretreatmentStage' as StageDateFinalPretreatmentStage,
        Record ->> '$.PrimaryPathway.Staging.StageDateIntegratedStage' as StageDateIntegratedStage,
        Record ->> '$.Treatment.TreatmentStartDateCancer' as TreatmentStartDateCancer,
        Record ->> '$.Treatment.Surgery.ProcedureDate' as ProcedureDate,
        unnest ([[Record ->> '$.NonPrimaryPathway.Recurrence.Relapse-MethodOfDetection.@code'], Record ->> '$.NonPrimaryPathway.Recurrence.Relapse-MethodOfDetection[*].@code'], recursive := true) as RelapseMethodOfDetection,
        Record ->> '$.LinkagePatientId.NhsNumber.@extension' as NhsNumber
    from omop_staging.cosd_staging_901
    where type = 'LU'
)
select
    distinct
        RelapseMethodOfDetection,
        NhsNumber,
        least(
            cast(DateFirstSeen as date),
            cast(DateFirstSeenCancerSpecialist as date),
            cast(DateOfPrimaryDiagnosisClinicallyAgreed as date),
            cast(StageDateFinalPretreatmentStage as date),
            cast(StageDateIntegratedStage as date),
            cast(TreatmentStartDateCancer as date),
            cast(ProcedureDate as date)
        ) as Date
from LU o
where o.RelapseMethodOfDetection is not null
  and not (
        DateFirstSeen is null and
        DateFirstSeenCancerSpecialist is null and
        DateOfPrimaryDiagnosisClinicallyAgreed is null and
        StageDateFinalPretreatmentStage is null and
        StageDateIntegratedStage is null and
        TreatmentStartDateCancer is null and
        ProcedureDate is null
    )

Comment or raise an issue for this mapping.

Cosd V9 Lung Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Cosd V9 Lung Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

CosdV8LungProcedureOccurrenceRelapseMethodOfDetection

Source column RelapseMethodDetectionType. Lookup RelapseMethodOfDetection concepts for lung cancer recurrence.

RelapseMethodDetectionType procedure_concept_id notes
1 4303062 Morphology
2 4276031 Flow
3 4233623 Molecular
4 4240345 Clinical Examination
9 0 Other (not listed)

Notes

with LU as (
    select 
        Record ->> '$.Lung.LungCore.LungCoreReferralAndFirstStageOfPatientPathway.DateFirstSeen' as DateFirstSeen,
        Record ->> '$.Lung.LungCore.LungCoreReferralAndFirstStageOfPatientPathway.SpecialistDateFirstSeen' as SpecialistDateFirstSeen,
        Record ->> '$.Lung.LungCore.LungCoreLinkageDiagnosticDetails.ClinicalDateCancerDiagnosis' as ClinicalDateCancerDiagnosis,
        Record ->> '$.Lung.LungCore.LungCoreStaging.IntegratedStageTNMStageGroupingDate' as IntegratedStageTNMStageGroupingDate,
        Record ->> '$.Lung.LungCore.LungCoreStaging.FinalPreTreatmentTNMStageGroupingDate' as FinalPreTreatmentTNMStageGroupingDate,
        unnest ([[Record ->> '$.Lung.LungCore.LungCoreTreatment.CancerTreatmentStartDate'], Record ->> '$.Lung.LungCore.LungCoreTreatment[*].CancerTreatmentStartDate'], recursive := true) as CancerTreatmentStartDate,
        Record ->> '$.Lung.LungCore.LungCoreTreatment.LungCoreSurgeryAndOtherProcedures.ProcedureDate' as ProcedureDate,
        Record ->> '$.Lung.LungCore.LungCoreNonPrimaryCancerPathwayALLAMLAndMPAL.RelapseMethodDetectionType.@code' as RelapseMethodDetectionType,
        Record ->> '$.Lung.LungCore.LungCoreLinkagePatientId.NHSNumber.@extension' as NhsNumber
    from omop_staging.cosd_staging_81
    where Type = 'LU'
)
select
      distinct
          RelapseMethodDetectionType,
          NhsNumber,
          least(
                cast (DateFirstSeen as date),
                cast (SpecialistDateFirstSeen as date),
                cast (ClinicalDateCancerDiagnosis as date),
                cast (IntegratedStageTNMStageGroupingDate as date),
                cast (FinalPreTreatmentTNMStageGroupingDate as date),
                cast (CancerTreatmentStartDate as date),
                cast (ProcedureDate as date)
              ) as Date
from LU o
where o.RelapseMethodDetectionType is not null
  and not (
    DateFirstSeen is null and
    SpecialistDateFirstSeen is null and
    ClinicalDateCancerDiagnosis is null and
    IntegratedStageTNMStageGroupingDate is null and
    FinalPreTreatmentTNMStageGroupingDate is null and
    CancerTreatmentStartDate is null and
    ProcedureDate is null
    )

Comment or raise an issue for this mapping.

Cosd V8 Lung Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 Lung Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 HA Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 HA Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 HA Procedure Occurrence Diagnostic Procedure Snomed Ct

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 HA Procedure Occurrence Diagnostic Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V901 CT Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V901 CT Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 CT Procedure Occurrence Primary Procedure OPCS

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 CO Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 CO Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 CO Procedure Occurrence Procedure OPCS

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 CO Procedure Occurrence Primary Procedure OPCS

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Cosd V9 Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Cosd V9 Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Cosd V8 Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

Cosd V8 Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 Breast Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 Breast Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 Breast Procedure Occurrence Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V8 Breast Procedure Occurrence Primary Procedure Opcs

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 BA Procedure Occurrence Procedure Opcs Procedure Date

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 BA Procedure Occurrence Primary Procedure Opcs Procedure Date

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.

COSD V9 BA Procedure Occurrence Biopsy Type Procedure Date

Source column BiopsyType. BIOPSY ANAESTHETIC TYPE

BiopsyType procedure_concept_id notes
1 4303995 Local anaesthetic
2 4219502 Sedation
3 4174669 General anaesthetic
9 0 Not Known (Not Recorded)
  • BiopsyType The type of biopsy carried out on Central Nervous System (CNS) tumours during a Central Nervous System Cancer Care Spell. Coded values include Frame-based stereotactic biopsy (1), Frameless stereotactic biopsy (2), Open biopsy (3), Percutaneous biopsy (4), Endoscopic biopsy (5), Other biopsy (6). Will be stored as procedure_source_value and mapped to a standard concept in a later ETL step. BIOPSY TYPE (CENTRAL NERVOUS SYSTEM TUMOURS)
select distinct
    Record ->> '$.LinkagePatientId.NhsNumber.@extension' as NhsNumber,
    Record ->> '$.Treatment.Surgery.ProcedureDate' as ProcedureDate,
    Record ->> '$.Treatment.Surgery.SurgeryCNS.BiopsyType.@code' as BiopsyType
from omop_staging.cosd_staging_901
where type = 'BA'
  and NhsNumber is not null
  and BiopsyType is not null;

Comment or raise an issue for this mapping.

COSD V8 BA Procedure Occurrence Primary Procedure OPCS Procedure Date

Source column procedure_source_concept_id. Maps concepts to standard valid concepts in the procedure domain.

Comment or raise an issue for this mapping.