Hi all,
I’ve adapted a PostgreSQL script to populate the CDM v5 dose_era table. I don’t have any v5 data, but would someone with test data for drug_exposure in v5 on a postgres server want to validate this?
Thanks!
Taylor
We haven’t populate the DOSE_ERA yet but are looking to start. @taylordelehanty - did anyone test your code here since July?
Hi Erica,
@taylordelehanty is a undergrad student who was working with me this past summer at the U of Colorado. I know that we haven’t done testing of the code because we don’t have a V5 instance. I’d certainly be interested in someone used it and let me know what they thought.
@DTorok, you had some code that we worked together on that was a cursor-based approach to building our dose_era. can you share that code with the community. if i recall correctly, we didn’t think it was the right final solution, but i think you put a lot of good thinking into that which could stimulate others to figure it out for us…
Patrick,
Here is code I worked on. Not sure of current state.
maybe it’s my side, but I don’t see any code. I don’t know how to include attachments, perhaps just paste the code inline?
CREATE OR REPLACE PACKAGE daily_dose_pkg AUTHID CURRENT_USER
IS
/* Create Daily Dose Era Table
Logic copied from Patick Ryans SQL Server script
CDMv4_build_dose_eras_18jul2013.sql
PreConditions
- drug_dose table that maps Drug_concept_id to
ingredient and dose value - empty dose_era table and global temp table dose_era_1
on commit preserve rows, see dose_era.ddl
( dose_era_id INTEGER NOT NULL
, person_id INTEGER NOT NULL
, drug_concept_id INTEGER NOT NULL
, daily_dose NUMBER NOT NULL
, dose_unit VARCHAR2( 60 ) NOT NULL
, dose_era_start_date DATE NOT NULL
, dose_era_end_date DATE NOT NULL
, drug_exposure_count NUMBER( 3 ) NOT NULL
); - The drug_exposure, dose_era and drug_dose
tables ( or view or synonyms ) all exist
in the schema running this procedure
PostCondition - dose_era table is filled
*/
PROCEDURE version;
PROCEDURE create_era;
PROCEDURE create_era( pass IN INTEGER );
END daily_dose_pkg;
/
CREATE OR REPLACE PACKAGE BODY daily_dose_pkg
IS
– global constant
version_ CONSTANT varchar2( 8 ) := 1.0;
newLine CONSTANT char(1) := chr( 10 ) ;
PROCEDURE get_exposure_cursor( pass IN integer
, drug_exposure_cur OUT sys_refCursor
)
IS
BEGIN
IF pass = 1
THEN
OPEN drug_exposure_cur
FOR
SELECT DISTINCT
de1.PERSON_ID
, dd1.ingredient_id as drug_concept_id
, ( 1.0*de1.quantity / de1.days_supply ) * dd1.dose_value AS daily_dose
, dd1.dose_unit
, de1.DRUG_EXPOSURE_START_DATE
, CASE WHEN de1.drug_exposure_end_date IS NULL
THEN CASE WHEN de1.days_supply > 0 and de1.days_supply < 365
THEN de1.DRUG_EXPOSURE_START_DATE + de1.days_supply
ELSE de1.DRUG_EXPOSURE_START_DATE
END
ELSE de1.drug_exposure_end_date
END AS drug_exposure_end_date
, 1 AS drug_exposure_count
FROM DRUG_EXPOSURE de1
INNER JOIN – get ingredient(s) and drug doses for drug_id
( SELECT drug_id, ingredient_id, dose_value, dose_unit
FROM drug_dose
WHERE dose_unit IS NOT NULL
) dd1 ON de1.DRUG_CONCEPT_ID = dd1.drug_id
WHERE de1.days_supply > 0 and de1.days_supply < 365
AND de1.quantity > 0 and de1.quantity < 1100
– AND de1.person_id = 1073900354 and dd1.ingredient_id = 1545958 --test case
ORDER BY de1.PERSON_ID, dd1.ingredient_id, de1.drug_exposure_start_date
, dd1.dose_unit, daily_dose;
ELSE – pass 2
OPEN drug_exposure_cur
FOR
SELECT PERSON_ID
, drug_concept_id
, daily_dose
, dose_unit
, dose_era_start_date
, dose_era_end_date
, drug_exposure_count
FROM dose_era_1
ORDER BY PERSON_ID, drug_concept_id, dose_era_start_date
, dose_unit, daily_dose;
END IF; – pass
END get_exposure_cursor;
PROCEDURE insert_dose( pass IN INTEGER
, id IN dose_era.dose_era_id%TYPE
, person_id IN dose_era.person_id%TYPE
, drug_concept IN dose_era.drug_concept_id%TYPE
, dose IN dose_era.daily_dose%TYPE
, unit IN dose_era.dose_unit%TYPE
, start_date IN date
, end_date IN date
, exposures IN dose_era.drug_exposure_count%TYPE
)
IS
BEGIN
IF pass = 1
THEN
INSERT INTO DOSE_ERA_1( dose_era_id, person_id, drug_concept_id, daily_dose
, dose_unit, dose_era_start_date, dose_era_end_date
, drug_exposure_count )
VALUES( id, person_id, drug_concept, dose, unit
, start_date, end_date, exposures );
ELSE – pass 2
INSERT INTO DOSE_ERA( dose_era_id, person_id, drug_concept_id, daily_dose
, dose_unit, dose_era_start_date, dose_era_end_date
, drug_exposure_count )
VALUES( id, person_id, drug_concept, dose, unit
, start_date, end_date, exposures );
END IF; -- pass
END insert_dose;
– exposed procedures
PROCEDURE version
IS
BEGIN
dbms_output.put_line( 'Version: ’ || version_ );
END version;
PROCEDURE create_era
IS
BEGIN
create_era( pass=> 1 );
create_era( pass=> 2 );
END create_era;
PROCEDURE create_era( pass IN integer )
IS
drug_exposure_cur SYS_refCursor;
last_personid drug_exposure.person_id%TYPE;
last_drug_concept_id drug_exposure.drug_concept_id%TYPE;
last_daily_dose NUMBER;
last_dose_unit drug_dose.dose_unit%TYPE;
last_dtstart DATE;
last_dtend DATE;
last_exposure_count INTEGER;
current_personid drug_exposure.person_id%TYPE;
current_dtstart DATE;
current_dtend DATE;
current_drug_concept_id drug_exposure.drug_concept_id%TYPE;
current_daily_dose NUMBER;
current_dose_unit drug_dose.dose_unit%TYPE;
current_exposure_count INTEGER;
drug_count INTEGER;
dose_era_id INTEGER;
BEGIN
get_exposure_cursor( pass=> pass, drug_exposure_cur=> drug_exposure_cur );
FETCH drug_exposure_cur
INTO last_personid, last_drug_concept_id, last_daily_dose
, last_dose_unit, last_dtstart, last_dtend
, last_exposure_count;
drug_count := last_exposure_count;
dose_era_id := 1;
LOOP
FETCH drug_exposure_cur
INTO current_personid, current_drug_concept_id, current_daily_dose
, current_dose_unit
, current_dtstart, current_dtend, current_exposure_count;
EXIT WHEN drug_exposure_cur%NOTFOUND;
/*************************************************/
IF current_personid = last_personid
AND current_drug_concept_id = last_drug_concept_id
AND current_dose_unit = last_dose_unit
THEN --2 --continuation of same dose era if daily dose within 10%
–, allow 10d of stockpiling and 30d persistence window
IF current_daily_dose >= 0.9 * last_daily_dose
AND current_daily_dose <= 1.1 * last_daily_dose
AND current_dtstart >= last_dtend - 10
AND current_dtstart <= last_dtend + 30
THEN --3
last_dtend := GREATEST( current_dtend, last_dtend );
last_exposure_count := last_exposure_count + current_exposure_count;
ELSE --3 – dose stays same but large overlap
– such that we really need to add the dose together
IF current_daily_dose >= 0.9 * last_daily_dose
AND current_daily_dose <= 1.1 * last_daily_dose
AND current_dtstart < last_dtend - 10
THEN --4
IF current_dtstart > last_dtstart
THEN --5
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> last_personid
, drug_concept=> last_drug_concept_id
, dose=> last_daily_dose
, unit=> last_dose_unit
, start_date=> last_dtstart
, end_date=> current_dtstart - 1
, exposures=> last_exposure_count ) ;
dose_era_id := dose_era_id + 1;
END IF; --5 current_dtstart > last_dtstart
--get up overlapping part
-- last_personid := current_personid;
-- last_drug_concept_id := current_drug_concept_id;
last_daily_dose := last_daily_dose + current_daily_dose;
last_dose_unit := current_dose_unit;
last_dtstart := current_dtstart;
last_dtend := LEAST( last_dtend, current_dtend );
last_exposure_count := last_exposure_count + current_exposure_count;
IF last_dtEnd != current_dtEnd
THEN
-- Have to save the end part of record that does not overlap
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> current_personid
, drug_concept=> current_drug_concept_id
, dose=> current_daily_dose
, unit=> current_dose_unit
, start_date=> LEAST( last_dtEnd, current_dtEnd ) + 1
, end_date=> GREATEST( last_dtEnd, current_dtEnd )
, exposures=> 1 ) ;
END IF; -- tail end of overlapping eras
ELSE--4
--if dailydose changes within 30d persistence window
IF( current_daily_dose < 0.9 * last_daily_dose
OR current_daily_dose > 1.1 * last_daily_dose
)
AND current_dtstart <= last_dtend + 30
THEN --5 --no overlap from prior record
IF current_dtstart >= last_dtend
THEN --6
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> last_personid
, drug_concept=> last_drug_concept_id
, dose=> last_daily_dose
, unit=> last_dose_unit
, start_date=> last_dtstart
, end_date=> last_dtend
, exposures=> last_exposure_count ) ;
dose_era_id := dose_era_id + 1;
-- last_personid := current_personid;
-- last_drug_concept_id := current_drug_concept_id;
last_daily_dose := current_daily_dose;
last_dose_unit := current_dose_unit;
last_dtstart := current_dtstart;
last_dtend := current_dtend;
last_exposure_count := current_exposure_count;
ELSE --6 --there is overlap, so need to combine dailydose as long as these overlap
--write row for part prior to overlap
IF current_dtstart > last_dtstart
THEN --7
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> last_personid
, drug_concept=> last_drug_concept_id
, dose=> last_daily_dose
, unit=> last_dose_unit
, start_date=> last_dtstart
, end_date=> current_dtstart - 1
, exposures=> current_exposure_count );
dose_era_id := dose_era_id + 1;
END IF; -- 7
--get up overlapping part
last_daily_dose := last_daily_dose + current_daily_dose;
-- last_personid := current_personid;
-- last_drug_concept_id := current_drug_concept_id;
last_dose_unit := current_dose_unit;
last_dtstart := current_dtstart;
last_dtend := LEAST( last_dtend, current_dtend );
last_exposure_count := last_exposure_count + current_exposure_count;
IF last_dtEnd != current_dtEnd
THEN
-- Have to save the end part of record that does not overlap
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> current_personid
, drug_concept=> current_drug_concept_id
, dose=> current_daily_dose
, unit=> current_dose_unit
, start_date=> LEAST( last_dtEnd, current_dtEnd ) + 1
, end_date=> GREATEST( last_dtEnd, current_dtEnd )
, exposures=> 1 ) ;
END IF; -- tail end of overlapping eras
END IF; --6 current_dtstart >= last_dtend
ELSE --5
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> last_personid
, drug_concept=> last_drug_concept_id
, dose=> last_daily_dose
, unit=> last_dose_unit
, start_date=> last_dtstart
, end_date=> last_dtend
, exposures=> last_exposure_count ) ;
dose_era_id := dose_era_id + 1;
-- last_personid := current_personid;
-- last_drug_concept_id := current_drug_concept_id;
last_daily_dose := current_daily_dose;
last_dose_unit := current_dose_unit;
last_dtstart := current_dtstart;
last_dtend := current_dtend;
last_exposure_count := current_exposure_count;
END IF; --5
END IF; --4
END IF; --3
ELSE --2
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> last_personid
, drug_concept=> last_drug_concept_id
, dose=> last_daily_dose
, unit=> last_dose_unit
, start_date=> last_dtstart
, end_date=> last_dtend
, exposures=> last_exposure_count ) ;
dose_era_id := dose_era_id + 1;
last_personid := current_personid;
last_drug_concept_id := current_drug_concept_id;
last_daily_dose := current_daily_dose;
last_dose_unit := current_dose_unit;
last_dtstart := current_dtstart;
last_dtend := current_dtend;
last_exposure_count := current_exposure_count;
END IF;
END LOOP;
CLOSE drug_exposure_cur;
insert_dose( pass=> pass
, id => dose_era_id
, person_id=> last_personid
, drug_concept=> last_drug_concept_id
, dose=> last_daily_dose
, unit=> last_dose_unit
, start_date=> last_dtstart
, end_date=> last_dtend
, exposures=> last_exposure_count ) ;
COMMIT;
END create_era;
END daily_dose_pkg;
/
Is there any update on what the latest code is for generating a DOSE_ERA table? The above code does not appear to use the DRUG_STRENGTH table, which would seem necessary, based off of the DOSE_ERA documentation here:
Is there code that addresses the 5 cases:
- Tablets and other fixed amount formulations
- Puffs of an inhaler
- Quantified Drugs which are formulated as a concentration
- Drugs with the total amount provided in quantity, e.g. chemotherapeutics
- Compounded drugs
Many thanks,
Christophe
1 Like
There is no publicly available code as far as I can tell. Don’s old one doesn’t incorporate the various use cases. So, in good Open Source manner - now it’s your job? 
If we don’t have any open source code, we should remove this sentence from the CDM specs.
Dose Eras will be derived from records in the DRUG_EXPOSURE table and the Dose information from the DRUG_STRENGTH table using a standardized algorithm.
Our current derived table code “lives” here:
The indication
“Dose Eras will be derived from records in the DRUG_EXPOSURE table and the Dose information from the DRUG_STRENGTH table using a standardized algorithm.”
it is still present in the CDM documentation and is misleading for those looking for the algorithm, because there is no algorithm.
1 Like