Import CA DPD drug data into FreeDiams

The mostly-SQL below will need clean up, it is not yet ready but gives some ideas


-- 1) Even without expanding FreeDiams to handle (e.g.) ATC codes, Canada does not provide some data used in France (which in some cases even FreeDiams does not yet use). Some FreeDiams tables need some columns which were "NOT NULL" redefined to permit null values:

DROP TABLE CIS;

CREATE TABLE `CIS` (  `CIS` int(11) NOT NULL,  `DENOMINATION` varchar(1000) NOT NULL,  `FORME` varchar(500) NOT NULL,  `ADMINISTRATION` varchar(100) NOT NULL,  `AMM` varchar(50),  `AUTORISATION` varchar(50),  `COMMERCIALISATION` varchar(50) NOT NULL,  `CODE_RPC` int(10));


-- **********************************

-- 2) Create temporary tables into which to import the Canadian drug data:

CREATE TABLE "temp"."drug" ("DRUG_CODE" INTEGER(8) PRIMARY KEY  NOT NULL , "PRODUCT_CATEGORIZATION" VARCHAR2(80), "CLASS" VARCHAR2(40), "DRUG_IDENTIFICATION_NUMBER" VARCHAR2(8), "BRAND_NAME" VARCHAR2(200), "PEDIATRIC_FLAG" VARCHAR2(1), "ACCESSION_NUMBER" VARCHAR2(5), "NUMBER_OF_AIS" VARCHAR2(10), "LAST_UPDATE_DATE" DATE, "AI_GROUP_NO" VARCHAR2(10));

CREATE TABLE "temp"."form" ("DRUG_CODE" INTEGER(8) NOT NULL , "PHARM_FORM_CODE" INTEGER(7), "PHARMACEUTICAL_FORM" VARCHAR2(40));

CREATE TABLE "temp"."route" ("DRUG_CODE" INTEGER(8)  NOT NULL , "ROUTE_OF_ADMINISTRATION_CODE" INTEGER(6), "ROUTE_OF_ADMINISTRATION" VARCHAR2(40));

CREATE  TABLE "temp"."status" ("DRUG_CODE" INTEGER(8)  NOT NULL , "CURRENT_STATUS_FLAG" VARCHAR2(1), "STATUS" VARCHAR2(40), "HISTORY_DATE" DATE);

CREATE TABLE "temp"."ingred" ("DRUG_CODE" INTEGER(8) NOT NULL , "ACTIVE_INGREDIENT_CODE" INTEGER(6), "INGREDIENT" VARCHAR2(240), "INGREDIENT_SUPPLIED_IND" VARCHAR2(1), "STRENGTH" VARCHAR2(20), "STRENGTH_UNIT" VARCHAR2(40), "STRENGTH_TYPE" VARCHAR2(40), "DOSAGE_VALUE" VARCHAR2(20), "BASE" VARCHAR2(1), "DOSAGE_UNIT" VARCHAR2(40), "NOTES" VARCHAR2(2000));

CREATE TABLE "temp"."ther" ("DRUG_CODE" INTEGER(8) NOT NULL , "TC_ATC_NUMBER" VARCHAR2(8), "TC_ATC" VARCHAR2(120), "TC_AHFS_NUMBER" VARCHAR2(20), "TC_AHFS" VARCHAR2(80));

-- ***********************************

-- 3. Each of the above tables needs data imported from CSV files (encoded in ISO-8859-1, not UTF-8)

cd ~
mkdir ca_dpd
wget http://www.hc-sc.gc.ca/dhp-mps/prodpharma/databasdon/txt/allfiles.zip
-- FIXME need to untar the .zip

-- the importing I did not from a command line, but from inside a Firefox add-on, SQL Manager Lite

-- ***********************************

-- Need a less-constrained COMPO: 

CREATE TABLE "temp"."COMPO" (  `CIS` int(10) NOT NULL,  `NOM` varchar(100)  NOT NULL,  `CODE_SUBST` int(11) NOT NULL,  `DENOMINATION` varchar(200) NOT NULL,  `DOSAGE` varchar(100)  NOT NULL,  `REF_DOSAGE` varchar(50),  `NATURE` varchar(2),  `LK_NATURE` int(11))

-- else delete French data from actual COMPO table

DELETE * FROM COMPO

INSERT INTO COMPO (CIS, NOM, CODE_SUBST, DENOMINATION, DOSAGE, REF_DOSAGE, NATURE, LK_NATURE)
SELECT A1.DRUG_CODE, A2.PHARMACEUTICAL_FORM, A1.ACTIVE_INGREDIENT_CODE, A1.INGREDIENT, A1.STRENGTH || A1.STRENGTH_UNIT || "/" || A1.DOSAGE_VALUE || A1.DOSAGE_UNIT,   "", "", ""
FROM temp.ingred A1, temp.form A2
WHERE A1.DRUG_CODE = A2.DRUG_CODE AND A1.DOSAGE_VALUE != ""
-- 4716 rows

INSERT INTO COMPO (CIS, NOM, CODE_SUBST, DENOMINATION, DOSAGE, REF_DOSAGE, NATURE, LK_NATURE)
SELECT A1.DRUG_CODE, A2.PHARMACEUTICAL_FORM, A1.ACTIVE_INGREDIENT_CODE, A1.INGREDIENT, A1.STRENGTH || A1.STRENGTH_UNIT,   "", "", ""
FROM temp.ingred A1, temp.form A2
WHERE A1.DRUG_CODE = A2.DRUG_CODE AND A1.DOSAGE_VALUE = ""
-- 38813 rows total (4716 + 34097)

SELECT STRENGTH || STRENGTH_UNIT || "/" || DOSAGE_VALUE || DOSAGE_UNIT
FROM ingred
WHERE DOSAGE_VALUE != ""

VARCHAR2(40)+VARCHAR2(40)+IF(DOSAGE_VALUE is null,"","/"+VARCHAR2(20)+VARCHAR2(40))

-- 4. Replace the drug data that came with FreeDiams with the Canadian data:

INSERT INTO CIS (CIS, DENOMINATION, FORME, ADMINISTRATION, COMMERCIALISATION)
SELECT A1.DRUG_CODE, A1.BRAND_NAME, A2.PHARMACEUTICAL_FORM, A3.ROUTE_OF_ADMINISTRATION, A4.CURRENT_STATUS_FLAG
FROM temp.drug A1, temp.form A2, temp.route A3, temp.status A4
WHERE A1.DRUG_CODE = A2.DRUG_CODE AND A1.DRUG_CODE= A3.DRUG_CODE AND A1.DRUG_CODE= A4.DRUG_CODE;

Topic revision: 18 Feb 2010, JamesBusser
 
Download.png
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback
Powered by Olark