WBL Success Rates Methodology - Master File Production Sample Code

********************************************************.
************Combine fuzzy match data files *****************.
********************************************************.

*Combine the files for the various years into one complete Master File – later years take precedence.

MATCH FILES FILE = 'QSR_WBL_0506_prematch.sav'
/FILE = 'QSR_WBL_0405_fuzzyl03.sav'
/FILE = 'QSR_WBL_0304_fuzzyl03.sav'
/FILE = 'QSR_WBL_0203_fuzzyl03.sav'
/FILE = 'QSR_WBL_0102_fuzzyl03.sav'
/BY L01 L03 A15 matchv.
EXECUTE.

*--------------------------------------------------------------------------.
*calculate expected end year, actual end year and start year.
*--------------------------------------------------------------------------.
*Band start year (note 2001/02 and 2002/03 year dates consistent with WBL periods).

COMPUTE X=1993.
LOOP.
IF (a27 >= date.dmy( 1, 8,X) and a27 <= date.dmy( 31, 7,(X+1))) startyr=X.
COMPUTE X=X+1.
END LOOP IF (X=2006).

*Band expected end year (note 2001/02 and 2002/03 year dates consistent with WBL periods).

COMPUTE X=1993.
LOOP.
IF (a28 >= date.dmy( 1, 8,X) and a28 <= date.dmy( 31, 7,(X+1))) expendyr=X.
COMPUTE X=X+1.
END LOOP IF (X=2025).

*Band actual end year (note 2001/02 and 2002/03 year dates consistent with WBL periods).

COMPUTE X=1993.
LOOP.
IF (a31 >= date.dmy( 1, 8,X) and a31 <= date.dmy( 31, 7,(X+1))) actendyr=X.
COMPUTE X=X+1.
END LOOP IF (X=2025).
EXECUTE.

*--------------------------------------------------------------------------.

*Identify learners who have gone missing or appeared between years.

COMPUTE miss2002= 0.
COMPUTE miss2003 = 0.
COMPUTE miss2004 = 0.
COMPUTE miss2005 = 0.
COMPUTE app_2002 = 0.
COMPUTE app_2003 = 0.
COMPUTE app_2004 = 0.
COMPUTE app_2005 = 0.
IF (sfl_0102 = 1 AND sfl_0203 = 0 AND SYSMIS(actendyr)) miss2002 = 1.
IF (sfl_0203 = 1 AND sfl_0304 = 0 AND SYSMIS(actendyr)) miss2003 = 1.
IF (sfl_0304 = 1 AND sfl_0405 = 0 AND SYSMIS(actendyr)) miss2004 = 1.
IF (sfl_0405 = 1 AND sfl_0506 = 0 AND SYSMIS(actendyr)) miss2005 = 1.
IF (sfl_0102 = 0 AND sfl_0203 = 1 AND startyr < 2002) app_2002 = 1.
IF (sfl_0203 = 0 AND sfl_0304 = 1 AND startyr < 2003) app_2003 = 1.
IF (sfl_0304 = 0 AND sfl_0405 = 1 AND startyr < 2004) app_2004 = 1.
IF (sfl_0405 = 0 AND sfl_0506 = 1 AND startyr < 2005) app_2005 = 1.
COMPUTE miss = miss2002 + miss2003 + miss2004 + miss2005.

* Learners in most recent year cannot be coded as missing.

IF (sfl_0506 = 1) miss = 0.

*band learners into age groups (includes leap years).

DO IF NOT SYSMIS (L11) AND NOT SYSMIS (A27).
+ DO IF ( XDATE.MONTH(L11) LT XDATE.MONTH(A27)).
+ COMPUTE A_AGEST=XDATE.YEAR(A27) - XDATE.YEAR(L11).
+ ELSE IF ((XDATE.MDAY(L11) LE XDATE.MDAY(A27)) AND (XDATE.MONTH(L11) = XDATE.MONTH(A27))).
+ COMPUTE A_AGEST=XDATE.YEAR(A27) - XDATE.YEAR(L11).
+ ELSE.
+ COMPUTE A_AGEST=(XDATE.YEAR(A27) -1) - XDATE.YEAR(L11).
+ END IF.
END IF.

COMPUTE age_band=0.
IF a_agest<=18 age_band=1.
IF a_agest>=19 age_band=2.

VALUE LABELS age_band 1 '16-18' 2 '19+'.

*/START/***FINAL DE-DUP AND TRANSFER ADJUSTMENT ***********************************************.
*de-dupe records (note two records with same l01 l03 a15 and matchv are thought to be best counted as one record, and hence deduped.
*with the earlier a27 placed in the later record.

Sort cases by l01 (A) l03(A) a15(A) matchv (A) trans (A) achieve_dup (A) a27(D).
COMPUTE dup1=1.
If (l01=lag(l01,1) and l03=lag(l03,1) AND a15=lag(a15,1) AND matchv=lag(matchv,1) AND ACHIEVE_DUP<>1)dup1=0.
sort cases by l01 (A) l03 (A) a15 (A) matchv (A) dup1(A).

*Select only unique records.

SELECT IF dup1=1.

*recode SYSMIS a31 to a large date so that they are put first in decending sort.

IF SYSMIS(a31) a31=date.dmy(1,8,5000).
SORT CASES BY l01(A) l03(A) a31(D).

*If there are two records with same l01 and l03, count earlier a31 as trans (unless it shows an NVQ or framework achievement).

IF (l01=lag(l01,1) and l03=lag(l03,1) AND leavers=1 AND NOT(any(achieve_dup,1,2))) trans=1.
IF a31=date.dmy(1,8,5000) a31=1/0.

*delete achievements if learner is counted as a transfer.

IF (trans = 1) leavers = 0.
IF (trans = 1) nvq_ach = 0.
IF (trans = 1) frm_ach = 0.
COMPUTE starters = count - trans.

*/END/***FINAL DE-DUP AND TRANSFER ADJUSTMENT ***********************************************.

Match in Live UPIN Flags

*select l01's who are live in chosen hybridendyr, this overcomes matching issues.

MATCH FILES FILE = *
/TABLE = 'livel01_2005.sav'
/BY l01.
MATCH FILES FILE = *
/TABLE = 'livel01_2004.sav'
/BY l01.
MATCH FILES FILE = *
/TABLE = 'livel01_2003.sav'
/BY l01.
MATCH FILES FILE = *
/TABLE = 'livel01_2002.sav'
/BY l01.
MATCH FILES FILE = *
/TABLE = 'livel01_2001.sav'
/BY l01.
RECODE livel01_0506 livel01_0405 livel01_0304 livel01_0203 livel01_0102 (MISSING=0).

*Flag which year people who went missing actually ended in.

IF (miss > 0 and sfl_0102 = 1) actendyr = 2001.
IF (miss > 0 and sfl_0203 = 1) actendyr = 2002.
IF (miss > 0 and sfl_0304 = 1) actendyr = 2003.
IF (miss > 0 and sfl_0405 = 1) actendyr = 2004.

IF (trans = 0 AND (miss > 0)) leavers = 1.

*Calculate Hybrid end year.

COMPUTE hybridendyr= MAX(expendyr,actendyr).

IF ((leavers = 0) AND SYSMIS(A31) AND not(miss >0)) continuing = 1.
RECODE continuing (MISSING=0) .

AGGREGATE
/OUTFILE='WBL (0506) New Master - TRIM.sav'
/BREAK=L01 L03 A26 A15 A_AOL A_SSA_T1 trans trans_provider plan_break leavers continuing frm_ach nvq_ach A27 A28 A31 A40 sfl_0506 sfl_0405 sfl_0304 sfl_0203
sfl_0102 startyr expendyr actendyr hybridendyr miss age_band L25 livel01_0102 livel01_0203 livel01_0304 livel01_0405 livel01_0506 period
/Count=N.

Creator

Analysis and MI Team

Date issued

31 March 2006

Date created

24 February 2006

Document ref.

\\records.lsc.local\NAT\23 LrngSkillsPolicyInfrastr\23-07 DataCollectAlysis\23-07-03 LrnrDataAlysisDiss\nat-wblsuccessratesmasterfileproductionsamplecode-report-31mar2006.doc

LSC office

Learning and Skills Council
Cheylesmore House Quinton Road Coventry CV1 2WT
T 0845 019 4170 F 024 7682 3675 www.lsc.gov.uk/