Active on Census Data Definition 2009/2010
A_OCT , A_NOV, A_JAN, L_OCT, L_NOV, L_JAN
DATA DEFINITION
1. Flag indicating aim or learner active on current census date in this year.
PURPOSE
2. To identify aims and learners that were active on the analytical census dates (1 October, 1 November and 1 January). Note: these dates are for analytical purposes, and are not the funding census dates.
RELEVANT COLLECTIONS
- ILR (LR)
- ILE (ER)
- ILR (UfI)
- ILR (ASL)
- ILR (ESF SR)
SOURCE DATA
3. The following variables are used as source data for the calculation of Flag indicating aim or learner active on current census date in this year.
| Field Name |
Label |
Dataset |
| A27 |
A27 Learning Start Date |
Aims Standard File |
| A28 |
A28 Learning Planned End Date |
Aims Standard File |
| A31 |
A31 Learning Actual End Date |
Aims Standard File |
| A34 |
A34 Completion Status |
Aims Standard File |
| A_ACTIVE |
A_ACTIVE Aim Active for the current academic year |
Aims Standard File |
DERIVED VARIABLES AND DATASETS
4. The definition produces the following derived variable(s)
| Field Name |
Label |
Dataset |
| A_OCT |
A_OCT Aim Active on 1 October In This Year |
Aims Standard File |
| A_NOV |
A_NOV Aim Active on 1 November In This Year |
Aims Standard File |
| A_JAN |
A_JAN Aim Active on 1 January In This Year |
Aims Standard File |
| L_OCT |
L_OCT Learner Active on 1 October In This Year |
Learner Standard File, Aims Standard File |
| L_NOV |
L_NOV Learner Active on 1 November In This Year |
Learner Standard File |
| L_JAN |
L_JAN Learner Active on 1 January In This Year |
Learner Standard File |
VALUES
5. The table below outlines the categories for A_OCT , A_NOV, A_JAN, L_OCT, L_NOV, L_JAN
| Value |
Label |
| -1 |
Not Applicable/Not Known* |
| 0 |
No |
| 1 |
Yes |
*Applies to system missing values and where there are non-active aims. Only applicable at learner level.
DETAILED DEFINITION
6. If the Learning Start Date (A27) for the aim is on the census date, then the aim is active on the census date (e.g. for 1 October, A_OCT=1).
7. If the Learning Start Date (A27) for the aim is before the census date and the Learning Actual End Date (A31) is on or after the census date, then the aim is active on the census date.
8. If the Learning Start Date (A27) for the aim is before the census date and the Learning Planned End Date (A28) is on or after the census date and there is no Learning Actual End Date (A31), then the aim is active on the census date.
9. If the Learning Start Date (A27) for the aim is before the census date, and the Learning Planned End Date (A28) is before the start of the academic year (1 August), but there is no Learning Actual End Date (A31), then the aim is active on the census date.
10. If the Learning Start Date (A27) for the aim is before the census date, and there is no Learning Planned End Date (A28) and no Learning Actual End Date (A31) and the Completion Status (A34) is either missing or has a value of 1 (the learner is continuing or intending to continue the learning activities leading to the learning aim), then the aim is active on the census date.
11. To work out if a learner was active at the particular census points. We look at all the aims and if any of them are active on the current census date then the learner is marked as being active on that census date.
12. For FE, UfI and ACL we exclude those aims that are not included for analytical purposes (using A_ACTIVE).
Active on Census Date Sample Code
1. The following SPSS code is provided to illustrate the Active on Census Date data definition.
A_OCT
2. The following table shows the steps required to derive A_OCT
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Any |
Set A_OCT to 0 |
Go to 2 |
| 2 |
A27=1 October current academic year |
set A_OCT to 1 |
Go To 3 |
| 3 |
A27 < 1 October current academic year and A31>=1 October current academic year |
set A_OCT to 1 |
Go To 4 |
| 4 |
A27< 1 October current academic year and A28 >= 1 October current academic year and no date in A31 |
set A_OCT to 1 |
Go To 5 |
| 5 |
A27< 1 October current academic year and A28 < 1 August current academic year and no date in A31 |
set A_OCT to 1 |
Go To 6 |
| 6 |
A27< 1 October current academic year, no date in A28, no date in A31 and A34 is either missing or = 1 |
set A_OCT to 1 |
Go to 7 |
| 7 |
Does A_OCT have a value |
|
Set A_OCT to -1 |
3. The following SPSS code illustrates how to derive A_OCT.
|
COMPUTE A_OCT=0.
/* If course starts on 1/10, then A_OCT=1.
IF A27 = DATE.DMY(1,10,current academic year) A_OCT =1./* If course starts before 1/10 and finishes after 1/10, then A_OCT=1.
IF A27 < DATE.DMY(1,10,current academic year) AND a31 >= DATE.DMY(1,10,current academic year) A_OCT =1.
/* If course starts before 1/10 and is expected to finish after 1/10 OR
/* If the course has an expected date of before 1/8 but the actual
/* enddate is blank, then A_OCT=1.
IF A27 < DATE.DMY(1,10,current academic year) AND (A28 >= DATE.DMY(1,10,current academic year) OR A28 < DATE.DMY(1,8,current academic year) ) AND MISSING(A31) A_OCT =1.
/* If course starts before 1/10, there is no expected or actual enddate
/* and a34=1 or missing, then a_oct=1.
IF ( A27 < DATE.DMY(1,10,current academic year) AND MISSING(A31) AND MISSING(A28) AND (A34=1 OR MISSING(A34))) A_OCT =1.
VARIABLE LABELS A_OCT 'A_OCT Qualification Active on 1 October in this year'.
VALUE LABELS A_OCT
-1 ‘Not Applicable/Not Known'
0 'No'
1 'Yes’.
|
4. The following SQL code illustrates how to derive A_OCT:
SQL code to derive A_OCT
|
--use of case when clause to set values for A_OCT
select
L01, L03, A05
, case when A27='1 october 2009' then 1
when A27<'1 october 2009' and A31>='1 october 2009' then 1
when A27<'1 october 2009' and A28>='1 october 2009' and A31 is null then 1
when A27<'1 october 2009' and A28<'1 august 2009' and A31 is null then 1
when A27<'1 october 2009' and A28 is null and A31 is null and (A34 is null or A34=1) then 1
else 0 end as A_OCT
from
ILR0910_E_AIMS |
L_OCT
5. The following table shows the steps required to derive L_OCT
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does A_ACTIVE =1 |
Go to 3 |
Set L_OCT=-1 |
| 2 |
Is any A_OCT=1 |
Set L_OCT to 1 |
Set L_OCT to 0 |
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does L_OCT contain a value |
None |
Set L_OCT= -1 |
| 1 |
Does L_ACTIVE=0 |
Set L_OCT = -1 |
None |
6. The following SPSS code illustrates how to derive L_OCT.
|
/* sort cases according to variables to be used in aggregate.
SORT CASES L01 L03 A_OCT.
/* A temporary select is made on all learners who are active according to the October Census date.
/* Aggregation is to learner level to obtain the maximum of a_oct. i.e where it equals 1.
SELECT IF A_ACTIVE = 1.
AGGREGATE OUTFILE='....L_OCT.SAV'
/PRESORTED
/BREAK=L01 L03
/L_OCT 'L_OCT Learner was Active on 1 Oct in this year'=max(A_OCT).
GET FILE '........Learner data set'.
SORT CASES L01 L03.
MATCH FILE
/FILE= *
/TABLE=....L_OCT.SAVE'
/BY L01 L03.
*/Sets L_OCT to -1 where the aims are not Active
RECODE L_OCT (SYSMIS=-1). |
7. The following SQL code illustrates how to derive L_OCT:
|
select
B.L01, B.L03
, case when L_OCT_qry is NULL then -1
else L_OCT_qry end as L_OCT
from
(
select
L01, L03, count(A05) as aims
, max(A_OCT) as L_OCT_qry
from
ILR0910_L01_AIMS
where A_ACTIVE=1
group by
L01, L03
)T
right outer join
ILR0910_L01_LEARNER B
on T.L01=B.L01 and T.L03=B.L03
order by
B.L01, B.L03 |
A_NOV
8. The following table shows the steps required to derive A_NOV
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Any |
set A_NOV to 0 |
Go To 2 |
| 2 |
A27=1 November current academic year |
set A_NOV to 1 |
Go To 3 |
| 3 |
A27 < 1 November current academic year and A31>=1 November current academic year |
set A_NOV to 1 |
Go To 4 |
| 4 |
A27< 1 November current academic year and A28 >= 1 November current academic year and no date in A31 |
set A_NOV to 1 |
Go To 5 |
| 5 |
A27< 1 November current academic year and A28 < 1 August current academic year and no date in A31 |
set A_NOV to 1 |
|
| 6 |
A27< 1 November current academic year, no date in A28, no date in A31 and A34 is either missing or = 1 |
set A_NOV to 1 |
|
| 7 |
Does A_NOV have a value |
set A_NOV to -1 |
|
9. The following SPSS code illustrates how to derive A_NOV.
|
COMPUTE A_NOV=0.
/* If course starts on 1/11, then A_NOV=1.
IF A27 = DATE.DMY(1,11,current academic year) A_NOV =1.
/* If course starts before 1/11 and finishes after 1/11, then A_NOV=1.
IF A27 < DATE.DMY(1,11,current academic year) AND A31 >= DATE.DMY(1,11,current academic year) A_NOV =1.
/* If course starts before 1/11 and is expected to finish after 1/11 OR
/* If the course has an expected date of before 1/8 but the actual
/* enddate is blank, then A_NOV=1.
IF A27 < DATE.DMY(1,11,current academic year) AND (A28 >= DATE.DMY(1,11,current academic year) OR A28 < DATE.DMY(1,8,current academic year) ) AND MISSING(A31) A_NOV =1.
/* If course starts before 1/11, there is no expected or actual enddate
/* and A34=1 or missing, then A_NOV=1.
IF ( A27 < DATE.DMY(1,11,current academic year) AND MISSING(A31) AND MISSING(A28) AND (A34=1 OR MISSING(A34))) A_NOV =1.
VARIABLE LABELS A_NOV 'A_NOV Qualification Active on 1 November in this year'.
VALUE LABELS A_NOV
-1 ‘Not Applicable/Not Known'
0 'No'
1 'Yes’. |
10. The following SQL code illustrates how to derive A_NOV:
|
--use of case when clause to set values for A_NOV
select
L01, L03, A05
, case when A27='1 november 2009' then 1
when A27<'1 november 2009' and A31>='1 november 2009' then 1
when A27<'1 november 2009' and A28>='1 november 2009' and A31 is null then 1
when A27<'1 november 2009' and A28<'1 august 2009' and A31 is null then 1
when A27<'1 november 2009' and A28 is null and A31 is null and (A34 is null or A34=1) then 1
else 0 end as A_NOV
from
ILR0910_E_AIMS |
L_NOV
11. The following table shows the steps required to derive L_NOV
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Is A_ACTIVE=1 |
Go to 3 |
Set L_NOV=-1 |
| 2 |
Is any A_NOV=1 |
Set L_NOV to 1 |
Set L_NOV to 0 |
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does L_NOV contain a value |
None |
Set L_NOV = -1 |
| 2 |
Does L_ACTIVE=0 |
Set L_NOV = -1 |
None |
12. The following SPSS code illustrates how to derive L_NOV.
|
SORT CASES L01 L03 A_NOV.
/* A temporary select is made on all learners who are active according to the November Census date.
/* Aggregation is to learner level to obtain the maximum of a_Nov. i.e where it equals 1.
SELECT IF A_ACTIVE = 1.
AGGREGATE OUTFILE='...L_NOV.SAV'
/PRESORTED
/BREAK=L01 L03
/L_NOV 'L_NOV Learner was active on 1 Nov in this year'=MAX(A_NOV).
GET FILE '........Learner data set'.
SORT CASES L01 L03
MATCH FILE
/FILE= *
/TABLE= '.....L_NOV.SAV'
/BY L01 L03.
*/Sets L_NOV to -1 where the aims are not Active.
RECODE L_NOV (SYSMIS=-1). |
13. The following SQL code illustrates how to derive L_NOV:
|
select
B.L01, B.L03
, case when L_NOV_qry is NULL then -1
else L_NOV_qry end as L_NOV
from
(
select
L01, L03, count(A05) as aims
, max(A_NOV) as L_NOV_qry
from
ILR0910_L01_AIMS
where A_ACTIVE=1
group by
L01, L03
)T
right outer join
ILR0910_L01_LEARNER B
on T.L01=B.L01 and T.L03=B.L03
order by
B.L01, B.L03 |
A_JAN
14. The following table shows the steps required to derive A_JAN
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Any |
set A_JAN to 0 |
|
| 2 |
A27=1 January current academic year + 1 |
set A_JAN to 1 |
Go To 3 |
| 3 |
A27 < 1 January current academic year + 1 and A31>=1 January current academic year + 1 |
set A_JAN to 1 |
Go To 4 |
| 4 |
A27< 1 January current academic year + 1 and A28 >= 1 January current academic year + 1 and no date in A31 |
set A_JAN to 1 |
Go To 5 |
| 5 |
A27< 1 January current academic year + 1 and A28 < 1 August current academic year and no date in A31 |
set A_JAN to 1 |
Go To 6 |
| 6 |
A27< 1 January current academic year, no date in A28, no date in A31 and A34 is either missing or = 1 |
set A_JAN to 1 |
|
| 7 |
Does A_JAN have a value |
None |
Set A_JAN to -1 |
15. The following SPSS code illustrates how to derive A_JAN
|
COMPUTE A_JAN=0.
/* If course starts on 1/1, then A_JAN=1.
IF A27 = DATE.DMY(1,1, current academic year+ 1) A_JAN =1.
/* If course starts before 1/1 and finishes after 1/1, then A_JAN=1.
IF A27 < DATE.DMY(1,1, current academic year+ 1) AND a31 >= DATE.DMY(1,1, current academic year+ 1) A_JAN =1.
/* If course starts before 1/1 and is expected to finish after 1/1 OR
/* If the course has an expected date of before 1/8 but the actual
/* enddate is blank, then A_JAN=1.
IF A27 < DATE.DMY(1,1, current academic year+ 1) AND (A28 >= DATE.DMY(1,1, current academic year) OR A28 < DATE.DMY(1,8, current academic year) ) AND MISSING(A31) A_JAN =1.
/* If course starts before 1/1, there is no expected or actual enddate
/* and a34=1 or missing, then A_JAN=1.
IF ( A27 < DATE.DMY(1,1, current academic year+ 1) AND MISSING(A31) AND MISSING(A28) AND (A34=1 OR MISSING(A34))) A_JAN =1.
VARIABLE LABELS A_JAN 'A_JAN Qualification active on 1 January in this year'.
VALUE LABELS A_JAN
-1 ‘Not Applicable/Not Known'
0 'No'
1 'Yes’.
|
16. The following SQL code illustrates how to derive A_JAN:
|
--use of case when clause to set values for A_JAN
select
L01, L03, A05
, case when A27='1 january 2010' then 1
when A27<'1 january 2010' and A31>='1 january 2010' then 1
when A27<'1 january 2010' and A28>='1 january 2010' and A31 is null then 1
when A27<'1 january 2010' and A28<'1 august 2009' and A31 is null then 1
when A27<'1 january 2010' and A28 is null and A31 is null and (A34 is null or A34=1) then 1
else 0 end as A_JAN
from
ILR0910_E_AIMS |
L_JAN
17. The following table shows the steps required to derive L_JAN
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does A_ACTIVE=1 |
Go to 2 |
Set L_JAN=-1. |
| 2 |
Is any A_JAN=1 |
Set L_JAN to 1 |
Set L_JAN to 0 |
| Step |
Condition |
Action/Action if true |
Action if false |
| 1 |
Does L_JAN contain a value |
None |
Set L_JAN = -1 |
| 2 |
Does L_ACTIVE=0 |
Set L_JAN=-1 |
None |
18. The following SPSS code illustrates how to derive L_JAN.
|
/* sort cases according to variables to be used in aggregate.
SORT CASES L01 L03 A_JAN.
/* A temporary select is made on all learners who are active according to the January Census date.
/* Aggregation is to learner level to obtain the maximum of a_Jan. i.e where it equals 1.
SELECT IF A_ACTIVE = 1.
AGGREGATE OUTFILE='....L_JAN.SAV'
/PRESORTED
/BREAK=L01 L03
/L_JAN 'L_JAN Learner was active on 1 Jan in this year'=MAX(A_JAN).
GET FILE '.......Learner data set'.
SORT CASES L01 L03
MATCH FILE
/FILE= *
/TABLE='....L_JAN.SAV'
/BY L01 L03
*/Sets L_JAN to -1 where the aims are not Active.
RECODE L_JAN (SYSMIS=-1). |
19. The following SQL code illustrates how to derive L_JAN:
|
select
B.L01, B.L03
, case when L_JAN_qry is NULL then -1
else L_JAN_qry end as L_JAN
from
(
select
L01, L03, count(A05) as aims
, max(A_JAN) as L_JAN_qry
from
ILR0910_L01_AIMS
where A_ACTIVE=1
group by
L01, L03
)T
right outer join
ILR0910_L01_LEARNER B
on T.L01=B.L01 and T.L03=B.L03
order by
B.L01, B.L03 |
Date last modified: 01st December 2009
