GET FILE=’……….Aims dataset’.
SORT CASES A09.
MATCH FILES /FILE=*
 /TABLE=’……….Analytical LAD dataset’
 /BY A09.

/*set the notional level of certain aims according to their aim type, these were
/* included where the notional level was incorrect in historical LADs.
IF (A_LATYPE EQ ‘0017’ OR A_LATYPE EQ ‘0004’ OR A_LATYPE EQ ‘0005’ OR A_LATYPE EQ ‘0007’ OR A_LATYPE EQ ‘0030’) A_NOTION=’3’.
IF (A_LATYPE EQ ‘0028’OR A_LATYPE EQ ‘0029’) A_NOTION=’2’.
IF (A_NOTION=’X’ AND A_ENGLEV=’H’) A_NOTION=’H’.

/* RECODE a_notion as appropriate into A_NOTIONLEV.
RECODE A_NOTION (‘E’ = 0) (‘1’=1)(‘2’=2)(‘3’=3)(‘4’=4)(‘5’=5)(‘H’=6)(ELSE=9) INTO A_NOTIONLEV.

RECODE A_NOTIONLEV (SYSMIS=-1).

VARIABLE LABELS A_NOTIONLEV ‘A_NOTIONLEV Notional NVQ level of the aim’.
VALUE LABELS A_NOTIONLEV
 0 ‘Entry level’
 1 ‘Level 1’
 2 ‘Level 2’
 3 ‘Level 3’
 4 ‘Level 4’
 5 ‘Level 5’
 6 ‘Higher’
 9 ‘Other’.

select L01, L03, A05, A.A09,
  A_NOTIONLEV = case when A_NOTION='E' then 0
       when A_NOTION='1' then 1
       when A_LATYPE in ('0004','0005','0007','0017','0030') then 3
       when A_LATYPE in ('0028','0029') then 2
       when A_NOTION='2' then 2
       when A_NOTION='3' then 3
       when A_NOTION='4' then 4
       when A_NOTION='5' then 5
       when A_NOTION='H' then 6
       when A_NOTION='X' and A_ENGLEV='H'then 6
       else 9 end
from
     AimsTable A
     join
     LADTable B
     on A.A09=B.A09

/* Temporarily map ‘other’ to -0.5, as we want to take the maximum level and in this regard ‘other’ needs to be counted as the lowest level.
RECODE A_NOTIONLEV (9=-0.5).

/*Only look at aims that the Activity of the aim for Inclusion in Analysis is greater than 1.
SELECT IF A_ACTIVE = 1.
/*Take the maximum value at learner level.
AGGREGATE OUTFILE=’….L_NOTIONLEV.SAV’
 /BREAK=L01 L03
 /L_NOTIONLEV ‘L_NOTIONLEV Notional NVQ Level of Learner‘=MAX(A_NOTIONLEV).

GET FILE ‘…………Learner data set’.

SORT CASES L01 L03.
MATCH FILE
/FILE= *
/TABLE=’…… L_NOTIONLEV.SAV’
/BY L01 L03.

/*Reset the NOTIONLEV categories back to correct values by mapping ‘other’ back to 9.
RECODE L_NOTIONLEV (-0.5=9).
RECODE L_NOTIONLEV (SYSMIS=-1).

select

      B.L01, B.L03
      , case when L_NOTIONLEV_qry is NULL then -1
      when L_NOTIONLEV_qry=-0.5 then 9
      else L_NOTIONLEV_qry end as L_NOTIONLEV

from
     (
     select

           L01, L03, count(A05) as aims
           , max(A_NOTIONLEV) as L_NOTIONLEV_qry

     from
     (
          select

               L01, L03, A05, A_ACTIVE
                , case when A_NOTIONLEV=9 then -0.5 else A_NOTIONLEV end as A_NOTIONLEV

          from

                ILR0910_L01_AIMS

          )T1

          where A_ACTIVE=1

     group by

           L01, L03, A_ACTIVE
     )T

     right outer join

     ILR0910_L01_LEARNER B

           on T.L01=B.L01 and T.L03=B.L03

     order by

           B.L01, B.L03