Outputs:

SQL: [IDI_Community].[dsbl_adhd].[adhd_202603]
SAS: libname cm_adhd dsn=IDI_community_srvprd schema=dsbl_adhd; run ;
How to access a code module in the Data Lab: Read here

Inputs:

Description

The purpose of this code module is to identify individuals in New Zealand in the IDI who have a recorded diagnosis of ADHD or records indicating an ADHD diagnosis is likely (for example pharmaceutical use).

The dataset will list individuals, the earliest event when an individual has a recorded/likely diagnosis of ADHD in the IDI, date of the earliest event, the individual’s age and whether they are alive at the time of the IDI refresh. For those individuals who have died, the dataset lists a date of death and age at death.

This module is not intended to be used to give a true prevalence rate of people in New Zealand with ADHD due to limitations of the datasets included in the IDI. The code module allows researchers to have a consistent way of identifying people with ADHD and will allow for comparisons of outcomes for people with ADHD and people without ADHD.

Key Concepts

• Individuals are identified from a diagnosis recorded using a recognised ICD-9-CM-A diagnosis code for ADHD or a diagnosis/likely diagnosis recorded through other means by a professional.
• An individual may have multiple entries in a source dataset or across a number of source datasets.
• This module collates the earliest reference to the individual with an appropriate diagnosis/likely diagnosis in each source dataset and then identifies the earliest reference amongst all datasets.
• The module indicates whether the individual was alive at the time of the most recent IDI refresh.
• The module will record events within the time period that data is included in the IDI.
• The earliest date recorded in the IDI is not a date of diagnosis or onset of a condition.

Business history

Code history and ICD code notes

ICD codes used are based on the ICD clinical code system for the earliest date of data as per Health New Zealand (HNZ) | Te Whatu Ora best practice. This means ICD-9-CM-A codes have been used in line with the start of the health-related datasets in the IDI.

On advice from HNZ clinical coding/classification staff, codes have first been looked up directly in ICD-10-AM 12th Edition (current Edition used), then back mapped to ICD-10-AM 1st Edition and then back mapped to ICD-9-CM-A codes.

ICD-9-CM-A codes have also been added to ensure coverage if the diagnosis was coded in ICD-9-CM-A using the latest ICD classification definitions (see Table 1).

References

1. Beltran-Castillon L and McLeod K (2023) From Data to Dignity: Health and Wellbeing Indicators for New Zealanders with Intellectual Disability. Published: IHC, Kotata Insight
2. Stats NZ (2025) Household Disability Survey 2023 - findings, definitions, and design summary.
3. New Zealand Formulary (2025) Release 153 - 1 March 2025.
4. Ministry of Health (2024) New Zealand Health Survey 2023/24 - Annual Data Explorer.

Community of Interest

Key business rules

Table 1: ICD-9-CM-A codes included to ensure coverage if coding in ICD-9-CM-A – includes forward/backward map to ICD-10-AM 1st Edition

To ensure coverage when using ICD-9-CM-A codes, the following codes are noted to forward/backward map to ICD-10-AM 1st Edition.

Note:
Forward mapping provides equivalent codes for deleted codes in the newer edition.
Backward mapping provides equivalent codes for new codes in the newer edition.

Data quality and other known issues

1. Not all individuals in New Zealand diagnosed with ADHD will be identified in this module. The module requires that the individual has had an ACC claim form, hospital discharge events, received mental health or Disability Support Services, had their death recorded, where the diagnosis of ADHD has been specified, or relevant medication dispensed where an ADHD diagnosis is likely.

2. Datasets included have different lag times for having updated data available in the IDI. This means the completeness of the data at the time of analysis will depend on the lag time of the different datasets.

3. Individuals identified in the SOCRATES disability dataset do not always appear in the referral or needs assessment datasets as well. Where an individual is only identified through the SOCRATES disability dataset there is no associated event date. In this case a proxy date of ‘3999-12-31’ will be seen in the final table.

4. Registerable stillbirths are included in the mortality dataset.

Comparison against other sources

• Counts of individuals published in the IHC publication ‘From Data to Dignity’, (2023) were for those individuals with ADHD in the study population defined as the 2018 Administrative Population Census (APC). The counts from this code module for people with ADHD are higher than those in the 2023 publication.

• Counts from the latest Stats NZ Household Disability Survey (HDS) estimated 3% of the New Zealand population aged 5 years and over living in New Zealand households had a diagnosis of ADHD (Stats NZ, 2025). This equates to around ■■■■■■■■■■■■■■. In comparison with the HDS, this module has a significantly lower count.

• Data from the 2023/24 New Zealand Health Survey (NZHS) estimates the prevalence of diagnosed ADHD in children aged 5 to 14 years old to be 5.3% (Ministry of Health, 2024). This module signficantly undercounts for this age group.

Parameters

The following parameters should be supplied to this module to run it in the database:

1. {targetdb}: The SQL database on which the dataset is to be created.
2. {idicleanversion}: The IDI Clean version that the dataset needs to be based on.
3. {targetschema}: The project schema under the target database into which the dataset is to be created.

Variable descriptions

Granularity:
One row represents an individual, the earliest identification of their diagnosis in the IDI, birthdate, gender and death (if applicable).

Module Version & Change History

Code

/*Set Parameters*/
/*Ref date should be the 15th of the month of the refresh used*/
 :setvar targetdb "targetdb"
:setvar targetschema "targetschema" 
:setvar idicleanversion "idicleanversion"    
:setvar refdate "refdate" 
:setvar idimetadataversion "idimetadataversion" 

USE $(targetdb);
GO

/*ACC claims with a code that includes ADHD */

DROP TABLE IF EXISTS #acc_adhd;

SELECT c.snz_uid
    , 'ACC' AS source_table
    , c.code
    , c.date AS event_date
    , CAST(NULL AS VARCHAR(10)) AS entity
INTO #acc_adhd
FROM (
    SELECT [snz_uid]
        , [acc_cla_accident_date] AS date
        , [acc_cla_primary_inj_code] AS code
    FROM [$(idicleanversion)].[acc_clean].[claims]
    WHERE(
        [acc_cla_primary_inj_code] IN ('E2E00', 'E2E01', 'E2E0.')
        AND [acc_cla_primary_inj_type_code] = 'Read Code'
    )
    UNION ALL
    SELECT b.snz_uid
        , b.acc_cla_accident_date AS date
        , [acc_med_injury_code] AS code
    FROM [$(idicleanversion)].[acc_clean].[medical_codes] AS a
    LEFT JOIN [$(idicleanversion)].[acc_clean].[claims] AS b
    ON a.[snz_acc_claim_uid] = b.[snz_acc_claim_uid]
    WHERE [acc_med_injury_code] IN ('E2E00', 'E2E01','E2E0.')
) AS c;

/*Publicly-funded hospital discharges where clinical code matches a diagnosis of ADHD*/

DROP TABLE IF EXISTS #moh_pub_adhd;
SELECT b.snz_uid
    , 'MOH_PUB' AS source_table
    , a.[moh_dia_submitted_system_code]
    , a.[moh_dia_diagnosis_type_code]
    , a.[moh_dia_event_id_nbr]
    , a.[moh_dia_clinical_code]
    , b.[moh_evt_birth_year_nbr]
    , CAST(b.[moh_evt_evst_date] AS DATE) AS event_date
    , CAST (NULL AS VARCHAR(10)) AS entity
INTO #moh_pub_adhd
FROM [$(idicleanversion)].[moh_clean].[pub_fund_hosp_discharges_diag] AS a
LEFT JOIN [$(idicleanversion)].[moh_clean].[pub_fund_hosp_discharges_event] AS b
ON a.moh_dia_event_id_nbr = b.moh_evt_event_id_nbr
WHERE(
    (
        SUBSTRING(a.[moh_dia_clinical_code],1,5) IN ('31400', '31401')
        OR SUBSTRING(a.[moh_dia_clinical_code],1,4) IN ('3141', '3142', '3148', '3149')
    )
    AND a.[moh_dia_clinical_sys_code] = '06'
    AND a.[moh_dia_diagnosis_type_code] IN ('A','B')
);

/*Privately-funded hospital discharges where clinical code matches a diagnosis of ADHD*/

DROP TABLE IF EXISTS #moh_pri_adhd;

SELECT b.snz_uid
    , 'MOH_PRI' AS source_table
    , a.[moh_pri_diag_event_id_nbr]
    , a.[moh_pri_diag_clinic_sys_code]
    , a.[moh_pri_diag_sub_sys_code]
    , a.[moh_pri_diag_diag_type_code]
    , a.[moh_pri_diag_clinic_code]
    , b.[moh_pri_evt_start_date] AS event_date
    , CAST (NULL AS VARCHAR(10)) AS entity
INTO #moh_pri_adhd
FROM [$(idicleanversion)].[moh_clean].[priv_fund_hosp_discharges_diag] AS a
LEFT JOIN [$(idicleanversion)].[moh_clean].[priv_fund_hosp_discharges_event] AS b
ON a.moh_pri_diag_event_id_nbr = b.moh_pri_evt_event_id_nbr
WHERE(
    (
        SUBSTRING(a.[moh_pri_diag_clinic_code],1,5) IN ('31400', '31401')
        OR SUBSTRING(a.[moh_pri_diag_clinic_code],1,4) IN ('3141', '3142', '3148', '3149')
    )
    AND a.[moh_pri_diag_clinic_sys_code] = '06'
    AND a.[moh_pri_diag_diag_type_code] IN ('A','B')
);

/*Registrations in the mortality dataset where clinical code matches a diagnosis of ADHD*/

DROP TABLE IF EXISTS #mos_adhd;

SELECT b.[snz_uid]
    , b.[snz_moh_uid]
    , 'MOH_MOS' AS source_table
    , DATEFROMPARTS(b.moh_mor_birth_year_nbr,b.moh_mor_birth_month_nbr,15) AS event_date
    , a.[moh_mort_diag_clinical_code] AS code
    , a.[moh_mort_diag_clinic_type_code]
    , a.[moh_mort_diag_clinic_sys_code]
    , a.[moh_mort_diag_diag_type_code]
    , CAST (NULL AS VARCHAR(10)) AS entity
INTO #mos_adhd
FROM [$(idicleanversion)].[moh_clean].[mortality_diagnosis] AS a
LEFT JOIN [$(idicleanversion)].[moh_clean].[mortality_registrations] AS b
ON a.[snz_dia_death_reg_uid] = b.[snz_dia_death_reg_uid]
WHERE(
    (
        SUBSTRING(a.[moh_mort_diag_clinical_code],1,5) IN ('31400', '31401')
        OR SUBSTRING(a.[moh_mort_diag_clinical_code],1,4) IN ('3141', '3142', '3148', '3149')
    )
    AND a.[moh_mort_diag_clinic_sys_code] = '06'
)
OR(
    SUBSTRING(a.[moh_mort_diag_clinical_code],1,4) IN ('F900', 'F909', 'F901', 'F908')
    AND a.[moh_mort_diag_clinic_sys_code] >= '10'
);

/* Individuals in the Ministry of Health SOCRATES database with code 1201. Date can either be first contact date or referral date.*/

DROP TABLE IF EXISTS #moh_soc_adhd;
SELECT DISTINCT b.snz_uid
    , 'SOC' AS source_table
    , CASE WHEN d.[soc_needs_ass_first_cont_date] IS NOT NULL THEN d.[soc_needs_ass_first_cont_date] 
	  WHEN c.[soc_referral_first_contact_date] IS NOT NULL THEN c.[soc_referral_first_contact_date]
	  WHEN c.[soc_referral_referral_date] IS NOT NULL THEN c.[soc_referral_referral_date]
	  WHEN c.[soc_referral_referral_date] IS NULL AND c.[soc_referral_first_contact_date] IS NULL AND d.[soc_needs_ass_first_cont_date] IS NULL THEN '3999-12-31'
	  END AS event_date
    , CAST (a.soc_dis_code AS VARCHAR(7)) AS code
    , a.[soc_dis_desc_text] AS Description
    , CAST (NULL AS VARCHAR(10)) AS entity
INTO #moh_soc_adhd
FROM [$(idicleanversion)].[moh_clean].[socrates_disability] AS a
LEFT JOIN [$(idicleanversion)].[moh_clean].[pop_cohort_demographics] AS b
ON a.snz_moh_uid = b.snz_moh_uid
LEFT JOIN(
    SELECT DISTINCT snz_moh_uid
        , [soc_referral_first_contact_date]
        , [soc_referral_referral_date]
    FROM $(idicleanversion).[moh_clean].[socrates_referral]) AS c
    ON a.snz_moh_uid = c.snz_moh_uid
    LEFT JOIN(
        SELECT DISTINCT snz_moh_uid
            , [soc_needs_ass_first_cont_date]
        FROM $(idicleanversion).[moh_clean].[socrates_needs_ass]) AS d
        ON a.snz_moh_uid = d.snz_moh_uid
        WHERE a.soc_dis_code IN ('1201');

/*ADHD diagnosis in PRIMHD diagnosis data*/
        DROP TABLE IF EXISTS #moh_PRIMHD_adhd;
        SELECT b.snz_uid
            , a.[snz_moh_uid]
            , 'MOH_PRIMHD' AS source_table
            , CAST (a.[organisation_id] AS VARCHAR(10)) AS entity
            , a.[classification_code_id]
            , a.[diagnosis_type]
            , CONVERT(date,a.[classification_start_date],103) AS event_date
            , a.[clinical_coding_system_id]
            , a.[clinical_code] AS code
        INTO #moh_PRIMHD_adhd
/* 
	NOTE TO DEVELOPERS: The following line has a hardcoded _202510 refresh version of a table. 
	Does this need to be updated? 
*/
        FROM [IDI_Adhoc].[clean_read_MOH_PRIMHD].[primhd_diagnoses_202510] AS a
        LEFT JOIN [$(idicleanversion)].[moh_clean].[pop_cohort_demographics] AS b
        ON a.snz_moh_uid = b.snz_moh_uid
        WHERE(
            (
                (
                    SUBSTRING(a.[clinical_code],1,5) IN ('31400', '31401')
                    OR SUBSTRING (a.[clinical_code],1,4) = '3149'
                )
                AND a.[clinical_coding_system_id] = '07'
            )
            OR(
                SUBSTRING(a.[clinical_code],1,4) IN ('F900', 'F909', 'F901', 'F908')
                AND a.[clinical_coding_system_id] >= '10'
            )
        );

/*ADHD diagnosis in PRIMHD MHINC data*/
        DROP TABLE IF EXISTS #moh_mhinc_adhd;
        SELECT b.snz_uid
            , 'MOH_MHINC' AS source_table
            , CAST (a.[organisation_id] AS VARCHAR(10)) AS entity
            , a.[classification_start] AS event_date
            , a.[clinical_coding_system_id]
            , a.[clinical_code] AS code
            , a.[diagnosis_type]
        INTO #moh_mhinc_adhd
        FROM [IDI_Adhoc].[clean_read_MOH_PRIMHD].[moh_primhd_mhinc] AS a
        LEFT JOIN [$(idicleanversion)].[moh_clean].[pop_cohort_demographics] AS b
        ON a.snz_moh_uid = b.snz_moh_uid
        WHERE(
            (
                (
                    SUBSTRING(a.[clinical_code],1,5) IN ('31400', '31401')
                    OR SUBSTRING (a.[clinical_code],1,4) = '3149'
                )
                AND a.[clinical_coding_system_id] = '07'
                AND diagnosis_type IN ('A','B','P')
            )
            OR(
                SUBSTRING(a.[clinical_code],1,5) IN ('31400', '31401')
                OR SUBSTRING (a.[clinical_code],1,4) IN ('3141','3149','3142','3148')
                AND a.[clinical_coding_system_id] = '06'
                AND diagnosis_type IN ('A','B','P')
            )
        );

/* Pharmaceutical prescriptions for Atomoxetine, a sole indication pharmaceutical for ADHD. */
/* There are other pharmaceuticals prescribed for ADHD but these have multiple indications and are not included in this module.*/

        DROP TABLE IF EXISTS #phh_adhd;
        SELECT c.snz_uid
            , MIN(start_date) AS event_date
            , MAX(end_date) AS end_date
            , 'PHH' AS source_table
            , CAST (NULL AS VARCHAR(10)) AS entity
        INTO #phh_adhd
        FROM (
            SELECT a.[snz_uid]
                , a.[moh_pha_dispensed_date] AS start_date
                , CASE WHEN a.[moh_pha_days_supply_nbr] > 0 THEN DATEADD (DAY,a.[moh_pha_days_supply_nbr],a.[moh_pha_dispensed_date]) 
ELSE a.[moh_pha_dispensed_date]
END AS end_date
            FROM [$(idicleanversion)].[moh_clean].[pharmaceutical] AS a
            LEFT JOIN [$(idimetadataversion)].[moh_pharm].[dim_form_pack_subsidy_code] AS b
            ON a.[moh_pha_dim_form_pack_code] = b.[dim_form_pack_subsidy_key]
            WHERE chemical_id IN (3887)
        ) AS c
        GROUP BY snz_uid;

/* collate data from all sources into one table*/

        DROP TABLE IF EXISTS #adhd_main;
        SELECT a.snz_uid
            , MIN(event_date) AS min_date
            , source_table
            , entity
        INTO #adhd_main
        FROM (
            SELECT snz_uid
                , event_date
                , source_table
                , entity
            FROM #mos_adhd
            UNION ALL
            SELECT snz_uid
                , event_date
                , source_table
                , entity
            FROM #moh_pub_adhd
            UNION ALL
            SELECT snz_uid
                , event_date
                , source_table
                , entity
            FROM #moh_pri_adhd
            UNION ALL
            SELECT snz_uid
                , event_date
                , source_table
                , entity
            FROM #moh_soc_adhd
            UNION ALL
            SELECT snz_uid
                , event_date
                , source_table
                , entity
            FROM #moh_mhinc_adhd
            UNION ALL
            SELECT snz_uid
                , event_date
                , source_table
                , entity
            FROM #moh_primhd_adhd
            UNION ALL
            SELECT snz_uid
                , event_date
                , source_table
                , entity
            FROM #acc_adhd
            UNION ALL
            SELECT snz_uid
                , event_date
                , source_table
                , entity
            FROM #phh_adhd
        ) AS a
        GROUP BY snz_uid
            , source_table
            , entity;

/* subset of individuals who are deceased and are in death data*/

        DROP TABLE IF EXISTS #adhd_dd;
        SELECT a.*
            , b.snz_birth_month_nbr
            , b.snz_birth_year_nbr
            , b.snz_sex_gender_code
            , b.snz_deceased_year_nbr
            , b.snz_deceased_month_nbr
            , b.snz_birth_date_proxy
            , NULL AS age
            , DATEFROMPARTS(b.snz_deceased_year_nbr, b.snz_deceased_month_nbr, 15) AS dod
            , 'ADHD' AS [type]
        INTO #adhd_dd
        FROM #adhd_main AS a
        INNER JOIN [$(idicleanversion)].[data].[personal_detail] AS b
        ON a.snz_uid = b.snz_uid
        WHERE a.snz_uid IN (SELECT DISTINCT snz_uid FROM [$(idicleanversion)].[dia_clean].[deaths])
        AND b.snz_deceased_year_nbr IS NOT NULL;

/* subset of individuals who are deceased but not in death data*/

        DROP TABLE IF EXISTS #adhd_od;
        SELECT a.*
            , b.snz_birth_month_nbr
            , b.snz_birth_year_nbr
            , b.snz_sex_gender_code
            , b.snz_deceased_year_nbr
            , b.snz_deceased_month_nbr
            , b.snz_birth_date_proxy
            , NULL AS age
            , DATEFROMPARTS(b.snz_deceased_year_nbr, b.snz_deceased_month_nbr, 15) AS dod
            , 'ADHD' AS [type]
        INTO #adhd_od
        FROM #adhd_main AS a
        INNER JOIN [$(idicleanversion)].[data].[personal_detail] AS b
        ON a.snz_uid = b.snz_uid
        WHERE a.snz_uid NOT IN (SELECT DISTINCT snz_uid FROM [$(idicleanversion)].[dia_clean].[deaths])
        AND b.snz_deceased_year_nbr IS NOT NULL;

/* subset of individuals who are alive*/

        DROP TABLE IF EXISTS #adhd_ad;
        SELECT a.*
            , b.snz_birth_month_nbr
            , b.snz_birth_year_nbr
            , b.snz_sex_gender_code
            , b.snz_deceased_year_nbr
            , b.snz_deceased_month_nbr
            , b.snz_birth_date_proxy
            , DATEDIFF(YEAR, b.snz_birth_date_proxy, '$(refdate)') AS age
            , '3999-12-31' AS dod
            , 'ADHD' AS [type]
        INTO #adhd_ad
        FROM #adhd_main AS a
        INNER JOIN [$(idicleanversion)].[data].[personal_detail] AS b
        ON a.snz_uid = b.snz_uid
        WHERE a.snz_uid NOT IN (SELECT DISTINCT snz_uid FROM [$(idicleanversion)].[dia_clean].[deaths])
        AND a.snz_uid NOT IN (SELECT DISTINCT snz_uid FROM #adhd_dd)
        AND a.snz_uid NOT IN (SELECT DISTINCT snz_uid FROM #adhd_od);

 /* join all tables together*/

        DROP TABLE IF EXISTS #adhd_final;
        WITH adhd_union AS(
            SELECT *
            FROM #adhd_dd
            UNION ALL
            SELECT *
            FROM #adhd_od
            UNION ALL
            SELECT *
            FROM #adhd_ad
        )

        SELECT *
            , DATEDIFF(YEAR, snz_birth_date_proxy, dod) AS age_at_death
            , CASE WHEN age IS NOT NULL THEN 0 ELSE 1 END AS death_status
        INTO #adhd_final
        FROM adhd_union;

/* select all required variables */

        DROP TABLE IF EXISTS #adhd_min;
        SELECT snz_uid
            , [type]
            , entity
            , source_table
            , min_date
            , snz_sex_gender_code
            , snz_birth_month_nbr
            , snz_birth_year_nbr
            , snz_birth_date_proxy
            , age
            , death_status
            , snz_deceased_year_nbr
            , snz_deceased_month_nbr
            , CASE WHEN dod = '3999-12-31' THEN NULL ELSE dod END AS dod
            , CASE WHEN age_at_death >1000 THEN NULL ELSE age_at_death END AS age_at_death
        INTO #adhd_min
        FROM #adhd_final ;

/*take earliest recorded date for each snz_uid and create table */
        DROP TABLE IF EXISTS #adhd_dbl;

        SELECT snz_uid
            , [type]
            , entity
            , source_table
            , min_date
            , snz_sex_gender_code
            , snz_birth_month_nbr
            , snz_birth_year_nbr
            , snz_birth_date_proxy
            , age
            , death_status
            , snz_deceased_year_nbr
            , snz_deceased_month_nbr
            , dod
            , age_at_death
        INTO #adhd_dbl
        FROM #adhd_min a
        WHERE min_date = (
            SELECT MIN(min_date)
            FROM #adhd_min b
            WHERE b.snz_uid = a.snz_uid
        )
        ORDER BY snz_uid;

/* remove duplicate snz_uid with the same minimum date but different entities or sources. Create table in IDI_Sandpit */

        DROP TABLE IF EXISTS [$(targetdb)].[$(targetschema)].[$(projprefix)_adhd];

        WITH cte AS(
            SELECT ROW_NUMBER() OVER(PARTITION BY snz_uid ORDER BY snz_uid DESC) AS rn
            FROM #adhd_dbl
        )

        DELETE
        FROM cte
        WHERE rn > 1;

        SELECT snz_uid
            , [type]
            , entity
            , source_table
            , min_date
            , snz_sex_gender_code
            , snz_birth_month_nbr
            , snz_birth_year_nbr
            , snz_birth_date_proxy
            , age
            , death_status
            , snz_deceased_year_nbr
            , snz_deceased_month_nbr
            , dod
            , age_at_death

        INTO [$(targetdb)].[$(targetschema)].[$(projprefix)_adhd]

        FROM #adhd_dbl
 ;