Context and more information is provided under 6. Highest Qualification - from census 2013 data
Output Table
SQL: [IDI_Community].edu_quals_census_2018.quals_census_2018_YYYYMM
SAS: libname cm_c18_q ODBC dsn=idi_community_srvprd schema=edu_quals_census_2018;
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}"
*/
-- Example
-- SQLCMD mode
:setvar targetdb "IDI_UserCode"
:setvar targetschema "DL-MAA2020-47"
:setvar projprefix "tmp"
:setvar idicleanversion "IDI_Clean_202306"
GO
/* Assign the target database to which all the components need to be created in. */
USE $(targetdb);
GO
/* Include 2018 census data wherever the qualifications come from an actual individual form (& not imputed).*/
DROP VIEW IF EXISTS [$(targetschema)].[$(projprefix)_cen2018_quals];
GO
create view [$(targetschema)].[$(projprefix)_cen2018_quals] as
--<!
select
b.snz_uid
,cast('CENSUS_2018' 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
/* 06 March 2018 was Census night for 2018, hence this is assumed to be the date the qualification was recorded.*/
,datefromparts(2018, 3, 6) 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_2018 b
/*inner join {populationdata} pop
on (b.snz_uid=pop.{idcolumn}
and datefromparts(2018, 3, 6) <= pop.{enddatecolumn}
and datefromparts(9999, 12, 31) >= pop.{startdatecolumn}
)*/
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_post_scl_level_imp_ind 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_sch_qual_imp_ind in (11)
/*and cen_ind_standard_hst_qual_code in ('00','01','02','03','04','05','06','07','08','09','10','11')*/ /*include only individuals with a highest qualification code */
;
GO
--!>