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

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

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

1 Exporting fewer variables to an ASCII text file

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

Open MLwiN
Select Open worksheet from the File menu
Navigate to the location of the saved worksheet “worksheet-1.ws”
Click the Open button

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

‌You can view the data by

Select View or edit data from the Data manipulation menu

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:

Type c1-c3 c6 in the Columns text box to export columns c1, c2, c3 and c6
Type worksheet-1.txt in the File text box. Note, MLwiN will save “worksheet-1.txt” to the current directory. To select a different location to save, click the Browse button, navigate to the directory where you want to save the file and type 'worksheet-1.txt' in the File name box of the window that appears, then click the Save button
Click the OK button

‌

The data has been outputted to an ASCII text file.

Open a text editor such as Notepad
Open the saved ASCII text file “worksheet-1.txt”

Your data should look like this:

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

Open Excel
Open the ASCII text file “worksheet-1.txt”

The following Text Import Wizard window will appear

Click the Finish button

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

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.

Open MLwiN
Select Open worksheet from the File menu
Navigate to the location of the saved worksheet “worksheet-1.ws”
Click the Open button

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

Open a new macro

Select New Macro from the File menu

The macro editor will appear:

To export all 6 columns, type the following three lines of code into the macro editor:
- DELI 44
- FDOU c1-c6
- worksheet-3.txt (8, 8, 8, 8, 8, 8)

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.

Click the Execute button

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.

Open a text editor such as Notepad
Open the saved ASCII text file “worksheet-3.txt (8,8,8,8,8,8)”

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

Open Excel
Open the ASCII text file “worksheet-3.txt (8,8,8,8,8,8)”

The following Text Import Wizard window will appear

Click the Next button
Tick the Comma check box

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.

Click the Finish button

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

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.

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

Open MLwiN
Select Open worksheet from the File menu
Navigate to the location of the saved worksheet “worksheet-2.ws”
Click the Open button

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

Select View or edit data from the Data manipulations menu

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.

Select Recode->By Range from the Data Manipulation menu

The following screen will appear

Type m in the Values in range and to text boxes
Type -999 in the to new value text box
In the Input columns box select all the variables
Click the Same as Input button

The Recode variables window should look like this

Click the Add to action list button

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

Click the Execute button

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

Close the Recode Variables window

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

Select View or edit datafrom the Data manipulation menu)‌

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.