We have a new website go to gov.scot

Paper 27th June 2000 - LGRAS(00)8

LGRAS (00) 8 - POINTS ON THE SUBMISSION OF "STATS 19" DATA

1. This paper covers some points regarding the submission of "STATS 19" data to the Scottish Executive.

The "Month of File" and "Year of File" in the "STATS 19" header record

2. As indicated in Section 4 of "STATS 21", a computer file of "STATS 19" data sent to the Scottish Executive must start with a file identity (header) record. The header record has a number of purposes, including identifying the file as one containing "STATS 19" data for a particular Police Force area. The header record also includes the "Month of File" and "Year of File". These are used to ensure that the Scottish Executive processes the returns in the correct order, and to identify any case where a return had been lost in transit. Occasionally, data suppliers have been uncertain what should appear in the "Month of File" and "Year of File" fields. The purpose of this note is to clarify matters.

3. The way in which "Month of File" and "Year of File" are used is as follows. For each Police Force area, 12 returns per year should be made to the Scottish Executive: one per month. Therefore, when it processes a return, the Scottish Executive's computer system checks that the "Month of File" and "Year of File" follow on in sequence from the "Month of File" and "Year of File" for the most recent return that it has processed successfully. Thus, if the latter return had "Month of File" = 12 and "Year of File" = 1999, the new return should have "Month of File" = 01 and "Year of File" = 2000. Should this not be the case, the Scottish Executive computer system will reject the return.

4. Please note that "Month of File" and "Year of File" need not be the same as the month and year of the majority of the accidents included in the return: a submission with "Month of File" = 12 and "Year of File" = 1999 could in theory consist solely of records for accidents which occurred in November 1999 or earlier. However, the Scottish Executive computer system will reject the whole of a return which contains any accident records which have a month and year which are after the "Month of File" and "Year of File" - for example, the whole of the return for which "Month of File" = 12 and "Year of File" = 1999 would be rejected if it included a single record with an accident date in January 2000.

5. Members of the Group are invited to ask any questions that they may have on this matter, and to comment on the attached draft proposed replacement for Annex 6 (page 20) of "STATS 21", which sets out the above points.

The format for the submission of "STATS 19" data

6. "STATS 21" specifies the format in which the "STATS 19" data should be submitted. For example, Section 4 states that records must have a length of either 100 characters or 125 characters. Local Processing Authorities (LPAs) who supply Contributory Factor data on the Attendant Circumstances record must submit 125-character records; others may choose to submit records of either 100 characters or 125 characters provided that the same length is used for all the data records in a submission. Section 5 specifies that data should be submitted in DOS-compatible ASCII files of fixed length records which contain character data only and are delimited by Carriage Return / Line Feed. Annexes 1, 2, 3 and 6 of "STATS 21" specify precisely the character positions on each record which must be used for each variable: for example, the Police Force code must appear in character positions 8-9 of every records, the accident date must appear in character positions 38-43 of the Attendant Circumstances record; the sex of the driver must appear in character position 58 of every Vehicle record; and the age of the casualty must appear in character positions 33- 34 of every Casualty record.

7. After the "STATS 21" was distributed, it was suggested by a member of the computer staff of one LPA that the above "fixed length, fixed column records with delimiters" format was an "out-of-date" one: he argued that, for several years, far more flexible means of transferring information have been available, usually based around the Comma Separated Values (CSV) format which, he said, were provided through almost any standard office application. At that stage, it was far too late to consider making any changes to the record format specified for the provision of "STATS 19" data. However, it was felt that the next meeting of LGRAS should be asked if other bodies might prefer to supply the data in formats other than the current one.

8. This matter is therefore being raised now. It must be emphasised that the Scottish Executive has no wish to change the format used for the "STATS 19" data. Nor does the Scottish Executive wish to see different formats used for the returns made by different data suppliers, because that would increase both the cost of developing and maintaining its computer system and the likelihood of problems arising: for Scottish Executive purposes, the most important point is that the same format should be used for all the returns (whether that format is the current one or, say, CSV, matters less to the Scottish Executive).

9. The first question, therefore, is: do many LPAs feel that they would benefit greatly if data could be supplied in a "new" format - and, if so, might there be a consensus view on what it should be (eg CSV? Excel spreadsheets? or some other format?) If a majority of LPAs would prefer data to be supplied in a particular new format, is it likely that all LPAs could supply the data in that format? Or, might several LPAs prefer data to continue to be supplied using the current format? In the latter case, the Scottish Executive would have to be persuaded that the benefits to LPAs of being able to choose whichever of the two formats (the current one, and the alternative one) was the more suitable for them would be sufficiently large to outweigh greatly the cost to it of operating with two different formats.

10. The development of any new format, or any alternative format, would affect not just the LPAs. There are, no doubt, other interested parties (such as the Councils which are not involved in supplying the data to the Scottish Executive) who currently expect to receive copies of the data in the format specified in "STATS 21". Discussions about any possible new or alternative format would have to involve them. Some of these bodies are represented on LGRAS: what are their views on the possible use of a new format, or of an alternative format?

11. A precise specification of a new format, or an alternative format, would have to be produced - if there were sufficient demand for it, if there were agreement on what it should be, and if the likely cost of changing computer systems could be justified. It should be noted that producing such a specification would not necessarily be a simple matter. For example, it would have to set out what should be done in the case of the fields which are, in effect, optional. (An example of such a field is "Year of Record". This is not one of the fields listed in "STATS 20". The Scottish Executive computer system derives it from the accident date in cases where the supplier has put zeroes in the "Year of Record" field, which "STATS 21" shows as occupying character positions 12-13 of every record.) It would also have to state that there would be no need for any equivalent of the "filler" fields, in the format were one (such as CSV) for which the concept of "fillers" is irrelevant.

12. Consideration would also have to be given to the timing of any change in the format - for example, it would probably be best for any new format to be introduced at the same time as any changes arising from the 20002 Quinquennial Review - and to the implications of any introduction of Common Database Software (see paper LGRAS [00] 2).

13. Members of the Group are asked for their views on this matter. For example:

  • do they feel that there is a need for an alternative format for the supply of "STATS 19" data?
  • if so, what do they think should it be based upon (e.g. Comma Separated Values? Excel spreadsheets, or something else)?
  • and when should any work on this be done - for example, should it be considered as part of the 2002 Quinquennial Review and/or the development of any Common Database Software?

"Location Query and Change" printouts

14. The new Scottish Executive Roads Information System (SERIS: a database of information about trunk roads) was installed last year. It was developed by contractors (a company called WDM), who also maintain it. After a period of parallel running, it is about to replace the old "CHIPS" database (which was maintained by SIAS).

15. Like CHIPS, SERIS produces printouts which give details of queries on, and changes to, the locational data (i.e. road number and class, and grid reference) for accidents which appear to have occurred on trunk roads. With the introduction of SERIS, the opportunity was taken to introduce a new style of printout. Some Local Processing Authorities (LPAs) were asked for comments on draft versions of the SERIS "location query and change" printouts, and all of them should subsequently have been sent SERIS printouts with details of any location queries and changes relating to accidents which appear to have occurred on trunk roads in their areas. (NB: because both SERIS and CHIPS were being maintained during the period of parallel running, LPAs received copies of both CHIPS and SERIS "location query and change" printouts. This duplication will cease once SERIS has replaced CHIPS.)

16. Members of the Group representing LPAs may wish to take this opportunity to ask questions about or comment upon both the format of the "location query and change" printouts (samples are attached) and the arrangements for raising and dealing with the queries and changes to which they refer.For example:

  • would LPAs like more information about how or why these queries are raised and changes are made?
  • can LPAs suggest improvements to (e.g.) the printouts or the arrangements for dealing with location queries and changes?

Draft replacement for page 20 of "STATS 21"

Annex 6 - File Identity (Header) Record

Character positionItemSTATS 19 refValue/comment
01 - 04Filler4 alphanumerics
05 - 06Record type01
07Filler1 alphanumeric
08 - 09Police force code1.291-98
10 - 30Filler21 alphanumerics
31 - 33ApplicationRAS
34 - 35Month of file (*)01-12
36 - 39Year of file (*)1978 onwards
40 - 100 (or 125**)Filler61 alphanumerics (or 86**)

Notes

(*) " Month of File" and " Year of File" are used to ensure that returns are processed in the correct order, and to identify any case where a return had been lost in transit. For each Police Force area, 12 returns per year should be made to the Scottish Executive: one per month. When it processes a return, the Scottish Executive's computer system checks that the "Month of File" and "Year of File" follow on in sequence from the "Month of File" and "Year of File" of the most recent return that it has processed successfully. Thus, if the latter return had "Month of File" = 12 and "Year of File" = 1999, the new return should have "Month of File" = 01 and "Year of File" = 2000. Should this not be the case, the Scottish Executive computer system will reject the return.

Please note that "Month of File" and "Year of File" need not be the same as the month and year of the majority of the accidents included in the return: a submission with "Month of File" = 12 and "Year of File" = 1999 could in theory consist solely of records for accidents which occurred in November 1999 or earlier. However, the Scottish Executive computer system will reject the whole of a return which contains any accident records which have a month and year which are after the "Month of File" and "Year of File" - for example, the whole of the return for which "Month of File" = 12 and "Year of File" = 1999 would be rejected if it included a single record with an accident date in January 2000.

(**) LPA's supplying "Contributory Factors" on the Attendant Circumstances record should use the longer record length (i.e. 125 characters). Others may use the longer length if they wish, provided that all the data records in a submission have the same length as the header record.

Note: There is no longer a requirement to supply a Standard Control Record (Trailer record).