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’.

 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'
            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 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