Getting data out of MLwiN

Saving data for use in other packages

MLwiN gives users the option to save their data as an SPSS (*.sav), Stata (*.dta), SAS transport (*.xpt) or Minitab (*.mtw) file. This can be done by choosing 'Save worksheet As', or for a subset of the data 'Export...', 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 Manual supplement in section 6.

Copying data to the clipboard

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 Microsoft Excel.

Saving data as ASCII files

  1. Exporting variables to an ASCII text file
  2. Exporting data to an ASCII text file using macros
  3. Recoding missing values before outputting data

The easiest way to output data as ASCII is to choose the Export... option from the File menu, select the columns to export and then choose either "comma separated text file", "tab delimited file" or "fixed width text file" from the "Save as type" dropdown. Alternatively if you require more control over how the data is saved you can use the ASCII text file output window from the File menu.

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 fewer variables to an ASCII text file

back to top

Exporting fewer variables to an ASCII text file will result in a text file where one record is saved per line of the file. This can then be easily imported into other packages (such as Microsoft Excel).

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

‌You can view the data by

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

‌Select ASCII text file output from the File menu

The following screen will appear:

The data has been outputted to an ASCII text file.

Your data should look like this:

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

The following Text Import Wizard window will appear

The data will appear in the spreadsheet

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

2  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.

Open a new macro

The macro editor will appear:

The macro editor should look like this

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.

The following Text Import Wizard window will appear

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.

The data will appear in the spreadsheet

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

3  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.

You can view the data by

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

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

The Recode variables window should look like this

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

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

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

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.