Module Output

SQL: [IDI_Community].[hlth_ash_indicator].ash_indicator_YYYYMM
SAS: libname ash ODBC dsn=idi_community_srvprd schema=hlth_ash_indicator; proc print data = ash.ash_indicator_YYYYMM; run ;
How to access a code module in the Data Lab:Read here

Context:

This code defines spells where clients have had a publicly funded hospital event which is considered to be an Ambulatory Sensitive Care Hospitalisation (ASH) event. This includes the diagnosis (ICD10 code) and the relevant ages for which it is considered an ASH event.

Ambulatory Sensitive Care Hospitalisation (ASH) conditions are health conditions for which adequate management, treatment and interventions delivered in the ambulatory care setting could potentially prevent hospitalisation. Ambulatory care refers to medical services performed on an outpatient basis, without admission to a hospital or other facility.

The expected business key for this spell dataset is one row per ASH event, ICD10 code and version of ASH. For example, if a single hospitalisation includes 2 diagnoses (ICD10s) and there are two version of ASH, one of child ASH.

Key Concepts

ASH measures the performance of the health system. ASH is NOT intended as indicators of whether treatment was appropriate or hospitalisations could have been prevented in any individual case. We expect some hospitalisations for each ASH condition even if the system is working optimally. For instance, of children with asthma, some may need hospitalisation even if they receive timely and appropriate diagnosis and care in the community.
However, if the proportion increases over time, this may be an indicator that there is an issue with the availability of asthma management in primary and community care. (It may also indicate a change in other factors affecting asthma such as poor housing, circulating respiratory illness etc).

It should also be noted that a child who has several ASH events is not necessarily in poorer health than a child who has none. There are many serious childhood illnesses that are not part of the ASH definition.

Community of Interest

Key Business Rules

ASH Definition

• The main ASH definition for ages 0-4 and 45-64 has been constructed from the description used by Ministry of Health as at 2024. This includes a list of diagnosis and procedure codes and the recommended age bracket for each condition. To get this list go to: (https://www.health.govt.nz/new-zealand-health-system/accountability-and-funding/planning-and-performance-data/ambulatory-sensitive-avoidable-hospital-admissions) and download one of the excel workbooks then go to the sheet ‘List of ASH Conditions’.

End Date

• The [end_date] in this table is the end of the hospital visit when diagnosis took place, NOT the date that the chronic condition ended.

Data Consistency

• Consistent counts start in July 1999.
• As at the June 2022 refresh the data for 2019 - 2021 are incomplete.
• Advice from MoH is that the hospitalisation data in NMDS generally has a one year lag. It is recommended that you look at the count of events over time to get a clear picture of the likely completeness at any point in time.
• It is likely that the end date of the reliable period will extend as the data is updated.

Age

• Linking the [snz_uid] to [personal.detail] resulted in a number of individuals with missing birth dates. For this reason, the age of each individual was determined using the [moh_evt_birth_year_nbr] and [moh_evt_birth_month_nbr] from [pub_fund_hosp_discharges_event]. This resulted in no undefined ages.

Hospitalisation

• An individual can be associated with more than one ICD10 code in a single event. This may result in two or more rows for the same hospitalisation.
• Note that a single stay may also include two events if they happen very closely together in time.

Child ASH

• Most Child ASH codes correspond to children aged 1 month to 14 years (pg. 28, www.journal.nzma.org.nz/journal-articles/developing-a-tool-to-monitor-potentially-avoidable-and-ambulatory-care-sensitive-hosptilisations-in-new-zealand-children), however certain vaccine preventable diseases correspond to different age groups.
• In engaging with MoH they asked that we exclude anyone born in the month of the hospitalisation or the month before that. Note that it will exclude slightly too many people because the baby will be between 30 and 60 days old before they start counting.

Diagnosis Definition

• In some cases you may wish to limit the definition to only include the principle (primary) diagnosis. This is done in the where clause of the first query. Official stats only include the primary diagnosis.

Exclusions/Inclusions

• Data limited to acute and arranged admitted events, and in some cases elective is included.
• Non-casemix events excluded.
• Note that the numbers published by Ministry of Health exclude transfers so will be lower than the numbers in the module. There is ongoing investigation into this issue to see if it can be resolved.

Import lookup tables:

1. Expand Databases in Object Explorer.
2. Right click on IDI_Sandpit > Tasks > Import Flat File…
3. Find location of lookup table to be imported.
4. Provide a new table name (must be identical to names above). Choose the table schema (project).
5. Modify columns

• change data type for diagnosis_code and applicable_ages to varchar(50).
• change data type for start_age_mnths, end_age_mnths, and code_char_len to int.
  Note that this will require the correct permissions for this table and user. If this does not work then another alternative is a BULK INSERT.

Parameters

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

1. {targetdb}: The SQL database on which the spell dataset is to be created.
2. {targetschema}: The project schema under the target database into which the spell datasets are to be created.
3. {idicleanrefresh}: The refresh version database that you want to use.
4. {targetprefix}: Prefix

Dependencies

{idicleanrefresh}.[moh_clean].[pub_fund_hosp_discharges_diag]
{idicleanrefresh}.[moh_clean].[pub_fund_hosp_discharges_event]
{targetdb}.{targetschema}.[moh_ASH_lookup]

Outputs

{targetdb}.{targetschema}.[{targetprefix}_ASH]

Variable Descriptions

The business key for this spell table is one row per snz_uid, moh_dia_event_id_nbr, start_date and end_date.

---

Variable Descriptions - Lookup Table

Module Version & Change History

Code

/* Set Parameters */

/*PARAMETERS
SQLCMD only (Activate by clicking Query->SQLCMD Mode)
Already in master.sql; Uncomment when running individually 
*/

:setvar idicleanversion "{idicleanversion}" 
:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar yyyymm "{yyyymm}"

/* Assign the target database to which all the components need to be created in. */
USE $(targetdb);
GO

DROP TABLE IF EXISTS #ASH_1; 
SELECT
	  b.[snz_uid]
	  ,a.[moh_dia_event_id_nbr]
	  ,b.[moh_evt_evst_date] AS [start_date]
	  ,b.[moh_evt_even_date] AS [end_date]
	  ,a.[moh_dia_clinical_code]
	  ,b.[moh_evt_birth_month_nbr]
	  ,b.[moh_evt_birth_year_nbr]
	  ,a.[moh_dia_diagnosis_type_code] /* to identify if principle diagnosis or other relevant diagnosis - in practice, the principle diagnosis is usually the initial diagnosis on the discharge. */
	  ,case	when b.moh_evt_adm_type_code in ('AP','WN','WP','ZW') then 1 else 0 end as elective_ind /* elective admission type */
	  ,case when b.moh_evt_adm_type_code in ('AA','AC','ZA','WU','RL','ZC') then 1 else 0 end as acute_ind /* this is both acute and arranged admission type */
	  ,b.moh_evt_purchaser_code /* how the event is funded - district, MOH, accredited employers, ACC, etc. District/MOH funded is 20, 34 and 35 */
	  ,b.[moh_evt_dhb_dom_code] /* to identify the domicile of the patient (domicile of residence) - overseas and unknown for ASH */
	  ,case when b.[moh_evt_hlth_spec_code] in ('M80','M81') then 1 else 0 end as palliative_care_ind /* identify events where the health specialty is palliative care - part of the 2013 ASH definition */
	  ,case when b.[moh_evt_hlth_spec_code] in ('M05','M06','M07','M08') and b.moh_evt_los_nbr <= 1 then 1 else 0 end as ed_shortstay /* identify events where health specialty is ED and length of stay is less than 1 (short stay) - part of the 2013 ASH definition */
	  /*Determine the age (in months) of the individual at time of event*/
	  ,DATEDIFF(month, (DATEFROMPARTS(b.[moh_evt_birth_year_nbr], b.[moh_evt_birth_month_nbr], 15)), b.[moh_evt_evst_date]) AS age_mnths
	  ,b.[moh_evt_pur_unit_text]
	  ,b.[moh_evt_acc_flag_code]
	  ,b.[moh_evt_adm_src_code]
	  ,b.[moh_evt_facility_xfer_from_code]
INTO #ASH_1	 
FROM [$(idicleanversion)].[moh_clean].[pub_fund_hosp_discharges_diag] a
/*Join to discharges_events to determine snz_uid and age of each individual.*/
	INNER 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 a.[moh_dia_submitted_system_code] = a.[moh_dia_clinical_sys_code] /* higher accuracy when systems match */
/* diagnosis in ICD10 */
	AND (a.[moh_dia_diagnosis_type_code] IN ('A', 'B') /*"A" is "Principle diagnosis" and "B" is "Other relevant diagnosis" */
	AND CAST(a.[moh_dia_clinical_sys_code] AS INTEGER) >= 10 /* ICD-10-AM - First, second, third, sixth, eighth etc edition*/
	AND DATEDIFF(month, (DATEFROMPARTS(b.[moh_evt_birth_year_nbr], b.[moh_evt_birth_month_nbr], 15)), b.[moh_evt_evst_date]) >= 2) /*The official counts exclude people less than 28 days old.  We cant do exactly the same exclusion but MoH have decided on this approximation*/
	AND b.moh_evt_pur_unit_text <> 'EXCLU'
	AND b.[moh_evt_dhb_dom_code] < 999;

/* Clear before creation */
DROP TABLE IF EXISTS #ASH; 
SELECT [snz_uid]
	  ,[moh_dia_event_id_nbr]
	  ,[start_date]
	  ,[end_date]
	  ,[moh_dia_clinical_code]
	  ,[moh_dia_diagnosis_type_code]
	  ,[source_type]
	  ,[age_mnths]
	  ,[moh_evt_dhb_dom_code]
	  ,ASH_Chapter
	  ,ASH_Condition
	  ,[moh_evt_pur_unit_text]
	  ,[moh_evt_acc_flag_code]
	  ,[moh_evt_adm_src_code]
	  ,[moh_evt_facility_xfer_from_code]
INTO #ASH
/* The following table is a lookup table.*/
FROM
	(
	SELECT
		a.[snz_uid]
	    ,a.[moh_dia_event_id_nbr]
	    ,a.[start_date]
	    ,a.[end_date]
	    ,a.[moh_dia_clinical_code]
	    ,b.[source_type] as source_type
		,b.[include_elective]
	    ,a.elective_ind
	    ,a.acute_ind
	    ,a.moh_evt_purchaser_code
	    ,a.age_mnths
		,a.[moh_dia_diagnosis_type_code]
		,a.[moh_evt_dhb_dom_code]
		,case when b.include_elective = 0 and a.elective_ind = 1 then 1 else 0 end as remove_evts /* Purpose of this is to include dental events that are elective as per the definition. All other events are acutely admitted into hospital. */
		,case when a.palliative_care_ind = 1 then 1 else 0 end as pallcare_2013_excl
		,case when a.ed_shortstay = 1 then 1 else 0 end as edstay_2013_excl
		,b.ASH_Chapter
		,b.ASH_Condition
		,a.[moh_evt_pur_unit_text]
		,a.[moh_evt_acc_flag_code]
	    ,a.[moh_evt_adm_src_code]
	    ,a.[moh_evt_facility_xfer_from_code]
	FROM #ASH_1 a
	LEFT JOIN (SELECT * FROM [IDI_Metadata].[clean_read_CLASSIFICATIONS].[moh_ASH_PAH_lookup] where [source_type] in ('main_2022_ASH','child_ASH'))  b 
			ON SUBSTRING(a.[moh_dia_clinical_code], 1, 3) = b.[diagnosis_code]
			AND b.code_char_len = 3
			AND a.age_mnths between b.start_age_mnths and b.end_age_mnths
	WHERE 
		  SUBSTRING(a.[moh_dia_clinical_code], 1, 3) = b.[diagnosis_code]
	) y
WHERE remove_evts = 0; 

/*Required to insert 4 length ICD10 codes seperately from 3. There are identical 3 and 4 letter [diagnosis_code] (ex. B18 and B181) which become single events when doing a LEFT JOIN. */
INSERT INTO #ASH
SELECT [snz_uid]
	  ,[moh_dia_event_id_nbr]
	  ,[start_date]
	  ,[end_date]
	  ,[moh_dia_clinical_code]
	  ,[moh_dia_diagnosis_type_code]
	  ,[source_type]
	  ,[age_mnths]
	  ,[moh_evt_dhb_dom_code]
	  ,ASH_Chapter
	  ,ASH_Condition
	  ,[moh_evt_pur_unit_text]
	  ,[moh_evt_acc_flag_code]
	  ,[moh_evt_adm_src_code]
	  ,[moh_evt_facility_xfer_from_code]
FROM
	(
	SELECT
		a.[snz_uid]
	    ,a.[moh_dia_event_id_nbr]
	    ,a.[start_date]
	    ,a.[end_date]
	    ,a.[moh_dia_clinical_code]
	    ,c.[source_type] as source_type
		,c.[include_elective]
	    ,a.elective_ind
	    ,a.acute_ind
	    ,a.moh_evt_purchaser_code
	    ,a.age_mnths
		,a.[moh_dia_diagnosis_type_code]
		,a.[moh_evt_dhb_dom_code]
		,case when c.include_elective = 0 and a.elective_ind = 1 then 1 else 0 end as remove_evts	/* Purpose of this is to include dental events that are elective as per the definition. All other events are acutely admitted into hospital. */
		,case when a.palliative_care_ind = 1 then 1 else 0 end as pallcare_2013_excl 
		,case when a.ed_shortstay = 1 then 1 else 0 end as edstay_2013_excl
		,c.ASH_Chapter
		,c.ASH_Condition
		,a.[moh_evt_pur_unit_text]
		,a.[moh_evt_acc_flag_code]
	    ,a.[moh_evt_adm_src_code]
	    ,a.[moh_evt_facility_xfer_from_code]
	FROM #ASH_1 a
		LEFT JOIN (SELECT * FROM [IDI_Metadata].[clean_read_CLASSIFICATIONS].[moh_ASH_PAH_lookup] where [source_type] in ('main_2022_ASH','child_ASH')) c
			ON SUBSTRING(a.[moh_dia_clinical_code], 1, 4) = c.[diagnosis_code]
			AND c.code_char_len = 4
			AND a.age_mnths between c.start_age_mnths and c.end_age_mnths
	WHERE SUBSTRING(a.[moh_dia_clinical_code], 1, 4) = c.[diagnosis_code]
	) x
WHERE remove_evts = 0; 

/*Write the proper table*/
DROP TABLE IF EXISTS #final_ASH;

/*Put the ASH ones in too*/
SELECT [snz_uid]
	  ,[moh_dia_event_id_nbr]
	  ,[start_date]
	  ,[end_date]
	  ,[moh_dia_clinical_code]
	  ,[moh_dia_diagnosis_type_code]
	  ,[source_type]
	  ,[age_mnths]
	  ,[moh_evt_dhb_dom_code]
	  ,ASH_Chapter
	  ,ASH_Condition
	  ,[moh_evt_pur_unit_text]
	  ,[moh_evt_acc_flag_code]
	  ,[moh_evt_adm_src_code]
	  ,[moh_evt_facility_xfer_from_code]
INTO #final_ASH
FROM #ASH

DROP TABLE IF EXISTS [IDI_Sandpit].[$(targetschema)].[$(targetprefix)_ASH];
 
CREATE TABLE [IDI_Sandpit].[$(targetschema)].[$(targetprefix)_ASH] (
snz_uid                         int          NOT NULL,
moh_dia_event_id_nbr            int          NOT NULL,
[start_date]                    date         NULL,
end_date                        date         NULL,
moh_dia_clinical_code           varchar(8)   NULL,
moh_dia_diagnosis_type_code     char(1)      NULL,
source_type                     varchar(19)  NOT NULL, 
age_mnths                       int          NULL,
moh_evt_dhb_dom_code            char(3)      NULL,
ASH_Chapter                     varchar(100) NULL,
ASH_Condition                   varchar(100) NULL,
moh_evt_pur_unit_text           varchar(10)  NULL,
moh_evt_acc_flag_code           char(1)      NULL,
moh_evt_adm_src_code            char(1)      NULL,
moh_evt_facility_xfer_from_code char(4)      NULL
);

INSERT INTO [IDI_Sandpit].[$(targetschema)].[$(targetprefix)_ASH] (
snz_uid,
moh_dia_event_id_nbr,
[start_date],
end_date,
moh_dia_clinical_code,
moh_dia_diagnosis_type_code,
source_type, 
age_mnths,
moh_evt_dhb_dom_code,
ASH_Chapter,
ASH_Condition,
moh_evt_pur_unit_text,
moh_evt_acc_flag_code,
moh_evt_adm_src_code,
moh_evt_facility_xfer_from_code)
	

	SELECT 
	*
	FROM #final_ASH