IBM SPSS Statistics 24 Core System User's Guide IBM_SPSS_Statistics_Core_System_User_Guide User

User Manual: IBM_SPSS_Statistics_Core_System_User_Guide user guide pdf - FTP File Search (14/20)

Open the PDF directly: View PDF PDF.
Page Count: 310 [warning: Documents this large are best viewed by clicking the View PDF Link!]

IBM SPSS Statistics 24 Core System
User's Guide
IBM
Note
Before using this information and the product it supports, read the information in “Notices” on page 289.
Product Information
This edition applies to version 24, release 0, modification 0 of IBM SPSS Statistics and to all subsequent releases and
modifications until otherwise indicated in new editions.
Contents
Chapter 1. Overview ......... 1
Windows ............... 1
Designated window versus active window ... 1
Variable names and variable labels in dialog box lists 2
Data type, measurement level, and variable list icons 2
Statistics Coach ............. 2
Finding out more ............. 3
Chapter 2. Getting Help ........ 5
Chapter 3. Data files ......... 7
Opening data files ............ 7
To open data files............ 7
Data file types ............. 7
Reading Excel Files ........... 8
Reading Older Excel Files and Other Spreadsheets 9
Reading dBASE files........... 9
Reading Stata files ........... 9
Reading CSV Files ........... 10
Text Wizard ............. 10
Reading Database Files ......... 14
Reading Cognos BI data ......... 20
Reading Cognos TM1 data ........ 22
Reading Data Collection Data ....... 23
File information ............. 25
Saving data files ............. 25
To save modified data files ........ 25
To save data files in code page character
encoding .............. 25
Saving data files in external formats ..... 26
Saving data files in Excel format ...... 29
Saving data files in SAS format....... 29
Saving data files in Stata format ...... 30
Saving Subsets of Variables ........ 31
Encrypting data files .......... 31
Exporting to a Database ......... 32
Exporting to Data Collection........ 38
Exporting to Cognos TM1 ........ 38
Comparing datasets ........... 40
Compare Datasets: Compare tab ...... 40
Compare Datasets: Attributes tab ...... 41
Comparing datasets: Output tab ...... 41
Protecting original data .......... 42
Virtual Active File ............ 42
Creating a Data Cache.......... 43
Chapter 4. Distributed Analysis Mode 45
Server Login .............. 45
Adding and Editing Server Login Settings ... 45
To Select, Switch, or Add Servers ...... 46
Searching for Available Servers ....... 47
Opening Data Files from a Remote Server .... 47
File Access in Local and Distributed Analysis Mode 47
Availability of Procedures in Distributed Analysis
Mode ................ 48
Absolute versus Relative Path Specifications ... 48
Chapter 5. Data Editor ........ 51
Data View ............... 51
Variable View.............. 51
To display or define variable attributes .... 52
Variable names ............ 52
Variable measurement level ........ 53
Variable type ............. 53
Variable labels ............ 55
Value labels ............. 55
Inserting line breaks in labels ....... 55
Missing values ............ 55
Roles ............... 56
Column width ............ 56
Variable alignment ........... 56
Applying variable definition attributes to
multiple variables ........... 56
Custom Variable Attributes ........ 57
Customizing Variable View ........ 59
Spell checking ............ 60
Entering data .............. 60
To enter numeric data .......... 60
To enter non-numeric data ........ 61
To use value labels for data entry ...... 61
Data value restrictions in the data editor ... 61
Editing data .............. 61
Replacing or modifying data values ..... 61
Cutting, copying, and pasting data values ... 61
Inserting new cases........... 62
Inserting new variables ......... 62
To change data type .......... 63
Finding cases, variables, or imputations ..... 63
Finding and replacing data and attribute values .. 64
Obtaining Descriptive Statistics for Selected
Variables ............... 64
Case selection status in the Data Editor ..... 65
Data Editor display options ......... 65
Data Editor printing ........... 65
To print Data Editor contents ....... 66
Chapter 6. Working with Multiple Data
Sources .............. 67
Basic Handling of Multiple Data Sources .... 67
Working with Multiple Datasets in Command
Syntax ................ 67
Copying and Pasting Information between Datasets 67
Renaming Datasets ............ 68
Suppressing Multiple Datasets ........ 68
Chapter 7. Data preparation...... 69
Variable properties ............ 69
Defining Variable Properties ......... 69
To Define Variable Properties ....... 70
iii
Defining Value Labels and Other Variable
Properties .............. 70
Assigning the Measurement Level ...... 71
Custom Variable Attributes ........ 72
Copying Variable Properties ........ 72
Setting measurement level for variables with
unknown measurement level ........ 72
Multiple Response Sets .......... 73
Defining Multiple Response Sets ...... 73
Copy Data Properties ........... 75
Copying Data Properties ......... 75
Identifying Duplicate Cases ......... 78
Visual Binning ............. 79
To Bin Variables ............ 79
Binning Variables ........... 80
Automatically Generating Binned Categories .. 81
Copying Binned Categories ........ 82
User-Missing Values in Visual Binning .... 83
Chapter 8. Data Transformations ... 85
Data Transformations ........... 85
Computing Variables ........... 85
Compute Variable: If Cases ........ 85
Compute Variable: Type and Label ..... 86
Functions ............... 86
Missing Values in Functions ......... 86
Random Number Generators ........ 87
Count Occurrences of Values within Cases .... 87
Count Values within Cases: Values to Count .. 87
Count Occurrences: If Cases ........ 87
Shift Values .............. 88
Recoding Values ............. 88
Recode into Same Variables ......... 88
Recode into Same Variables: Old and New Values 89
Recode into Different Variables ........ 90
Recode into Different Variables: Old and New
Values ............... 90
Automatic Recode ............ 91
Rank Cases .............. 92
Rank Cases: Types ........... 93
Rank Cases: Ties............ 93
Date and Time Wizard........... 94
Dates and Times in IBM SPSS Statistics .... 94
Create a Date/Time Variable from a String ... 95
Create a Date/Time Variable from a Set of
Variables .............. 95
Add or Subtract Values from Date/Time
Variables .............. 96
Extract Part of a Date/Time Variable ..... 98
Time Series Data Transformations ....... 98
Define Dates ............. 98
Create Time Series ........... 99
Replace Missing Values ......... 101
Chapter 9. File handling and file
transformations .......... 103
File handling and file transformations ..... 103
Sort cases............... 103
Sort variables ............. 104
Transpose .............. 105
Merging Data Files ........... 105
Add Cases ............. 105
Add Variables ............ 106
Aggregate Data............. 108
Aggregate Data: Aggregate Function .... 109
Aggregate Data: Variable Name and Label... 109
Split file ............... 109
Select cases .............. 110
Select cases: If ............ 111
Select cases: Random sample ....... 111
Select cases: Range........... 111
Weight cases.............. 111
Restructuring Data ........... 112
To Restructure Data .......... 112
Restructure Data Wizard: Select Type .... 112
Restructure Data Wizard (Variables to Cases):
Number of Variable Groups ....... 115
Restructure Data Wizard (Variables to Cases):
Select Variables ............ 115
Restructure Data Wizard (Variables to Cases):
Create Index Variables ......... 116
Restructure Data Wizard (Variables to Cases):
Create One Index Variable ........ 117
Restructure Data Wizard (Variables to Cases):
Create Multiple Index Variables ...... 118
Restructure Data Wizard (Variables to Cases):
Options .............. 118
Restructure Data Wizard (Cases to Variables):
Select Variables ............ 119
Restructure Data Wizard (Cases to Variables):
Sort Data .............. 119
Restructure Data Wizard (Cases to Variables):
Options .............. 119
Restructure Data Wizard: Finish ...... 120
Chapter 10. Working with output ... 123
Working with output ........... 123
Viewer ............... 123
Showing and hiding results ....... 123
Moving, deleting, and copying output .... 123
Changing initial alignment ........ 124
Changing alignment of output items .... 124
Viewer outline ............ 124
Adding items to the Viewer ....... 125
Finding and replacing information in the Viewer 126
Closing output items .......... 127
Copying output into other applications..... 127
Interactive output ............ 128
Export output ............. 128
HTML options ............ 129
Web report options .......... 130
Word/RTF options .......... 131
Excel options ............ 131
PowerPoint options .......... 132
PDF options ............. 132
Text options ............. 133
Graphics only options ......... 134
Graphics format options......... 134
Viewer printing ............ 135
To print output and charts ........ 135
Print Preview ............ 135
iv IBM SPSS Statistics 24 Core System User's Guide
Page Attributes: Headers and Footers .... 135
Page Attributes: Options......... 136
Saving output ............. 136
To save a Viewer document ....... 137
Chapter 11. Pivot tables ....... 139
Pivot tables .............. 139
Manipulating a pivot table ......... 139
Activating a pivot table ......... 139
Pivoting a table............ 139
Changing display order of elements within a
dimension ............. 139
Moving rows and columns within a dimension
element .............. 139
Transposing rows and columns ...... 140
Grouping rows or columns ........ 140
Ungrouping rows or columns ....... 140
Rotating row or column labels....... 140
Sorting rows............. 140
Inserting rows and columns ....... 141
Controlling display of variable and value labels 141
Changing the output language ...... 141
Navigating large tables ......... 142
Undoing changes ........... 142
Working with layers ........... 142
Creating and displaying layers ...... 142
Go to layer category .......... 143
Showing and hiding items ......... 143
Hiding rows and columns in a table..... 143
Showing hidden rows and columns in a table 143
Hiding and showing dimension labels .... 143
Hiding and showing table titles ...... 143
TableLooks .............. 143
To apply a TableLook.......... 144
To edit or create a TableLook ....... 144
Table properties ............ 144
To change pivot table properties ...... 144
Table properties: general......... 144
Table properties: notes ......... 145
Table properties: cell formats ....... 146
Table properties: borders ........ 146
Table properties: printing ........ 146
Cell properties ............. 147
Font and background.......... 147
Format value ............ 147
Alignment and margins ......... 147
Footnotes and captions .......... 147
Adding footnotes and captions ...... 147
To hide or show a caption ........ 148
To hide or show a footnote in a table .... 148
Footnote marker ........... 148
Renumbering footnotes ......... 149
Editing footnotes in legacy tables...... 149
Data cell widths ............ 149
Changing column width.......... 150
Displaying hidden borders in a pivot table ... 150
Selecting rows, columns and cells in a pivot table 150
Printing pivot tables ........... 150
Controlling table breaks for wide and long
tables ............... 150
Creating a chart from a pivot table ...... 151
Legacy tables ............. 151
Chapter 12. Models ......... 153
Interacting with a model ......... 153
Working with the Model Viewer ...... 153
Printing a model ............ 154
Exporting a model............ 155
Saving fields used in the model to a new dataset 155
Saving predictors to a new dataset based on
importance .............. 155
Ensemble Viewer ............ 155
Models for Ensembles ......... 155
Split Model Viewer ........... 157
Chapter 13. Automated Output
Modification ............ 159
Style Output: Select ........... 159
Style Output ............. 160
Style Output: Labels and Text ....... 162
Style Output: Indexing ......... 162
Style Output: TableLooks ........ 163
Style Output: Size ........... 163
Table Style .............. 163
Table Style: Condition ......... 164
Table Style: Format .......... 164
Chapter 14. Working with Command
Syntax .............. 167
Syntax Rules ............. 167
Pasting Syntax from Dialog Boxes ...... 168
To Paste Syntax from Dialog Boxes ..... 168
Copying Syntax from the Output Log ..... 168
To Copy Syntax from the Output Log .... 169
Using the Syntax Editor .......... 169
Syntax Editor Window ......... 169
Terminology............. 170
Auto-Completion ........... 171
Color Coding ............ 171
Breakpoints ............. 172
Bookmarks ............. 173
Commenting or Uncommenting Text .... 174
Formatting Syntax........... 174
Running Command Syntax ........ 175
Character Set Encoding in Syntax Files .... 176
Multiple Execute Commands ....... 176
Character Set Encoding in Syntax Files ..... 177
Multiple Execute Commands ........ 177
Encrypting syntax files .......... 178
Chapter 15. Overview of the chart
facility .............. 181
Building and editing a chart ........ 181
Building Charts ........... 181
Editing Charts ............ 182
Chart definition options .......... 183
Adding and Editing Titles and Footnotes ... 183
Setting General Options ......... 184
Contents v
Chapter 16. Scoring data with
predictive models ......... 185
Scoring Wizard ............. 185
Matching model fields to dataset fields .... 186
Selecting scoring functions ........ 187
Scoring the active dataset ........ 188
Merging model and transformation XML files .. 188
Chapter 17. Utilities......... 191
Utilities ............... 191
Variable information ........... 191
Data file comments ........... 191
Variable sets .............. 192
Defining variable sets .......... 192
Using variable sets to show and hide variables .. 192
Reordering target variable lists ....... 193
Chapter 18. Options ........ 195
Options ............... 195
General options ............ 195
Viewer Options............. 196
Data Options ............. 197
Changing the default variable view ..... 199
Language options ............ 199
Currency options ............ 200
To create custom currency formats ..... 200
Output options ............. 200
Chart options ............. 201
Data Element Colors .......... 201
Data Element Lines .......... 201
Data Element Markers ......... 202
Data Element Fills ........... 202
Pivot table options ........... 203
File locations options ........... 204
Script options ............. 205
Syntax editor options........... 205
Multiple imputations options ........ 206
Chapter 19. Customizing Menus and
Toolbars ............. 209
Customizing Menus and Toolbars ...... 209
Menu Editor.............. 209
Customizing Toolbars .......... 209
Show Toolbars ............. 209
To Customize Toolbars .......... 209
Toolbar Properties ........... 210
Edit Toolbar ............. 210
Create New Tool ........... 210
Chapter 20. Extensions ....... 211
Extension Hub ............. 211
Explore tab ............. 212
Installed tab ............. 212
Settings .............. 213
Extension Details ........... 213
Installing local extension bundles....... 214
Installation locations for extensions ..... 214
Required R packages .......... 215
Batch installation of extension bundles .... 216
Creating and managing custom dialogs..... 216
Custom Dialog Builder layout ....... 217
Building a custom dialog ........ 217
Dialog Properties ........... 218
Specifying the Menu Location for a Custom
Dialog............... 219
Laying out controls on the canvas ..... 220
Building the Syntax Template ....... 220
Previewing a custom dialog ....... 222
Control types ............ 222
Extension Properties .......... 243
Managing custom dialogs ........ 246
Custom Dialogs for Extension Commands... 250
Creating Localized Versions of Custom Dialogs 251
Creating and editing extension bundles..... 252
Chapter 21. Production jobs ..... 255
Syntax files .............. 256
Output ............... 256
HTML options ............ 257
PowerPoint options .......... 257
PDF options ............. 257
Text options ............. 258
Production jobs with OUTPUT commands .. 258
Runtime values............. 258
Run options .............. 259
Server login .............. 259
Adding and Editing Server Login Settings... 259
User prompts ............. 260
Background job status .......... 260
Running production jobs from a command line .. 260
Converting Production Facility files ...... 261
Chapter 22. Output Management
System .............. 263
Output object types ........... 264
Command identifiers and table subtypes .... 265
Labels ................ 266
OMS options ............. 266
Logging ............... 268
Excluding output display from the viewer.... 269
Routing output to IBM SPSS Statistics data files 269
Data files created from multiple tables .... 269
Controlling column elements to control
variables in the data file ......... 270
Variable names in OMS-generated data files .. 270
OXML table structure .......... 270
OMS identifiers ............ 273
Copying OMS identifiers from the viewer
outline .............. 273
Chapter 23. Scripting Facility .... 275
Autoscripts .............. 276
Creating Autoscripts .......... 276
Associating Existing Scripts with Viewer Objects 277
Scripting with the Python Programming Language 277
Running Python Scripts and Python programs 278
Script Editor for the Python Programming
Language.............. 279
Scripting in Basic ............ 279
vi IBM SPSS Statistics 24 Core System User's Guide
Compatibility with Versions Prior to 16.0 ... 279
The scriptContext Object ........ 281
Startup Scripts ............. 282
Chapter 24. TABLES and IGRAPH
Command Syntax Converter ..... 285
Chapter 25. Encrypting data files,
output documents, and syntax files.. 287
Notices .............. 289
Trademarks .............. 291
Index ............... 293
Contents vii
viii IBM SPSS Statistics 24 Core System User's Guide
Chapter 1. Overview
Windows
There are a number of different types of windows in IBM®SPSS®Statistics:
Data Editor. The Data Editor displays the contents of the data file. You can create new data files or
modify existing data files with the Data Editor. If you have more than one data file open, there is a
separate Data Editor window for each data file.
Viewer. All statistical results, tables, and charts are displayed in the Viewer. You can edit the output and
save it for later use. A Viewer window opens automatically the first time you run a procedure that
generates output.
Pivot Table Editor. Output that is displayed in pivot tables can be modified in many ways with the Pivot
Table Editor. You can edit text, swap data in rows and columns, add color, create multidimensional tables,
and selectively hide and show results.
Chart Editor. You can modify high-resolution charts and plots in chart windows. You can change the
colors, select different type fonts or sizes, switch the horizontal and vertical axes, rotate 3-D scatterplots,
and even change the chart type.
Text Output Editor. Text output that is not displayed in pivot tables can be modified with the Text
Output Editor. You can edit the output and change font characteristics (type, style, color, size).
Syntax Editor. You can paste your dialog box choices into a syntax window, where your selections appear
in the form of command syntax. You can then edit the command syntax to use special features that are
not available through dialog boxes. You can save these commands in a file for use in subsequent sessions.
Designated window versus active window
If you have more than one open Viewer window, output is routed to the designated Viewer window. If
you have more than one open Syntax Editor window, command syntax is pasted into the designated
Syntax Editor window. The designated windows are indicated by a plus sign in the icon in the title bar.
You can change the designated windows at any time.
The designated window should not be confused with the active window, which is the currently selected
window. If you have overlapping windows, the active window appears in the foreground. If you open a
window, that window automatically becomes the active window and the designated window.
Changing the designated window
1. Make the window that you want to designate the active window (click anywhere in the window).
2. Click the Designate Window button on the toolbar (the plus sign icon).
or
3. From the menus choose:
Utilities > Designate Window
Note: For Data Editor windows, the active Data Editor window determines the dataset that is used in
subsequent calculations or analyses. There is no "designated" Data Editor window. See the topic “Basic
Handling of Multiple Data Sources” on page 67 for more information.
© Copyright IBM Corporation 1989, 2016 1
Variable names and variable labels in dialog box lists
You can display either variable names or variable labels in dialog box lists, and you can control the sort
order of variables in source variable lists. To control the default display attributes of variables in source
lists, choose Options on the Edit menu. See the topic “General options” on page 195 for more
information.
You can also change the variable list display attributes within dialogs. The method for changing the
display attributes depends on the dialog:
vIf the dialog provides sorting and display controls above the source variable list, use those controls to
change the display attributes.
vIf the dialog does not contain sorting controls above the source variable list, right-click any variable in
the source list and select the display attributes from the pop-up menu.
You can display either variable names or variable labels (names are displayed for any variables without
defined labels), and you can sort the source list by file order, alphabetical order, or measurement level. (In
dialogs with sorting controls above the source variable list, the default selection of None sorts the list in
file order.)
Data type, measurement level, and variable list icons
The icons that are displayed next to variables in dialog box lists provide information about the variable
type and measurement level.
Table 1. Measurement level icons
Numeric String Date Time
Scale (Continuous) n/a
Ordinal
Nominal
vFor more information on measurement level, see “Variable measurement level” on page 53.
vFor more information on numeric, string, date, and time data types, see “Variable type” on page 53.
Statistics Coach
If you are unfamiliar with IBM SPSS Statistics or with the available statistical procedures, the Statistics
Coach can help you get started by prompting you with simple questions, nontechnical language, and
visual examples that help you select the basic statistical and charting features that are best suited for your
data.
To use the Statistics Coach, from the menus in any IBM SPSS Statistics window choose:
Help > Statistics Coach
The Statistics Coach covers only a selected subset of procedures. It is designed to provide general
assistance for many of the basic, commonly used statistical techniques.
2IBM SPSS Statistics 24 Core System User's Guide
Finding out more
For a comprehensive overview of the basics, see the online tutorial. From any IBM SPSS Statistics menu
choose:
Help > Tutorial
Chapter 1. Overview 3
4IBM SPSS Statistics 24 Core System User's Guide
Chapter 2. Getting Help
The Help system contains a number of different sections.
Help Information on the user interface. There is a separate section for each optional module.
Reference
Reference information for the command language, GPL, VizML, and schemas. The reference
material for the command language is also available in PDF form: Help>Command Syntax
Reference.
Tutorial
Step-by-step instructions on how to use many of the basic features.
Case Studies
Hands-on examples of how to create various types of statistical analyses and how to interpret the
results.
Statistics Coach
Guides you through the process of finding the procedure that you want to use.
Integration Plug-ins
Separate sections for each programming plug-in, including Python, R, Java, and .Net
Context-sensitive help
In many places in the user interface, you can get context-sensitive help.
vHelp buttons in dialog boxes take you directly to the help topic for that dialog.
vRight-click on terms in an activated pivot table in the Viewer and choose What's This? from the
pop-up menu to display definitions of the terms.
vIn a command syntax window, position the cursor anywhere within a syntax block for a command and
press F1 on the keyboard. The help for that command is displayed
Other resources
Answers to many common problems can be found at http://www.ibm.com/support .
If you're a student using a student, academic or grad pack version of any IBM SPSS software product,
please see our special online Solutions for Education pages for students. If you're a student using a
university-supplied copy of the IBM SPSS software, please contact the IBM SPSS product coordinator at
your university.
The IBM SPSS Predictive Analytics community has resources for all levels of users and application
developers. Download utilities, graphics examples, new statistical modules, and articles. Visit the IBM
SPSS Predictive Analytics community at https://developer.ibm.com/predictiveanalytics/.
Documentation in PDF format is available at http://www.ibm.com/support/
docview.wss?uid=swg27047033.
Documentation of statistical algorithms is available at http://www.ibm.com/support/
docview.wss?uid=swg27047033.
© Copyright IBM Corporation 1989, 2016 5
6IBM SPSS Statistics 24 Core System User's Guide
Chapter 3. Data files
Data files come in a wide variety of formats, and this software is designed to handle many of them,
including:
vExcel spreadsheets
vDatabase tables from many database sources, including Oracle, SQLServer, DB2, and others
vTab-delimited, CSV, and other types of simple text files
vSAS data files
vStata data files
Opening data files
In addition to files saved in IBM SPSS Statistics format, you can open Excel, SAS, Stata, tab-delimited,
and other files without converting the files to an intermediate format or entering data definition
information.
vOpening a data file makes it the active dataset. If you already have one or more open data files, they
remain open and available for subsequent use in the session. Clicking anywhere in the Data Editor
window for an open data file will make it the active dataset. See the topic Chapter 6, “Working with
Multiple Data Sources,” on page 67 for more information.
vIn distributed analysis mode using a remote server to process commands and run procedures, the
available data files, folders, and drives are dependent on what is available on or from the remote
server. The current server name is indicated at the top of the dialog box. You will not have access to
data files on your local computer unless you specify the drive as a shared device and the folders
containing your data files as shared folders. See the topic Chapter 4, “Distributed Analysis Mode,” on
page 45 for more information.
To open data files
1. From the menus choose:
File > Open > Data...
2. In the Open Data dialog box, select the file that you want to open.
3. Click Open.
Optionally, you can:
vAutomatically set the width of each string variable to the longest observed value for that variable
using Minimize string widths based on observed values. This is particularly useful when reading
code page data files in Unicode mode. See the topic “General options” on page 195 for more
information.
vRead variable names from the first row of spreadsheet files.
vSpecify a range of cells to read from spreadsheet files.
vSpecify a worksheet within an Excel file to read (Excel 95 or later).
For information on reading data from databases, see “Reading Database Files” on page 14. For
information on reading data from text data files, see “Text Wizard” on page 10. For information on
reading IBM Cognos®data, see “Reading Cognos BI data” on page 20.
Data file types
SPSS Statistics. Opens data files that are saved in IBM SPSS Statistics format and also the DOS product
SPSS/PC+.
© Copyright IBM Corporation 1989, 2016 7
SPSS Statistics Compressed. Opens data files that are saved in IBM SPSS Statistics compressed format.
SPSS/PC+. Opens SPSS/PC+ data files. This option is available only on Windows operating systems.
Portable. Opens data files that are saved in portable format. Saving a file in portable format takes
considerably longer than saving the file in IBM SPSS Statistics format.
Excel. Opens Excel files.
Lotus 1-2-3. Opens data files that are saved in 1-2-3 format for release 3.0, 2.0, or 1A of Lotus.
SYLK. Opens data files that are saved in SYLK (symbolic link) format, a format that is used by some
spreadsheet applications.
dBASE. Opens dBASE-format files for either dBASE IV, dBASE III or III PLUS, or dBASE II. Each case is
a record. Variable and value labels and missing-value specifications are lost when you save a file in this
format.
SAS. SAS versions 6–9 and SAS transport files.
Stata. Stata versions 4–13.
Reading Excel Files
This topic applies to Excel 95 and later files. To read Excel 4 or earlier versions, see the topic “Reading
Older Excel Files and Other Spreadsheets” on page 9.
Worksheet
Excel files can contain multiple worksheets. By default, the Data Editor reads the first worksheet.
To read a different worksheet, select the worksheet from the list.
Range You can also read a range of cells. Use the same method for specifying cell ranges as you would
in Excel. For example: A1:D10.
Read variable names from first row of data
You can read variable names from the first row of the file or the first row of the defined range.
Values that don't conform to variable naming rules are converted to valid variable names, and the
original names are used as variable labels.
Percentage of values that determine data type
The data type for each variable is determined by the percentage of values that conform to the
same format.
vThe value must be greater than 50.
vThe denominator used to determine the percentage is the number of non-blank values for each
variable.
vIf no consistent format is used by the specified percentage of values, the variable is assigned
the string data type.
vFor variables that are assigned a numeric format (including date and time formats) based on
the percentage value, values that do not conform to that format are assigned the
system-missing value.
Ignore hidden rows and columns
Hidden rows and columns in the Excel file are not included. This option is available only for
Excel 2007 and later files (XLSX, XLSM).
Remove leading spaces from string values
Any blank spaces at the beginning of string values are removed.
8IBM SPSS Statistics 24 Core System User's Guide
Remove trailing spaces from string values
Blank spaces at the end of the string values are removed. This setting affects the calculation of the
defined width of string variables.
Reading Older Excel Files and Other Spreadsheets
This topic applies to reading Excel 4 or earlier files, Lotus 1-2-3 files and SYLK format spreadsheet files.
For information on reading Excel 95 or later files, see the topic “Reading Excel Files” on page 8.
Read variable names. For spreadsheets, you can read variable names from the first row of the file or the
first row of the defined range. The values are converted as necessary to create valid variable names,
including converting spaces to underscores.
Range. For spreadsheet data files, you can also read a range of cells. Use the same method for specifying
cell ranges as you would with the spreadsheet application.
How Spreadsheets are Read
vThe data type and width for each variable are determined by the column width and data type of the
first data cell in the column. Values of other types are converted to the system-missing value. If the
first data cell in the column is blank, the global default data type for the spreadsheet (usually numeric)
is used.
vFor numeric variables, blank cells are converted to the system-missing value, indicated by a period. For
string variables, a blank is a valid string value, and blank cells are treated as valid string values.
vIf you do not read variable names from the spreadsheet, the column letters (A, B, C, ...) are used for
variable names for Excel and Lotus files. For SYLK files and Excel files saved in R1C1 display format,
the software uses the column number preceded by the letter Cfor variable names (C1, C2, C3, ...).
Reading dBASE files
Database files are logically very similar to IBM SPSS Statistics data files. The following general rules
apply to dBASE files:
vField names are converted to valid variable names.
vColons used in dBASE field names are translated to underscores.
vRecords marked for deletion but not actually purged are included. The software creates a new string
variable, D_R, which contains an asterisk for cases marked for deletion.
Reading Stata files
The following general rules apply to Stata data files:
vVariable names. Stata variable names are converted to IBM SPSS Statistics variable names in
case-sensitive form. Stata variable names that are identical except for case are converted to valid
variable names by appending an underscore and a sequential letter (_A, _B, _C, ..., _Z, _AA, _AB, ...,
and so forth).
vVariable labels. Stata variable labels are converted to IBM SPSS Statistics variable labels.
vValue labels. Stata value labels are converted to IBM SPSS Statistics value labels, except for Stata
value labels assigned to "extended" missing values. Value labels longer than 120 bytes are truncated.
vString variables. Stata strl variables are converted to string variables. Values longer than 32K bytes are
truncated. Stata strl values that contain blobs (binary large objects) are converted to blank strings.
vMissing values. Stata "extended" missing values are converted to system-missing values.
vDate conversion. Stata date format values are converted to IBM SPSS Statistics DATE format (d-m-y)
values. Stata "time-series" date format values (weeks, months, quarters, and so on) are converted to
simple numeric (F) format, preserving the original, internal integer value, which is the number of
weeks, months, quarters, and so on, since the start of 1960.
Chapter 3. Data files 9
Reading CSV Files
To read CSV files, from the menus choose: File > Import Data > CSV
The Read CSV File dialog reads CSV format text data files that use a comma, a semicolon, or a tab as the
delimiter between values.
If the text file uses a different delimiter, contains text at the beginning of the file that is not variable
names or data values, or has other special considerations, use the Text Wizard to read the files.
First line contains variable names
The first non-blank line in the file contains label text that is used as variable names. Values that
are invalid as variable names are automatically converted to valid variable names.
Remove leading spaces from string values
Any blank spaces at the beginning of string values are removed.
Remove trailing spaces from string values
Blank spaces at the end of the string values are removed. This setting affects the calculation of the
defined width of string variables.
Delimiter between values
The delimiter can be a comma, a semicolon, or a tab. If the delimiter is any other character or a
blank space, use the Text Wizard to read the file.
Decimal symbol
The symbol that is used to indicate decimals in the text data file. The symbol can be a period or a
comma.
Text Qualifier
Character that is used to enclose values that contain the delimiter character. The qualifier appears
at the start and the end of the value. The qualifier can be double quotation mark, single quotation
mark, or none.
Percentage of values that determine data type
The data type for each variable is determined by the percentage of values that conform to the
same format.
vThe value must be greater than 50.
vIf no consistent format is used by the specified percentage of values, the variable is assigned
the string data type.
vFor variables that are assigned a numeric format (including date and time formats) based on
the percentage value, values that do not conform to that format are assigned the
system-missing value.
Cache data locally
A data cache is a complete copy of the data file that is stored in temporary disk space. Caching
the data file can improve performance.
Text Wizard
The Text Wizard can read text data files formatted in a variety of ways:
vTab-delimited files
vSpace-delimited files
vComma-delimited files
vFixed-field format files
For delimited files, you can also specify other characters as delimiters between values, and you can
specify multiple delimiters.
10 IBM SPSS Statistics 24 Core System User's Guide
To Read Text Data Files
1. From the menus choose:
File > Import Data > Text Data...
2. Select the text file in the Open Data dialog box.
3. If necessary, select the encoding of the file.
4. Follow the steps in the Text Wizard to define how to read the data file.
Encoding
The encoding of a file affects the way character data are read. Unicode data files typically contain a byte
order mark that identifies the character encoding. Some applications create Unicode files without a byte
order mark, and code page data files do not contain any encoding identifier.
vUnicode (UTF-8). Reads the file as Unicode UTF-8.
vUnicode (UTF-16). Reads the file as Unicode UTF-16 in the endianness of the operating system.
vUnicode (UTF-16BE). Reads the file as Unicode UTF-16, big endian.
vUnicode (UTF-16LE). Reads the file as Unicode UTF-16, little endian.
vLocal Encoding. Reads the file in current locale code page character encoding.
If a file contains a Unicode byte order mark, it is read in that Unicode encoding, regardless of the
encoding you select. If a file does not contain a Unicode byte order mark, by default the encoding is
assumed to be the current locale code page character encoding, unless you select one of the Unicode
encodings.
To change the current locale for data files in a different code page character encoding, select Edit>Options
from the menus, and change the locale on the Language tab.
Text Wizard: Step 1
The text file is displayed in a preview window. You can apply a predefined format (previously saved
from the Text Wizard) or follow the steps in the Text Wizard to specify how the data should be read.
Text Wizard: Step 2
This step provides information about variables. A variable is similar to a field in a database. For example,
each item in a questionnaire is a variable.
How are your variables arranged?
The arrangement of variables defines the method that is used to differentiate one variable from
the next.
Delimited
Spaces, commas, tabs, or other characters are used to separate variables. The variables are
recorded in the same order for each case but not necessarily in the same column
locations.
Fixed width
Each variable is recorded in the same column location on the same record (line) for each
case in the data file. No delimiter is required between variables. The column location
determines which variable is being read.
Note: The Text Wizard cannot read fixed-width Unicode text files. You can use the DATA
LIST command to read fixed-width Unicode files.
Are variable names included at the top of your file?
The values on the specified line number are used to create variable names. Values that don't
conform to variable naming rules are converted to valid variable names.
What is the decimal symbol?
The character that indicates decimal values can be either a period or a comma.
Chapter 3. Data files 11
Text Wizard: Step 3 (Delimited Files)
This step provides information about cases. A case is similar to a record in a database. For example, each
respondent to a questionnaire is a case.
The first case of data begins on which line number? Indicates the first line of the data file that contains
data values. If the top line(s) of the data file contain descriptive labels or other text that does not
represent data values, this will not be line 1.
How are your cases represented? Controls how the Text Wizard determines where each case ends and
the next one begins.
vEach line represents a case. Each line contains only one case. It is fairly common for each case to be
contained on a single line (row), even though this can be a very long line for data files with a large
number of variables. If not all lines contain the same number of data values, the number of variables
for each case is determined by the line with the greatest number of data values. Cases with fewer data
values are assigned missing values for the additional variables.
vA specific number of variables represents a case. The specified number of variables for each case
tells the Text Wizard where to stop reading one case and start reading the next. Multiple cases can be
contained on the same line, and cases can start in the middle of one line and be continued on the next
line. The Text Wizard determines the end of each case based on the number of values read, regardless
of the number of lines. Each case must contain data values (or missing values indicated by delimiters)
for all variables, or the data file will be read incorrectly.
How many cases do you want to import? You can import all cases in the data file, the first ncases (nis a
number you specify), or a random sample of a specified percentage. Since the random sampling routine
makes an independent pseudo-random decision for each case, the percentage of cases selected can only
approximate the specified percentage. The more cases there are in the data file, the closer the percentage
of cases selected is to the specified percentage.
Text Wizard: Step 3 (Fixed-Width Files)
This step provides information about cases. A case is similar to a record in a database. For example, each
respondent to questionnaire is a case.
The first case of data begins on which line number? Indicates the first line of the data file that contains
data values. If the top line(s) of the data file contain descriptive labels or other text that does not
represent data values, this will not be line 1.
How many lines represent a case? Controls how the Text Wizard determines where each case ends and
the next one begins. Each variable is defined by its line number within the case and its column location.
You need to specify the number of lines for each case to read the data correctly.
How many cases do you want to import? You can import all cases in the data file, the first ncases (nis a
number you specify), or a random sample of a specified percentage. Since the random sampling routine
makes an independent pseudo-random decision for each case, the percentage of cases selected can only
approximate the specified percentage. The more cases there are in the data file, the closer the percentage
of cases selected is to the specified percentage.
Text Wizard: Step 4 (Delimited Files)
This step specifies delimiters and text qualifiers that are used in the text data file. You can also specify
the treatment of leading and trailing spaces in string values.
Which delimiters appear between variables?
The characters or symbols that separate data values. You can select any combination of spaces,
commas, semicolons, tabs, or other characters. Multiple, consecutive delimiters without
intervening data values are treated as missing values.
12 IBM SPSS Statistics 24 Core System User's Guide
What is the text qualifier?
Characters that are used to enclose values that contain delimiter characters. The text qualifier
appears at both the beginning and the end of the value, enclosing the entire value.
Leading and Trailing Spaces
Controls the treatment of leading and trailing blank spaces in string values.
Remove leading spaces from string values
Any blank spaces at the beginning of string values are removed.
Remove trailing spaces from string values
Blank spaces at the end of a value are ignored when the defined width of string variables
is calculated. If Space is selected as a delimiter, multiple consecutive blank spaces are not
treated as multiple delimiters.
Text Wizard: Step 4 (Fixed-Width Files)
This step displays the Text Wizard's best guess on how to read the data file and allows you to modify
how the Text Wizard will read variables from the data file. Vertical lines in the preview window indicate
where the Text Wizard currently thinks each variable begins in the file.
Insert, move, and delete variable break lines as necessary to separate variables. If multiple lines are used
for each case, the data will be displayed as one line for each case, with subsequent lines appended to the
end of the line.
Notes:
For computer-generated data files that produce a continuous stream of data values with no intervening
spaces or other distinguishing characteristics, it may be difficult to determine where each variable begins.
Such data files usually rely on a data definition file or some other written description that specifies the
line and column location for each variable.
Text Wizard: Step 5
This step controls the variable name and the data format that is used to read each variable. You can also
specify variables to exclude.
Variable name
You can overwrite the default variable names with your own variable names. If you read variable
names from the data file, names that do not conform to variable naming rules are automatically
modified. Select a variable in the preview window and then enter a variable name.
Data format
Select a variable in the preview window and then select a format from the list.
vAutomatic determines the data format based on an evaluation of all the data values.
vTo exclude a variable, select Do Not Import.
Percentage of values that determine Automatic data format
For automatic format, the data format for each variable is determined by the percentage of values
that conform to the same format.
vThe value must be greater than 50.
vThe denominator used to determine the percentage is the number of non-blank values for each
variable.
vIf no consistent format is used by the specified percentage of values, the variable is assigned
the string data type.
vFor variables that are assigned a numeric format (including date and time formats) based on
the percentage value, values that do not conform to that format are assigned the
system-missing value.
Chapter 3. Data files 13
Text Wizard Formatting Options: Formatting options for reading variables with the Text Wizard
include:
Automatic. The format is determined based on an evaluation of all the data values.
Do not import. Omit the selected variable(s) from the imported data file.
Numeric. Valid values include numbers, a leading plus or minus sign, and a decimal indicator.
String. Valid values include virtually any keyboard characters and embedded blanks. For delimited files,
you can specify the number of characters in the value, up to a maximum of 32,767. By default, the Text
Wizard sets the number of characters to the longest string value encountered for the selected variable(s)
in the first 250 rows of the file. For fixed-width files, the number of characters in string values is defined
by the placement of variable break lines in step 4.
Date/Time. Valid values include dates of the general format dd-mm-yyyy, mm/dd/yyyy, dd.mm.yyyy,
yyyy/mm/dd, hh:mm:ss, and a variety of other date and time formats. Months can be represented in digits,
Roman numerals, or three-letter abbreviations, or they can be fully spelled out. Select a date format from
the list.
Dollar. Valid values are numbers with an optional leading dollar sign and optional commas as thousands
separators.
Comma. Valid values include numbers that use a period as a decimal indicator and commas as thousands
separators.
Dot. Valid values include numbers that use a comma as a decimal indicator and periods as thousands
separators.
Note: Values that contain invalid characters for the selected format will be treated as missing. Values that
contain any of the specified delimiters will be treated as multiple values.
Text Wizard: Step 6
This is the final step of the Text Wizard. You can save your specifications in a file for use when importing
similar text data files. You can also paste the syntax generated by the Text Wizard into a syntax window.
You can then customize and/or save the syntax for use in other sessions or in production jobs.
Cache data locally. A data cache is a complete copy of the data file, stored in temporary disk space.
Caching the data file can improve performance.
Reading Database Files
You can read data from any database format for which you have a database driver. In local analysis
mode, the necessary drivers must be installed on your local computer. In distributed analysis mode
(available with IBM SPSS Statistics Server), the drivers must be installed on the remote server. See the
topic Chapter 4, “Distributed Analysis Mode,” on page 45 for more information.
Note: If you are running the Windows 64-bit version of IBM SPSS Statistics, you cannot read Excel,
Access, or dBASE database sources, even though they may appear on the list of available database
sources. The 32-bit ODBC drivers for these products are not compatible.
To Read Database Files
1. From the menus choose:
File > Import Data > Database > New Query...
2. Select the data source.
14 IBM SPSS Statistics 24 Core System User's Guide
3. If necessary (depending on the data source), select the database file and/or enter a login name,
password, and other information.
4. Select the table(s) and fields. For OLE DB data sources (available only on Windows operating
systems), you can only select one table.
5. Specify any relationships between your tables.
6. Optionally:
vSpecify any selection criteria for your data.
vAdd a prompt for user input to create a parameter query.
vSave your constructed query before running it.
Connection Pooling
If you access the same database source multiple times in the same session or job, you can improve
performance with connection pooling.
1. In the last step of the wizard, paste the command syntax into a syntax window.
2. At the end of the quoted CONNECT string, add Pooling=true.
To Edit Saved Database Queries
1. From the menus choose:
File > Import Data > Database > Edit Query...
2. Select the query file (*.spq) that you want to edit.
3. Follow the instructions for creating a new query.
To Read Database Files with Saved Queries
1. From the menus choose:
File > Import Data > Database > Run Query...
2. Select the query file (*.spq) that you want to run.
3. If necessary (depending on the database file), enter a login name and password.
4. If the query has an embedded prompt, enter other information if necessary (for example, the quarter
for which you want to retrieve sales figures).
Selecting a Data Source
Use the first screen of the Database Wizard to select the type of data source to read.
ODBC Data Sources
If you do not have any ODBC data sources configured, or if you want to add a new data source, click
Add ODBC Data Source.
vOn Linux operating systems, this button is not available. ODBC data sources are specified in odbc.ini,
and the ODBCINI environment variables must be set to the location of that file. For more information,
see the documentation for your database drivers.
vIn distributed analysis mode (available with IBM SPSS Statistics Server), this button is not available.
To add data sources in distributed analysis mode, see your system administrator.
An ODBC data source consists of two essential pieces of information: the driver that will be used to
access the data and the location of the database you want to access. To specify data sources, you must
have the appropriate drivers installed. Drivers for a variety of database formats are included with the
installation media.
To access OLE DB data sources (available only on Microsoft Windows operating systems), you must have
the following items installed:
Chapter 3. Data files 15
v.NET framework. To obtain the most recent version of the .NET framework, go to http://
www.microsoft.com/net.
vData Collection Survey Reporter Developer Kit.
The following limitations apply to OLE DB data sources:
vTable joins are not available for OLE DB data sources. You can read only one table at a time.
vYou can add OLE DB data sources only in local analysis mode. To add OLE DB data sources in
distributed analysis mode on a Windows server, consult your system administrator.
vIn distributed analysis mode (available with IBM SPSS Statistics Server), OLE DB data sources are
available only on Windows servers, and both .NET and Data Collection Survey Reporter Developer Kit
must be installed on the server.
To add an OLE DB data source:
1. Click Add OLE DB Data Source.
2. In Data Link Properties, click the Provider tab and select the OLE DB provider.
3. Click Next or click the Connection tab.
4. Select the database by entering the directory location and database name or by clicking the button to
browse to a database. (A user name and password may also be required.)
5. Click OK after entering all necessary information. (You can make sure the specified database is
available by clicking the Test Connection button.)
6. Enter a name for the database connection information. (This name will be displayed in the list of
available OLE DB data sources.)
7. Click OK.
This takes you back to the first screen of the Database Wizard, where you can select the saved name from
the list of OLE DB data sources and continue to the next step of the wizard.
Deleting OLE DB Data Sources
To delete data source names from the list of OLE DB data sources, delete the UDL file with the name of
the data source in:
[drive]:\Documents and Settings\[user login]\Local Settings\Application Data\SPSS\UDL
Selecting Data Fields
The Select Data step controls which tables and fields are read. Database fields (columns) are read as
variables.
If a table has any field(s) selected, all of its fields will be visible in the following Database Wizard
windows, but only fields that are selected in this step will be imported as variables. This enables you to
create table joins and to specify criteria by using fields that you are not importing.
Displaying field names. To list the fields in a table, click the plus sign (+) to the left of a table name. To
hide the fields, click the minus sign (–) to the left of a table name.
To add a field. Double-click any field in the Available Tables list, or drag it to the Retrieve Fields In This
Order list. Fields can be reordered by dragging and dropping them within the fields list.
To remove a field. Double-click any field in the Retrieve Fields In This Order list, or drag it to the
Available Tables list.
Sort field names. If this check box is selected, the Database Wizard will display your available fields in
alphabetical order.
16 IBM SPSS Statistics 24 Core System User's Guide
By default, the list of available tables displays only standard database tables. You can control the type of
items that are displayed in the list:
vTables. Standard database tables.
vViews. Views are virtual or dynamic "tables" defined by queries. These can include joins of multiple
tables and/or fields derived from calculations based on the values of other fields.
vSynonyms. A synonym is an alias for a table or view, typically defined in a query.
vSystem tables. System tables define database properties. In some cases, standard database tables may
be classified as system tables and will only be displayed if you select this option. Access to real system
tables is often restricted to database administrators.
Note: For OLE DB data sources (available only on Windows operating systems), you can select fields only
from a single table. Multiple table joins are not supported for OLE DB data sources.
Creating a Relationship between Tables
The Specify Relationships step allows you to define the relationships between the tables for ODBC data
sources. If fields from more than one table are selected, you must define at least one join.
Establishing relationships. To create a relationship, drag a field from any table onto the field to which
you want to join it. The Database Wizard will draw a join line between the two fields, indicating their
relationship. These fields must be of the same data type.
Auto Join Tables. Attempts to automatically join tables based on primary/foreign keys or matching field
names and data type.
Join Type. If outer joins are supported by your driver, you can specify inner joins, left outer joins, or
right outer joins.
vInner joins. An inner join includes only rows where the related fields are equal. In this example, all
rows with matching ID values in the two tables will be included.
vOuter joins. In addition to one-to-one matching with inner joins, you can also use outer joins to merge
tables with a one-to-many matching scheme. For example, you could match a table in which there are
only a few records representing data values and associated descriptive labels with values in a table
containing hundreds or thousands of records representing survey respondents. A left outer join
includes all records from the table on the left and, from the table on the right, includes only those
records in which the related fields are equal. In a right outer join, the join imports all records from the
table on the right and, from the table on the left, imports only those records in which the related fields
are equal.
Computing New Fields
If you are in distributed mode, connected to a remote server (available with IBM SPSS Statistics Server),
you can compute new fields before you read the data into IBM SPSS Statistics.
You can also compute new fields after you read the data into IBM SPSS Statistics, but computing new
fields in the database can save time for large data sources.
New Field Name. The name must comply with IBM SPSS Statistics variable name rules.
Expression. Enter the expression to compute the new field. You can drag existing field names from the
Fields list and functions from the Functions list.
Limiting Retrieved Cases
The Limit Retrieved Cases step allows you to specify the criteria to select subsets of cases (rows).
Limiting cases generally consists of filling the criteria grid with criteria. Criteria consist of two
expressions and some relation between them. The expressions return a value of true, false, or missing for
each case.
Chapter 3. Data files 17
vIf the result is true, the case is selected.
vIf the result is false or missing, the case is not selected.
vMost criteria use one or more of the six relational operators (<, >, <=, >=, =, and <>).
vExpressions can include field names, constants, arithmetic operators, numeric and other functions, and
logical variables. You can use fields that you do not plan to import as variables.
To build your criteria, you need at least two expressions and a relation to connect the expressions.
1. To build an expression, choose one of the following methods:
vIn an Expression cell, type field names, constants, arithmetic operators, numeric and other
functions, or logical variables.
vDouble-click the field in the Fields list.
vDrag the field from the Fields list to an Expression cell.
vChoose a field from the drop-down menu in any active Expression cell.
2. To choose the relational operator (such as = or >), put your cursor in the Relation cell and either type
the operator or choose it from the drop-down menu.
If the SQL contains WHERE clauses with expressions for case selection, dates and times in expressions
need to be specified in a special manner (including the curly braces shown in the examples):
vDate literals should be specified using the general form {d yyyy-mm-dd}.
vTime literals should be specified using the general form {t hh:mm:ss}.
vDate/time literals (timestamps) should be specified using the general form {ts yyyy-mm-dd
hh:mm:ss}.
vThe entire date and/or time value must be enclosed in single quotes. Years must be expressed in
four-digit form, and dates and times must contain two digits for each portion of the value. For
example January 1, 2005, 1:05 AM would be expressed as:
{ts 2005-01-01 01:05:00}
Functions. A selection of built-in arithmetic, logical, string, date, and time SQL functions is provided.
You can drag a function from the list into the expression, or you can enter any valid SQL function.
See your database documentation for valid SQL functions.
Use Random Sampling. This option selects a random sample of cases from the data source. For large
data sources, you may want to limit the number of cases to a small, representative sample, which can
significantly reduce the time that it takes to run procedures. Native random sampling, if available for
the data source, is faster than IBM SPSS Statistics random sampling, because IBM SPSS Statistics
random sampling must still read the entire data source to extract a random sample.
vApproximately. Generates a random sample of approximately the specified percentage of cases.
Since this routine makes an independent pseudorandom decision for each case, the percentage of
cases selected can only approximate the specified percentage. The more cases there are in the data
file, the closer the percentage of cases selected is to the specified percentage.
vExactly. Selects a random sample of the specified number of cases from the specified total number
of cases. If the total number of cases specified exceeds the total number of cases in the data file, the
sample will contain proportionally fewer cases than the requested number.
Note: If you use random sampling, aggregation (available in distributed mode with IBM SPSS
Statistics Server) is not available.
Prompt For Value. You can embed a prompt in your query to create a parameter query. When users
run the query, they will be asked to enter information (based on what is specified here). You might
want to do this if you need to see different views of the same data. For example, you may want to
run the same query to see sales figures for different fiscal quarters.
3. Place your cursor in any Expression cell, and click Prompt For Value to create a prompt.
18 IBM SPSS Statistics 24 Core System User's Guide
Creating a Parameter Query
Use the Prompt for Value step to create a dialog box that solicits information from users each time
someone runs your query. This feature is useful if you want to query the same data source by using
different criteria.
To build a prompt, enter a prompt string and a default value. The prompt string is displayed each time a
user runs your query. The string should specify the kind of information to enter. If the user is not
selecting from a list, the string should give hints about how the input should be formatted. An example is
as follows: Enter a Quarter (Q1, Q2, Q3, ...).
Allow user to select value from list. If this check box is selected, you can limit the user to the values that
you place here. Ensure that your values are separated by returns.
Data type. Choose the data type here (Number, String, or Date).
Date and time values must be entered in special manner:
vDate values must use the general form yyyy-mm-dd.
vTime values must use the general form: hh:mm:ss.
vDate/time values (timestamps) must use the general form yyyy-mm-dd hh:mm:ss.
Aggregating Data
If you are in distributed mode, connected to a remote server (available with IBM SPSS Statistics Server),
you can aggregate the data before reading it into IBM SPSS Statistics.
You can also aggregate data after reading it into IBM SPSS Statistics, but preaggregating may save time
for large data sources.
1. To create aggregated data, select one or more break variables that define how cases are grouped.
2. Select one or more aggregated variables.
3. Select an aggregate function for each aggregate variable.
4. Optionally, create a variable that contains the number of cases in each break group.
Note: If you use IBM SPSS Statistics random sampling, aggregation is not available.
Defining Variables
Variable names and labels. The complete database field (column) name is used as the variable label.
Unless you modify the variable name, the Database Wizard assigns variable names to each column from
the database in one of two ways:
vIf the name of the database field forms a valid, unique variable name, the name is used as the variable
name.
vIf the name of the database field does not form a valid, unique variable name, a new, unique name is
automatically generated.
Click any cell to edit the variable name.
Converting strings to numeric values. Select the Recode to Numeric box for a string variable if you
want to automatically convert it to a numeric variable. String values are converted to consecutive integer
values based on alphabetical order of the original values. The original values are retained as value labels
for the new variables.
Width for variable-width string fields. This option controls the width of variable-width string values. By
default, the width is 255 bytes, and only the first 255 bytes (typically 255 characters in single-byte
languages) will be read. The width can be up to 32,767 bytes. Although you probably don't want to
truncate string values, you also don't want to specify an unnecessarily large value, which will cause
processing to be inefficient.
Chapter 3. Data files 19
Minimize string widths based on observed values. Automatically set the width of each string variable
to the longest observed value.
Sorting Cases
If you are in distributed mode, connected to a remote server (available with IBM SPSS Statistics Server),
you can sort the data before reading it into IBM SPSS Statistics.
You can also sort data after reading it into IBM SPSS Statistics, but presorting may save time for large
data sources.
Results
The Results step displays the SQL Select statement for your query.
vYou can edit the SQL Select statement before you run the query, but if you click the Back button to
make changes in previous steps, the changes to the Select statement will be lost.
vTo save the query for future use, use the Save query to file section.
vTo paste complete GET DATA syntax into a syntax window, select Paste it into the syntax editor for
further modification. Copying and pasting the Select statement from the Results window will not
paste the necessary command syntax.
Note: The pasted syntax contains a blank space before the closing quote on each line of SQL that is
generated by the wizard. These blanks are not superfluous. When the command is processed, all lines of
the SQL statement are merged together in a very literal fashion. Without the space, there would be no
space between the last character on one line and first character on the next line.
Reading Cognos BI data
If you have access to a IBM Cognos Business Intelligence server, you can read IBM Cognos Business
Intelligence data packages and list reports into IBM SPSS Statistics.
To read IBM Cognos Business Intelligence data:
1. From the menus choose:
File > Import Data > Cognos Business Intelligence
2. Specify the URL for the IBM Cognos Business Intelligence server connection.
3. Specify the location of the data package or report.
4. Select the data fields or report that you want to read.
Optionally, you can:
vSelect filters for data packages.
vImport aggregated data instead of raw data.
vSpecify parameter values.
Mode. Specifies the type of information you want to read: Data or Report. The only type of report that
can be read is a list report.
Connection. The URL of the Cognos Business Intelligence server. Click the Edit button to define the
details of a new Cognos connection from which to import data or reports. See the topic “Cognos
connections” on page 21 for more information.
Location. The location of the package or report that you want to read. Click the Edit button to display a
list of available sources from which to import content. See the topic “Cognos location” on page 21 for
more information.
Content. For data, displays the available data packages and filters. For reports, display the available
reports.
20 IBM SPSS Statistics 24 Core System User's Guide
Fields to import. For data packages, select the fields you want to include and move them to this list.
Report to import. For reports, select the list report you want to import. The report must be a list report.
Filters to apply. For data packages, select the filters you want to apply and move them to this list.
Parameters. If this button is enabled, the selected object has parameters defined. You can use parameters
to make adjustments (for example, perform a parameterized calculation) before importing the data. If
parameters are defined but no default is provided, the button displays a warning triangle.
Aggregate data before performing import. For data packages, if aggregation is defined in the package,
you can import the aggregated data instead of the raw data.
Cognos connections
The Cognos Connections dialog specifies the IBM Cognos Business Intelligence server URL and any
required additional credentials.
Cognos server URL. The URL of the IBM Cognos Business Intelligence server. This is the value of the
"external dispatcher URI" environment property of IBM Cognos Configuration on the server. Contact your
system administrator for more information
Mode. Select Set Credentials if you need to log in with a specific namespace, username and password
(for example, as an administrator). Select Use Anonymous connection to log in with no user credentials,
in which case you do not fill in the other fields. Select Stored Credentials to use the login information
from a stored credential. To use a stored credential, you must be connected to the IBM SPSS
Collaboration and Deployment Services Repository that contains the credential. After you are connected
to the repository, click Browse to see the list of available credentials.
Namespace ID. The security authentication provider used to log on to the server. The authentication
provider is used to define and maintain users, groups, and roles, and to control the authentication
process.
User name. Enter the user name with which to log on to the server.
Password. Enter the password associated with the specified user name.
Save as Default. Saves these settings as your default, to avoid having to re-enter them each time.
Cognos location
The Specify Location dialog box enables you to select a package from which to import data, or a package
or folder from which to import reports. It displays the public folders that are available to you. If you
select Data in the main dialog, the list will display folders containing data packages. If you select Report
in the main dialog, the list will display folders containing list reports. Select the location you want by
navigating through the folder structure.
Specifying parameters for data or reports
If parameters have been defined, either for a data object or a report, you can specify values for these
parameters before importing the data or report. An example of parameters for a report would be start
and end dates for the report contents.
Name. The parameter name as it is specified in the IBM Cognos Business Intelligence database.
Type. A description of the parameter.
Value. The value to assign to the parameter. To enter or edit a value, double-click its cell in the table.
Values are not validated here; any invalid values are detected at run time.
Chapter 3. Data files 21
Automatically remove invalid parameters from table. This option is selected by default and will remove
any invalid parameters found within the data object or report.
Changing variable names
For IBM Cognos Business Intelligence data packages, package field names are automatically converted to
valid variable names. You can use the Fields tab of the Read Cognos Data dialog to override the default
names. Names must be unique and must conform to variable naming rules. See the topic “Variable
names” on page 52 for more information.
Reading Cognos TM1 data
If you have access to an IBM Cognos TM1®database, you can import TM1 data from a specified view
into IBM SPSS Statistics. The multidimensional OLAP cube data from TM1 is flattened when read into
SPSS Statistics.
Important: To enable the exchange of data between SPSS Statistics and TM1, you must copy the
following three processes from SPSS Statistics to the TM1 server: ExportToSPSS.pro, ImportFromSPSS.pro,
and SPSSCreateNewMeasures.pro. To add these processes to the TM1 server, you must copy them to the
data directory of the TM1 server and restart the TM1 server. These files are available from the
common/scripts/TM1 directory under the SPSS Statistics installation directory.
Restriction:
vThe TM1 view from which you import must include one or more elements from a measure dimension.
vThe data to be imported from TM1 must be in UTF-8 format.
All of the data in the specified TM1 view are imported. It is therefore best to limit the view to the data
that are required for the analysis. Any necessary filtering of the data is best done in TM1, for example
with the TM1 Subset Editor.
To read TM1 data:
1. From the menus, choose:
File > Import Data > Cognos TM1
2. Connect to the TM1 Performance Management system.
3. Log in to the TM1 server.
4. Select a TM1 cube and select the view that you want to import.
Optionally, you can override the default names of the SPSS Statistics variables that are created from the
names of the TM1 dimensions and measures.
PM System
The URL of the Performance Management system that contains the TM1 server to which you
want to connect. The Performance Management system is defined as a single URL for all TM1
servers. From this URL, all TM1 servers that are installed and running on your environment can
be discovered and accessed. Enter the URL and click Connect.
TM1 Server
When the connection to the Performance Management system is established, select the server that
contains the data you want to import and click Login. If you did not previously connect to this
server, you are prompted to log in.
Username and password
Select this option to log in with a specific username and password. If the server uses
authentication mode 5 (IBM Cognos security), then select the namespace that identifies
the security authentication provider from the available list.
Stored credential
Select this option to use the login information from a stored credential. To use a stored
22 IBM SPSS Statistics 24 Core System User's Guide
credential, you must be connected to the IBM SPSS Collaboration and Deployment
Services Repository that contains the credential. After you are connected to the repository,
click Browse to see the list of available credentials.
Select a TM1 cube view to import
Lists the names of the cubes within the TM1 server from which you can import data.
Double-click a cube to display a list of the views that you can import. Select a view and click the
right arrow to move it into the View to import field.
Column dimension(s)
Lists the names of the column dimensions in the selected view.
Row dimension(s)
Lists the names of the row dimensions in the selected view.
Context dimension(s)
Lists the names of the context dimensions in the selected view.
Note:
vWhen the data are imported, a separate SPSS Statistics variable is created for each regular dimension
and for each element in the measure dimension.
vEmpty cells and cells with a value of zero in TM1 are converted to the system-missing value.
vCells with string values that cannot be converted to a numeric value are converted to the
system-missing value.
Changing variable names
By default, valid IBM SPSS Statistics variable names are automatically generated from the dimension
names and names of elements in the measure dimension from the selected IBM Cognos TM1 cube view.
You can use the Fields tab of the Import from TM1 dialog to override the default names. Names must be
unique and must conform to variable naming rules.
Reading Data Collection Data
On Microsoft Windows operating systems, you can read data from Data Collection products. (Note: This
feature is only available with IBM SPSS Statistics installed on Microsoft Windows operating systems.)
To read Data Collection data sources, you must have the following items installed:
v.NET framework. To obtain the most recent version of the .NET framework, go to http://
www.microsoft.com/net.
vData Collection Survey Reporter Developer Kit.
You can read Data Collection data sources only in local analysis mode. This feature is not available in
distributed analysis mode using IBM SPSS Statistics Server.
To read data from a Data Collection data source:
1. In any open IBM SPSS Statistics window, from the menus choose:
File > Import Data > Data Collection
2. On the Connection tab of Data Link Properties, specify the metadata file, the case data type, and the
case data file.
3. Click OK.
4. In the Data Collection Data Import dialog box, select the variables that you want to include and select
any case selection criteria.
5. Click OK to read the data.
Chapter 3. Data files 23
Data Link Properties Connection tab
To read a Data Collection data source, you need to specify:
Metadata Location. The metadata document file (.mdd) that contains questionnaire definition information.
Case Data Type. The format of the case data file. Available formats include:
vQuancept Data File (DRS). Case data in a Quancept .drs, .drz, or .dru file.
vQuanvert Database. Case data in a Quanvert database.
vData Collection Database (MS SQL Server). Case data in a relational database in SQL Server.
vData Collection XML Data File. Case data in an XML file.
Case Data Location. The file that contains the case data. The format of this file must be consistent with
the selected case data type.
Note: The extent to which other settings on the Connection tab or any settings on the other Data Link
Properties tabs may or may not affect reading Data Collection data into IBM SPSS Statistics is not known,
so we recommend that you do not change any of them.
Select Variables tab
You can select a subset of variables to read. By default, all standard variables in the data source are
displayed and selected.
vShow System variables. Displays any "system" variables, including variables that indicate interview
status (in progress, completed, finish date, and so on). You can then select any system variables that you
want to include. By default, all system variables are excluded.
vShow Codes variables. Displays any variables that represent codes that are used for open-ended
"Other" responses for categorical variables. You can then select any Codes variables that you want to
include. By default, all Codes variables are excluded.
vShow SourceFile variables. Displays any variables that contain filenames of images of scanned
responses. You can then select any SourceFile variables that you want to include. By default, all
SourceFile variables are excluded.
Case Selection Tab
For Data Collection data sources that contain system variables, you can select cases based on a number of
system variable criteria. You do not need to include the corresponding system variables in the list of
variables to read, but the necessary system variables must exist in the source data to apply the selection
criteria. If the necessary system variables do not exist in the source data, the corresponding selection
criteria are ignored.
Data collection status. You can select respondent data, test data, or both. You can also select cases based
on any combination of the following interview status parameters:
vCompleted successfully
vActive/in progress
vTimed out
vStopped by script
vStopped by respondent
vInterview system shutdown
vSignal (terminated by a signal statement in the script)
Data collection finish date. You can select cases based on the data collection finish date.
vStart Date. Cases for which data collection finished on or after the specified date are included.
vEnd Date. Cases for which data collection finished before the specified date are included. This does not
include cases for which data collection finished on the end date.
24 IBM SPSS Statistics 24 Core System User's Guide
vIf you specify both a start date and end date, this defines a range of finish dates from the start date to
(but not including) the end date.
File information
A data file contains much more than raw data. It also contains any variable definition information,
including:
vVariable names
vVariable formats
vDescriptive variable and value labels
This information is stored in the dictionary portion of the data file. The Data Editor provides one way to
view the variable definition information. You can also display complete dictionary information for the
active dataset or any other data file.
To Display Data File Information
1. From the menus in the Data Editor window choose:
File > Display Data File Information
2. For the currently open data file, choose Working File.
3. For other data files, choose External File, and then select the data file.
The data file information is displayed in the Viewer.
Saving data files
In addition to saving data files in IBM SPSS Statistics format, you can save data in a wide variety of
external formats, including:
vExcel and other spreadsheet formats
vTab-delimited and CSV text files
vSAS
vStata
vDatabase tables
To save modified data files
1. Make the Data Editor the active window (click anywhere in the window to make it active).
2. From the menus choose:
File > Save
The modified data file is saved, overwriting the previous version of the file.
To save data files in code page character encoding
Unicode data files cannot be read by IBM SPSS Statistics versions prior to version 16.0. In Unicode mode,
to save a data file in code page character encoding:
1. Make the Data Editor the active window (click anywhere in the window to make it active).
2. From the menus choose:
File > Save As
3. From the Save as type drop-down list in the Save Data dialog, select SPSS Statistics Local Encoding.
4. Enter a name for the new data file.
Chapter 3. Data files 25
The modified data file is saved in the current locale code page character encoding. This action has no
effect on the active dataset. The encoding of the active dataset is not changed. Saving a file in code page
character encoding is similar to saving a file in an external format, such as tab-delimited text or Excel.
Saving data files in external formats
1. Make the Data Editor the active window (click anywhere in the window to make it active).
2. From the menus choose:
File > Save As...
3. Select a file type from the drop-down list.
4. Enter a file name for the new data file.
Options
Depending on the file type, additional options are available.
Encoding
Available for SAS files and text data formats: tab-delimited, comma-delimited, and fixed ASCII
text.
Write variable names to file
Available for Excel, tab-delimited, comma-delimited, 1-2-3, and SYLK. For Excel 97 and later
versions, you can write either variable names or labels. For variables without defined variable
labels, the variable name is used.
Sheet name
For Excel 2007 and later versions, you can specify a sheet name. You can also append a sheet to
an existing file.
Save value labels to a .sas file
SAS 6 and later versions.
For information on exporting data to database tables, see “Exporting to a Database” on page 32.
Saving data: Data file types
You can save data in the following formats:
SPSS Statistics (*.sav). IBM SPSS Statistics format.
vData files saved in IBM SPSS Statistics format cannot be read by versions of the software prior to
version 7.5. Data files saved in Unicode encoding cannot be read by releases of IBM SPSS Statistics
prior to version 16.0
vWhen using data files with variable names longer than eight bytes in version 10.x or 11.x, unique,
eight-byte versions of variable names are used—but the original variable names are preserved for use
in release 12.0 or later. In releases prior to 10.0, the original long variable names are lost if you save the
data file.
vWhen using data files with string variables longer than 255 bytes in versions prior to release 13.0, those
string variables are broken up into multiple 255-byte string variables.
SPSS Statistics Compressed (*.zsav). Compressed IBM SPSS Statistics format.
vZSAV files have the same features as SAV files, but they take up less disk space.
vZSAV files may take more or less time to open and save, depending on the file size and system
configuration. Extra time is needed to de-compress and compress ZSAV files. However, because ZSAV
files are smaller on disk, they reduce the time needed to read and write from disk. As the file size gets
larger, this time savings surpasses the extra time needed to de-compress and compress the files.
vOnly IBM SPSS Statistics version 21 or higher can open ZSAV files.
26 IBM SPSS Statistics 24 Core System User's Guide
vThe option to save the data file with your local code page encoding is not available for ZSAV files.
These files are always saved in UTF-8 encoding.
SPSS Statistics Local Encoding (*.sav). In Unicode mode, this option saves the data file in the current
locale code page character encoding. This option is not available in code page mode.
SPSS 7.0 (*.sav). Version 7.0 format. Data files saved in version 7.0 format can be read by version 7.0 and
earlier versions but do not include defined multiple response sets or Data Entry for Windows
information.
SPSS/PC+ (*.sys). SPSS/PC+ format. If the data file contains more than 500 variables, only the first 500
will be saved. For variables with more than one defined user-missing value, additional user-missing
values will be recoded into the first defined user-missing value. This format is available only on
Windows operating systems.
Portable (*.por). Portable format that can be read by other versions of IBM SPSS Statistics and versions
on other operating systems. Variable names are limited to eight bytes and are automatically converted to
unique eight-byte names if necessary. In most cases, saving data in portable format is no longer necessary,
since IBM SPSS Statistics data files should be platform/operating system independent. You cannot save
data files in portable file in Unicode mode. See the topic “General options” on page 195 for more
information.
Tab-delimited (*.dat). Text files with values separated by tabs. (Note: Tab characters embedded in string
values are preserved as tab characters in the tab-delimited file. No distinction is made between tab
characters embedded in values and tab characters that separate values.) You can save files in Unicode
encoding or local code page encoding.
Comma-delimited (*.csv). Text files with values separated by commas or semicolons. If the current IBM
SPSS Statistics decimal indicator is a period, values are separated by commas. If the current decimal
indicator is a comma, values are separated by semicolons. You can save files in Unicode encoding or local
code page encoding.
Fixed ASCII (*.dat). Text file in fixed format, using the default write formats for all variables. There are
no tabs or spaces between variable fields. You can save files in Unicode encoding or local code page
encoding.
Excel 2007 (*.xlsx). Microsoft Excel 2007 XLSX-format workbook. The maximum number of variables is
16,000; any additional variables beyond the first 16,000 are dropped. If the dataset contains more than one
million cases, multiple sheets are created in the workbook.
Excel 97 through 2003 (*.xls). Microsoft Excel 97 workbook. The maximum number of variables is 256;
any additional variables beyond the first 256 are dropped. If the dataset contains more than 65,356 cases,
multiple sheets are created in the workbook.
Excel 2.1 (*.xls). Microsoft Excel 2.1 spreadsheet file. The maximum number of variables is 256, and the
maximum number of rows is 16,384.
1-2-3 Release 3.0 (*.wk3). Lotus 1-2-3 spreadsheet file, release 3.0. The maximum number of variables that
you can save is 256.
1-2-3 Release 2.0 (*.wk1). Lotus 1-2-3 spreadsheet file, release 2.0. The maximum number of variables that
you can save is 256.
1-2-3 Release 1.0 (*.wks). Lotus 1-2-3 spreadsheet file, release 1A. The maximum number of variables that
you can save is 256.
Chapter 3. Data files 27
SYLK (*.slk). Symbolic link format for Microsoft Excel and Multiplan spreadsheet files. The maximum
number of variables that you can save is 256.
dBASE IV (*.dbf). dBASE IV format.
dBASE III (*.dbf). dBASE III format.
dBASE II (*.dbf). dBASE II format.
SAS v9+ Windows (*.sas7bdat). SAS versions 9 for Windows. You can save files in Unicode (UTF-8) or
local code page encoding.
SAS v9+ UNIX (*.sas7bdat). SAS versions 9 for UNIX. You can save files in Unicode (UTF-8) or local
code page encoding.
SAS v7-8 Windows short extension (*.sd7). SAS versions 7–8 for Windows short filename format.
SAS v7-8 Windows long extension (*.sas7bdat). SAS versions 7–8 for Windows long filename format.
SAS v7-8 for UNIX (*.sas7bdat). SAS v8 for UNIX.
SAS v6 for Windows (*.sd2). SAS v6 file format for Windows/OS2.
SAS v6 for UNIX (*.ssd01). SAS v6 file format for UNIX (Sun, HP, IBM).
SAS v6 for Alpha/OSF (*.ssd04). SAS v6 file format for Alpha/OSF (DEC UNIX).
SAS Transport (*.xpt). SAS transport file.
Stata Version 13 Intercooled (*.dta).
Stata Version 13 SE (*.dta).
Stata Version 12 Intercooled (*.dta).
Stata Version 12 SE (*.dta).
Stata Version 11 Intercooled (*.dta).
Stata Version 11 SE (*.dta).
Stata Version 10 Intercooled (*.dta).
Stata Version 10 SE (*.dta).
Stata Version 9 Intercooled (*.dta).
Stata Version 9 SE (*.dta).
Stata Version 8 Intercooled (*.dta).
Stata Version 8 SE (*.dta).
Stata Version 7 Intercooled (*.dta).
Stata Version 7 SE (*.dta).
28 IBM SPSS Statistics 24 Core System User's Guide
Stata Version 6 (*.dta).
Stata Versions 4–5 (*.dta).
Note: SAS data file names can be up to 32 characters in length. Blank spaces and non-alphanumeric
characters other than the underscore ("_") are not allowed and names have to start with a letter or an
underscore, numbers can follow.
Saving data files in Excel format
You can save your data in one of three Microsoft Excel file formats. Excel 2.1, Excel 97, and Excel 2007.
vExcel 2.1 and Excel 97 are limited to 256 columns; so only the first 256 variables are included.
vExcel 2007 is limited to 16,000 columns; so only the first 16,000 variables are included.
vExcel 2.1 is limited to 16,384 rows; so only the first 16,384 cases are included.
vExcel 97 and Excel 2007 also have limits on the number of rows per sheet, but workbooks can have
multiple sheets, and multiple sheets are created if the single-sheet maximum is exceeded.
Options
vFor all versions of Excel, you can include variable names as the first row of the Excel file.
vFor Excel 97 and later versions, you can write either variable names or labels. For variables without
defined variable labels, the variable name is used.
vFor Excel 2007 and later versions, you can specify a sheet name. You can also append a sheet to an
existing file.
Variable types
The following table shows the variable type matching between the original data in IBM SPSS Statistics
and the exported data in Excel.
Table 2. How Excel data formats map to IBM SPSS Statistics variable types and formats
IBM SPSS Statistics Variable Type Excel Data Format
Numeric 0.00; #,##0.00; ...
Comma 0.00; #,##0.00; ...
Dollar $#,##0_); ...
Date d-mmm-yyyy
Time hh:mm:ss
String General
Saving data files in SAS format
Special handling is given to various aspects of your data when saved as a SAS file. These cases include:
vCertain characters that are allowed in IBM SPSS Statistics variable names are not valid in SAS, such as
@, #, and $. These illegal characters are replaced with an underscore when the data are exported.
vIBM SPSS Statistics variable names that contain multibyte characters (for example, Japanese or Chinese
characters) are converted to variables names of the general form Vnnn, where nnn is an integer value.
vIBM SPSS Statistics variable labels containing more than 40 characters are truncated when exported to
a SAS v6 file.
vWhere they exist, IBM SPSS Statistics variable labels are mapped to the SAS variable labels. If no
variable label exists in the IBM SPSS Statistics data, the variable name is mapped to the SAS variable
label.
Chapter 3. Data files 29
vSAS allows only one value for system-missing, whereas IBM SPSS Statistics allows numerous
user-missing values in addition to system-missing. As a result, all user-missing values in IBM SPSS
Statistics are mapped to a single system-missing value in the SAS file.
vSAS 6-8 data files are saved in the current IBM SPSS Statistics locale encoding, regardless of current
mode (Unicode or code page). In Unicode mode, SAS 9 files are saved in UTF-8 format. In code page
mode, SAS 9 files are saved in the current locale encoding.
vA maximum of 32,767 variables can be saved to SAS 6-8.
vSAS data file names can be up to 32 characters in length. Blank spaces and non-alphanumeric
characters other than the underscore ("_") are not allowed and names have to start with a letter or an
underscore, numbers can follow.
Save value labels
You have the option of saving the values and value labels associated with your data file to a SAS syntax
file. This syntax file contains proc format and proc datasets commands that can be run in SAS to create
a SAS format catalog file.
This feature is not supported for the SAS transport file.
Variable types
The following table shows the variable type matching between the original data in IBM SPSS Statistics
and the exported data in SAS.
Table 3. How SAS variable types and formats map to IBM SPSS Statistics types and formats
IBM SPSS Statistics Variable Type SAS Variable Type SAS Data Format
Numeric Numeric 12
Comma Numeric 12
Dot Numeric 12
Scientific Notation Numeric 12
Date Numeric (Date) for example, MMDDYY10, ...
Date (Time) Numeric Time18
Dollar Numeric 12
Custom Currency Numeric 12
String Character $8
Saving data files in Stata format
vData can be written in Stata 5–13 format and in both Intercooled and SE format (version 7 or later).
vData files that are saved in Stata 5 format can be read by Stata 4.
vThe first 80 bytes of variable labels are saved as Stata variable labels.
vFor Stata releases 4-8, the first 80 bytes of value labels for numeric variables are saved as Stata value
labels. For Stata release 9 or later, the complete value labels for numeric variables are saved. Value
labels are dropped for string variables, non-integer numeric values, and numeric values greater than an
absolute value of 2,147,483,647.
vFor versions 7 and later, the first 32 bytes of variable names in case-sensitive form are saved as Stata
variable names. For earlier versions, the first eight bytes of variable names are saved as Stata variable
names. Any characters other than letters, numbers, and underscores are converted to underscores.
vIBM SPSS Statistics variable names that contain multi-byte characters (for example, Japanese or Chinese
characters) are converted to generic single-byte variable names.
30 IBM SPSS Statistics 24 Core System User's Guide
vFor versions 5–6 and Intercooled versions 7 and later, the first 80 bytes of string values are saved. For
Stata SE 7–12, the first 244 bytes of string values are saved. For Stata SE 13 or later, complete string
values are saved, regardless of length.
vFor versions 5–6 and Intercooled versions 7 and later, only the first 2,047 variables are saved. For Stata
SE 7 or later, only the first 32,767 variables are saved.
Table 4. How Stata variable type and format map to IBM SPSS Statistics type and format
IBM SPSS Statistics Variable Type Stata Variable Type Stata Data Format
Numeric Numeric g
Comma Numeric g
Dot Numeric g
Scientific Notation Numeric g
Date*, Datetime Numeric D_m_Y
Time, DTime Numeric g (number of seconds)
Wkday Numeric g (1–7)
Month Numeric g (1–12)
Dollar Numeric g
Custom Currency Numeric g
String String s
*Date, Adate, Edate, SDate, Jdate, Qyr, Moyr, Wkyr
Saving Subsets of Variables
The Save Data As Variables dialog box allows you to select the variables that you want saved in the new
data file. By default, all variables will be saved. Deselect the variables that you don't want to save, or
click Drop All and then select the variables that you want to save.
Visible Only. Selects only variables in variable sets currently in use. See the topic “Using variable sets to
show and hide variables” on page 192 for more information.
To Save a Subset of Variables
1. Make the Data Editor the active window (click anywhere in the window to make it active).
2. From the menus choose:
File > Save As...
3. Click Variables.
4. Select the variables that you want to save.
Encrypting data files
For IBM SPSS Statistics data files, you can protect confidential information stored in a data file by
encrypting the file with a password. Once encrypted, the file can only be opened by providing the
password.
1. Make the Data Editor the active window (click anywhere in the window to make it active).
2. From the menus choose:
File > Save As...
3. Select Encrypt file with password in the Save Data As dialog box.
4. Click Save.
Chapter 3. Data files 31
5. In the Encrypt File dialog box, provide a password and re-enter it in the Confirm password text box.
Passwords are limited to 10 characters and are case-sensitive.
Warning: Passwords cannot be recovered if they are lost. If the password is lost the file cannot be opened.
Creating strong passwords
vUse eight or more characters.
vInclude numbers, symbols and even punctuation in your password.
vAvoid sequences of numbers or characters, such as "123" and "abc", and avoid repetition, such as
"111aaa".
vDo not create passwords that use personal information such as birthdays or nicknames.
vPeriodically change the password.
Note: Storing encrypted files to an IBM SPSS Collaboration and Deployment Services Repository is not
supported.
Modifying encrypted files
vIf you open an encrypted file, make modifications to it and choose File > Save, the modified file will be
saved with the same password.
vYou can change the password on an encrypted file by opening the file, repeating the steps for
encrypting it, and specifying a different password in the Encrypt File dialog box.
vYou can save an unencrypted version of an encrypted file by opening the file, choosing File > Save As
and deselecting Encrypt file with password in the Save Data As dialog box.
Note: Encrypted data files and output documents cannot be opened in versions of IBM SPSS Statistics
prior to version 21. Encrypted syntax files cannot be opened in versions prior to version 22.
Exporting to a Database
You can use the Export to Database Wizard to:
vReplace values in existing database table fields (columns) or add new fields to a table.
vAppend new records (rows) to a database table.
vCompletely replace a database table or create a new table.
To export data to a database:
1. From the menus in the Data Editor window for the dataset that contains the data you want to export,
choose:
File > Export > Database
2. Select the database source.
3. Follow the instructions in the export wizard to export the data.
Creating Database Fields from IBM SPSS Statistics Variables
When creating new fields (adding fields to an existing database table, creating a new table, replacing a
table), you can specify field names, data type, and width (where applicable).
Field name. The default field names are the same as the IBM SPSS Statistics variable names. You can
change the field names to any names allowed by the database format. For example, many databases
allow characters in field names that aren't allowed in variable names, including spaces. Therefore, a
variable name like CallWaiting could be changed to the field name Call Waiting.
Type. The export wizard makes initial data type assignments based on the standard ODBC data types or
data types allowed by the selected database format that most closely matches the defined IBM SPSS
32 IBM SPSS Statistics 24 Core System User's Guide
Statistics data format--but databases can make type distinctions that have no direct equivalent in IBM
SPSS Statistics, and vice versa. For example, most numeric values in IBM SPSS Statistics are stored as
double-precision floating-point values, whereas database numeric data types include float (double),
integer, real, and so on. In addition, many databases don't have equivalents to IBM SPSS Statistics time
formats. You can change the data type to any type available in the drop-down list.
As a general rule, the basic data type (string or numeric) for the variable should match the basic data
type of the database field. If there is a data type mismatch that cannot be resolved by the database, an
error results and no data are exported to the database. For example, if you export a string variable to a
database field with a numeric data type, an error will result if any values of the string variable contain
non-numeric characters.
Width. You can change the defined width for string (char, varchar) field types. Numeric field widths are
defined by the data type.
By default, IBM SPSS Statistics variable formats are mapped to database field types based on the
following general scheme. Actual database field types may vary, depending on the database.
Table 5. Format conversion for databases
IBM SPSS Statistics Variable Format Database Field Type
Numeric Float or Double
Comma Float or Double
Dot Float or Double
Scientific Notation Float or Double
Date Date or Datetime or Timestamp
Datetime Datetime or Timestamp
Time, DTime Float or Double (number of seconds)
Wkday Integer (1–7)
Month Integer (1–12)
Dollar Float or Double
Custom Currency Float or Double
String Char or Varchar
User-Missing Values
There are two options for the treatment of user-missing values when data from variables are exported to
database fields:
vExport as valid values. User-missing values are treated as regular, valid, nonmissing values.
vExport numeric user-missing as nulls and export string user-missing values as blank spaces.
Numeric user-missing values are treated the same as system-missing values. String user-missing values
are converted to blank spaces (strings cannot be system-missing).
Selecting a Data Source
In the first panel of the Export to Database Wizard, you select the data source to which you want to
export data.
You can export data to any database source for which you have the appropriate ODBC driver. (Note:
Exporting data to OLE DB data sources is not supported.)
If you do not have any ODBC data sources configured, or if you want to add a new data source, click
Add ODBC Data Source.
Chapter 3. Data files 33
vOn Linux operating systems, this button is not available. ODBC data sources are specified in odbc.ini,
and the ODBCINI environment variables must be set to the location of that file. For more information,
see the documentation for your database drivers.
vIn distributed analysis mode (available with IBM SPSS Statistics Server), this button is not available.
To add data sources in distributed analysis mode, see your system administrator.
An ODBC data source consists of two essential pieces of information: the driver that will be used to
access the data and the location of the database you want to access. To specify data sources, you must
have the appropriate drivers installed. Drivers for a variety of database formats are included with the
installation media.
Some data sources may require a login ID and password before you can proceed to the next step.
Choosing How to Export the Data
After you select the data source, you indicate the manner in which you want to export the data.
The following choices are available for exporting data to a database:
vReplace values in existing fields. Replaces values of selected fields in an existing table with values
from the selected variables in the active dataset. See the topic “Replacing Values in Existing Fields” on
page 35 for more information.
vAdd new fields to an existing table. Creates new fields in an existing table that contain the values of
selected variables in the active dataset. See the topic “Adding New Fields” on page 35 for more
information. This option is not available for Excel files.
vAppend new records to an existing table. Adds new records (rows) to an existing table containing the
values from cases in the active dataset. See the topic “Appending New Records (Cases)” on page 36 for
more information.
vDrop an existing table and create a new table of the same name. Deletes the specified table and
creates a new table of the same name that contains selected variables from the active dataset. All
information from the original table, including definitions of field properties (for example, primary keys,
data types) is lost. See the topic “Creating a New Table or Replacing a Table” on page 36 for more
information.
vCreate a new table. Creates a new table in the database containing data from selected variables in the
active dataset. The name can be any value that is allowed as a table name by the data source. The
name cannot duplicate the name of an existing table or view in the database. See the topic “Creating a
New Table or Replacing a Table” on page 36 for more information.
Selecting a Table
When modifying or replacing a table in the database, you need to select the table to modify or replace.
This panel in the Export to Database Wizard displays a list of tables and views in the selected database.
By default, the list displays only standard database tables. You can control the type of items that are
displayed in the list:
vTables. Standard