Getting data out of MLwiN

Getting data out of MLwiN 2.1

MLwiN 2.1 gives users the option to save their data as an SPSS (*.sav), Stata (*.dta) or Minitab (*.mtw) file. This can be done by choosing 'Save worksheet As' from the File menu and then selecting the relevant option from the drop-down box, or by using a command. Full documentation is available in the MLwiN 2.10 Manual supplement in section 6 on pp77-78.

Copying to the clipboard is simplified in MLwiN 2.1 compared to version 2.02. In the Names window, simply highlight all the variables you wish to export (use ctrl + click to make multiple selections) and then press the Copy button at the top of the window. The data are transferred to the clipboard and can be pasted for example into Excel.

The method described below for outputting data from version 2.02 will also work in 2.10 Beta (though it is not as simple as the two ways just mentioned and so is generally not the first choice of method). You may find that the problem described in section 2 below that occurs in version 2.02 when exporting more than 5 variables does not occur in 2.1 and so more than 5 variables can be exported in exactly the same way as 5 or fewer variables (i.e. as described in section 1 below). However this is not guaranteed so if attempting this you should experiment first to check what the behaviour of your version is.

Getting data out of MLwiN 2.02

  1. Exporting 5 or fewer variables to an ASCII text file
  2. Exporting more than 5 variables to an ASCII text file
  3. Exporting data to an ASCII text file using macros
  4. Recoding missing values before outputting data

The easiest way to output data from MLwiN version 2.02 is to use the ASCII text file output window from the File menu. Once the data is exported, you can import the ASCII text file into other packages (such as SPSS, STATA, SAS or EXCEL).

MLwiN will export data to an ASCII text file storing the variables in the text file in up to a maximum of 5 columns. This ceiling limitation can pose problems when importing 6 or more variables from the ASCII text file into certain packages (e.g. Excel). To understand this, first note that exporting 1, 2, 3, 4 or 5 variables from MLwiN will result in an ASCII text file with 1, 2, 3, 4 or 5 columns respectively. This is as expected and will not pose problems when importing into other packages. However, exporting 6 or more variables from MLwiN will always result in an ASCII text file with only 5 columns. For each record, the value of the 6th and subsequent variables will be placed on a new line in the text file. Exporting 11 variables from MLwiN will result in three lines of text in the ASCII file for each record in MLwiN. Since Excel cannot unwrap this ASCII data, we recommend Excel users output a maximum of 5 variables at a time from MLwiN. Should the user need to export more variables than this they can export the variables in batches of five and then re-assemble the data in Excel.

Here we assume the reader has an electronic version of this document. Below is a simple MLwiN worksheet

mlwin data filesimple MLwiN worksheet (To download: Right-click and 'save target as' or 'save link as')

1 Exporting 5 or fewer variables to an ASCII text file

back to top

Exporting 5 or fewer variables to an ASCII text file will result in a well-behaved text file where one record is saved per line of the file. This can then be easily imported into other packages (such as SPSS, STATA, SAS or EXCEL).

The Names window will appear showing you that the worksheet has 6 variables. Each variable has 20 observations.

1

You can view the data by

The Data window should look like this (after resizing as necessary):

2

The following screen will appear:

3

4

The data has been outputted to an ASCII text file.

Your data should look like this:

5

The four variables have been stored as four columns in the ASCII text file.

The following Text Import Wizard window will appear

6

The data will appear in the spreadsheet

7

You could now insert a row above the table and type in the relevant variable names.

2  Exporting more than 5 variables to an ASCII text file

back to top

Exporting more than 5 variables to an ASCII text file will result in a badly behaved text file where records are wrapped after every five variables. This type of file cannot be unwrapped when it is imported into Excel although other packages (such as STATA) can. If the user imports into Excel 5 or more variables from an MLwiN generated ASCII file, they will have to unwrap the data manually in Excel.

The Names window will appear showing you that the worksheet has 6 variables. Each variable has 20 observations.

8

The following screen will appear:

10

The data has been outputted to an ASCII text file.

Your data should look like this:

11

The six variables have been stored as five columns in the ASCII text file. Each series of six values is outputted to a new line in the ASCII text file. Unfortunately, Excel is not able to unwrap this data.

The following Text Import Wizard window will appear

12

Although you can see in the Preview section of the wizard that the data is wrapped, there is no option to unwrap it.

The data will appear incorrectly in the spreadsheet.

13

For small worksheets like this one you could now manually correct the data to restore the 20 records to 20 rows in the Excel spreadsheet. This is fiddly and time consuming. Instead, we recommend Excel users output a maximum of 5 variables from MLwiN. Should users need to export more variables than this, they can export variables in batches of five and then re-assemble the data in Excel.

3  Exporting data to an ASCII text file using macros

back to top

For macro users, the FDOUtput command provides an alternative way to export data from MLwiN. A distinct benefit of this approach is the ability to export more than five variables to a single line of the ASCII text file.

The Names window will appear showing you that the worksheet has 6 variables. Each variable has 20 observations.

9

Open a new macro

The macro editor will appear:

14

The macro editor should look like this

15

The first command specifies the delimiter to be a comma. This will separate the variables in the ASCII text file by commas. The second line instructs MLwiN to export columns c1 through to c6. The final line specifies the file name and how the data will be written to the file. The code (8,8,8,8,8,8) tells MLwiN to write values for each of the six variables  as 8 characters. For example, the first value of the first variable in the MLwiN worksheet is 134. This value will be exported as 8 characters: 134.0000.

MLwiN outputs the variables to an ASCII text file. However it has saved the file as “worksheet-3.txt (8,8,8,8,8,8)” instead of “worksheet-3.txt”. This is a bug, but it does not pose any problems. Note that when opening files in Excel or Notepad, you will need to have the option All Files selected in the Files of type drop down box rather than Text Files or Text Documents.

Your data should look like this. Note how the values are separated by columns and that each value has 8 characters.

16

The following Text Import Wizard window will appear

18

The Text Import Wizard should look like this. Note that in the Data preview window the six variables are separated by vertical lines. This indicates that Excel has realised that the ASCII text file contains 6 variables.

19

The data will appear in the spreadsheet

20

You could now insert a row above the table and type in the relevant variable names.

4  Recoding missing values before outputting data

back to top

If you have missing values in your worksheet, you may wish to recode them to a particular numeric value before outputting your data. You can do this with the Recode variables window in MLwiN.

Here we assume the reader has an electronic version of this document. Below is a second version of the simple MLwiN worksheet, which this time contains missing values.

mlwin data filesimple MLwiN worksheet with missing values (To download: Right-click and 'save target as' or 'save link as')

The Names window will appear showing you that the worksheet has 6 variables. Each variable has 20 observations. However, note that the variable district now has one missing value, the variable price has three missing values and the variable type has two missing values.

21

You can view the data by

The Data window should look like this (after re-sizing as necessary):

22

We can use the Recode variables window to change values of MISSING to a number of our choice, for example, -999.

The following screen will appear

23

The Recode variables window should look like this

24

The Action list displays six recode actions, one for each variable selected in the Input columns box.

recodevar

Note the asterisk that has been added to the beginning of the six recode actions. This indicates that the actions have been executed.

26

If you look at the Data window (which you should now see; if not then:

27

You will see that the missing values have now been converted to the value -999.

You can now output your data using either the ASCII text file output window or the FDOUtput macro.

Now try and export your data out of MLwiN.