FE Success Rates 2004/05 Sample Code

*****************************************************************.
** Pre sort the hierarchy table.
*****************************************************************.

GET FILE = 'Hierarchy Table.sav'.
SORT CASES BY a09.
SAVE OUTFILE = 'Hierarchy Table.sav'.

*****************************************************************.
** Step to create start year, actual end year and expected end year
** for xxxx/xx data
*****************************************************************.

GET FILE='Tri File xxxx/xx.sav'.

SORT CASES by A09.
MATCH FILES /FILE=*
/TABLE='Hierarchy Table.sav'
/BY A09.
RECODE A28 (0=SYSMIS) (ELSE=COPY).

* If mapcode is missing then recode mapcode to equal qual aim ref.

IF mapcode = (" ") mapcode = a09.

COMPUTE startyr = 0.
DO IF XDATE.MONTH(a27) >= 8.
COMPUTE startyr = XDATE.YEAR(a27).
ELSE IF XDATE.MONTH(a27) < 8.
COMPUTE startyr = (XDATE.YEAR(a27) - 1).
END IF.

COMPUTE actendyr = 0.
DO IF XDATE.MONTH(a31) >= 8.
COMPUTE actendyr = XDATE.YEAR(a31).
ELSE IF XDATE.MONTH(a31) < 8.
COMPUTE actendyr = (XDATE.YEAR(a31) - 1).
END IF.

COMPUTE expendyr = startyr.
DO IF XDATE.MONTH(a28) >= 8.
COMPUTE expendyr = XDATE.YEAR(a28).
ELSE IF XDATE.MONTH(a28) < 8.
COMPUTE expendyr = (XDATE.YEAR(a28) - 1).
END IF.

FORMATS startyr actendyr expendyr (F4).

**This piece of code has been added to take out UFI qualifications.

SELECT IF A46A<>1.
SELECT IF A46B<>1.

SORT CASES BY newref, mapcode, startyr, expendyr.
SAVE OUTFILE = 'Tri_File xxxx/xx.sav'.

**This code needs to be repeated for each years tri-file

*****************************************************************.
** Identify qualifications expected to end in 2004/05 from
** F05 2003/04 and F05 2004/05
*****************************************************************.

GET FILE = ‘Tri_file 2003/04.sav’.
COMPUTE f050304=0.
IF (startyr=2003 AND expendyr=2004 AND A34=1) f050304 =1.
SAVE OUTFILE = ‘Tri_file 2003/04.sav’.

GET FILE = ‘Tri_file 2004/05.sav’.
COMPUTE f050405=0.
IF (startyr=2003 AND expendyr=2004) f050405=1.
SAVE OUTFILE = ‘Tri_file 2004/05.sav’.

*****************************************************************.
** MERGE ALL FIVE ISR/ILRs TOGETHER.
*****************************************************************.
** If a student has the same newref and the same qualification
** aim code (MAPCODE) in two or more files, then the qualification in
** the 2004-05 file takes prescedent over the 2003-04 file which is in
** turn taken over the 2002-03 file which is taken over other files.

GET FILE = ‘Tri_file 2004/05.sav’.

MATCH FILES FILE = *
/FILE = ‘Tri_file 2003/04.sav’
/FILE = ‘Tri_file 2002/03.sav’
/FILE = ‘Tri_file 2001/02.sav’
/FILE = ‘Tri_file 2000/01.sav’
/BY newref mapcode startyr expendyr.
exe.

*****************************************************************.
** Code to identify successful and unsuccessful matches across
** 2003/04 and 2004/05.
*****************************************************************.

RECODE F050304 (SYSMIS=0).
RECODE F050405 (SYSMIS=0).

COMPUTE match = F050304 + F050405.
COMPUTE expmatch=0.
COMPUTE didmatch=0.

IF (F050304 = 1) expmatch=1.
IF (match = 2) didmatch=1.

*****************************************************************.
** Drop cases that are not required for analysis.
*****************************************************************.

**code to ignore qualifications withdrawn before 1 November in first year of qualification adapted for new cyglh code.

SELECT IF NOT (A_EXP_A <=0 and (startyr=expendyr)).
SELECT IF NOT (A_EXP_A <=0.01 AND (A31 < DATE.DMY(1,11,startyr)) AND A34= 3 AND ANY (A35,0,3)).

**Drop all qualifications that are additional units.

SELECT IF mapqty<>"X901".

** Remove Unitisation quals.

SELECT IF mapqty<>"8009".
SELECT IF mapqty<>"8008".

** Drop all qualifications that are diagnostic tests as per database.

SELECT IF NOT(ANY(MAPCODE,'MP027552','MP027554','MP027555','MP027557')).

** Drop tutorial support and complementary studies

SELECT IF NOT(ANY(a09,"77777777","CMISC001","XESF0001")).

** Keep only cases where expected end year is 2004/05, 2003/04 or 2002/03.

SELECT IF ((expendyr = 2002) OR (expendyr = 2003) OR (expendyr = 2004)).

** Add a marker for UFI qualifications

COMPUTE ufi=0.
IF (A46A=1) OR (A46B=1) OR (mapawb="UFI") ufi =1.

VARIABLE LABELS ufi 'ufi quals'.
VALUE LABELS ufi 0 'not ufi qual' 1 'ufi qual'.
exe.

*****************************************************************.
** Code to calculate learner's age at 31 August of the year in which
** the started and to categorise into 16-18 and 19+ age groups.
*****************************************************************.

DO IF (NOT SYSMIS (L11)).
DO IF ( XDATE.MONTH(L11) LE 8).
COMPUTE l_age=startyr - XDATE.YEAR(L11).
ELSE.
COMPUTE l_age=(startyr - 1)-XDATE.YEAR(L11).
END IF.
END IF.

RECODE l_age (SYSMIS=9) (0 THRU 15=1) (16 THRU 18=2) (19 THRU 20=3)
(21 THRU 24=4) (25 THRU 59=5) (60 THRU 120=6) (ELSE=9) INTO l_ageb.

VARIABLE LABELS
l_age 'DV - Age of the learner as at 31 August of start year'
l_ageb 'DV - Age of the learner as at 31 August of start year banded'.

VALUE LABELS l_ageb
1 'Under 16'
2 '16-18'
3 '19-20'
4 '21-24'
5 '25-59'
6 '60 AND over'
9 'missing age'.

**Note 16-18 age group includes under 16 and 19&over age group includes unknowns for purpose of benchmarking.

COMPUTE agegroup = 0.
IF (L_AGEB = 1 or L_AGEB = 2) agegroup = 1.

VARIABLE LABELS agegroup 'age group of student'.
VALUE LABELS agegroup 1 '16-18' 0 '19 & over'.
exe.

*****************************************************************.
** Calculate the expected duration of the learning programme.
*****************************************************************.

** Determine he number of academic years the qualification spans, this is capped at 4 years.

DO IF (NOT ((MISSING (A28)) AND (MISSING (A31)))).
COMPUTE noayears = ((expendyr - startyr) + 1).
ELSE IF ((MISSING (A28)) and (MISSING (A31))).
COMPUTE noayears = 1.
END IF.
IF (noayears > 4) noayears = 4.
VARIABLE LABELS noayears 'Number of academic years qual spans'.

** Calculate the expected duration (of the learning programme) as the number of days.

* This uses expected end date where this is available and actual end date where this is not shown.
* Where both actual and expected end dates are missing, the qualification is assumed to have a short expected
* duration (99 days used to ensure qual is categorised as short (5 - 24 weeks).
* Note: we do not want qualifications with zero cyglh to have an expected duration, therefore code these as missing (-9); these should
* be the only quals with an expected duration of -9.

COMPUTE expdur = -9.
DO IF (NOT (MISSING(A28))).
COMPUTE expdur = CTIME.DAYS(A28) - CTIME.DAYS(A27) + 1.
ELSE IF (NOT (MISSING (A31))).
COMPUTE expdur = CTIME.DAYS(A31) - CTIME.DAYS(A27) + 1.
ELSE IF ((MISSING (A28)) and (MISSING (A31))).
COMPUTE expdur = 99.
END IF.
VARIABLE LABELS expdur 'expected duration of the qualification'.
VALUE LABELS expdur -9 'missing'.

** Define duration category - combination of 'noyears' and 'expdur', taking due account of the impact of leap years.

*If there is a leap year during the expected duration of the course an extra day needs to be added to 'expdur'.

COMPUTE leap =0.
DO IF (A27 <= DATE.DMY(28,2,2000)) AND (A28 > DATE.DMY(28,2,2000)).
COMPUTE leap=1.
ELSE IF (A27 <= DATE.DMY(28,2,2004)) AND (A28 > DATE.DMY(28,2,2004)).
COMPUTE leap=1.
END IF.

COMPUTE dur = 0.

DO IF leap=0.
DO IF (expdur = -9).
COMPUTE dur = -9.
ELSE IF (expdur >= 1 and expdur <= 167).
COMPUTE dur = 1.
ELSE IF (expdur >= 168 and expdur <= 365 and noayears = 1).
COMPUTE dur = 2.
ELSE IF(expdur >= 168 and expdur <= 365 and noayears = 2).
COMPUTE dur = 3.
ELSE IF (expdur >= 366 and expdur <= 730 and noayears <= 2).
COMPUTE dur = 4.
ELSE IF (expdur >= 366 and expdur <= 730 and noayears = 3).
COMPUTE dur = 5.
ELSE IF (expdur >= 731 and expdur <= 1095 and noayears <= 3).
COMPUTE dur = 6.
ELSE IF (expdur >= 731 and expdur <= 1095 and noayears = 4).
COMPUTE dur = 7.
ELSE IF (expdur >= 1096).
COMPUTE dur = 8.
END IF.
END IF.

DO IF leap=1.
DO IF (expdur = -9).
COMPUTE dur = -9.
ELSE IF (expdur >= 1 and expdur <= 167).
COMPUTE dur = 1.
ELSE IF (expdur >= 168 and expdur <= 366 and noayears = 1).
COMPUTE dur = 2.
ELSE IF (expdur >= 168 and expdur <= 366 and noayears = 2).
COMPUTE dur = 3.
ELSE IF (expdur >= 367 and expdur <= 731 and noayears <= 2).
COMPUTE dur = 4.
ELSE IF (expdur >= 367 and expdur <= 731 and noayears = 3).
COMPUTE dur = 5.
ELSE IF (expdur >= 732 and expdur <= 1096 and noayears <= 3).
COMPUTE dur = 6.
ELSE IF (expdur >= 732 and expdur <= 1096 and noayears = 4).
COMPUTE dur = 7.
ELSE IF (expdur >= 1097).
COMPUTE dur = 8.
END IF.
END IF.

VARIABLE LABELS dur 'category qualifications lie in'.

VALUE LABELS dur
-9 'missing'
1 'short'
2 '1year_1ay'
3 '1year_2ay'
4 '2year_2ay'
5 '2year_3ay'
6 '3year_3ay'
7 '3year_4ay'
8 '4year or more 4ay or more'.

** Code to split short quals into 'Short (< 5 wks)' and 'Short (5 - 24 wks)'.

COMPUTE shortdur =0.
IF (expdur >=1) AND (expdur <35) shortdur=1.
IF (expdur >=35) AND (expdur<=167) shortdur=2.

VALUE LABLES shortdur
0 'Long' 1 'Short (<5wks)' 2 'Short (5-24 wks)'.
exe.

*****************************************************************.
** Create groupings of learning aims.
*****************************************************************.

** Produce QTYPE2 goup.

RECODE mapqty
('0001'=3) ('0002'=1)('0003'=5) ('1414','1415','1416','1417','1418'=7) ('0017','0028'=10) ('0029','0030','0886'=10) ('0035'=7) ('0036'=15) ('0004','0005','0007'=17)
('0031','0032'=18)('1327'=30) ('1413'=31)(ELSE=19) INTO qtype2.

IF (SUBSTR(A09,1,5)='X9000') qtype2=20.
IF (A09 = '55555555' or A09 = '66666666') qtype2 = 20.
IF (A09 = '77777777' or A09 = '88888888') qtype2 = 20.
IF (A09 = '99999000' or A09 = '99999111') qtype2 = 20.
IF (A09 = '99999222' or A09 = '99999333') qtype2 = 20.
IF (A09 = '99999999') qtype2 = 20.

IF (SUBSTR(A09,1,4)='X9CE') qtype2=20.
IF (SUBSTR(A09,1,4)='X9CN') qtype2=20.
IF (SUBSTR(A09,1,5)='X9G01') qtype2=2.
IF (SUBSTR(A09,1,5)='X9G02') qtype2=4.
IF (SUBSTR(A09,1,5)='X9G03' OR SUBSTR(A09,1,4)='X9G3') qtype2=6.
IF (SUBSTR(A09,1,4)='X9GQ') qtype2=8.
IF (SUBSTR(A09,1,4)='X9NQ') qtype2=16.
IF (SUBSTR(A09,1,4)='X9VQ') qtype2=9.

VARIABLE LABELS qtype2 'reduced type of qual'.

VALUE LABELS qtype2
1 'A'
2 'A generic'
3 'AS'
4 'AS generic'
5 'GCSE'
6 'GCSE generic'
7 'GNVQ and AVCE'
8 'GNVQ generic'
9 'GNVQ/NVQ units'
10 'GNVQ precursors'
15 'NVQ'
16 'NVQ generic'
17 'Access'
18 'HND/HNC'
19 'Other'
20 ' Other generic'
30 'Key Skills'
31 'A2'.

** Compute a field to identify the spreadsheet on which each qualification will appear in the benchmarking data qualification level data.

COMPUTE ssheet=0.
DO IF ((qtype2 >=1 and qtype2 <=6) or qtype2=31).
COMPUTE ssheet=1.
ELSE IF (qtype2 >= 7 and qtype2 <= 10).
COMPUTE ssheet=2.
ELSE IF (qtype2=15 or qtype2=16).
COMPUTE ssheet=3.
ELSE IF (qtype2 >= 17 and dur=1).
COMPUTE ssheet=4.
ELSE.
COMPUTE ssheet=5.
END IF.

VARIABLE LABELS ssheet 'spreadsheet number'.

VALUE LABELS ssheet
1 'A AS and GCSEs including A2'
2 'GNVQ and GNVQ pre'
3 'NVQs'
4 'Other short'
5 'Other not short'.

** Recode qtype2 into qtype3 for the summaries. This is a slightly aggregated version of qtype2, and even broader qualifications.

RECODE qtype2 (2=1) (4=3) (6=5) (8=7) (16=15) (ELSE=COPY) into qtype3.

IF (qtype2 = 17 and ssheet = 4) qtype3 = 21.
IF (qtype2 = 18 and ssheet = 4) qtype3 = 22.
IF (qtype2 = 19 and ssheet = 4) qtype3 = 23.
IF (qtype2 = 20 and ssheet = 4) qtype3 = 24.
IF (qtype2 = 17 and ssheet = 5) qtype3 = 25.
IF (qtype2 = 18 and ssheet = 5) qtype3 = 26.
IF (qtype2 = 19 and ssheet = 5) qtype3 = 27.
IF (qtype2 = 20 and ssheet = 5) qtype3 = 28.
IF (qtype2 = 30 and ssheet = 4) qtype3 = 29.
IF (qtype2 = 30 and ssheet = 5) qtype3 = 30.

VARIABLE LABELS qtype3 'reduced version of qtype2 with ss for other'.

VALUE LABELS qtype3
1 'A level inc generic'
3 'AS level inc generic'
5 'GCSE inc generic'
7 'GNVQ/AVCE inc generic'
9 'GNVQ/NVQ units'
10 'GNVQ precursors'
15 'NVQs inc generic'
21 'Access short'
22 'HND/HNC short'
23 'Other short'
24 ' Other generic short'
25 'Access long'
26 'HND/HNC long'
27 'Other long'
28 ' Other generic long'
29 'Key Skills short'
30 'Key Skills long'
31 'A2 quals'.

** Create a high level group for use in SFR that combines length with notional level - area1.

RECODE qtype2 (18="H") INTO map_nl.
RECODE map_nl ("E"="1")("4"="H")("5"="H")("M"="X")("U"="X")(" "="X")(else=copy).
RECODE map_ssa1 (" "="U")(ELSE =COPY).

COMPUTE area1 = 0.
IF (qtype2 = 30) area1 =7.
IF (dur = 1 AND NOT (map_nl = "X") AND NOT (qtype2=30)) area1 = 6.
IF (dur > 1 AND map_nl = "1" AND NOT (qtype2=30)) area1 = 1.
IF (dur > 1 AND map_nl = "2" AND NOT (qtype2=30)) area1 = 2.
IF (dur > 1 AND map_nl = "3" AND NOT (qtype2=30)) area1 = 3.
IF (dur > 1 AND map_nl = "H" AND NOT (qtype2=30)) area1 = 4.
IF (dur > 1 AND map_nl = "X" AND NOT (qtype2=30)) area1 = 5.

VARIABLE LABELS area1 'high level aggregation of learning aims'.
VALUE LABELS area1
0 'Short nl X'
1 'Long nl 1'
2 'Long nl 2'
3 'Long nl 3'
4 'Long nl H'
5 'Long nl X'
6 'Short nl 123H'
7 'Key Skills'.

**Calculate a more detailed version of Area variable which shows long and short broad qualification type duration for supporting data tables - area2.

COMPUTE area2 = 0.
IF (dur > 1 AND ssheet = 2 AND map_nl = "1") area2 = 1.
IF (dur > 1 AND ssheet = 3 AND map_nl = "1") area2 = 2.
IF (dur > 1 AND ssheet = 5 AND map_nl = "1" AND NOT (qtype2=30)) area2 = 3.
IF (dur > 1 AND ssheet = 1 AND map_nl = "2") area2 = 4.
IF (dur > 1 AND ssheet = 2 AND map_nl = "2") area2 = 5.
IF (dur > 1 AND ssheet = 3 AND map_nl = "2") area2 = 6.
IF (dur > 1 AND ssheet = 5 AND map_nl = "2" AND NOT (qtype2=30)) area2 = 7.
IF (dur > 1 AND ssheet = 1 AND map_nl = "3") area2 = 8.
IF (dur > 1 AND ssheet = 2 AND map_nl = "3") area2 = 9.
IF (dur > 1 AND ssheet = 3 AND map_nl = "3") area2 = 10.
IF (dur > 1 AND ssheet = 5 AND map_nl = "3" AND NOT (qtype2=30)) area2 = 11.
IF (dur > 1 AND ssheet = 2 AND map_nl = "H") area2 = 12.
IF (dur > 1 AND ssheet = 3 AND map_nl = "H") area2 = 13.
IF (dur > 1 AND ssheet = 5 AND map_nl = "H" AND NOT (qtype2=30)) area2 = 14.
IF (dur > 1 AND ssheet = 1 AND map_nl = "X") area2 = 15.
IF (dur > 1 AND ssheet = 2 AND map_nl = "X") area2 = 16.
IF (dur > 1 AND ssheet = 3 AND map_nl = "X") area2 = 17.
IF (dur > 1 AND ssheet = 5 AND map_nl = "X" AND NOT (qtype2=30)) area2 = 18.
IF (dur = 1 AND map_nl = "1"AND NOT (qtype2=30)) area2 = 19.
IF (dur = 1 AND map_nl = "2"AND NOT (qtype2=30)) area2 = 20.
IF (dur = 1 AND map_nl = "3"AND NOT (qtype2=30)) area2 = 21.
IF (dur = 1 AND map_nl = "H" AND NOT (qtype2=30)) area2 = 22.
IF (dur = 1 AND map_nl = "X" AND NOT (qtype2=30)) area2 = 23.
IF (qtype2 = 30) area2 = 24.

VARIABLE LABELS area2 'area of supporting data'.

VALUE LABELS area2
1 'Long GNVQ nl 1'
2 'Long nvq nl 1'
3 'Long other nl 1'
4 'Long GCSEs nl 2'
5 'Long GNVQ nl 2'
6 'Long NVQ nl 2'
7 'Long other nl 2'
8 'Long AAS (including A2) nl 3'
9 'Long GNVQ nl 3'
10 'Long NVQ nl 3'
11 'Long other nl 3'
12 'Long GNVQ nl H'
13 'Long NVQ nl H'
14 'Long other nl H'
15 'Long AASGCSE nl X'
16 'Long GNVQ nl X'
17 'Long NVQ nl X'
18 'Long other nl X'
19 'Short all quals nl 1'
20 'Short all quals nl 2'
21 'Short all quals nl 3'
22 'Short all quals nl H'
23 'Short all quals nl X'
24 'Key Skills'.
exe.

*****************************************************************.
** Create a series of derived variables - count, trans, achieve,
** complete, continue, known outcome, high grade, full level 2,
** full level 3.
*****************************************************************.

** Number of cases - count.

COMPUTE count = 1.

** Transfer to another learning aim - trans.

COMPUTE trans = 0.
IF (A34 = 4) trans = 1.
VARIABLE LABELS trans 'student has transferred onto another qual'.

** Learning aim has been achieved - achieved.

COMPUTE achieved = 0.
IF (A35 = 1) OR (A35=6) OR (A35=7) achieved = 1.
VARIABLE LABELS achieved 'qual is achieved'.

** Learning aim has been completed - complete.

COMPUTE complete = 0.
IF (A34 = 2) complete = 1.
VARIABLE LABELS complete 'qual is completed'.

** Learning aim is continuing - contin.

COMPUTE contin = 0.
IF (A34 = 1) contin = 1.

** Learning aim has a known outcome - compv.

COMPUTE compv = 0.
IF (A34 = 2) AND (A35 =1 OR A35 = 2 OR A35 = 3 OR A35 >= 6) compv = 1.
VARIABLE LABELS compv 'completed and valid outcome'.

** Reasons for unknown outomes.

COMPUTE exam = 0.
IF (A35 = 4) exam = 1.
VARIABLE LABELS exam 'exam taken but result not yet known'.

COMPUTE learn = 0.
IF (A35 = 5) learn = 1.
VARIABLE LABELS learn 'learning complete but exam not yet taken'.

** Indicator for learning aims starting before 1 November.

COMPUTE nov = 0.
IF (A27 <= DATE.DMY(1, 11, startyr )) nov = 1.
VARIABLE LABELS nov 'did qual start before 1 Nov'.

** Code to create high grade variable - for A levels this is A or B.
** for other quals, excluding national diplomas and certificates
** this is A,B or C.
** and for National Diplomas and Certificates
** this is DD, DM, MD or MM.

STRING grade (A8).
RECODE A36 (""="NA") ('A', '*', '*A', 'A*',
'AA', 'AB', 'AC', 'AD',
'AE', 'AF', 'AG', 'B', 'BB', 'BA', 'BC',
'BD', 'BE', 'BF', 'BG'='HIGH_AB')
('C', 'CA', 'CB', 'CC', 'CD', 'CE',
'CF', 'CG', 'ME', 'DS', 'DI', 'HI' = 'HIGH')
(ELSE=COPY) into grade

COMPUTE gradek = 1.
IF (grade = "NA") gradek = 0.
VARIABLE LABELS gradek 'qual has a known grade'.

* Create a variable to identify A A/S and A2 quals.

COMPUTE aa2as =0.
IF(qtype3 = 1 OR qtype3 =3 OR qtype3 =31
OR (mapqty ='1414') or (mapqty ='1415')
OR (mapqty ='1416') OR (mapqty ='1417')
OR (mapqty ='1418')) aa2as=1.

* create code to deal with BTEC National Cert (mapqty = 1424)
* where DD, DM, MD or MM are high grades.
* BTEC National Dip with three grade letters are converted
* to A36 = "HI" in the current year tri file code.

COMPUTE high = 0.
IF (achieved = 1 AND grade = "HIGH_AB" AND aa2as=1 ) high = 1.
IF (achieved =1 AND aa2as <> 1 AND grade ="HIGH") high=1.
IF (achieved =1 AND aa2as <> 1 AND grade = "HIGH_AB") high=1.
IF (achieved = 1 AND mapqty = "1424" AND (grade = "DD"
OR grade = "DM" OR grade = "MD" OR grade = "MM")) high = 1.
IF (achieved = 1 AND (mapqty = "1418" OR mapqty = "1415")
AND A36 = "DD") high =1.
VARIABLE LABELS high 'qual achieved and high grade'.

**Code to identify full level 2 quals.

COMPUTE full2 = 0.

*NVQ/ GNVQ & level 2.

IF (map_nl = "2" AND ANY(mapqty,"0035","0036")) full2=1.

*EDEXCEL First Diploma.

IF(mapqty = "0029" AND mapawb = "EDEXCEL") full2 = 1.

*OCR Diploma.

IF(mapqty = "0006" AND mapawb = "OCR" AND map_nl = "2") full2 = 1.

*EDEXCEL First Diploma (new syllabus).

IF(mapqty = "1423") full2 = 1.

*other VRQs that also count as a full L2.

IF(ANY(a09,'10010774','10011699','10013106','10013830',
'10015036','10015048','10015061',
'10015073','10017033','10017045','10017562','10017690',
'1000645X','10016478','10024748',
'10025984','10025996','10025972','10012461','10024839',
'10006473','10017513','10017525',
'10029230','10020153','10022156','10026277','10026289'))
full2 = 1.

*Further VRQs added after a second look at the list by DFES (includes horticulture).

IF (ANY(a09,'10012886','1001505X','1001665X')) fulL2 = 1.

** Additions to full level 3 marker imported via the hierarchy table.

IF (qtype2 = 17) full3 = "8".
IF (expendyr = 2002) full3 = "X".

SORT CASES BY a09.
SAVE OUTFILE = ‘Master_temp.sav'.

*****************************************************************.
** Code to merge in the Skills for Life and Basic Skills markers.
*****************************************************************.

** Pre-sort the basic skills lookup table.

GET FILE='Basic_Skills_lookup.sav'.
SORT CASES BY a09.
SAVE OUTFILE='Basic_Skills_lookup.sav'.

** Match basic skill variables into main file.

MATCH FILES
/FILE = 'Master_temp.sav'
/TABLE = 'Basic_Skills_lookup.sav'
/BY a09.

RECODE bs_partpn (SYSMIS = 0).
RECODE bs_flag (SYSMIS = 0).
RECODE bs_type (SYSMIS = 0).
RECODE bs_esol (SYSMIS = 0).

** Need to select specific a09s to identify GCSE Maths and English with grades D to G.
** a09s chosen from analytical LAD where qual type = 0003 and A_BSPAR = 1.

DO IF ANY(a09,"00109616","10000150","10001111","10001112","10001500",
"10002400","10004732","10019868","10020020","10019777",
"10020573","10019789","10020603") AND ANY(a36,"D","E","F","G").
COMPUTE bs_type = 9.
ELSE IF ANY(a09,"00204111","00255789","10011262","10024815","00221154",
"10023082","00258301","1000855X","1001102X","10011183",
"10011432","10011456","10011468","10011420") AND ANY(a36,"D","E","F","G").
COMPUTE bs_type = 10.
END IF.

VARIABLE LABELS
bs_partpn 'learning aims that meet criteria for Skills for Life'
bs_flag 'Skills for Life learning aims that contribute towards target'
bs_type 'Learning aim category'
bs_esol 'Skills for Life learning aims that are also ESOL'.

VALUE LABELS bs_partpn
0 'learning aim is not part of Skills for Life'
1 'learning aim is part of Skills for Life'.

VALUE LABELS bs_flag
0 'learning aim does not contribute to the target'
1 'learning aim does contribute to the target'.

VALUE LABELS bs_type
0 'Not a basic skills learning aim'
1 'Literacy, Entry Level, Counting towards target'
2 'Numeracy, Entry Level, Counting towards target'
3 'Language, Entry Level, Counting towards target'
4 'Literacy, Level 1, Counting towards target'
5 'Numeracy, Level 1, Counting towards target'
6 'Language, Level 1, Counting towards target'
7 'Key Skills - Communication, Level 1'
8 'Key Skills - Application of Number, Level 1'
9 'GCSE English, Level 1 (Grade D-G)'
10 'GCSE Math, Level 1 (Grade D-G)'
11 'Literacy, Level 2, Counting towards target'
12 'Numeracy, Level 2, Counting towards target'
13 'Language, Level 2, Counting towards target'
14 'Key Skills - Communication, Level 2'
15 'Key Skills - Application of Number, Level 2'
16 'GCSE English, Level 2 (Grade A*-C)'
17 'GCSE Math, Level 2 (Grade A*-C)'
18 'Literacy, Entry Level, Not counting towards target'
19 'Numeracy, Entry Level, Not counting towards target'
20 'Language, Entry Level, Not counting towards target'
21 'Literacy, Level 1, Not counting towards target'
22 'Numeracy, Level 1, Not counting towards target'
23 'Language, Level 1, Not counting towards target'
24 Literacy, Level 2, Not counting towards target'
25 Numeracy, Level 2, Not counting towards target'
26 'Language, Level 2, Not counting towards target'.

VALUE LABELS bs_esol
0 'learning aim is not ESOL'
1 'learning aim is ESOL'.

*****************************************************************.
** Code to identify learner cohorts without aggregating the file
** and thus loosing the learner and learning aim reference.
*****************************************************************.

** Code to eliminate any missing areas of learning.

RECODE map_aol (SYSMIS = 98) (ELSE = COPY).

**Sort cases in preparation.

SORT CASES BY l01, ssheet, qtype2, qtype3, mapcode, map_aol, mapawb, maptitle, map_nl, dur, expendyr, agegroup, ufi, area1, area2, map_ssa1.

COMPUTE caseno = $casenum.

** Need to identify members of the same cohort. Having pre-sorted the variables used to define a cohort, the.
** variable CHANGE is used to identify where any leaner is in a different cohort from the learner in the row above.
** This is achieved by setting the change variable to 0 for all cases and then the IF command sets the change variable to 1 .
** where the learner is in the same cohort as the case in the row above.
** Therefore cases where change = 0 are the first case of the new cohort.

COMPUTE change =0.
IF (caseno = 1) change = 0.
IF (l01 = LAG(l01,1)
AND ssheet = LAG(ssheet,1)
AND qtype2 = LAG(qtype2,1)
AND qtype3 = LAG(qtype3,1)
AND mapcode = LAG(mapcode,1)
AND map_aol = LAG(map_aol,1)
AND mapawb = LAG(mapawb,1)
AND maptitle = LAG(maptitle,1)
AND map_nl = LAG(map_nl,1)
AND dur = LAG(dur,1)
AND expendyr = LAG(expendyr,1)
AND agegroup = LAG(agegroup,1)
AND ufi = LAG(ufi,1)
AND area1 = LAG(area1,1)
AND area2 = LAG(area2,1)
AND map_ssa1 = LAG(map_ssa1,1)) change = 1.

** The COHORT variable sequentially numbers each cohort. The start of a new cohort is identified by cases where.
** change = 0. Because this piece of code uses the LAG command, there are problems with the first row. By using 'caseno'
** it is possible to force the correct value in the first case - all other cases work from the LAG command without problem.

COMPUTE cohort = 0.
IF (caseno = 1) cohort = 1.
IF (caseno > 1 AND change = 0) cohort = LAG(cohort,1) + 1.
IF (caseno > 1 AND change = 1) cohort = LAG(cohort,1).

SAVE OUTFILE = 'Cohort_temp.sav'
/COMPRESSED.

** It is now necessary to find the number of concil funded learners in each cohort - this is achieved by the AGGREGATE command.
** The resulting two column table is saved in a new file.

AGGREGATE OUTFILE = 'fundstat_agg.sav'
/BREAK cohort
/sum_fund = SUM(fundstat).

** The code re-opens the cohort temp file saved two commands earlier and matches in for each case the number of council funded learners in their cohort.

GET FILE = 'Cohort_temp.sav'.

MATCH FILES / FILE = *
/TABLE ='fundstat_agg.sav'
/BY cohort.

** Remove cohorts with no council funded learners.

SELECT IF (sum_fund <> 0).

SAVE OUTILE = 'Pre-agg.sav'
/DROP=L11.

Aggregate to create aim master file

****************************************************************.
** Aggregate to create qual master file.
*****************************************************************.

** Pre-sort the inst lookup file.

GET FILE='Institution_lookup.sav'.
SORT CASES BY l01.
SAVE OUTFILE='Institution_lookup.sav'.

* Aggregate pre-agg file before matching in inst details.

GET FILE = 'Pre-agg.sav'.
SORT CASES BY l01 ssheet qtype2 qtype3 mapcode map_aol mapawb maptitle map_nl dur expendyr agegroup ufi area1 area2 map_ssa1 full2 full3 shortdur .

AGGREGATE OUTFILE=*
/PRESORTED
/BREAK= l01 ssheet qtype2 qtype3 mapcode map_aol mapawb maptitle map_nl dur expendyr agegroup ufi area1 area2 map_ssa1 full2 full3 shortdur
/count = sum(count)
/nov = sum(nov)
/fundstat = sum(fundstat)
/trans=sum(trans)
/contin=sum(contin)
/complete = sum(complete)
/achieved = sum(achieved)
/gradek=sum(gradek)
/high = sum(high)
/compv=sum(compv)
/exam=sum(exam)
/learn=sum(learn)
/expglh=sum(A_EXP_A)
/expmatch=sum(expmatch)
/didmatch=sum(didmatch).

SORT CASES BY l01.

* Match in inst details.

MATCH FILES
/FILE=*
/TABLE='Institution_lookup.sav'
/BY l01.

SAVE OUTFILE='Aim_master.sav'
/COMPRESSED.

****************************************************************.
** Aggregate to create demographic master file.
*****************************************************************.

GET FILE = 'Pre-agg.sav'.

*Remove key skills and ufi qualifications and make sure any missing blank disability lines are coded as unknown.

SELECT IF (area1 <> 7 and ufi = 0).
RECODE L14 (MISSING = 9) (else = copy).

*Recode ethnicity to avoid duplicate categories.

STRING ethncity (A20).
RECODE L12 (11,01 = "Bangladeshi") (12,06 = "Indian") (13, 07 = "Pakistani") (14, 09 = "Other Asian") (15, 02 = "Black African")
(16, 03 = "Black Caribbean") (17, 04 = "Black Other") (18, 05 = "Chinese") (19, 20, 21, 22 = "Mixed") (08, 23, 24, 25 = "White")
(98 = "Any other") (99 = "Unknown") (else = "Unknown") INTO ethncity.

* Use shortdur variable to show if qual is short or long.

SORT CASES by L01, area1, shortdur, expendyr, agegroup, L13, L14, ethncity.
AGGREGATE OUTFILE=*
/PRESORTED
/BREAK=L01, area1, shortdur, expendyr, agegroup, L13, L14, ethncity
/count = sum(count)
/trans=sum(trans)
/contin=sum(contin)
/complete = sum(complete)
/achieved = sum(achieved)
/compv=sum(compv).

*Match in full institution names and LLSC details.

SORT CASES BY l01.
MATCH FILES
/FILE=*
/TABLE='Institution_lookup.sav'
/BY l01.

SAVE OUTFILE=’Demographic_master.sav'
/COMPRESSED.

Creator

Analysis and MI Team

Date issued

16 January 2006

Date created

1 November 2005

Document ref.

\\records.lsc.local\NAT\23 LrngSkillsPolicyInfrastr\23-07 DataCollectAlysis\23-07-03 LrnrDataAlysisDiss\nat-fesuccessratemethodology200405samplecode-report-16jan2006.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/