Transcript SPSS TALK

A tale of UCAS and SPSS:
taking today’s data from an
intricate database and producing
comprehensible managementfriendly comparative statistics by
10 a.m.
Charles Lee
Sheila Buckthorpe
Mathematics, University of Bolton
Abstract
UCAS data is fed to and from universities as a series of transactions for
each student, with a typical student having 15 to 20 transactions over
the year. At any date in the applications cycle it is useful to be able to
show how this year’s applications compare to last year’s, for both a
whole university and for individual courses. SPSS is an ideal tool for
this. Particularly valuable are its good data capture facilities, the ability
to program in syntax files, and the ease with which comprehensible
graphs and tables can be constructed. In addition, the ability to build a
syntax file to perform the entire task makes it feasible to produce
complex reports from data which is only an hour or two old.
Included in this talk will be some of the data manipulation features of
SPSS which enable strange and wonderful things to be done with very
messy data.
Home, overseas and part time
undergraduates:
unconditional firm acceptances
for Sept 07 and Sept 08
Home ft
2007/8
Home ft
2008/9
fte
375.0
fte
327.5
Business School
88.0
Built Environment & Eng
overseas
2007/8
overseas
2008/9
fte
fte
part time
2007/8
part time
2008/9
3.5
1.0
fte
53.3
83.0
14.0
48.0
4.0
14.0
90.0
148.0
23.0
53.0
65.3
70.7
Games Comp & Creative Tech & BLIS
222.0
226.0
11.0
28.0
1.3
2.0
Health & Social Sciences
303.0
280.5
8.5
6.0
72.0
68.0
1078.0
1065.0
60.0
136.0
196.0
220.0
Arts, Media & Education
Whole University
fte
65.3
September 2007 and September 2008
Undergraduate Unconditional Firm Acceptances
As at 21st August
Home ft
2007/8
Home ft
2008/9
fte
Arch Tech
3.0
fte
10.0
HNC/D Building
4.0
Bld Surv & Prop Mgt
overseas
2007/8
overseas
2008/9
part time
2007/8
part time
2008/9
fte
fte
fte
fte
1.0
1.0
2.7
2.0
6.0
1.0
.
22.0
32.0
9.0
12.0
.
.
4.0
3.3
Construction Mgmt
8.0
5.0
1.0
.
.7
1.3
Prop Dev & Int Des
8.0
17.0
.
.
.
.
HNC PM & V, FM & P
.
.
.
.
1.3
.7
Qu Surv & Com Mgmt
8.0
22.0
2.0
2.0
4.7
6.7
Fnd Deg QS & Com Mgt
1.0
.
.
.
.
.
HND/C or BSc Civil Eng
11.0
8.0
4.0
2.0
24.7
21.3
1.0
2.0
.
.
.
.
Found Deg Civ Eng
Today's data for this year's students
newid
1
2
3
4
5
scheme
mascode
year
dept
course
route
mode
dec1
resp1
0800294
UGS
KNFG-U
2008/9
CSA
UGS120000011
QSC
11
C
D
0800295
UGS
GQ13-U
2008/9
ESH
UGS120000011
ENGAMAS
11
C
F
0800296
UGS
NN14-U
2008/9
BNS
UGS120000011
ACCABMG
11
C
D
0800297
UGS
W101-U
2008/9
ART
UGS120000011
FIA
11
C
I
0800494
UGS
W250-U
2008/9
ART
ART120000011
DEIF
11
C
dec2
resp2
U
F
U
I
dec3
resp3
The interactions between UCAS and the university - the record for one student who applied last year
newid
date
course
route
1
0703443
10-JAN-2007
UGS120000011
LAW
2
0703443
02-FEB-2007
UGS120000011
LAW
3
0703443
02-FEB-2007
UGS120000011
4
0703443
02-FEB-2007
5
0703443
6
7
8
decresp
status
mascode
scheme
mode
A
M100-U
UGS
11
C
A
M100-U
UGS
11
LAW
C
A
M100-U
UGS
11
UGS120000011
LAW
C
A
M100-U
UGS
11
02-FEB-2007
UGS120000011
LAW
C
A
M100-U
UGS
11
0703443
02-FEB-2007
UGS120000011
LAW
C
A
M100-U
UGS
11
0703443
02-FEB-2007
UGS120000011
LAW
C
A
M100-U
UGS
11
0703443
05-FEB-2007
UGS120000011
LAW
C
A
M100-U
UGS
11
9
0703443
24-MAY-2007
UGS120000011
LAW
CI
A
M100-U
UGS
11
10
0703443
13-AUG-2007
UGS120000011
LAW
CI
A
M100-U
UGS
11
11
0703443
14-AUG-2007
UGS120000011
LAW
CF
A
M100-U
UGS
11
12
0703443
14-AUG-2007
UGS120000011
LAW
CFU
A
M100-U
UGS
11
13
0703443
14-AUG-2007
UGS120000011
LAW
CFU
A
M100-U
UGS
11
14
0703443
14-AUG-2007
UGS120000011
LAW
CFU
A
M100-U
UGS
11
15
0703443
14-AUG-2007
UGS120000011
LAW
CFU
A
M100-U
UGS
11
16
0703443
14-AUG-2007
UGS120000011
LAW
CFU
A
M100-U
UGS
11
17
0703443
14-AUG-2007
UGS120000011
LAW
CFUF
A
M100-U
UGS
11
18
0703443
14-AUG-2007
UGS120000011
LAW
CFUF
A
M100-U
UGS
11
Splitting students on j oint degrees - step1
1
2
3
4
5
newid
0800294
0800295
0800296
0800297
0800494
course
UGS120000011
UGS120000011
UGS120000011
UGS120000011
ART120000011
route
QSC
ENGAMAS
ACCABMG
FIA
DEIF
dec1
C
C
C
C
C
resp1
D
F
D
I
dec2
resp2
U
F
U
I
string c (a1) /l (a3) /r (a3) .
compute c=substr(route,4,1).
compute l=substr(route,1,3).
if not(c='T' or c='A' or c='W' or c='S' or c=' ') l=' '.
compute r=substr(route,5,3).
if not(c='T' or c='A' or c='W') r=' '.
execute.
Splitting students on joint degrees - step 2
1
2
3
4
5
newid
0800294
0800295
0800296
0800297
0800494
course
UGS120000011
UGS120000011
UGS120000011
UGS120000011
ART120000011
route
QSC
ENGAMAS
ACCABMG
FIA
DEIF
dec1
C
C
C
C
C
resp1
D
F
D
I
dec2
resp2
U
F
U
I
c
l
QSC
A ENG
A ACC
FIA
F
r
MAS
BMG
compute count=1.
if c='A' count=0.5.
if c='A' countr=0.5.
VARSTOCASES /ID = id
/MAKE pathway FROM l r
/MAKE newcount FROM count countr
/KEEP = newid scheme mascode year dept course
dec1 resp1 dec2 resp2 dec3 resp3 route
/NULL = DROP .
Splitting students on joint degrees - step 2
1
2
3
4
5
6
7
newid
0800294
0800295
0800295
0800296
0800296
0800297
0800494
course
UGS120000011
UGS120000011
UGS120000011
UGS120000011
UGS120000011
UGS120000011
ART120000011
route
QSC
ENGAMAS
ENGAMAS
ACCABMG
ACCABMG
FIA
DEIF
dec1
C
C
C
C
C
C
C
resp1
D
F
F
D
D
I
dec2
resp2
U
U
F
F
U
I
path
way
QSC
ENG
MAS
ACC
BMG
FIA
new
count
1.0
.5
.5
.5
.5
1.0
1.0
Making a single student from their UCAS transaction log - step 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
newid
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
date
10-JAN-2007
02-FEB-2007
02-FEB-2007
02-FEB-2007
02-FEB-2007
02-FEB-2007
02-FEB-2007
05-FEB-2007
24-MAY-2007
13-AUG-2007
14-AUG-2007
14-AUG-2007
14-AUG-2007
14-AUG-2007
14-AUG-2007
14-AUG-2007
14-AUG-2007
14-AUG-2007
29-NOV-2006
07-DEC-2006
07-DEC-2006
07-DEC-2006
07-DEC-2006
07-DEC-2006
07-DEC-2006
07-DEC-2006
03-MAY-2007
13-AUG-2007
13-AUG-2007
13-AUG-2007
13-AUG-2007
13-AUG-2007
13-AUG-2007
13-AUG-2007
14-AUG-2007
23-AUG-2007
24-AUG-2007
24-AUG-2007
24-AUG-2007
26-AUG-2007
route
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
decresp
C
C
C
C
C
C
C
CI
CI
CF
CFU
CFU
CFU
CFU
CFU
CFUF
CFUF
C
C
C
C
C
C
C
CF
CFU
CFU
CFU
CFU
CFU
CFU
CFUF
CFUF
CFUF
CFUF
CFUF
CFUF
CFUF
status
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
W
W
W
W
W
compute firm=index(decresp,'F').
if firm>1 firm=1.
if firm=1 firmdate=date.
formats firmdate(date11).
compute unc=index(decresp,'U').
if unc>1 unc=1.
if unc=1 uncdate=date.
formats uncdate(date11).
if status='AE' or status='AH'
or status='W' or status='UE'
or status='RPL' or status='ZC'
or status='ZM' gonedate=date.
formats gonedate(date11).
execute.
Making a single student from their UCAS transaction log - step 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
newid
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0703443
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
0701160
date
10-JAN-2007
02-FEB-2007
02-FEB-2007
02-FEB-2007
02-FEB-2007
02-FEB-2007
02-FEB-2007
05-FEB-2007
24-MAY-2007
13-AUG-2007
14-AUG-2007
14-AUG-2007
14-AUG-2007
14-AUG-2007
14-AUG-2007
14-AUG-2007
14-AUG-2007
14-AUG-2007
29-NOV-2006
07-DEC-2006
07-DEC-2006
07-DEC-2006
07-DEC-2006
07-DEC-2006
07-DEC-2006
07-DEC-2006
03-MAY-2007
13-AUG-2007
13-AUG-2007
13-AUG-2007
13-AUG-2007
13-AUG-2007
13-AUG-2007
13-AUG-2007
14-AUG-2007
23-AUG-2007
24-AUG-2007
24-AUG-2007
24-AUG-2007
26-AUG-2007
route
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
LAW
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
PSC
decresp
C
C
C
C
C
C
C
CI
CI
CF
CFU
CFU
CFU
CFU
CFU
CFUF
CFUF
C
C
C
C
C
C
C
CF
CFU
CFU
CFU
CFU
CFU
CFU
CFUF
CFUF
CFUF
CFUF
CFUF
CFUF
CFUF
status
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
A
W
W
W
W
W
firm
0
0
0
0
0
0
0
0
0
0
1
1
1
1
1
1
1
1
0
0
0
0
0
0
0
0
1
1
1
1
1
1
1
1
1
1
1
1
1
1
firmdate
.
.
.
.
.
.
.
.
.
.
14-AUG-07
14-AUG-07
14-AUG-07
14-AUG-07
14-AUG-07
14-AUG-07
14-AUG-07
14-AUG-07
.
.
.
.
.
.
.
.
03-MAY-07
13-AUG-07
13-AUG-07
13-AUG-07
13-AUG-07
13-AUG-07
13-AUG-07
13-AUG-07
14-AUG-07
23-AUG-07
24-AUG-07
24-AUG-07
24-AUG-07
26-AUG-07
unc
0
0
0
0
0
0
0
0
0
0
0
1
1
1
1
1
1
1
0
0
0
0
0
0
0
0
0
1
1
1
1
1
1
1
1
1
1
1
1
1
uncdate
gonedate
.
.
.
.
.
.
.
.
.
.
.
14-AUG-07
14-AUG-07
14-AUG-07
14-AUG-07
14-AUG-07
14-AUG-07
14-AUG-07
.
.
.
.
.
.
.
.
.
13-AUG-07
13-AUG-07
13-AUG-07
13-AUG-07
13-AUG-07
13-AUG-07
13-AUG-07
14-AUG-07
23-AUG-07
24-AUG-07
24-AUG-07
24-AUG-07
26-AUG-07
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
23-AUG-07
24-AUG-07
24-AUG-07
24-AUG-07
26-AUG-07
aggregate outfile=*/break=newid route
/firmdate=first(firmdate)
/uncdate=first(uncdate)
/gonedate=first(gonedate)
/date1=first(date)
/date2=last(date)
/lastfirm=last(firmdate).
Making a single student from their UCAS transaction log - step 3
1
2
newid
0701160
0703443
route
PSC
LAW
firmdate
03-MAY-07
14-AUG-07
uncdate
13-AUG-07
14-AUG-07
gonedate
23-AUG-07
.
date1
29-NOV-06
10-JAN-07
date2
26-AUG-07
14-AUG-07
lastfirm
26-AUG-07
14-AUG-07
* Date and Time Wizard: today.
COMPUTE today = XDATE.DATE($TIME).
VARIABLE LABEL today.
VARIABLE LEVEL today (SCALE).
FORMATS today (DATE11).
VARIABLE WIDTH today(11).
EXECUTE.
compute lastyr=today-86400*366.
compute gone=0.
if year='2008/9' and status='AE' or status='AH' or status='W' or status='UE' or
status='AF' or status='RPL' or status='ZC' or status='ZM' gone=1.
if year='2007/8' and uncdate<lastyr and firmdate<lastyr and ((gonedate>lastyr) or
missing(gonedate)) and feb=0 and newmode=1 and not(appcap='O') homely=newcount.
if year='2008/9' and unc=1 and firm=1 and gone=0 and newmode=1 and
not(appcap='O') homety=newcount.
if year='2007/8' and uncdate<lastyr and firmdate<lastyr and ((gonedate>lastyr) or
missing(gonedate)) and feb=0 and newmode=2 and not(appcap='O')
partly=newcount*2/3.
if year='2008/9' and unc=1 and firm=1 and gone=0 and newmode=2 and
not(appcap='O') party=newcount*2/3.
if year='2007/8' and uncdate<lastyr and firmdate<lastyr and ((gonedate>lastyr) or
missing(gonedate)) and feb=0 and newmode=1 and appcap='O' overly=newcount.
if year='2008/9' and unc=1 and firm=1 and gone=0 and newmode=1 and appcap='O'
overty=newcount.
September 2007 and September 2008
Undergraduate Unconditional Firm Acceptances
As at 21st August
Home ft
2007/8
Home ft
2008/9
fte
Arch Tech
3.0
fte
10.0
HNC/D Building
4.0
Bld Surv & Prop Mgt
overseas
2007/8
overseas
2008/9
part time
2007/8
part time
2008/9
fte
fte
fte
fte
1.0
1.0
2.7
2.0
6.0
1.0
.
22.0
32.0
9.0
12.0
.
.
4.0
3.3
Construction Mgmt
8.0
5.0
1.0
.
.7
1.3
Prop Dev & Int Des
8.0
17.0
.
.
.
.
HNC PM & V, FM & P
.
.
.
.
1.3
.7
Qu Surv & Com Mgmt
8.0
22.0
2.0
2.0
4.7
6.7
Fnd Deg QS & Com Mgt
1.0
.
.
.
.
.
HND/C or BSc Civil Eng
11.0
8.0
4.0
2.0
24.7
21.3
1.0
2.0
.
.
.
.
Found Deg Civ Eng