Sources of Funding Data Definition 2009/2010

Set A_FUNDINGSOURCE=

Set A_FUNDINGSOURCE =2.

Set A_FUNDINGSOURCE =3.

Set A_FUNDINGSOURCE =0.

GET

FILE=….aims file

/* Create A_FUNDING SOURCE variable and assign funding stream values to the aims. Note the A_ACTIVE check is not /*applied to DLF funded aim as it is possible for an inactive aim to generate funding.

COMPUTE A_FUNDINGSOURCE=-1.

IF A_to_date_SLN_PAYMENT = 1 A_FUNDINGSOURCE = 1.

IF Any(A10,80,10) A_FUNDINGSOURCE=2.

IF A10=70 A_FUNDINGSOURCE=3.

IF A10=99 A_FUNDINGSOURCE=9.

IF (Any(A10,21,22,45,46) AND A_to_date_SLN_Payment = 0) A_FUNDINGSOURCE=0.

VARIABLE LABELS A_FUNDINGSOURCE ‘A_FUNDINGSOURCE Sources of Funding for the Aim’.

VALUE LABELS A_FUNDINGSOURCE

9 ‘No LSC Funding’

0 ‘LSC DLF but generates no Funding’

1 ‘LSC DLF Funding’

2 ‘LSC Non Formula Funding’

3 ‘ESF Co-Financing’

select 

      L01,  L03,  A05
      , case when A_TO_DATE_SLN_PAYMENT = 1 then 1
      when A10 in (10, 80) then 2
      when  A10 = 70 then 3
      when  (A10 in (21 , 22 , 45 , 46) and (A_TO_DATE_SLN_PAYMENT = 0)) then 0
      when A10 = 99 then 9
      else -1 end as A_FUNDINGSOURCE

from

     dbo.ILR0910_E_AIMS

Set LSCDLF=1
Go to 2

Set OTHLSC=1.
Go to 3

Set ESF=1.
Go to 4

Set NOLSC=1.
Go to 5

Set NODLF=1.
Go to 6

DLFLSCS is the maximum value of all of the aims in DLFLSC.

OTHLSCS is the maximum value of all of the aims in OTHLSC.

ESFS is the maximum value of all of the aims in ESFS.

NOLSCS is the maximum value of all of the aims in NOLSC.

NODLFS is the maximum value of all of the aims in NODLF.

(for above variables aim with highest data set sequence (A04) is taken)

Create a Numeric Variable TOTAL = Sum of DLFLSCS OTHLSCS, ESFS

Create Numeric Variable

L_FUNDINGSOURCE = -1.

GET

FILE=….aims file

/* Create four variables one for each of the funding streams.

IF A_FUNDINGSOURCE=1 DLFLSC = 1.

IF A_FUNDINGSOURCE=2 OTHLSC=1.

IF A_FUNDINGSOURCE=3 ESF=1.

IF A_FUNDINGSOURCE=9 NOLSC=1.

IF A_FUNDINGSOURCE=0 NODLF=1.

/* If the aims aren’t active set the new variables OTHLSC to NO DLF to zero, so that they don’t count in the aggregation for analytical purposes.

/*The recode is not applied to DLFLSC, so that active/inactive aims are included in the calculation at learner level.

DO IF A_ACTIVE=0.

RECODE OTHLSC TO NODLF (SYSMIS=0), (ELSE=0).

END IF.

SORT CASES L01 L03 A05.

/* Aggregate to learner level so that there is only one value per funding stream per learner

AGGREGATE OUTFILE =*

MODE=ADDVAR

/PRESORTED

/BREAK=L01 L03

/DLFLSCS OTHLSCS ESFS NOLSCS NODLFS=MAX(DLFLSC OTHLSC ESF NOLSC NODLF).

/* Create a variable total which will indicate the number of different funding sources a learner has.

COMPUTE TOTAL=SUM (DLFLSCS TO ESFS).
RECODE TOTAL (SYSMIS=0).

COMPUTE L_FUNDINGSOURCE=-1.

IF TOTAL=0 AND NODLFS=1 L_FUNDINGSOURCE=0.

IF TOTAL=0 AND NOLSCS=1 L_FUNDINGSOURCE=9.

IF TOTAL=1 AND DLFLSCS=1 L_FUNDINGSOURCE=1.

IF TOTAL=1 AND OTHLSCS=1 L_FUNDINGSOURCE=5.

IF TOTAL = 1 AND ESFS=1 L_FUNDINGSOURCE = 7.

IF TOTAL=2 AND DLFLSCS+OTHLSCS=2 L_FUNDINGSOURCE=2.

IF TOTAL=2 AND DLFLSCS+ESFS=2 L_FUNDINGSOURCE=3.

IF TOTAL=3 L_FUNDINGSOURCE=4.

IF TOTAL=2 AND OTHLSCS+ESFS=2 L_FUNDINGSOURCE=6.

VARIABLE LABELS L_FUNDINGSOURCE ‘L_FUNDINGSOURCE Sources of Funding for the Learner’.

VALUE LABELS L_FUNDINGSOURCE

9 ‘No LSC Funding claimed for the Learner’

0 ‘LSC DLF but no Funding generated for the Learner’

1 ‘LSC DLF Funding only for the Learner’

2 ‘LSC DLF & LSC Non Formula Funding for the Learner’

3 ‘LSC DLF and ESF Co-financing Funding for the Learner’

4 ‘LSC DLF, LSC Non Formula and ESF Co-financing Funding for the Learner’

5 ‘LSC Non Formula Funding only for the Learner’

6 ‘LSC Non Formula & ESF Co-financing Funding for the Learner’

7 ‘ESF Co-financing funding only for the Learner’

SELECT
      B.L01 , B.L03 
      , case when TOTAL =0 and NOLSCS=1 then 9
      when TOTAL =1 and DLFLSCS=1 then 1
      when TOTAL =1 and OTHLSCS=1 then 5
      when TOTAL =1 and ESFS=1 then 7
      when TOTAL =0 and NODLFS=1 then 0
      when TOTAL =2 and DLFLSCS+OTHLSCS=2 then 2
      when TOTAL =2 and DLFLSCS+ESFS=2 then 3
      when TOTAL =2 and OTHLSCS+ESFS=2 then 6
      when TOTAL=3 then 4
      else -1 end as L_FUNDINGSOURCE

from
     (
     select
           L01 , L03, sum(aims) as aim_count , count (*) as learner_count
           , sum (DLFLSCS+OTHLSCS+ESFS) as TOTAL
           , max(DLFLSCS) as DLFLSCS, max(OTHLSCS) as OTHLSCS, max(ESFS) as ESFS, max(NOLSCS) as NOLSCS, max(NODLFS) as NODLFS

     from
          (
          select
                L01 , L03 , count (*) as aims
                , max(DLFLSC) as DLFLSCS, max(OTHLSC) as OTHLSCS, max(ESF) as ESFS, max(NOLSC) as NOLSCS, max(NODLF) as NODLFS

          from
               (
               select 
                     L01,  L03,  A05
                     , CASE WHEN A_TO_DATE_SLN_PAYMENT = 1 then 1 else 0 END AS DLFLSC,
                     CASE WHEN A_ACTIVE=1 and A10 in (10, 80) then 1
 when A_ACTIVE=0 then 0 else 0 END AS OTHLSC,
                     CASE WHEN A_ACTIVE=1 and A10 = 70 then 1
 when A_ACTIVE=0 then 0 else 0 END AS ESF,
                     CASE WHEN A_ACTIVE=1 and A10 = 99 then 1
 when A_ACTIVE=0 then 0 else 0 END AS NOLSC,
                     CASE WHEN A_ACTIVE=1 and A10 in (21, 22, 45, 46) and A_TO_DATE_SLN_PAYMENT = 0 then 1 
                     when A_ACTIVE=0 then 0 else 0 END AS NODLF

               from

                    dbo.ILR0910_L01_AIMS A

               )T

          group by

                L01 , L03

          )T2

          group by

                L01 , L03

     )T3

     right outer join dbo.ILR0910_L01_LEARNER B
          on T3.L01=B.L01 and T3.L03=B.L03

order by

      B.L01 , B.L03