ETL Instructions for Mapping NAACCR Treatment Data into the OMOP CDM

This document details instructions for mapping and ETLing NAACCR treatment data into the OMOP CDM. NAACCR treatment data will be stored differently within various tumor registry software systems. The document assumes an idealized, generic tumor registry system. The hope is that instructions specific to actual tumor registry systems will be drafted as they are encountered by the OHDSI community.

The document only currently provides an example of ETLing NAACCR treatment data with no attempt to make connections to lower level clinical events. Thus, no insertions into the EPISODE_EVENT table. Further vocabulary work will be necessary be able to provide such examples.

Patient Identity

The insertion of any NAACCR treatment data will require the mapping of patient identity from NAACCR to the OMOP PERSON table. How this is achieved will be unique to each institution's OMOP instance. However, in most cases NAACCR Item #2300 'Medical Record Number' will be used to map from a tumor registry patient to an OMOP Person entry. For the purposes of this document, we will assume this mapping has occurred and been placed into a @person_id variable.

Chemotherapy Treatment Data

Chemotherapy treatment data in NAACCR is stored in the following NAACCR items (grouped by OMOP destination table):

Episode

NAACCR Item #1390 'RX Summ--Chemo'
NAACCR Item #1220 'RX Date Chemo'

Here are the steps to map these NAACCR items into the OMOP EPISODE table.

Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Episode concept ('Treatment Regimen') into an @episode_concept_id variable:

    SELECT @episode_concept_id = c1.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Episode'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Episode'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1390'

Extract the value into @naaccr_item_1390_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1390 'RX Summ--Chemo'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Treatment concept ('Chemotherapy') into an @episode_object_concept_id variable and an @episode_source_concept_id variable:

    SELECT   @episode_object_concept_id = c1.concept_id
           , @episode_source_concept_id = c2.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Treatment'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Treatment'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1390_' || @naaccr_item_1390_value

Not all possible values of NAACCR Item #1390 'RX Summ--Chemo' map to a standard Treatment concept ('Chemotherapy'). Some of NAACCR Item #1390's possible values represent the absence of a Chemotherapy treatment. Absence of data is not represented within OMOP.

If a standard Treatment concept cannot be mapped, then stop. No entry should be inserted into the EPISODE table.
If a standard Treatment concept can be mapped, then proceed.

Extract the value into @episode_start_date variable from the column in the table in your tumor registry mapped to NAACCR Item #1220 'RX Date Chemo'.
Extract the appropriate value into an @episode_concept_type_id variable.

    SELECT @episode_concept_type_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Episode Type'
    AND concept_name = 'Pre-made treatment abstraction with no clinical events connections.'

Execute the following SQL to insert an entry into the OMOP EPISODE table:

     INSERT INTO EPISODE(
                           episode_id
                         , person_id
                         , episode_concept_id
                         , episode_start_date
                         , episode_start_datetime
                         , episode_parent_id
                         , episode_type_concept_id
                         , episode_source_value
                         , episode_source_concept_id
                        )
     VALUES             (
                           @next_episode_id
                         , @person_id
                         , @episode_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @next_episode_id
                         , @episode_type_concept_id
                         , @naaccr_item_1390_value
                         , @episode_source_concept_id
                        );

Hormone Therapy Treatment Data

Hormone therapy treatment data in NAACCR is stored in the following NAACCR items (grouped by OMOP destination table):

Episode

NAACCR Item #1400 'RX Summ--Hormone'
NAACCR Item #1230 'RX Date Hormone'

Here are the steps to map these NAACCR items into the OMOP EPISODE table.

Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Episode concept ('Treatment Regimen') into an @episode_concept_id variable:

    SELECT @episode_concept_id = c1.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Episode'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Episode'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1400'

Extract the value into @naaccr_item_1400_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1400 'RX Summ--Hormone'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Treatment concept ('Hormone therapy') into an @episode_object_concept_id variable and an @episode_source_concept_id variable:

    SELECT   @episode_object_concept_id = c1.concept_id
           , @episode_source_concept_id = c2.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Treatment'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Treatment'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1400_' || @naaccr_item_1400_value

Not all possible values of NAACCR Item #1400 'RX Summ--Hormone' map to a standard Treatment concept (' Hormone therapy'). Some of NAACCR Item #1400's possible values represent the absence of a Hormone therapy treatment. Absence of data is not represented within OMOP.

If a standard Treatment concept cannot be mapped, then stop. No entry should be inserted into the EPISODE table.
If a standard Treatment concept can be mapped, then proceed.

Extract the value into @episode_start_date variable from the column in the table in your tumor registry mapped to NAACCR Item #1230 'RX Date Hormone'.
Extract the appropriate value into an @episode_concept_type_id variable.

    SELECT @episode_concept_type_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Episode Type'
    AND concept_name = 'Pre-made treatment abstraction with no clinical events connections.'

Execute the following SQL to insert an entry into the OMOP EPISODE table:

     INSERT INTO EPISODE(
                           episode_id
                         , person_id
                         , episode_concept_id
                         , episode_start_date
                         , episode_start_datetime
                         , episode_parent_id
                         , episode_type_concept_id
                         , episode_source_value
                         , episode_source_concept_id
                        )
     VALUES             (
                           @next_episode_id
                         , @person_id
                         , @episode_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @next_episode_id
                         , @episode_type_concept_id
                         , @naaccr_item_1400_value
                         , @episode_source_concept_id
                        );

Immunological Therapy Treatment Data

Immunological therapy treatment data in NAACCR is stored in the following NAACCR items (grouped by OMOP destination table):

Episode

NAACCR Item #1410 'RX Summ--BRM'
NAACCR Item #1240 'RX Date BRM'

Here are the steps to map these NAACCR items into the OMOP EPISODE table.

Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Episode concept ('Treatment Regimen') into an @episode_concept_id variable:

    SELECT @episode_concept_id = c1.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Episode'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Episode'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1410'

Extract the value into @naaccr_item_1410_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1410 'RX Summ--BRM'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Treatment concept (' Hormone therapy') into an @episode_object_concept_id variable and an @episode_source_concept_id variable:

    SELECT   @episode_object_concept_id = c1.concept_id
           , @episode_source_concept_id = c2.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Treatment'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Treatment'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1410_' || @naaccr_item_1410_value

Not all possible values of NAACCR Item #1410 'RX Summ--BRM' map to a standard Treatment concept ('Hormone therapy'). Some of NAACCR Item #1410's possible values represent the absence of a Immunological therapy treatment. Absence of data is not represented within OMOP.

If a standard Treatment concept cannot be mapped, then stop. No entry should be inserted into the EPISODE table.
If a standard Treatment concept can be mapped, then proceed.

Extract the value into @episode_start_date variable from the column in the table in your tumor registry mapped to NAACCR Item #1240 'RX Date BRM'.
Extract the appropriate value into an @episode_concept_type_id variable.

    SELECT @episode_concept_type_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Episode Type'
    AND concept_name = 'Pre-made treatment abstraction with no clinical events connections.'

Execute the following SQL to insert an entry into the OMOP EPISODE table:

     INSERT INTO EPISODE(
                           episode_id
                         , person_id
                         , episode_concept_id
                         , episode_start_date
                         , episode_start_datetime
                         , episode_parent_id
                         , episode_type_concept_id
                         , episode_source_value
                         , episode_source_concept_id
                        )
     VALUES             (
                           @next_episode_id
                         , @person_id
                         , @episode_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @next_episode_id
                         , @episode_type_concept_id
                         , @naaccr_item_1410_value
                         , @episode_source_concept_id
                        );

Radiotherapy Treatment Data

Radiotherapy treatment data in NAACCR is stored in the following NAACCR items (grouped by OMOP destination table):

Episode

NAACCR Item #1506 'Phase I Radiation Treatment Modality'
NAACCR Item #1516 'Phase II Radiation Treatment Modality'
NAACCR Item #1526 'Phase III Radiation Treatment Modality'
NAACCR Item #1210 'RX Date Radiation'
NAACCR Item #3220 'RX Date Rad Ended'

Measurement

NAACCR Item #1501 'Phase I Dose per Fraction'
NAACCR Item #1502 'Phase I Radiation External Beam Planning Tech'
NAACCR Item #1503 'Phase I Number of Fractions'
NAACCR Item #1504 'Phase I Radiation Primary Treatment Volume'
NAACCR Item #1505 'Phase I Radiation to Draining Lymph Nodes'
NAACCR Item #1507 'Phase I Total Dose'
NAACCR Item #1511 'Phase II Dose per Fraction'
NAACCR Item #1512 'Phase II Radiation External Beam Planning Tech'
NAACCR Item #1513 'Phase II Number of Fractions'
NAACCR Item #1514 'Phase II Radiation Primary Treatment Volume'
NAACCR Item #1515 'Phase II Radiation to Draining Lymph Nodes'
NAACCR Item #1517 'Phase II Total Dose'
NAACCR Item #1521 'Phase II Dose per Fraction'
NAACCR Item #1522 'Phase II Radiation External Beam Planning Tech'
NAACCR Item #1513 'Phase II Number of Fractions'
NAACCR Item #1524 'Phase II Radiation Primary Treatment Volume'
NAACCR Item #1525 'Phase II Radiation to Draining Lymph Nodes'
NAACCR Item #1527 'Phase II Total Dose'
NAACCR Item #1533 'Number of Phases of Rad Treatment to this Volume'
NAACCR Item #1533 'Total Dose'

Because NAACCR breaks down the tracking of radiotherapy treatment into 3 phases, we will need to create parent/child relationship entries within the EPISODE table to represent each phase. We will create a top-level entry in the EPISODE table that spans all phases and represents the entire radiotherapy treatment. This top-level entry will be the parent of all radiotherapy phases. Here are the steps to map these NAACCR items into the OMOP EPISODE and MEASUREMENT tables.

Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Episode concept ('Treatment Regimen') into an @episode_concept_id variable:

    SELECT @episode_concept_id = c1.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Episode'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Episode'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1506'

Extract the value into @naaccr_item_1506_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1506 'Phase I Radiation Treatment Modality'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Treatment concept ('Radiothearpy') into an @episode_object_concept_id variable and an @episode_source_concept_id variable:

    SELECT   @episode_object_concept_id = c1.concept_id
           , @episode_source_concept_id = c2.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Treatment'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Treatment'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1506_' || @naaccr_item_1506_value

Not all possible values of NAACCR Item #1506 'Phase I Radiation Treatment Modality' map to a standard Treatment concept ('Radiotherapy'). Some of NAACCR Item #1506's possible values represent the absence of radiotherapy treatment. Absence of data is not represented within OMOP.

If a standard Treatment concept cannot be mapped, then stop. No entry should be inserted into the EPISODE table.
If a standard Treatment concept can be mapped, then proceed.

Extract the value into @episode_start_date variable from the column in the table in your tumor registry mapped to NAACCR Item #1210 'RX Date Radiation'.
Extract the value into @episode_end_date variable from the column in the table in your tumor registry mapped to NAACCR Item #3220 'RX Date Rad Ended'.
Extract the appropriate value into an @episode_concept_type_id variable.

    SELECT @episode_concept_type_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Episode Type'
    AND concept_name = 'Pre-made treatment abstraction with no clinical events connections.'

Execute the following SQL to insert an entry into the OMOP EPISODE table:

     INSERT INTO EPISODE(
                           episode_id
                         , person_id
                         , episode_concept_id
                         , episode_start_date
                         , episode_start_datetime
                         , episode_end_date
                         , episode_end_datetime
                         , episode_parent_id
                         , episode_type_concept_id
                         , episode_source_value
                         , episode_source_concept_id
                        )
     VALUES             (
                           @next_episode_id
                         , @person_id
                         , @episode_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @episode_end_date
                         , @episode_end_date
                         , @next_episode_id
                         , @episode_type_concept_id
                         , @naaccr_item_1506_value
                         , @episode_source_concept_id
                        );

To insert modifier attribute NAACCR Item #1532 'Number of Phases of Rad Treatment to this Volume' for the EPISODE entry inserted in step 8, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Number of Phases of Rad Treatment to this Volume') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1532'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1532'

Extract the value into @naaccr_item_1532_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1532 'Number of Phases of Rad Treatment to this Volume'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Meas Value concept into a @value_as_concept_id variable and a @value_source_value variable:

    SELECT   @value_as_concept_id = c1.concept_id
           , @value_source_value = @naaccr_item_1532_value
    FROM concept c1
    WHERE c1.domain_id = 'Meas Value'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '1532_' || @naaccr_item_1532_value

Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @value_as_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1532_value
                        );

To insert modifier attribute NAACCR Item #1533 'Total Dose' for the EPISODE entry inserted in step 8, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Total Dose') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1533'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1533'

Extract the value into @naaccr_item_1533_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1532 'Total Dose'.
Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Extract the appropriate value into an @unit_concept_id variable.

    SELECT @unit_concept_id = concept_id
    FROM concept
    WHERE domain_id = 'Unit'
    AND concept_name = 'cGy'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_number
                         , unit_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @naaccr_item_1533_value
                         , @unit_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1533_value
                        );

If more than one phase of radiotherapy treatment is represented in NAACCR, we will need to create child entries in the EPISODE table to represent each phase. First we will create an entry in the EPISODE table to represent Phase 1 of the radiotherapy treatment and make it a child of the EPISODE entry created in step 8. Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Episode concept ('Treatment Regimen') into an @episode_concept_id variable:

    SELECT @episode_concept_id = c1.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Episode'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Episode'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1506'

Extract the value into @naaccr_item_1506_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1506 'Phase I Radiation Treatment Modality'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Treatment concept ('Radiotherapy') into an @episode_object_concept_id variable and an @episode_source_concept_id variable:

    SELECT   @episode_object_concept_id = c1.concept_id
           , @episode_source_concept_id = c2.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Treatment'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Treatment'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1506_' || @naaccr_item_1506_value

Not all possible values of NAACCR Item #1506 'Phase I Radiation Treatment Modality' map to a standard Treatment concept ('Radiotherapy'). Some of NAACCR Item #1506's possible values represent the absence of radiotherapy treatment. Absence of data is not represented within OMOP.

If a standard Treatment concept cannot be mapped, then stop. No entry should be inserted into the EPISODE table.
If a standard Treatment concept can be mapped, then proceed.

Extract the value into @episode_start_date variable from the column in the table in your tumor registry mapped to NAACCR Item #1210 'RX Date Radiation'.
Extract the appropriate value into an @episode_concept_type_id variable.

    SELECT @episode_concept_type_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Episode Type'
    AND concept_name = 'Pre-made treatment abstraction with no clinical events connections.'

Execute the following SQL to insert an entry into the OMOP EPISODE table (@prior_episode_id should be set to the value inserted into the 'episode_id' column in step 8):

     INSERT INTO EPISODE(
                           episode_id
                         , person_id
                         , episode_concept_id
                         , episode_start_date
                         , episode_start_datetime
                         , episode_parent_id
                         , episode_type_concept_id
                         , episode_source_value
                         , episode_source_concept_id
                        )
     VALUES             (
                           @next_episode_id
                         , @person_id
                         , @episode_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @prior_episode_id
                         , @episode_type_concept_id
                         , @naaccr_item_1506_value
                         , @episode_source_concept_id
                        );

To insert modifier attribute NAACCR Item #1501 'Phase I Dose per Fraction' for the EPISODE entry inserted in step 25, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase I Dose per Fraction') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1501'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1501'

Extract the value into @naaccr_item_1501_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1501 'Phase I Dose per Fraction'.
Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Extract the appropriate value into an @unit_concept_id variable.

    SELECT @unit_concept_id = concept_id
    FROM concept
    WHERE domain_id = 'Unit'
    AND concept_name = 'cGy'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_number
                         , unit_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @naaccr_item_1501_value
                         , @unit_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1501_value
                        );

To insert modifier attribute NAACCR Item #1502 'Phase I Radiation External Beam Planning Tech' for the EPISODE entry inserted in step 25, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase I Radiation External Beam Planning Tech') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1502'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1502'

Extract the value into @naaccr_item_1502_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1502 'Phase I Radiation External Beam Planning Tech'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Meas Value concept into a @value_as_concept_id variable and a @value_source_value variable:

    SELECT   @value_as_concept_id = c1.concept_id
           , @value_source_value = @naaccr_item_1502_value
    FROM concept c1
    WHERE c1.domain_id = 'Meas Value'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '1502_' || @naaccr_item_1502_value

Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @value_as_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1502_value
                        );

To insert modifier attribute NAACCR Item #1503 'Phase I Number of Fractions' for the EPISODE entry inserted in step 25, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase I Number of Fractions') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1503'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1503'

Extract the value into @naaccr_item_1503_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1503 'Phase I Number of Fractions'.
Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_number
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @naaccr_item_1503_value
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1503_value
                        );

To insert modifier attribute NAACCR Item #1504 'Phase I Radiation Primary Treatment Volume' for the EPISODE entry inserted in step 25, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase I Radiation Primary Treatment Volume') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1504'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1504'

Extract the value into @naaccr_item_1504_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1504 'Phase I Radiation Primary Treatment Volume'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Meas Value concept into a @value_as_concept_id variable and a @value_source_value variable:

    SELECT   @value_as_concept_id = c1.concept_id
           , @value_source_value = @naaccr_item_1504_value
    FROM concept c1
    WHERE c1.domain_id = 'Meas Value'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '1504_' || @naaccr_item_1504_value

Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @value_as_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1504_value
                        );

To insert modifier attribute NAACCR Item #1505 'Phase I Radiation to Draining Lymph Nodes' for the EPISODE entry inserted in step 25, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase I Radiation to Draining Lymph Nodes') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1505'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1505'

Extract the value into @naaccr_item_1505_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1505 'Phase I Radiation to Draining Lymph Nodes'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Meas Value concept into a @value_as_concept_id variable and a @value_source_value variable:

    SELECT   @value_as_concept_id = c1.concept_id
           , @value_source_value = @naaccr_item_1505_value
    FROM concept c1
    WHERE c1.domain_id = 'Meas Value'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '1505_' || @naaccr_item_1505_value

Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @value_as_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1505_value
                        );

To insert modifier attribute NAACCR Item #1507 'Phase I Total Dose' for the EPISODE entry inserted in step 25, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase I Total Dose') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1507'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1507'

Extract the value into @naaccr_item_1507_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1507 'Phase I Total Dose'.
Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Extract the appropriate value into an @unit_concept_id variable.

    SELECT @unit_concept_id = concept_id
    FROM concept
    WHERE domain_id = 'Unit'
    AND concept_name = 'cGy'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_number
                         , unit_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @naaccr_item_1507_value
                         , @unit_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1507_value
                        );

If a phase II of radiotherapy treatment is represented in NAACCR, we will need to create child entry in the EPISODE table to represent it. First we will create an entry in the EPISODE table to represent phase II of the radiotherapy treatment and make it a child of the EPISODE entry created in step 8. Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Episode concept ('Treatment Regimen') into an @episode_concept_id variable:

    SELECT @episode_concept_id = c1.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Episode'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Episode'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1516'

Extract the value into @naaccr_item_1516_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1516 'Phase II Radiation Treatment Modality'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Treatment concept ('Radiotherapy') into an @episode_object_concept_id variable and an @episode_source_concept_id variable:

    SELECT   @episode_object_concept_id = c1.concept_id
           , @episode_source_concept_id = c2.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Treatment'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Treatment'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1516_' || @naaccr_item_1516_value

Not all possible values of NAACCR Item #1516 'Phase II Radiation Treatment Modality' map to a standard Treatment concept ('Radiotherapy'). Some of NAACCR Item #1516's possible values represent the absence of radiotherapy treatment. Absence of data is not represented within OMOP.

If a standard Treatment concept cannot be mapped, then stop. No entry should be inserted into the EPISODE table.
If a standard Treatment concept can be mapped, then proceed.

NAACCR does not contain sufficient data items to be able to track the start or end of a phase II of radiotherapy if a phase III phase is also is tracked. So we will leave the episode_start_date, episode_start_datetime, episode_end_date and episode_end_datetime null if a phase III is tracked. Otherwise, Extract the value into @episode_end_date variable from the column in the table in your tumor registry mapped to NAACCR Item #3220 'RX Date Rad Ended'.
Extract the appropriate value into an @episode_concept_type_id variable.

    SELECT @episode_concept_type_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Episode Type'
    AND concept_name = 'Pre-made treatment abstraction with no clinical events connections.'

Execute the following SQL to insert an entry into the OMOP EPISODE table (@prior_episode_id should be set to the value inserted into the 'episode_id' column in step 8):

     INSERT INTO EPISODE(
                           episode_id
                         , person_id
                         , episode_concept_id
                         , episode_end_date
                         , episode_end_datetime                        
                         , episode_parent_id
                         , episode_type_concept_id
                         , episode_source_value
                         , episode_source_concept_id
                        )
     VALUES             (
                           @next_episode_id
                         , @person_id
                         , @episode_concept_id
                         , @episode_end_date
                         , @episode_end_date
                         , @prior_episode_id
                         , @episode_type_concept_id
                         , @naaccr_item_1516_value
                         , @episode_source_concept_id
                        );

To insert modifier attribute NAACCR Item #1511 'Phase II Dose per Fraction' for the EPISODE entry inserted in step 61, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase II Dose per Fraction') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1511'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1511'

Extract the value into @naaccr_item_1511_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1511 'Phase II Dose per Fraction'.
Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Extract the appropriate value into an @unit_concept_id variable.

    SELECT @unit_concept_id = concept_id
    FROM concept
    WHERE domain_id = 'Unit'
    AND concept_name = 'cGy'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_number
                         , unit_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @naaccr_item_1511_value
                         , @unit_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1511_value
                        );

To insert modifier attribute NAACCR Item #1512 'Phase II Radiation External Beam Planning Tech' for the EPISODE entry inserted in step 61, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase II Radiation External Beam Planning Tech') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1512'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1512'

Extract the value into @naaccr_item_1512_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1512 'Phase II Radiation External Beam Planning Tech'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Meas Value concept into a @value_as_concept_id variable and a @value_source_value variable:

    SELECT   @value_as_concept_id = c1.concept_id
           , @value_source_value = @naaccr_item_1512_value
    FROM concept c1
    WHERE c1.domain_id = 'Meas Value'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '1512_' || @naaccr_item_1512_value

Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @value_as_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1512_value
                        );

To insert modifier attribute NAACCR Item #1513 'Phase II` Number of Fractions' for the EPISODE entry inserted in step 61, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase II Number of Fractions') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1513'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1513'

Extract the value into @naaccr_item_1513_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1503 'Phase II Number of Fractions'.
Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_number
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @naaccr_item_1513_value
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1513_value
                        );

To insert modifier attribute NAACCR Item #1514 'Phase II Radiation Primary Treatment Volume' for the EPISODE entry inserted in step 61, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase II Radiation Primary Treatment Volume') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1514'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1514'

Extract the value into @naaccr_item_1514_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1514 'Phase II Radiation Primary Treatment Volume'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Meas Value concept into a @value_as_concept_id variable and a @value_source_value variable:

    SELECT   @value_as_concept_id = c1.concept_id
           , @value_source_value = @naaccr_item_1514_value
    FROM concept c1
    WHERE c1.domain_id = 'Meas Value'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '1514_' || @naaccr_item_1514_value

Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @value_as_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1514_value
                        );

To insert modifier attribute NAACCR Item #1515 'Phase II Radiation to Draining Lymph Nodes' for the EPISODE entry inserted in step 61, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase II Radiation to Draining Lymph Nodes') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1515'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1515'

Extract the value into @naaccr_item_1515_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1515 'Phase II Radiation to Draining Lymph Nodes'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Meas Value concept into a @value_as_concept_id variable and a @value_source_value variable:

    SELECT   @value_as_concept_id = c1.concept_id
           , @value_source_value = @naaccr_item_1515_value
    FROM concept c1
    WHERE c1.domain_id = 'Meas Value'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '1515_' || @naaccr_item_1515_value

Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @value_as_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1515_value
                        );

To insert modifier attribute NAACCR Item #1517 'Phase II Total Dose' for the EPISODE entry inserted in step 61, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase II Total Dose') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1517'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1517'

Extract the value into @naaccr_item_1517_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1517 'Phase II Total Dose'.
Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Extract the appropriate value into an @unit_concept_id variable.

    SELECT @unit_concept_id = concept_id
    FROM concept
    WHERE domain_id = 'Unit'
    AND concept_name = 'cGy'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_number
                         , unit_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @naaccr_item_1517_value
                         , @unit_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1517_value
                        );

If a phase III of radiotherapy treatment is represented in NAACCR, we will need to create child entry in the EPISODE table to represent it. First we will create an entry in the EPISODE table to represent phase III of the radiotherapy treatment and make it a child of the EPISODE entry created in step 8. Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Episode concept ('Treatment Regimen') into an @episode_concept_id variable:

    SELECT @episode_concept_id = c1.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Episode'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Episode'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1526'

Extract the value into @naaccr_item_1526_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1526 'Phase III Radiation Treatment Modality'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Treatment concept ('Radiotherapy') into an @episode_object_concept_id variable and an @episode_source_concept_id variable:

    SELECT   @episode_object_concept_id = c1.concept_id
           , @episode_source_concept_id = c2.concept_id
    FROM concept c1 JOIN concept_relationship ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2           ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Treatment'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Treatment'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1526_' || @naaccr_item_1526_value

Not all possible values of NAACCR Item #1526 'Phase IIII Radiation Treatment Modality' map to a standard Treatment concept ('Radiotherapy'). Some of NAACCR Item #15@6's possible values represent the absence of radiotherapy treatment. Absence of data is not represented within OMOP.

If a standard Treatment concept cannot be mapped, then stop. No entry should be inserted into the EPISODE table.
If a standard Treatment concept can be mapped, then proceed.

Extract the value into @episode_end_date variable from the column in the table in your tumor registry mapped to NAACCR Item #3220 'RX Date Rad Ended'.
Extract the appropriate value into an @episode_concept_type_id variable.

    SELECT @episode_concept_type_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Episode Type'
    AND concept_name = 'Pre-made treatment abstraction with no clinical events connections.'

Execute the following SQL to insert an entry into the OMOP EPISODE table (@prior_episode_id should be set to the value inserted into the 'episode_id' column in step 8):

     INSERT INTO EPISODE(
                           episode_id
                         , person_id
                         , episode_concept_id
                         , episode_end_date
                         , episode_end_datetime
                         , episode_parent_id
                         , episode_type_concept_id
                         , episode_source_value
                         , episode_source_concept_id
                        )
     VALUES             (
                           @next_episode_id
                         , @person_id
                         , @episode_concept_id
                         , @episode_end_date
                         , @episode_end_date
                         , @prior_episode_id
                         , @episode_type_concept_id
                         , @naaccr_item_1526_value
                         , @episode_source_concept_id
                        );

To insert modifier attribute NAACCR Item #1521 'Phase III Dose per Fraction' for the EPISODE entry inserted in step 25, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase I Dose per Fraction') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1521'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1521'

Extract the value into @naaccr_item_1521_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1521 'Phase III Dose per Fraction'.
Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Extract the appropriate value into an @unit_concept_id variable.

    SELECT @unit_concept_id = concept_id
    FROM concept
    WHERE domain_id = 'Unit'
    AND concept_name = 'cGy'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_number
                         , unit_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @naaccr_item_1521_value
                         , @unit_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1521_value
                        );

To insert modifier attribute NAACCR Item #1522 'Phase III Radiation External Beam Planning Tech' for the EPISODE entry inserted in step 97, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase III Radiation External Beam Planning Tech') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1522'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1522'

Extract the value into @naaccr_item_1522_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1522 'Phase III Radiation External Beam Planning Tech'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Meas Value concept into a @value_as_concept_id variable and a @value_source_value variable:

    SELECT   @value_as_concept_id = c1.concept_id
           , @value_source_value = @naaccr_item_1522_value
    FROM concept c1
    WHERE c1.domain_id = 'Meas Value'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '1522_' || @naaccr_item_1522_value

Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @value_as_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1522_value
                        );

To insert modifier attribute NAACCR Item #1523 'Phase III Number of Fractions' for the EPISODE entry inserted in step 97, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase III Number of Fractions') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1523'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1523'

Extract the value into @naaccr_item_1523_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1523 'Phase III Number of Fractions'.
Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_number
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @naaccr_item_1523_value
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1523_value
                        );

To insert modifier attribute NAACCR Item #1524 'Phase III Radiation Primary Treatment Volume' for the EPISODE entry inserted in step 97, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase III Radiation Primary Treatment Volume') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1524'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1524'

Extract the value into @naaccr_item_1524_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1524 'Phase III Radiation Primary Treatment Volume'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Meas Value concept into a @value_as_concept_id variable and a @value_source_value variable:

    SELECT   @value_as_concept_id = c1.concept_id
           , @value_source_value = @naaccr_item_1524_value
    FROM concept c1
    WHERE c1.domain_id = 'Meas Value'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '1524_' || @naaccr_item_1524_value

Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @value_as_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1524_value
                        );

To insert modifier attribute NAACCR Item #1525 'Phase III Radiation to Draining Lymph Nodes' for the EPISODE entry inserted in step 97, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase III Radiation to Draining Lymph Nodes') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1525'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1525'

Extract the value into @naaccr_item_1525_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1525 'Phase III Radiation to Draining Lymph Nodes'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Meas Value concept into a @value_as_concept_id variable and a @value_source_value variable:

    SELECT   @value_as_concept_id = c1.concept_id
           , @value_source_value = @naaccr_item_1525_value
    FROM concept c1
    WHERE c1.domain_id = 'Meas Value'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '1525_' || @naaccr_item_1525_value

Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @value_as_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1525_value
                        );

To insert modifier attribute NAACCR Item #1527 'Phase I Total Dose' for the EPISODE entry inserted in step 97, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Phase I Total Dose') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1527'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1527'

Extract the value into @naaccr_item_1527_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1527 'Phase III Total Dose'.
Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Extract the appropriate value into an @unit_concept_id variable.

    SELECT @unit_concept_id = concept_id
    FROM concept
    WHERE domain_id = 'Unit'
    AND concept_name = 'cGy'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_number
                         , unit_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @naaccr_item_1527_value
                         , @unit_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1527_value
                        );

Surgery Treatment Data

Surgery treatment data in NAACCR/SEER is stored in the following NAACCR items (grouped by OMOP destination table):

Episode

NAACCR Item #1290 'RX Summ--Surg Prim Site'
NAACCR Item #1200 'RX Date Surgery'

Measurement

NAACCR Item #1320 'RX Summ--Surgical Margins'
NAACCR Item #3180 'RX Date Surg Disch'
NAACCR Item #3190 'Readm Same Hosp 30 Days'

NAACCR/SEER use different sets of surgery codes for different 'sites'. NAACCR/SEER calls these sets of surgery codes 'Site-Specific Surgery Codes'. NAACCR/SEER defines a 'site' by a list of ICDO3 topography codes. See here for SEER's crosswalk from surgery sites to ICDO3 topography codes: https://seer.cancer.gov/manuals/2018/appendixc.html. Unfortunately, NAACCR/SEER reuses the same numeric codes across different sites. Code '50' for the Prostate Site (ICDO3 topography code C61.9) means 'Radical prostatectomy, NOS; total prostatectomy, NOS'. However, code '50' for the Breast Site (C50.0, C50.1, C50.2, C50.3, C50.4, C50.5, C50.6, C50.7, C50.9) means 'Modified radical mastectomy'. Consequently, in order to map NAACCR/SEER surgery data into the OMOP CDM, we will need to take into account the ICDO3 topography code of the surgery's tumor. This is accomplished by the OMOP vocabulary making a reference from the NAACCR surgery code entry in the CONCEPT to the ICDO3 topography code entry in the CONCEPT via Has Topography ICDO' relationship entry in CONCEPT_RELATIONSHIP . Here are the steps to map these NAACCR items into the OMOP EPISODE and MEASUREMENT tables (this example will be for mapping for the Breast Site code 41 'Total (simple) mastectomy WITHOUT removal of uninvolved contralateral breast' for ICDO3 topography code C50.1 'Central portion of breast').

Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Episode concept ('Treatment Regimen') into an @episode_concept_id variable:

    SELECT @episode_concept_id = c1.concept_id
    FROM concept c1 JOIN concept_relationship     ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2               ON concept_relationship.concept_id_2 = c2.concept_id
    WHERE c1.domain_id = 'Episode'
    AND concept_relationship.relationship_id = 'Maps to'
    AND c2.domain_id = 'Episode'
    AND c2.vocabulary_id = 'NAACCR'
    AND c2.concept_code ='1290'

Extract the value into @naaccr_item_1290_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1290 'RX Summ--Surg Prim Site'.
Extract the value into @naaccr_item_400_value variable from the column in the table in your tumor registry mapped to NAACCR Item #400 'Primary Site' for the surgery mapped in step 2. In NAACCR/SEER, each treatment will be associated with an ICDO3 site (NAACCR Item #400 'Primary Site') and histology (NAACCR Item #522 'Histologic Type ICD-O-3').
Using the NAACCR item code extracted into @naaccr_item_1290_value variable and the ICD03 topography extracted into @naaccr_item_400_value variable, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Treatment concept ('Total (simple) mastectomy WITHOUT removal of uninvolved contralateral breast') into an @episode_object_concept_id variable and an @episode_source_concept_id variable:

    SELECT   @episode_object_concept_id = c1.concept_id
           , @episode_source_concept_id = c2.concept_id
    FROM concept c1 JOIN concept_relationship     ON c1.concept_id = concept_relationship.concept_id_1
                    JOIN concept c2               ON concept_relationship.concept_id_2 = c2.concept_id                
    WHERE c1.domain_id = 'Treatment'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '1290_%%_' || @naaccr_item_1290_value
    AND concept_relationship.relationship_id = 'Has Topography ICDO'
    AND c2.vocabulary_id = 'ICDO3'
    AND c2.concept_class_id = 'ICDO Topography'
    AND c2.concept_code = @naaccr_item_400_value

Not all possible values of NAACCR Item #1290 'RX Summ--Surg Prim Site' map to a standard Treatment concept ('Total (simple) mastectomy WITHOUT removal of uninvolved contralateral breast'). Some of NAACCR Item #1290's possible values represent the absence of a Surgery treatment. Absence of data is not represented within OMOP.

If a standard Treatment concept cannot be mapped, then stop. No entry should be inserted into the EPISODE table.
If a standard Treatment concept can be mapped, then proceed.

Extract the value into @episode_start_date variable from the column in the table in your tumor registry mapped to NAACCR Item #1200 'RX Date Surgery'.
Extract the appropriate value into an @episode_concept_type_id variable.

    SELECT @episode_concept_type_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Episode Type'
    AND concept_name = 'Pre-made treatment abstraction with no clinical events connections.'

Execute the following SQL to insert an entry into the OMOP EPISODE table:

     INSERT INTO EPISODE(
                           episode_id
                         , person_id
                         , episode_concept_id
                         , episode_start_date
                         , episode_start_datetime
                         , episode_parent_id
                         , episode_type_concept_id
                         , episode_source_value
                         , episode_source_concept_id
                        )
     VALUES             (
                           @next_episode_id
                         , @person_id
                         , @episode_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @next_episode_id
                         , @episode_type_concept_id
                         , @naaccr_item_1290_value
                         , @episode_source_concept_id
                        );

To insert modifier attribute NAACCR Item #1320 'RX Summ--Surgical Margins' for the EPISODE entry inserted in step 8, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('RX Summ--Surgical Margins') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '1320'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '1320'

Extract the value into @naaccr_item_1320_value variable from the column in the table in your tumor registry mapped to NAACCR Item #1320 'RX Summ--Surgical Margins'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Meas Value concept into a @value_as_concept_id variable and a @value_source_value variable:

    SELECT   @value_as_concept_id = c1.concept_id
           , @value_source_value = @naaccr_item_1320_value
    FROM concept c1
    WHERE c1.domain_id = 'Meas Value'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '1320_' || @naaccr_item_1320_value

Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @value_as_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_1320_value
                        );

To insert modifier attribute NAACCR Item #3180 'RX Date Surg Disch' for the EPISODE entry inserted in step 8, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('RX Date Surg Disch') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '3180'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '3180'

Extract the value into @naaccr_item_3180_value variable from the column in the table in your tumor registry mapped to NAACCR Item #3180 'RX Date Surg Disch'.
Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @naaccr_item_3180_value
                         , @naaccr_item_3180_value
                         , @measurement_type_concept_id
                         , @naaccr_item_3180_value
                         , @measurement_source_concept_id
                         , @naaccr_item_3180_value
                        );

To insert modifier attribute NAACCR Item #3190 'Readm Same Hosp 30 Days' for the EPISODE entry inserted in step 8, execute the following SQL against the OMOP vocabulary tables to retrieve a standard Measurement concept ('Readm Same Hosp 30 Days') into a @measurement_concept_id variable, a @measurement_source_value variable and a @measurement_source_concept_id variable:

    SELECT  @measurement_concept_id = c1.concept_id
          , @measurement_source_value = '3190'
          , @measurement_source_concept_id = c1.concept_id
    FROM concept c1
    WHERE c1.domain_id = 'Measurement'
    AND c1.vocabulary_id = 'NAACCR'
    AND c2.concept_code = '3190'

Extract the value into @naaccr_item_3190_value variable from the column in the table in your tumor registry mapped to NAACCR Item #3190 'Readm Same Hosp 30 Days'.
Execute the following SQL against the OMOP vocabulary tables to retrieve a standard Meas Value concept into a @value_as_concept_id variable and a @value_source_value variable:

    SELECT   @value_as_concept_id = c1.concept_id
           , @value_source_value = @naaccr_item_3190_value
    FROM concept c1
    WHERE c1.domain_id = 'Meas Value'
    AND c1.vocabulary_id = 'NAACCR'
    AND c1.concept_code = '3190_' || @naaccr_item_3190_value

Extract the appropriate value into a @measurement_type_concept_id variable:

    SELECT @measurement_type_concept_id = concept_id
    FROM concept
    WHERE vocabulary_id = 'Meas Type'
    AND concept_name = 'Tumor Registry'

Execute the following SQL to insert an entry into the OMOP MEASUREMENT table:

     INSERT INTO MEASUREMENT(
                           measurement_id
                         , person_id
                         , measurement_concept_id
                         , measurement_date
                         , measurement_datetime
                         , measurement_type_concept_id
                         , value_as_concept_id
                         , measurement_source_value
                         , measurement_source_concept_id
                         , value_source_value
                        )
     VALUES             (
                           @next_measurement_id
                         , @person_id
                         , @measurement_concept_id
                         , @episode_start_date
                         , @episode_start_date
                         , @measurement_type_concept_id
                         , @value_as_concept_id
                         , @measurement_source_value
                         , @measurement_source_concept_id
                         , @naaccr_item_3190_value
                        );