P01: Find procedure by concept ID

This query enables extraction of concept details associated with a concept identifier. The query is a tool for quick reference for the name, class, level and source vocabulary details associated with a concept identifier.
Please note that along with concept details, the query returns a flag indicating whether the concept is a procedure concept (part of the PROCEDURE domain). Standard concepts in the Procedure domain include the following:
  • SNOMED-CT procedure concepts
  • CPT-4 / HCPCS / ICD9 procedure concepts
If the Concept is not in the Prodedure domain, the query still returns the concept details with the Is_Procedure_Concept_Flag field set to ‘No’.

Input:
 Parameter  Example  Mandatory  Notes
 Concept ID  4336464  Yes Concept Identifier for ‘Coronary artery bypass graft’
 As of date  Sysdate  No Valid record as of specific date. Current date – sysdate is a default

Sample query run:
The following is a sample run of the query to run a search for procedure concept ID of 4336464. The input parameters are highlighted in blue.
SELECT  C.concept_id         Procedure_concept_id,
        C.concept_name       Procedure_concept_name,
        C.concept_code       Procedure_concept_code,
        C.concept_class      Procedure_concept_class,
        C.concept_level      Procedure_concept_level,
        C.vocabulary_id      Procedure_concept_vocab_id,
        V.vocabulary_name    Procedure_concept_vocab_name,
       (
       CASE C.vocabulary_id 
            WHEN 3  THEN 'Yes'
            WHEN 4  THEN 'Yes'
            WHEN 5  THEN 'Yes'
            WHEN 57 THEN 'Yes'
            WHEN 1  THEN 
                     CASE lower(C.concept_class)
                          WHEN 'procedure' THEN 'Yes'
                          ELSE 'No'
                     END
            ELSE  'No'
       END)                  Is_Procedure_Concept_flag
FROM    vocabulary.concept      C, 
        vocabulary.vocabulary   V
WHERE   C.concept_id    = 4336464 
AND     C.vocabulary_id = V.vocabulary_id
AND     sysdate BETWEEN C.valid_start_date AND C.valid_end_date
 Output:
Output field list
 Field  Description
 Procedure_Concept_ID  Concept Identifier entered as input
 Procedure_Concept_Name  Name of the standard concept
 Procedure_Concept_Code  Concept code of the standard concept in the source vocabulary
 Procedure_Concept_Class  Concept class of standard vocabulary concept
 Procedure_Concept_Level  Level of the concept if defined as part of a hierarchy
 Procedure_Concept_Vocab_ID  Vocabulary the standard concept is derived from as vocabulary ID
 Procedure_Concept_Vocab_Name  Name of the vocabulary the standard concept is derived from
 Is_Procedure_Concept_Flag  Flag indicating whether the Concept ID belongs to a disease concept
‘Yes’ if disease concept, ‘No’ if not a disease concept

Sample output record:
 Field  Value
 Procedure_Concept_ID  4336464
 Procedure_Concept_Name  Coronary artery bypass graft
 Procedure_Concept_Code  232717009
 Procedure_Concept_Class  Procedure
 Procedure_Concept_Level  2
 Procedure_Concept_Vocab_ID  1
 Procedure_Concept_Vocab_Name  SNOMED-CT
 Is_Procedure_Concept_Flag  Yes
Comments