Context and more information is provided under 6. Highest Qualification - from census 2013 data

Output Table

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

Code

 
/* Establish database for writing views */
:setvar targetdb "{targetdb}"
:setvar targetschema "{targetschema}"
:setvar projprefix "{projprefix}"
:setvar idicleanversion "{idicleanversion}"
 
/*
:setvar targetdb "IDI_UserCode"
:setvar targetschema "DL-MAA2020-47"
:setvar projprefix "mdb"
:setvar idicleanversion "IDI_Clean_202410"
*/
 
/* Assign the target database to which all the components need to be created in. */
 
USE $(targetdb);
GO

 
DROP VIEW IF EXISTS [$(targetschema)].$(projprefix)_cen2023_quals;
GO

create view [$(targetschema)].$(projprefix)_cen2023_quals as

select 
	b.snz_uid
	,cast('CENSUS_2023' as varchar(50)) as data_source	
	/*'Highest qualification is derived for people aged 15 years and over, and combines highest 
		secondary school qualification and post-school qualification to obtain a single 
		highest qualification by category of attainment.  */		
	,cen_ind_standard_hst_qual_code as highest_qual_code 
	,c.descriptor_text as highest_qual_text
	,b.cen_ind_post_scl_subjt_code as subject_area_code
	,subj.descriptor as subject_area_text
	/* 07 March 2023 was Census night for 2023, hence this is assumed to be the date the qualification was recorded.*/
	,datefromparts(2023, 3, 7) as qual_recorded_date
	,datefromparts(9999, 12, 31) as qual_expiry_date
	,case 
		when cen_ind_standard_hst_qual_code ='00' then 0
		when cen_ind_standard_hst_qual_code ='01' then 1
		when cen_ind_standard_hst_qual_code ='02' then 2
		when cen_ind_standard_hst_qual_code ='03' then 3
		when cen_ind_standard_hst_qual_code ='04' then 4
		when cen_ind_standard_hst_qual_code ='05' then 5
		when cen_ind_standard_hst_qual_code ='06' then 6
		when cen_ind_standard_hst_qual_code ='07' then 7
		when cen_ind_standard_hst_qual_code ='08' then 8
		when cen_ind_standard_hst_qual_code ='09' then 9
		when cen_ind_standard_hst_qual_code ='10' then 10
		/*Code value: 11 is for overseas secondary school qualification. This has been tentatively assigned to
			NQF level 1, which may be underestimating highest qual. Change this if you want a different assumption. */
		when cen_ind_standard_hst_qual_code ='11' then 1 
		when cen_ind_standard_hst_qual_code in ('97','99') then null
		else null 
	end as high_qual_nqf
from [$(idicleanversion)].[cen_clean].[census_individual_2023] b
left join [IDI_Metadata].[clean_read_CLASSIFICATIONS].[CEN_STDHQ06] c 
	on b.cen_ind_standard_hst_qual_code = c.cat_code
left join [IDI_Metadata].[clean_read_CLASSIFICATIONS].[CEN_QFIELD2_V3.0] subj 
	on (b.cen_ind_post_scl_subjt_code = subj.code)
where 
	/* Filter to ensure Post-school qual level is directly measured in 2018 census form, and not imputed from other sources*/ 
		cen_ind_ps_in_nz_ind_src_code in (11)

	/* Filter to ensure secondary school qual level is directly measured in 2018 census form, and not imputed from other sources*/ 
    and cen_ind_scdry_scl_qual_src_code in (11)
	;