Output Table

SQL: [IDI_Community].cm_read_MOE_QUALS_TERTIARY_QUALIFICATIONS.moe_quals_tertiary_qualifications_YYYYMM
SAS: libname cmedqtq ODBC dsn=idi_community_srvprd schema=cm_read_MOE_QUALS_TERTIARY_QUALIFICATIONS;
How to access a code module in the Data Lab : Read here

Context

This script creates the list of tertiary qualifications that an individual has completed and the date it wasthey were attained, in a spell-based format. The corresponding NZQF level of the qualification is also provided.

The business key of the output spell dataset is expected to be one record per student per completion year per provider code per qualification code. However, we can find a few duplicates on this business key - the reason seems to be multiple student IDs being matched to the same Stats NZ snz_uid column. This affects very few records and the codes ignores this problem.

Key Business Rules

1. The scope of the data is all NZ-based tertiary education organizsations (TEOs) that receive government funding, and it givesincludes the formal qualifications attained by the student enrolled with them. This means that qualifications gained from non-Ggovernment funded organisations or those gained outside New Zealand are not covered (except when the organisation is a branch of a govt-funded TEO). The primary source of this data is the Tertiary Education Commission.
2. Researchers should note that this captures achievement in formally-recognised qualifications only. It doesn’t capture, for example, formal achievement in programmes of study that don’t result in a formal qualification. For, for example, the courses, modules, unit standards, micro-credentials etcetc. that can often can lead towards qualifications, or that are doneundertaken as stand-alone programmes of study. Researchers who are interested in this type of tertiary achievement should use the [course] file. Also not counted here is cCompletion of non-formal qualifications is also not counted.
3. The scope of the output spells only covers tertiary qualifications attained from the year 2000 onwards. Records before this date are poorly matched to the IDI spine. Data on qualification completion in private tertiary providers was only collected from 2000 onwards. Completions prior to that (1994-99) relate to public providers only. National Students Numbers (NSNs) introduced in 2003 significantly aided the ability to link students across different providers and years from 2003 on, nevertheless from 2000 on is still considered reliable. For those interested in spells before 2000: these are for public providers only, the use of provider code and student id still provide a reasonable way to identify spells. However, the linking of this key to snz_uid is less reliable, in particular before 1997.
4. The NZQF levels used in the Tertiary dataset is a “character” datatype column, and uses “99” as a valid level (to represent unknown NZQF values). This usage is inconsistent with the NZQF levels used in MoE’s school-related datasets. Hence, a business rule is applied that replaces the “99” with “0” and typecasts the column as integer. This is to ensure consistency across various MoE datasets. ‘99’ is an historic code used between 1994 and 2002. From 2003 onward, missing values (blanks) are used. MoE is currently looking into replacing the historic 99’s with blanks for future supplies, this will improve consistency. Missing values since 2003 represent less than 0.02% of all qualification completions. Missing values (99’s) before 2003 make up around 2.5%.
5. A “Level-10 ID” column has been created as an output column for this dataset. This uses an MOoE approach using QACC in conjunction with NZQF Level in order to distinguish both level of qualification gained as well as the type. For example,This is useful for those wanting to, for example, distinguish bachelor degrees from other types of NZQF level 7 qualifications. The approach was also used to address early historical quality issues with the NZQF level, when QACC was more reliable. These historical quality issues with NZQF levels have largely now been remedied, especially from 2000 on. But, this is the main method used by MoE for their tertiary education statistics and analysis by qualification type and level. The name ‘Lev1Oid’ mirrors the MoE format name and refers to the 10 ‘levels’ created. For those researchers needing to distinguish type of qualification in addition to level, then this field can be used in preference to NZQF level. - this is a supposed alternative to the NZQF levels and are used internally by the Ministry of Education. The rationale behind is not clear, but the column has been added here nevertheless. This business logic uses a combination of the QACC (Qualification Award Category Code) and the moe_com_qual_level_code (which is the NZQF level) to derive the Level 10 ID. The exact descriptions of the QACC codes are available under the IDI metadata section for Ministry of Education - Tertiary.
6. The conceptual difference between NZQF level and QACC is that QACC captures the type of qualification - e.g. certificate, diploma degree, doctorate etc. that NZQF does not. For example, for researchers wanting to analyse bachelor degree completion, NZQF level alone would only be a proxy, since a qualification at NZQF Level 7 could be a certificate, diploma or a bachelor degree. The combination of QACC along with NZQF level can describe both the type of qualification and the level of the framework that it sits. Hence MoE’s use of this combination in education statistics.
7. The spell dates are pseudo-dates. MoE Tertiary data only supplies the calendar year in which the student completed the tertiary qualification. Here, we are left to assume that the “latest” date by which that qualification was obtained is the last day of this completion year. The tertiary qualifications are assumed to be valid for a lifetime, and hence the qualification expiry date is conceptually “infinite” and represented as 31-Dec-9999. The spell is effectively the period between this completion date and qualification expiry date.
8. For IDI researchers, who did have a particular need to be able to better identify the time within the year that a qualification was ‘completed’, another option is to use the [moe_enr_prog_end_date] field from corresponding qualification enrolment record for that year in the moe_enrolment table. Linking on year, provider_code, student id and qualification_code. In this context the completion event relates to the time the academic requirements for that qualification were met, not the time the qualification was conferred, or when the student graduated. The year is sufficient for most use cases, and so a pseudo date of 31th December can be used in this case.
9. Non-formal QACC codes only apply in the enrolments tables ([course] and [enrolment]). They cannot exist in the qualification completions table - which relates to formal qualifications only, therefore level 11 was removed from this table.

Inputs

1. IDI_UserCode: The SQL database on which the spell datasets are to be created.
2. {targetschema}: The project schema under the target database into which the spell datasets are to be created.
3. {projprefix}: A (short) prefix that enables you to identify the spell dataset easily in the schema, and prevent overwriting any existing datasets with the same name.

Outputs

Dependencies

[IDI_Clean_202310].[moe_clean].completion

Version Log

List of key contacts from the Community of Interest

Code

/* Establish database for writing views */
:setvar targetdb "IDI_UserCode"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "[IDI_Clean_202310]"
GO

/* Delete the database object if it already exists */
USE $(targetdb);
GO

/* Delete the database object if it already exists */
IF OBJECT_ID('[$(targetschema)].[$(projprefix)_moe_tertiary_quals]','V') IS NOT NULL
DROP VIEW [$(targetschema)].[$(projprefix)_moe_tertiary_quals];
GO

CREATE VIEW [$(targetschema)].[$(projprefix)_moe_tertiary_quals] AS

select  
    a.snz_uid
    ,snz_student_id_uid
    ,cast('TERTIARY' as varchar(50)) as data_source 
    ,a.moe_com_qacc_code as qacc_code
    ,a.moe_com_qual_nzsced_code as nzsced_code_fieldofstudy     
    ,a.moe_com_provider_code as provider_code
    ,a.moe_com_qual_code as qualification_code
    ,datefromparts(a.moe_com_year_nbr, 12, 31) as completion_date
    ,datefromparts(9999, 12, 31) as qual_expiry_date
    /* NZQF levels in tertiary is a varchar column and use '99' as a valid code. This can be confusing and inconsistent with other MoE sources and any attempt to derive maximum of NZQF would result in incorrect values. Hence we decode
    '99' to '0' and cast this column as an integer.*/
    ,case cast(a.moe_com_qual_level_code as smallint) 
        when 99 then 0 
        else cast(a.moe_com_qual_level_code as smallint)
    end as nzqflevel
    /* The following code uses QACC in conjunction with NZQF Level in order to distinguish both level of qualification gained
    as well as type. For example, for those wanting to distinguish bachelor degrees from other types of NZQF level 7 qualifications.
    The approach was also used to address early historical quality issues with the NZQF level, when QACC was more reliable.
    These historical quality issues with NZQF level have largely now been remedied, especially from 2000 on.
    This is the main method used by MoE for their tertiary education statistics and analysis by qualification type and level.
    The name 'Lev1Oid' mirrors the MoE format name and refers to the 10 'levels' created. For those researchers needing to distinguish
    type of qualification in addition to level, then this field can be used in preference to NZQF level. */
    ,case when a.moe_com_qacc_code in ('40','41','46','60') and a.moe_com_qual_level_code='01' then 1 /* Level 1 Certificates */
            when a.moe_com_qacc_code in ('40' ,'41' ,'46','60') and a.moe_com_qual_level_code='02' then 2 /* Level 2 Certificates */
            when a.moe_com_qacc_code in ('30' ,'31','32','33','34','35') then 5 /* Diplomas NZQF Levels 5-7 mostly */
            when a.moe_com_qacc_code in ('20') then 6 /* Bachelors degree (Level 7) */
            when a.moe_com_qacc_code in ('21') then 7 /* Graduate certificates and diplomas mostly Level 7 */
            when a.moe_com_qacc_code in ('12','13','14') then 8 /* Bachelors with Honours, Postgraduate certificates and diplomas Level 8 */
            when a.moe_com_qacc_code in ('11') then 9 /* Masters Level 9 */
            when a.moe_com_qacc_code in ('10','01') then 10 /* doctorates Level 10 */
            /* The below case consist of non-formal qualifications.*/
            else null 
    end as lv10id   
from [$(idicleanversion)].[moe_clean].completion  a
where 
    /* Records before year 2000 are of comparatively poorer matching quality and do not include private tertiary providers.*/
    moe_com_year_nbr >= 2000;
GO