In Year SLN Payment Data Definition 2009/2010

A_IY_SLN_PAYMENT, P_IY_SLN_PAYMENT, L_IY_SLN_PAYMENT

DATA DEFINITION

1. In year SLN payment at learner, aim or programme level.

PURPOSE

2. To identify whether the aim, programme or learner generates a Standard Learner Number (SLN) or receives an in year payment up to the current period.

RELEVANT COLLECTIONS

ILR (LR)
ILR (ER)

SOURCE DATA

3. The following variables are used as source data for the calculation of Year To Date SLN Payment data definition.

Field Name	Label	Dataset
A_TOTAL_SLN_Y2D	A_TOTAL_SLN_Y2D SLN year to date	LR Aims DLF Standard File
A_TOTAL_PAYMENT_Y2D	A_TOTAL_PAYMENT_Y2D Payment Year to Date	LR Aims DLF Standard File
L_ACTIVE	L_ACTIVE Learner Active for Current Academic year	Aims Standard File, Learner Standard File

DERIVED VARIABLES AND DATASETS

4. The definition produces the following derived variable(s)

Field Name	Label	Dataset
A_IY_SLN_PAYMENT	A_IY_SLN_PAYMENT Does the Aim generate SLN or receive an In Year Payment, up to the current period	Aims Standard File
P_IY_SLN_PAYMENT	P_IY_SLN_PAYMENT Programme generates SLN or Receives and In Year Payment up to the Current Period	Aims Standard File
L_IY_SLN_PAYMENT	L_IY_SLN_PAYMENT Learner Received SLN or Payment This Year To Date	Aims and Learner Standard File

VALUES

5. The table below outlines the categories for A_IY_SLN_PAYMENT, P_IY_SLN_PAYMENT and L_IY_SLN_PAYMENT

Value	Label
0	No
1	Yes
-1	Not Applicable/Not Known*

*Applies to system missing values and where there are non-active aims. Only applicable at programme level and Learner Level

DETAILED DEFINITION

Aim Level
6. The Aim level in year to date Standard Learner Number payment is matched in from the Employer Responsive Demand Led Funding dataset.

Programme Level
7. Programme level (P_IY_SLN_PAYMENT) is the highest value of does the aim receive an in year payment (A_IY_SLN_PAYMENT) for all of the learner’s aims within a programme.

Learner Level
8. Learner level (L_IY_SLN_PAYMENT) is the highest value of does the aim receive an in year payment (A_IY_SLN_PAYMENT) for all of the learner’s aims.

In Year SLN Payment Sample Code

1. The following SPSS code is provided to illustrate the In Year SLN Payment data definition.

A_IY_SLN_PAYMENT, P_IY_SLN_PAYMENT, L_IY_SLN_PAYMENT

2. The following tables show the steps required to derive A_IY_SLN_PAYMENT, P_IY_SLN_PAYMENT, L_IY_SLN_PAYMENT

A_IY_SLN_PAYMENT

Step	Condition	Action/Action if true
1	Any	Set P_IY_SLN_PAYMENT = 0
2	If (A_TOTAL_SLN_Y2D > 0) or (A_TOTAL_PAYMENT_Y2D > 0)	Set A_IY_SLN_PAYMENT = 1 in Aims File

P_IY_SLN_PAYMENT

Step	Condition	Action/Action if true
1	(Aggregate) For all aims where the learner (L03), Provider (L01), Programme Type (A15) and Framework Code (A26) are the same.	Set P_IY_SLN_PAYMENT to the maximum value of A_IY_SLN_PAYMENT.

L_IY_SLN_PAYMENT

Step	Condition	Action/Action if true
1	(Aggregate) For all aims where the learner (L03) and Provider (L01) are the same.	Set L_IY_SLN_PAYMENT to the maximum value of A_IY_SLN_PAYMENT.
2	Does L_ACTIVE = 0	Set L_IY_SLN_PAYMENT to -1

3. The following SPSS code illustrates how to derive A_IY_SLN_PAYMENT, P_IY_SLN_PAYMENT and L_IY_SLN_PAYMENT.

GET FILE = …………Aims Data set.
SORT CASES BY L01 L03 A15 A26 A05.

            MATCH FILES

             /FILE = *

             /TABLE = ….. ER Aims DLF Data set

             /BY L01 L03 A15 A26 A05.

RECODE A_IY_SLN_PAYMENT (SYSMIS=0).

            *Aggregate to programme level taking the maximum in year SLN payment value.

            SORT CASES L01 L03 A15 A26.

            AGGREGATE 

               /OUTFILE = * 

               MODE=ADDVARIABLES

              /PRESORTED

              /BREAK = L01 L03 A15 A26

              /P_IY_SLN_PAYMENT= max(A_IY_SLN_PAYMENT).

            VALUE LABELS A_IY_SLN_PAYMENT

              0 'No’

              1 'Yes’
*Aggregate to Learner level taking the maximum in year SLN payment value.

            SORT CASES L01 L03.

            AGGREGATE

               /OUTFILE = * 

              /PRESORTED

              /BREAK = L01 L03

              /L_IY_SLN_PAYMENT= max(A_IY_SLN_PAYMENT).

4. The following SQL code illustrates how to derive A_IY_SLN_PAYMENT:

select L01, L03, A05 , case when A_TOTAL_SLN_Y2D>0 or A_TOTAL_PAYMENT_Y2D>0 then 1 else 0 end as A_IY_SLN_PAYMENT from dbo.ILR0910_E_AIMS_DLF

select

L01, L03, A05
, case when A_TOTAL_SLN_Y2D>0 or A_TOTAL_PAYMENT_Y2D>0 then 1 else 0 end as A_IY_SLN_PAYMENT

from

dbo.ILR0910_E_AIMS_DLF

5. The following SQL code illustrates how to derive P_IY_SLN_PAYMENT:

select L01, L03, A15, A26 , max(A_IY_SLN_PAYMENT) as P_IY_SLN_PAYMENT from ILR0910_E_AIMS_DLF group by L01, L03, A15, A26 order by L01, L03, A15, A26

select

L01, L03, A15, A26
, max(A_IY_SLN_PAYMENT) as P_IY_SLN_PAYMENT

from

ILR0910_E_AIMS_DLF

group by

L01, L03, A15, A26

order by

L01, L03, A15, A26

6. The following SQL code illustrates how to derive L_IY_SLN_PAYMENT:

select B.L01, B.L03 , case when L_IY_SLN_PAYMENT_qry is NULL then -1 else L_IY_SLN_PAYMENT_qry end as L_IY_SLN_PAYMENT from ( select L01, L03, count(A05) as aims , max(A_IY_SLN_PAYMENT) as L_IY_SLN_PAYMENT_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

select

     B.L01, B.L03
     , case when L_IY_SLN_PAYMENT_qry is NULL then -1
     else L_IY_SLN_PAYMENT_qry end as L_IY_SLN_PAYMENT

from
(
select

L01, L03, count(A05) as aims
, max(A_IY_SLN_PAYMENT) as L_IY_SLN_PAYMENT_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: 02nd December 2009