 
 
 --fix inert ingredients in contraceptive packs
-
 update pc_stage
 set amount='7'
  where (pack_concept_code,drug_concept_code) in

                            FROM ds_Stage
                            WHERE numerator_unit IN ('[hp_X]','[hp_C]'));

-- Manually add absent units in drug_strength
UPDATE DS_STAGE
   SET AMOUNT_UNIT = '[U]'
WHERE DRUG_CONCEPT_CODE = 'OMOP467711'

   AND a.vocabulary_id = 'RxNorm Extension'
   AND a.concept_class_id LIKE '%Branded%Pack%'
  LEFT JOIN concept c2
         ON c2.concept_name = REPLACE (REPLACE (REGEXP_SUBSTR (REGEXP_SUBSTR (a.concept_name,'Pack\s\[.*\]'),'\[.*\]'),'['),']') -- take it from name
        AND c2.vocabulary_id LIKE 'RxNorm%'
        AND c2.concept_class_id = 'Brand Name'
WHERE concept_id_1 NOT IN (SELECT concept_id_1
