<jupyter_start><jupyter_text>**Getting Full Dates**Welcome to the examples for the chapter [Getting Full Dates](https://github.com/OHDSI/ETL--PulmonaryHypertensionRegistries/wiki/Getting-Full-Dates). General information on how to impute missing and partial dates can be found in the wiki by the link above. Here are a few practical examples of how it has been done for PAH registry data stored in the [SDTM](https://www.cdisc.org/standards/foundational/sdtm). The following data excerpt is close to the actual registry but not exactly real data.   **Brief data description**In this example, we have clinical and demographic data for two patients. The following tables are given:- DM - demographics- AE - adverse events- DS - disposition events- EX - exposure (to protocol-specified study treatment)- SV - subject visits- VS - vital signsYou may also see the imputation_dates among the tables, but it is not an SDTM source table; more on this table later.Among these tables, we are going to impute dates in the three tables: AE, EX, and SV. Other tables give a foundation for our imputation logic.  Preparatory steps  **Postgres and file manipulations**<jupyter_code>#@title **This block of code installs PosgreSQL**
%%capture
!wget -qO- https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
!echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" >/etc/apt/sources.list.d/pgdg.list
!apt -qq update
!apt -yq install postgresql-12 postgresql-client-12
!service postgresql start
# make calling psql shorter
!sudo -u postgres psql -c "CREATE USER root WITH SUPERUSER"  
!psql postgres -c "CREATE DATABASE root"  # now just !psql -c "..."
# load SQL extensions
%load_ext sql
%config SqlMagic.feedback=False 
%config SqlMagic.autopandas=True
%sql postgresql+psycopg2://@/postgres
#@title **Download the data to local environment**

!wget -q https://raw.githubusercontent.com/OHDSI/ETL--PulmonaryHypertensionRegistries/master/getting_full_dates/data/ae_sample.csv
!wget -q https://raw.githubusercontent.com/OHDSI/ETL--PulmonaryHypertensionRegistries/master/getting_full_dates/data/dm_sample.csv
!wget -q https://raw.githubusercontent.com/OHDSI/ETL--PulmonaryHypertensionRegistries/master/getting_full_dates/data/ds_sample.csv
!wget -q https://raw.githubusercontent.com/OHDSI/ETL--PulmonaryHypertensionRegistries/master/getting_full_dates/data/ex_sample.csv
!wget -q https://raw.githubusercontent.com/OHDSI/ETL--PulmonaryHypertensionRegistries/master/getting_full_dates/data/imputation_dates.csv
!wget -q https://raw.githubusercontent.com/OHDSI/ETL--PulmonaryHypertensionRegistries/master/getting_full_dates/data/sv_sample.csv
!wget -q https://raw.githubusercontent.com/OHDSI/ETL--PulmonaryHypertensionRegistries/master/getting_full_dates/data/vs_sample.csv

!mkdir -p dates_imputation
!mv *.csv dates_imputation/
!chmod -R 755 /content/dates_imputation<jupyter_output><empty_output><jupyter_text>**Upload source tables**<jupyter_code>#@title **Clean the source schema**
%%sql

DROP SCHEMA IF EXISTS src CASCADE;

CREATE SCHEMA src;
#@title **Create AE table**
%%sql

DROP TABLE IF EXISTS src.ae;

CREATE TABLE src.ae
(
    studyid     VARCHAR(10),
    domain      VARCHAR(2),
    usubjid     VARCHAR(22),
    aeseq       NUMERIC,
    aespid      VARCHAR(20),
    aeterm      VARCHAR(200),
    aellt       VARCHAR(100),
    aelltcd     INTEGER,
    aedecod     VARCHAR(100),
    aeptcd      INTEGER,
    aehlt       VARCHAR(100),
    aehltcd     INTEGER,
    aehlgt      VARCHAR(100),
    aehlgtcd    INTEGER,
    aecat       VARCHAR(50),
    aescat      VARCHAR(50),
    aepresp     VARCHAR(2),
    aebodsys    VARCHAR(100),
    aebdsycd    INTEGER,
    aesoc       VARCHAR(100),
    aesoccd     INTEGER,
    aeser       VARCHAR(1),
    aeacn       VARCHAR(30),
    aerel       VARCHAR(50),
    aeout       VARCHAR(35),
    aesdth      VARCHAR(2),
    aeshosp     VARCHAR(2),
    epoch       VARCHAR(50),
    aestdtc     VARCHAR(10),
    aeendtc     VARCHAR(10),
    aestdy      INTEGER,
    aeendy      INTEGER
);

COPY
    src.ae(
        studyid,
        domain,
        usubjid,
        aeseq,
        aespid,
        aeterm,
        aellt,
        aelltcd,
        aedecod,
        aeptcd,
        aehlt,
        aehltcd,
        aehlgt,
        aehlgtcd,
        aecat,
        aescat,
        aepresp,
        aebodsys,
        aebdsycd,
        aesoc,
        aesoccd,
        aeser,
        aeacn,
        aerel,
        aeout,
        aesdth,
        aeshosp,
        epoch,
        aestdtc,
        aeendtc,
        aestdy,
        aeendy
        )
FROM '/content/dates_imputation/ae_sample.csv' DELIMITER ',' NULL 'NULL' CSV HEADER;
#@title **Create DM table**
%%sql

DROP TABLE IF EXISTS src.dm;
CREATE TABLE src.dm
(
	studyid		VARCHAR(10),
	domain		VARCHAR(2),
	usubjid		VARCHAR(22),
	subjid		VARCHAR(11),
	rfstdtc		VARCHAR(10),  
	rfendtc		VARCHAR(10),  
	rfxstdtc	VARCHAR(10),  
	rfxendtc	VARCHAR(10),  
	rficdtc		VARCHAR(10),  
	rfpendtc	VARCHAR(10),  
	dthdtc		VARCHAR(10),  
	dthfl		VARCHAR(1),
	siteid		VARCHAR(20),
    brthdtc     INTEGER,
	age			INTEGER,
	ageu		VARCHAR(10),
	sex			VARCHAR(1),
	race		VARCHAR(50),
	armcd		VARCHAR(20),
	arm			VARCHAR(200),
	actarmcd	VARCHAR(20),
	actarm		VARCHAR(200),
	country		VARCHAR(3)
);

COPY
    src.dm(
        studyid,
    	domain,
    	usubjid,
    	subjid,
    	rfstdtc,
    	rfendtc,
    	rfxstdtc,
    	rfxendtc,
    	rficdtc,
    	rfpendtc,
    	dthdtc,
    	dthfl,
    	siteid,
        brthdtc,
    	age,
    	ageu,
    	sex,
    	race,
    	armcd,
    	arm,
    	actarmcd,
    	actarm,
    	country
    )
FROM '/content/dates_imputation/dm_sample.csv' DELIMITER ',' NULL 'NULL' CSV HEADER;
#@title **Create DS table**
%%sql

DROP TABLE IF EXISTS src.ds;
CREATE TABLE src.ds
(
	studyid		VARCHAR(10),
	domain		VARCHAR(2),
	usubjid		VARCHAR(22),
	dsseq		NUMERIC,
	dsspid		VARCHAR(20),
	dsterm		VARCHAR(200),
	dsdecod		VARCHAR(100),
	dscat		VARCHAR(50),
    dsscat      VARCHAR(50),
	epoch		VARCHAR(50),
	dsdtc		VARCHAR(10),
	dsstdtc		VARCHAR(10), 
	dsdy		INTEGER,
	dsstdy		INTEGER
);

COPY
    src.ds(
        studyid,
    	domain,
    	usubjid,
    	dsseq,
    	dsspid,
    	dsterm,
    	dsdecod,
    	dscat,
        dsscat,
    	epoch,
    	dsdtc,
    	dsstdtc,
    	dsdy,
    	dsstdy
    )
FROM '/content/dates_imputation/ds_sample.csv' DELIMITER ',' NULL 'NULL' CSV HEADER;
#@title **Create EX table**
%%sql

DROP TABLE IF EXISTS src.ex;
CREATE TABLE src.ex (
	studyid		VARCHAR(10),
	domain		VARCHAR(2),
	usubjid		VARCHAR(22),
	exseq		INTEGER,
    exspid      VARCHAR(50),
	extrt		VARCHAR(200),
    exdecod     VARCHAR(100),
    excat       VARCHAR(50),
    exclas      VARCHAR(200),
    exclascd    VARCHAR(20),
	exdose		NUMERIC,
    exdostxt    VARCHAR(200),
	exdosu		VARCHAR(20),
	exdosfrm	VARCHAR(20),
	exdosfrq	VARCHAR(20),
	exroute		VARCHAR(20),
	epoch		VARCHAR(50),
	exstdtc		VARCHAR(10),
	exendtc		VARCHAR(10),
	exstdy		INTEGER,
	exendy		INTEGER
);

COPY
    src.ex(
        studyid,
    	domain,
    	usubjid,
    	exseq,
        exspid,
    	extrt,
        exdecod,
        excat,
        exclas,
        exclascd,
    	exdose,
        exdostxt,
    	exdosu,
    	exdosfrm,
    	exdosfrq,
    	exroute,
    	epoch,
    	exstdtc,
    	exendtc,
    	exstdy,
    	exendy
    )
FROM '/content/dates_imputation/ex_sample.csv' DELIMITER ',' NULL 'NULL' CSV HEADER;
#@title **Create SV table**
%%sql

DROP TABLE IF EXISTS src.sv;
CREATE TABLE src.sv
(
	studyid		VARCHAR(10),
	domain		VARCHAR(2),
	usubjid		VARCHAR(22),
	visitnum	NUMERIC,
	visit		VARCHAR(50),
	visitdy		INTEGER,
	epoch		VARCHAR(50),
	svstdtc		VARCHAR(10),
	svendtc		VARCHAR(10),
	svstdy		INTEGER,
	svendy		INTEGER
);

COPY
    src.sv
    (
        studyid,
    	domain,
    	usubjid,
    	visitnum,
    	visit,
    	visitdy,
    	epoch,
    	svstdtc,
    	svendtc,
    	svstdy,
    	svendy
    )
FROM '/content/dates_imputation/sv_sample.csv' DELIMITER ',' NULL 'NULL' CSV HEADER;
#@title **Create VS table**
%%sql

DROP TABLE IF EXISTS src.vs;
CREATE TABLE src.vs
(
	studyid		VARCHAR(10),
	domain		VARCHAR(2),
	usubjid		VARCHAR(22),
	vsseq		INTEGER,
    vsspid      VARCHAR(20),
	vstestcd	VARCHAR(8),
	vstest		VARCHAR(40),
	vsorres		VARCHAR(20),
	vsorresu	VARCHAR(20),
	vsstresc	VARCHAR(20),
	vsstresn	NUMERIC,
	vsstresu	VARCHAR(20),
	vsstat		VARCHAR(20),
	vsblfl		VARCHAR(2),
	visitnum	NUMERIC,
	visit		VARCHAR(50),
	epoch		VARCHAR(50),
	vsdtc		VARCHAR(10),
	vsdy		INTEGER,
	vstpt		VARCHAR(50),
	vstptref	VARCHAR(50)
);

COPY
    src.vs
    (
        studyid,
	    domain,
	    usubjid,
	    vsseq,
        vsspid,
	    vstestcd,
	    vstest,
	    vsorres,
	    vsorresu,
	    vsstresc,
	    vsstresn,
	    vsstresu,
	    vsstat,
	    vsblfl,
	    visitnum,
	    visit,
	    epoch,
	    vsdtc,
	    vsdy,
	    vstpt,
	    vstptref
    )
FROM '/content/dates_imputation/vs_sample.csv' DELIMITER ',' NULL 'NULL' CSV HEADER;<jupyter_output><empty_output><jupyter_text>**Helper function**<jupyter_code>%%sql

-- a custom CAST function that returns custom value if casting was unsuccessful 
CREATE OR REPLACE FUNCTION try_cast(_in text, INOUT _out ANYELEMENT)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT %L::%s', $1, pg_typeof(_out))
   INTO  _out;
EXCEPTION WHEN others THEN
   -- do nothing: _out already carries default
END
$func$;<jupyter_output><empty_output><jupyter_text>This function supresses CAST errors with user-defined values. It is a direct copy-paste from [this StackExchange post](https://dba.stackexchange.com/a/203986). For an example run the cell below 👇<jupyter_code>%%sql

select try_cast('2018-10', NULL::DATE);<jupyter_output><empty_output><jupyter_text>**Dates imputations**  **Schemas**Regarding schemas, three are used. The source tables are retained in the **src** schema. Temporary tables are created in the **temp** schema. Finally, cleaned source tables with full dates are stored in the staging **stg** schema.<jupyter_code>#@title Clean the temp schema
%%sql
DROP SCHEMA IF EXISTS temp CASCADE;

CREATE SCHEMA temp;
#@title Clean the staging schema
%%sql
DROP SCHEMA IF EXISTS stg CASCADE;

CREATE SCHEMA stg;<jupyter_output><empty_output><jupyter_text>**Imputation Dates Table**As it was mentioned in the wiki, each patient has several reference dates, which form a ground for the date imputation. Let's lift the veil. The following dates are gathered for this particular example:- birth date- death date- first Uptravi date is a date when a patient administered Uptravi for the first time- end of study date- the last available date is the latest date present in the data for a patient- baseline visit- pah drug initiation date, it is a date when the first dose of a PAH-specific drug was administeredThe imputation dates table is introduced to store these reference dates.Whereas other dates can be directly pulled from a specific source table, the **last available date** cannot. So to get this date, you need to scan through all the tables, take every date, and then take the **maximal** date. During this procedure, partial dates are imputed with the first date of a month or a year since this will distort the result less.Since populating the imputation dates table demands all the source tables and is time-consuming, it was omitted in this example. So here we are just uploading this table from a file, assuming it was populated the way described. Later in the example code, you will find that imputation often happens in two steps, so the first step is used for the calculation of the **last available date**.<jupyter_code>#@title Upload the imputation dates table
%%sql

DROP TABLE IF EXISTS temp.imputation_dates;

CREATE TABLE temp.imputation_dates
(
	person_source_value		VARCHAR(22),
	birth_date		        DATE,
	death_date		        DATE,
	first_uptravi_date		DATE,
    end_of_study_date       DATE,
	last_available_date	    DATE,
	baseline_visit		    DATE,
	pah_initiation_date		DATE
);

COPY
    temp.imputation_dates
    (
        person_source_value,
	    birth_date,
	    death_date,
	    first_uptravi_date,
        end_of_study_date,
	    last_available_date,
	    baseline_visit,
	    pah_initiation_date
    )
FROM '/content/dates_imputation/imputation_dates.csv' DELIMITER ',' NULL 'NULL' CSV HEADER;<jupyter_output><empty_output><jupyter_text>**The DS and VS tables**Dates in these tables are not imputed, but they are used for dates imputation in the AE, EX, and VS tables. The disposition table (DS) contains records about various 'disposition' events that happen with a patient, i.e., the patient enters or terminates a study.The subject visits (VS) table holds information about the timing of subject visits, i.e., dates of baseline and follow-up visits.Note here _step1 and _clean tables created in the temp and staging schemas correspondingly. The step1 is what is used to estimate the last available date discussed above, while the clean table is a resulting table that will be used further in the ETL.  DS<jupyter_code>%%sql

DROP TABLE IF EXISTS temp.ds_clean_step1;

CREATE TABLE temp.ds_clean_step1 AS
  SELECT usubjid,
         dsdecod,
         dsstdtc,
         dscat,
         dsscat,
         dsterm,
         Cast(dsstdtc AS DATE) AS dsstdtc_clean,
         -- _imp date is used for calculating last_available_date
         Cast(CASE
                WHEN dsstdtc LIKE '--%'
                      OR Length(Replace(dsstdtc, '---', '-')) = 0 THEN NULL
                WHEN Length(Replace(dsstdtc, '---', '-')) = 4 THEN dsstdtc
                                                                   || '-01-01'
                WHEN Length(Replace(dsstdtc, '---', '-')) = 7 THEN dsstdtc
                                                                   || '-01'
                ELSE dsstdtc
              END AS DATE)     AS dsstdtc_imp
  FROM   src.ds src; 
%%sql

select *
from src.ds;
%%sql

DROP TABLE IF EXISTS stg.ds_clean;

CREATE TABLE stg.ds_clean AS
  SELECT usubjid,
         dsdecod,
         dscat,
         dsscat,
         dsterm,
         dsstdtc               AS dsstdtc_orig,
         dsstdtc_clean,
         Cast(dsstdtc AS date) AS dsstdtc
  FROM   temp.ds_clean_step1 src;<jupyter_output><empty_output><jupyter_text>SV<jupyter_code>%%sql

DROP TABLE IF EXISTS temp.sv_clean_step1;

CREATE TABLE temp.sv_clean_step1 AS
  SELECT usubjid,
         visitnum,
         visit,
         svstdtc,
         try_cast(svstdtc, NULL::DATE) AS svstdtc_clean,
         -- _imp date is used for calculating last_available_date
         CAST(CASE
                WHEN svstdtc LIKE '--%' OR Length(Replace(svstdtc, '---', '-')) = 0 THEN NULL
                WHEN Length(Replace(svstdtc, '---', '-')) = 4  THEN svstdtc || '-01-01'
                WHEN Length(Replace(svstdtc, '---', '-')) = 7  THEN svstdtc || '-01'
                ELSE svstdtc
            END AS DATE) AS svstdtc_imp,
         svendtc,
         try_cast(svendtc, NULL::DATE) AS svendtc_clean,
         -- _imp date is used for calculating last_available_date
         CAST(CASE
                WHEN svendtc LIKE '--%' OR Length(Replace(svendtc, '---', '-')) = 0 THEN NULL
                WHEN Length(Replace(svendtc, '---', '-')) = 4  THEN svendtc || '-01-01'
                WHEN Length(Replace(svendtc, '---', '-')) = 7  THEN svendtc || '-01'
                ELSE svendtc
            END AS DATE) AS svendtc_imp
FROM src.sv src;
%%sql

DROP TABLE IF EXISTS stg.sv_clean;

CREATE TABLE stg.sv_clean AS
  SELECT usubjid,
         visitnum,
         visit,
         svstdtc               AS svstdtc_orig,
         svendtc               AS svendtc_orig,
         svstdtc_clean,
         svendtc_clean,
         Cast(svstdtc AS date) AS svstdtc,
         Cast(svendtc AS date) AS svendtc
  FROM   temp.sv_clean_step1;<jupyter_output><empty_output><jupyter_text>**EX**Let's look into the Exposure table:| usubjid | exseq | extrt | exdose| exdosu | exstdtc | exendtc|| :-: | :-: | :-: | :-: | :-: | :- | :- || A0001-0001 | 2 | Uptravi | 400 | ug | 2018-10-22 | 2018-10| A0001-0001 | 3 | Uptravi | 800 | ug |	2018-10-29 | 2018-11-16| A0001-0001 | 4 | Uptravi | 1200 | ug | 2018 |	2018-12-14| A0001-0001 | 5 | Uptravi | 1600 | ug | 2018-12 | NULL| A0001-0002 | 2 | Uptravi | 400 | ug |	2018-08-08 | 2018-08-22| A0001-0002 | 3 | Uptravi | 800 | ug | 2018 | 2018-08-30| A0001-0002 | 4 | Uptravi | 400 | ug | 2018-10-24 | 2018-11-21| A0001-0002 | 5 | Uptravi | 600| ug | 2018-11-22 | NULL |Here we see that drug administration records represent a sequence (the EXSEQ contains sequence order), i.e., the end of the previous treatment episode is adjacent to the start date of the current episode. This fact can be used for imputation. The SAP for this study also recommends imputations based on next or previous dose dates if they are available (i.e., complete).The following rules have been proposed for the start date:1. impute with the first date of month/year (YYYY-MM-01/YYYY-01-01)2. impute the end date3. re-impute start date as the end of previous episode + 1 dayFor the end date:1. impute with the start date of the next episode - 1 dayAt the same time, this makes sense if available date parts coincide with ones from the adjacent episode. An additional condition will be that we do not want imputed dates to be greater than the last available date for this person.<jupyter_code>%%sql

DROP TABLE IF EXISTS temp.ex_clean_step1;

CREATE TABLE temp.ex_clean_step1 AS
  SELECT usubjid,
         exspid,
         excat,
         extrt,
         exseq,
         exdose,
         exdosu,
         exdosfrm,
         exdosfrq,
         exdostxt,
         exroute,
         exdecod,
         exstdtc,
         try_cast(exstdtc, NULL::DATE) AS exstdtc_clean,
         -- _imp date is used for calculating last_available_date
         Cast(CASE
                WHEN exstdtc LIKE '--%'
                      OR Length(Replace(exstdtc, '---', '-')) = 0 THEN NULL
                WHEN Length(Replace(exstdtc, '---', '-')) = 4 THEN exstdtc
                                                                   || '-01-01'
                WHEN Length(Replace(exstdtc, '---', '-')) = 7 THEN exstdtc
                                                                   || '-01'
                ELSE exstdtc
              END AS DATE)     AS exstdtc_imp,
         exendtc,
         try_cast(exendtc, NULL::DATE) AS exendtc_clean,
         -- _imp date is used for calculating last_available_date
         Cast(CASE
                WHEN exendtc LIKE '--%'
                      OR Length(Replace(exendtc, '---', '-')) = 0 THEN NULL
                WHEN Length(Replace(exendtc, '---', '-')) = 4 THEN exendtc
                                                                   || '-01-01'
                WHEN Length(Replace(exendtc, '---', '-')) = 7 THEN exendtc
                                                                   || '-01'
                ELSE exendtc
              END AS DATE)     AS exendtc_imp
  FROM   src.ex ex; 
%%sql

DROP TABLE IF EXISTS temp.ex_clean_step2;

CREATE TABLE temp.ex_clean_step2 AS
    SELECT  src.usubjid,
            exspid,
            excat,
            extrt,
            exseq,
            exdose,
            exdosu,
            exdosfrm,
            exdosfrq,
            exdostxt,
            exroute,
            exdecod,
            exstdtc AS exstdtc_orig,
            exendtc AS exendtc_orig,
            exstdtc_clean,
            exendtc_clean,
            CAST
            (
                CASE
                    WHEN 
                        LENGTH(exstdtc) = 4
                    THEN 
                        exstdtc || '-01-01'
                    
                    WHEN 
                        LENGTH(exstdtc) = 7  
                    THEN 
                        exstdtc || '-01'
                    
                    ELSE 
                        exstdtc
                END 

                AS DATE
            )                                                                   AS exstdtc,
            CASE
                WHEN
                    LENGTH(COALESCE(exendtc, '')) = 0
                    AND LEAD(src.exstdtc) OVER (PARTITION BY src.usubjid, src.extrt ORDER BY src.exstdtc) IS NOT NULL
                THEN
                    CAST(LEAD(src.exstdtc) OVER (PARTITION BY src.usubjid, src.extrt ORDER BY src.exstdtc) AS DATE) - INTERVAL '1 DAY'
                
                WHEN 
                    LENGTH(COALESCE(exendtc, '')) = 0
                THEN
                    imp.last_available_date
                
                WHEN 
                    LENGTH(exendtc) = 4 
                    AND exendtc = CAST(EXTRACT(YEAR FROM CAST(LEAD(src.exstdtc) OVER (PARTITION BY src.usubjid, src.extrt ORDER BY src.exstdtc) AS DATE)) AS VARCHAR)
                THEN 
                    CAST(LEAD(src.exstdtc) OVER (PARTITION BY src.usubjid, src.extrt ORDER BY src.exstdtc) AS DATE) - INTERVAL '1 DAY'
                
                WHEN 
                    LENGTH(exendtc) = 4 
                    AND exendtc = CAST(EXTRACT(YEAR FROM imp.last_available_date) AS VARCHAR)
                THEN 
                    imp.last_available_date

                WHEN
                    LENGTH(exendtc) = 4
                THEN
                    CAST(exendtc || '-01-01' AS DATE)
                
                WHEN 
                    LENGTH(exendtc) = 7 
                    AND SPLIT_PART(exendtc, '-', 1) = CAST(EXTRACT(YEAR FROM CAST(LEAD(src.exstdtc) OVER (PARTITION BY src.usubjid, src.extrt ORDER BY src.exstdtc) AS DATE)) AS VARCHAR)
                    AND SPLIT_PART(exendtc, '-', 2) = CAST(EXTRACT(MONTH FROM CAST(LEAD(src.exstdtc) OVER (PARTITION BY src.usubjid, src.extrt ORDER BY src.exstdtc) AS DATE)) AS VARCHAR)
                THEN 
                    CAST(LEAD(src.exstdtc) OVER (PARTITION BY src.usubjid, src.extrt ORDER BY src.exstdtc) AS DATE) - INTERVAL '1 DAY'
                
                WHEN 
                    LENGTH(exendtc) = 7 
                    AND SPLIT_PART(exendtc, '-', 1) = CAST(EXTRACT(YEAR FROM imp.last_available_date) AS VARCHAR)
                    AND SPLIT_PART(exendtc, '-', 2) = CAST(EXTRACT(MONTH FROM imp.last_available_date) AS VARCHAR)
                THEN 
                    imp.last_available_date
                
                WHEN 
                    LENGTH(exendtc) = 7 
                THEN 
                    CAST(exendtc || '-01' AS DATE)
                
                ELSE 
                    CAST(exendtc AS DATE)
            END 
                                                                    AS exendtc
FROM 
    temp.ex_clean_step1 src
LEFT JOIN 
    temp.imputation_dates imp
        ON src.usubjid = imp.person_source_value
LEFT JOIN
    temp.ds_clean_step1 src_ds
        ON  src.usubjid = src_ds.usubjid
        AND src_ds.dscat = 'DISPOSITION EVENT';
%%sql

DROP TABLE IF EXISTS stg.ex_clean;

CREATE TABLE stg.ex_clean AS
SELECT   src.usubjid,
         exspid,
         excat,
         extrt,
         exseq,
         exdose,
         exdosu,
         exdosfrm,
         exdosfrq,
         exdostxt,
         exroute,
         exdecod,
         exstdtc_orig,
         exendtc_orig,
         exstdtc_clean,
         exendtc_clean,
         CASE
                  WHEN Length(exstdtc_orig) = 4
                  AND      exstdtc < Lag(exendtc) over ( partition BY usubjid, extrt ORDER BY exseq) 
                  AND      lag(exendtc) over ( partition BY usubjid, extrt ORDER BY exseq) + INTERVAL '1 DAY' <= imp.last_available_date
                    THEN lag(exendtc) over ( partition BY usubjid, extrt ORDER BY exseq) + INTERVAL '1 DAY'
                  WHEN Length(exstdtc_orig) = 4
                  AND      exstdtc < Lag(exendtc) over ( partition BY usubjid, extrt ORDER BY exseq) 
                  AND      lag(exendtc) over ( partition BY usubjid, extrt ORDER BY exseq) + INTERVAL '1 DAY' > imp.last_available_date
                    THEN imp.last_available_date
                  WHEN length(exstdtc_orig) = 7
                  AND      exstdtc < lag(exendtc) over ( partition BY usubjid, extrt ORDER BY exseq)
                  AND lag(exendtc) over ( partition BY usubjid, extrt ORDER BY exseq) + INTERVAL '1 DAY' <= imp.last_available_date
                    THEN lag(exendtc) over ( partition BY usubjid, extrt ORDER BY exseq) + INTERVAL '1 DAY'
                  WHEN length(exstdtc_orig) = 7
                  AND      exstdtc < lag(exendtc) over ( partition BY usubjid, extrt ORDER BY exseq)
                  AND lag(exendtc) over ( partition BY usubjid, extrt ORDER BY exseq) + INTERVAL '1 DAY' > imp.last_available_date
                    THEN imp.last_available_date
                  ELSE exstdtc
         end AS exstdtc,
         exendtc
FROM     temp.ex_clean_step2 src
LEFT JOIN 
    temp.imputation_dates imp
        ON src.usubjid = imp.person_source_value;<jupyter_output><empty_output><jupyter_text>Let's check how our imputaion logic worked:<jupyter_code>%%sql

SELECT usubjid,
       exseq,
       extrt,
       exdose,
       exdosu,
       exstdtc_orig,
       exendtc_orig,
       exstdtc,
       exendtc
FROM   stg.ex_clean
ORDER  BY usubjid,
          exseq;<jupyter_output><empty_output><jupyter_text>**AE**All advese event dates are missing for our patients, but there are some clues in the date on when those events happened.| usubjid | aeseq | aespid | aeterm | aestdtc | aeendtc || :-: | :-: | :-: | :-: | :-: | :-: || A0001-0001 | 1 | ME-U02 | PAH disease progression	| NULL | NULL| A0001-0001 | 2 | ME-U03 | PAH disease progression	| NULL | NULL| A0001-0002 | 1 | FU01 | Diarrhea | NULL | NULL| A0001-0002 | 2 | ME-U02 | Diarrhea | NULL | NULL| A0001-0002 | 3 | FU01 | Headache | NULL | NULL| A0001-0002 | 4 | ME-U02 | Headache | NULL | NULL|First of all, an adverse event belongs to the Condition domain, so its end date is not mandatory, so there is no sense in imputing if the end date (in fact, imputations, where they are not necessary, could lead to misrepresentation of the source data).  Secondly, the imputation algorithm could be based on the sponsor-defined identifier values (AESPID) since they point to a visit or a treatment episode when this or that adverse event happens. So we need to look for a corresponding date in the SV or EX table. An important thing to note here is that the imputation logic for this table (AE) is based on the imputed dates in the other table (EX).A special case, which is an exception from this logic, would be an adverse event that resulted in death, but our examples do not have such a case. See the code below.<jupyter_code>%%sql

DROP TABLE IF EXISTS temp.ae_clean_step1;

CREATE TABLE temp.ae_clean_step1 AS
  SELECT usubjid,
         aeseq,
         aespid,
         aeterm,
         aellt,
         aelltcd,
         aedecod,
         aeptcd,
         aehlt,
         aehltcd,
         aehlgt,
         aehlgtcd,
         aescat,
         aecat,
         aeout,
         aestdtc,
        try_cast(aestdtc, NULL::DATE) AS aestdtc_clean,
        -- _imp date is used for calculating last_available_date
        CAST(CASE
                WHEN aestdtc LIKE '--%' OR Length(Replace(aestdtc, '---', '-')) = 0 THEN NULL
                WHEN Length(Replace(aestdtc, '---', '-')) = 4  THEN aestdtc || '-01-01'
                WHEN Length(Replace(aestdtc, '---', '-')) = 7  THEN aestdtc || '-01'
                ELSE aestdtc
            END AS DATE) AS aestdtc_imp,
        aeendtc,
        try_cast(aeendtc, NULL::DATE) AS aeendtc_clean,
        -- _imp date is used for calculating last_available_date
        CAST(CASE
                WHEN aeendtc LIKE '--%' OR Length(Replace(aeendtc, '---', '-')) = 0 THEN NULL
                WHEN Length(Replace(aeendtc, '---', '-')) = 4  THEN aeendtc || '-01-01'
                WHEN Length(Replace(aeendtc, '---', '-')) = 7  THEN aeendtc || '-01'
                ELSE aeendtc
            END AS DATE) AS aeendtc_imp
FROM src.ae src;
%%sql

DROP TABLE IF EXISTS stg.ae_clean;

CREATE TABLE stg.ae_clean AS
  SELECT DISTINCT 
        src.usubjid,
        aeseq,
        aespid,
        aeterm,
        aellt,
        aelltcd,
        aedecod,
        aeptcd,
        aehlt,
        aehltcd,
        aehlgt,
        aehlgtcd,
        aescat,
        aecat,
        aeout,
        aestdtc AS aestdtc_orig,
        aeendtc AS aeendtc_orig,
        aestdtc_clean,
        aeendtc_clean,
        CASE
            -- 'FATAL' outcome case
            WHEN
                Length(src.aestdtc) != 10
                AND src.aeout = 'FATAL'
            THEN
                imp.death_date

            -- missing date
            WHEN
                Length(COALESCE(src.aestdtc, '')) = 0
                AND src.aespid = 'BL'
            THEN
                src_sv_baseline.svstdtc

            WHEN
                Length(COALESCE(src.aestdtc, '')) = 0
                AND src.aespid LIKE 'FU%'
            THEN
                src_sv_fu.svstdtc

            WHEN
                Length(COALESCE(src.aestdtc, '')) = 0
                AND src.aespid LIKE 'ME-U%'
            THEN
                src_ex.exendtc

            -- yyyy partial date
            WHEN
                Length(src.aestdtc) = 4
                AND src.aespid LIKE 'FU%'
                AND src.aestdtc = CAST(EXTRACT(YEAR FROM src_sv_fu.svstdtc) AS VARCHAR)
            THEN
                src_sv_fu.svstdtc

            WHEN
                Length(src.aestdtc) = 4
                AND src.aespid LIKE 'FU%'
            THEN
                CAST(src.aestdtc || '-01-01' AS DATE)

            -- yyyy-mm partial date
            WHEN
                Length(src.aestdtc) = 7
                AND src.aespid LIKE 'FU%'
                AND SPLIT_PART(src.aestdtc, '-', 1) = CAST(EXTRACT(YEAR FROM src_sv_fu.svstdtc) AS VARCHAR)
                AND SPLIT_PART(src.aestdtc, '-', 1) = CAST(EXTRACT(MONTH FROM src_sv_fu.svstdtc) AS VARCHAR)
            THEN
                src_sv_fu.svstdtc

            WHEN
                Length(src.aestdtc) = 7
                AND src.aespid LIKE 'FU%'
            THEN
                CAST(src.aestdtc || '-01' AS DATE)

            ELSE
                CAST(src.aestdtc AS DATE)
        END           AS aestdtc,
        aeendtc_clean AS aeendtc
FROM 
    temp.ae_clean_step1 src
LEFT JOIN 
    temp.imputation_dates imp
        ON src.usubjid = imp.person_source_value
LEFT JOIN
    stg.sv_clean src_sv_baseline
    -- temp.sv_clean_step1 src_sv_baseline
        ON  src_sv_baseline.usubjid = src.usubjid
        AND src_sv_baseline.visit = 'BASELINE'
LEFT JOIN
    stg.sv_clean src_sv_fu
    -- temp.sv_clean_step1 src_sv_fu
        ON  src_sv_fu.usubjid = src.usubjid
        AND src.aespid = 'FU' || LPAD(CAST(src_sv_fu.visitnum - 1 AS VARCHAR), 2, '0')
LEFT JOIN
    stg.ex_clean src_ex
        ON  src.usubjid = src_ex.usubjid
        AND src.aespid = src_ex.exspid;<jupyter_output><empty_output><jupyter_text>Here are the imputed dates:<jupyter_code>%%sql

SELECT usubjid,
       aeseq,
       aespid,
       aeterm,
       aestdtc_orig,
       aeendtc_orig,
       aestdtc,
       aeendtc
FROM   stg.ae_clean
ORDER  BY usubjid,
          aeseq;<jupyter_output><empty_output><jupyter_text>**VS**The date of vital signs assessment is imputed based on the planned time point (VSTPT) and time point reference (VSTPTREF).| usubjid| vsseq | vstest | visit | vsdtc | vstpt | vstptref || :-: | :-: | :-: | :-: | :- | :- | :-: || A0001-0001 | 1 | Body Mass Index | BASELINE | 2018-10-22 | AT | BASELINE || A0001-0001 | 2 | Diastolic Blood Pressure | BASELINE | 2018-10 | WITHIN 3 MONTHS PRIOR TO OR AT | BASELINE || A0001-0001 | 3 | Height | BASELINE | 2018-10-22 | AT | BASELINE || A0001-0001 | 4 | Heart Rate | BASELINE | 2018-10 | WITHIN 3 MONTHS PRIOR TO OR AT | BASELINE || A0001-0001 | 5 | Systolic Blood Pressure | UPTRAVI INITIATION | 2018-10 | WITHIN 3 MONTHS PRIOR TO OR AT | UPTRAVI INITIATION || A0001-0001 | 6 | Weight | BASELINE | 2018-10-22 | AT | BASELINE || A0001-0002 | 1 | Body Mass Index | BASELINE | NULL | AT | BASELINE || A0001-0002 | 2 | Diastolic Blood Pressure | UPTRAVI INITIATION | 2018 | AT | UPTRAVI INITIATION || A0001-0002 | 3 | Height | BASELINE | 2018-08-08 | AT | BASELINE || A0001-0002 | 4 | Heart Rate | BASELINE | 2018-08-08 | WITHIN 3 MONTHS PRIOR TO OR AT | BASELINE || A0001-0002 | 5 | Systolic Blood Pressure | BASELINE | 2018-08-08 | WITHIN 3 MONTHS PRIOR TO OR AT | BASELINE || A0001-0002 | 6 | Weight | BASELINE | 2018-08-08 | AT | BASELINE || A0001-0002 | 7 | Weight | FOLLOW-UP 1 | 2018-08-24 | SINCE LAST VISIT | BASELINE |The date of vital signs assessment is imputed based on the planned time point (VSTPT) and time point reference (VSTPTREF).The idea here is to find an appropriate reference date and use it in imputation. This use can be direct, i.e., substituting the missing/partial date, or indirect when days or months are added or subtracted from the reference date. See the code below for a more comprehensive understanding.<jupyter_code>%%sql

DROP TABLE IF EXISTS temp.vs_clean_step1;

CREATE TABLE temp.vs_clean_step1 AS
  SELECT usubjid,
         vsseq,
         vstestcd,
         vstest,
         vsorres,
         vsorresu,
         vsstresc,
         vsstresn,
         vsstresu,
         visitnum,
         vsstat,
         vstptref,
         vstpt,
         vsdtc,
         try_cast(vsdtc, NULL::DATE) AS vsdtc_clean,
         CAST(CASE
                 WHEN vsdtc LIKE '--%' OR Length(Replace(vsdtc, '---', '-')) = 0 THEN NULL
                 WHEN Length(Replace(vsdtc, '---', '-')) = 4  THEN vsdtc || '-01-01'
                 WHEN Length(Replace(vsdtc, '---', '-')) = 7  THEN vsdtc || '-01'
                 ELSE VSDTC
             END AS DATE) as vsdtc_imp
FROM src.vs;
%%sql

DROP TABLE IF EXISTS stg.vs_clean;

CREATE TABLE stg.vs_clean AS
  SELECT
        src.usubjid,
        vsseq,
        vstestcd,
        vstest,
        vsstat, 
        vsorres,
        vsorresu,
        vsstresc,
        vsstresn,
        vsstresu,
        src.visitnum,
        vstptref,
        vstpt,
        vsdtc           as vsdtc_orig,
        vsdtc_clean,
        CASE
            WHEN
                Length(COALESCE(vsdtc, '')) = 0
                AND vstptref = 'BASELINE'
                AND vstpt = 'PRIOR TO OR AT'
            THEN
                LEAST(imp.baseline_visit, imp.pah_initiation_date)

            WHEN
                Length(COALESCE(vsdtc, '')) = 0
                AND vstptref = 'BASELINE'
                AND (vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR TO OR AT' OR vstpt = 'AT')
            THEN
                imp.baseline_visit -- see row #7 in the resulting vs_clean table

            WHEN
                Length(COALESCE(vsdtc, '')) = 0
                AND vstptref = 'UPTRAVI INITIATION'
            THEN
                imp.first_uptravi_date

            WHEN
                Length(COALESCE(vsdtc, '')) = 0
                AND vstpt = 'SINCE LAST VISIT'
            THEN
                src_sv.svstdtc

            WHEN
                Length(vsdtc) = 4
                AND vstptref = 'BASELINE'
                AND vstpt = 'PRIOR TO OR AT'
                AND (vsdtc = CAST(EXTRACT(YEAR FROM imp.baseline_visit) AS VARCHAR)
                    OR vsdtc = CAST(EXTRACT(YEAR FROM imp.pah_initiation_date) AS VARCHAR))
            THEN
                LEAST(imp.baseline_visit, imp.pah_initiation_date)

            WHEN
                Length(vsdtc) = 4
                AND vstptref = 'BASELINE'
                AND vstpt = 'PRIOR TO OR AT'
            THEN
                CAST(vsdtc || '-06-30' AS DATE)

            WHEN
                Length(vsdtc) = 4
                AND vstptref = 'BASELINE'
                AND 
                (
                    vstpt = 'AT'
                    OR vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR TO OR AT' 
                    OR vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR'
                )
                AND vsdtc = CAST(EXTRACT(YEAR FROM imp.baseline_visit) AS VARCHAR)
            THEN
                imp.baseline_visit

            WHEN
                Length(vsdtc) = 4
                AND vstptref = 'BASELINE'
                AND 
                (
                    vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR TO OR AT'
                    OR vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR'
                )
                AND CAST(vsdtc AS INT) < CAST(EXTRACT(YEAR FROM imp.baseline_visit) AS INT)
            THEN
                imp.baseline_visit - CAST(substring(vstpt from '(12|6|3)') || ' MONTH' AS INTERVAL)

            WHEN
                Length(vsdtc) = 4
                AND vstptref = 'UPTRAVI INITIATION'
                AND vstpt = 'PRIOR TO OR AT'
                AND vsdtc = CAST(EXTRACT(YEAR FROM imp.first_uptravi_date) AS VARCHAR)
            THEN
                imp.first_uptravi_date

            WHEN
                Length(vsdtc) = 4
                AND vstptref = 'UPTRAVI INITIATION'
                AND vstpt = 'PRIOR TO OR AT'
            THEN
                CAST(vsdtc || '-06-30' AS DATE)

            WHEN
                Length(vsdtc) = 4
                AND vstptref = 'UPTRAVI INITIATION'
                AND (vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR TO OR AT' OR vstpt = 'AT')
                AND vsdtc = CAST(EXTRACT(YEAR FROM imp.first_uptravi_date) AS VARCHAR)
            THEN
                imp.first_uptravi_date -- see row #8 in the resulting vs_clean table

            WHEN
                Length(vsdtc) = 4
                AND vstptref = 'UPTRAVI INITIATION'
                AND vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR TO OR AT'
                AND CAST(vsdtc AS INT) < CAST(EXTRACT(YEAR FROM imp.first_uptravi_date) AS INT)
            THEN
                imp.first_uptravi_date - CAST(substring(vstpt from '(12|6|3)') || ' MONTH' AS INTERVAL)

            WHEN
                Length(vsdtc) = 4
                AND vstpt = 'SINCE LAST VISIT'
                AND vsdtc = CAST(EXTRACT(YEAR FROM src_sv.svstdtc) AS VARCHAR)
            THEN
                src_sv.svstdtc

            WHEN
                Length(vsdtc) = 4
                AND vstpt = 'SINCE LAST VISIT'
            THEN
                CAST(vsdtc || '-06-30' AS DATE)

            WHEN
                Length(vsdtc) = 7
                AND vstptref = 'BASELINE'
                AND vstpt = 'PRIOR TO OR AT'
                AND 
                (
                    (
                        SPLIT_PART(vsdtc, '-', 1) = CAST(EXTRACT(YEAR FROM imp.baseline_visit) AS VARCHAR)
                        AND SPLIT_PART(vsdtc, '-', 2) = CAST(EXTRACT(MONTH FROM imp.baseline_visit) AS VARCHAR)
                    )
                    OR
                    (
                        SPLIT_PART(vsdtc, '-', 1) = CAST(EXTRACT(YEAR FROM imp.pah_initiation_date) AS VARCHAR)
                        AND SPLIT_PART(vsdtc, '-', 2) = CAST(EXTRACT(MONTH FROM imp.pah_initiation_date) AS VARCHAR)
                    )
                )
            THEN
                LEAST(imp.baseline_visit, imp.pah_initiation_date)

            WHEN
                Length(vsdtc) = 7
                AND vstptref = 'BASELINE'
                AND vstpt = 'PRIOR TO OR AT'
            THEN
                CAST(vsdtc || '-15' AS DATE)

            WHEN
                Length(vsdtc) = 7
                AND vstptref = 'BASELINE'
                AND 
                (
                    vstpt = 'AT'
                    OR vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR TO OR AT' 
                    OR vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR'
                )
                AND SPLIT_PART(vsdtc, '-', 1) = CAST(EXTRACT(YEAR FROM imp.baseline_visit) AS VARCHAR)
                AND SPLIT_PART(vsdtc, '-', 2) = CAST(EXTRACT(MONTH FROM imp.baseline_visit) AS VARCHAR)
            THEN
                imp.baseline_visit -- see rows #1, 3, 4 in the resulting vs_clean table

            WHEN
                Length(vsdtc) = 7
                AND vstptref = 'BASELINE'
                AND 
                (
                    vstpt = 'AT'
                    OR vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR TO OR AT' 
                    OR vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR'
                )
            THEN
                CAST(vsdtc || '-15' AS DATE)

            WHEN
                Length(vsdtc) = 7
                AND vstptref = 'UPTRAVI INITIATION'
                AND vstpt = 'PRIOR TO OR AT'
                AND SPLIT_PART(vsdtc, '-', 1) = CAST(EXTRACT(YEAR FROM imp.first_uptravi_date) AS VARCHAR)
                AND SPLIT_PART(vsdtc, '-', 2) = CAST(EXTRACT(MONTH FROM imp.first_uptravi_date) AS VARCHAR)
            THEN
                imp.first_uptravi_date

            WHEN
                Length(vsdtc) = 7
                AND vstptref = 'UPTRAVI INITIATION'
                AND vstpt = 'PRIOR TO OR AT'
            THEN
                CAST(vsdtc || '-15' AS DATE)

            WHEN
                Length(vsdtc) = 7
                AND vstptref = 'UPTRAVI INITIATION'
                AND (vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR TO OR AT' OR vstpt = 'AT')
                AND SPLIT_PART(vsdtc, '-', 1) = CAST(EXTRACT(YEAR FROM imp.first_uptravi_date) AS VARCHAR)
                AND SPLIT_PART(vsdtc, '-', 2) = CAST(EXTRACT(MONTH FROM imp.first_uptravi_date) AS VARCHAR)
            THEN
                imp.first_uptravi_date

            WHEN
                Length(vsdtc) = 7
                AND vstptref = 'UPTRAVI INITIATION'
                AND (vstpt SIMILAR TO 'WITHIN (12|6|3) MONTHS PRIOR TO OR AT' OR vstpt = 'AT')
            THEN
                CAST(vsdtc || '-15' AS DATE)

            WHEN
                Length(vsdtc) = 7
                AND vstpt = 'SINCE LAST VISIT'
                AND SPLIT_PART(vsdtc, '-', 1) = CAST(EXTRACT(YEAR FROM src_sv.svstdtc) AS VARCHAR)
                AND SPLIT_PART(vsdtc, '-', 2) = CAST(EXTRACT(MONTH FROM src_sv.svstdtc) AS VARCHAR)
            THEN
                src_sv.svstdtc

            WHEN
                Length(vsdtc) = 7
                AND vstpt = 'SINCE LAST VISIT'
            THEN
                CAST(vsdtc || '-15' AS DATE)

            ELSE
                CAST(vsdtc AS DATE)
        END                             AS vsdtc
FROM 
    temp.vs_clean_step1 src
LEFT JOIN
    temp.imputation_dates imp 
        ON src.usubjid = imp.person_source_value
LEFT JOIN
    stg.sv_clean src_sv
        ON  src_sv.usubjid = src.usubjid
        AND src_sv.visitnum = src.visitnum
 ;<jupyter_output><empty_output><jupyter_text>Here are the imputed dates:<jupyter_code>%%sql

SELECT usubjid,
       vsseq,
       vstest,
       vsstat,
       vsorres,
       vsorresu,
       vsstresc,
       vsstresn,
       vsstresu,
       visitnum,
       vstptref,
       vstpt,
       vsdtc_orig,
       vsdtc
FROM   stg.vs_clean
ORDER  BY usubjid,
          vsseq;<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0867855148017129, 'fraction_numerical': 0.022784397691305157, 'mean_word_length': 1.8091657949790796, 'pattern_counts': {'":': 0, '<': 65, '<?xml version=': 0, '>': 60, 'https://': 11, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 27, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '640200', 'license': 'Apache-2.0', 'n_tokens_mistral': 15637, 'n_tokens_neox': 14147, 'n_words': 3865, 'path': 'getting_full_dates/getting_full_dates.ipynb', 'repo_name': 'OHDSI/ETL--PulmonaryHypertensionRegistries', 'size': '42968'}