IBM Support

Export SPSS Statistics *.sav dataset to Excel *.xls workbook which contains sorted data in multiple sheets

Technote (FAQ)


How do I manipulate data in Statistics and then export it to an Excel *.xls workbook that contains sheets based on manipulation?


File-> Save As-> Type: *.xls,
which produces the command syntax:
does not offer functionality to save data to different sheets in a *.xls workbook.


ODBC and SQL command syntax must be used to establish a connection to an Excel database and populate it with Statistics data as necessary. SQL "TABLES" represent "sheets" in the *.xls workbook.

File > Export to Database > Excel data source > destination *.xls > Create a new table > variables to include in sheets

which produces the command syntax:

SAVE TRANSLATE /TYPE=ODBC /CONNECT= 'DSN=Excel Files; DBQ='path of destination *.xls' /SQL = 'CREATE TABLE 'temp_table' (variable names and types for sheet columns )' /REPLACE /TABLE = ('name_of_ sheet') /KEEP = (variables) /SQL = 'INSERT INTO 'temp_table' (variables) SELECT variables FROM 'name_of_sheet'

In order to populate multiple sheets (TABLES) with a single set of SAVE TRANSLATE /TYPE=ODBC command syntax, a loop is required. Since the loop will hold procedural syntax, a macro is needed (DEFINE-!ENDDEFINE).


DEFINE @macro_name() !DO !i = start_numeric !TO end_numeric

SELECT IF sorting_var = !i.





Use !CONCAT() to concatenate sheet name with !i to name each iteration of sheet creation a different name.

Note 1: When using command syntax without the dialog menus, the destination *.xls workbook does not need to be created prior to running syntax. Statistics will create the destination *.xls workbook for you if it does not exist.

Note2: The format *.xls (Excel 97-2003 Workbook) must be used in Statistics 19 and 20. Version 21 is the first to support the current *.xlsx format.

Related information

Need more help? Our Statistics forum is Live!

Document information

More support for: SPSS Statistics

Software version: 19.0, 20.0, 21.0

Operating system(s): Windows

Reference #: 1615805

Modified date: 07 September 2016