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

Technote (FAQ)


Question

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

Cause

File-> Save As-> Type: *.xls,

which produces the command syntax:
SAVE TRANSLATE OUTFILE /TYPE=XLS
does not offer functionality to save data to different sheets in a *.xls workbook.


Answer

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.

SAVE TRANSLATE /TYPE=ODBC . . .

!DOEND !ENDDEFINE

@macro_name.

-----------------------------------------------------------------

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.


Rate this page:

(0 users)Average rating

Document information


More support for:

SPSS Statistics

Software version:

19.0, 20.0, 21.0

Operating system(s):

Windows

Reference #:

1615805

Modified date:

2013-04-25

Translate my page

Machine Translation

Content navigation