Using SPSS for Ad Hoc Data

1
Using SPSS for Ad Hoc Data

• August 14, 2007
• Soon-Yong Choi
• One-Stop Management/
• Analytics FoxPoint

2
What Is SPSS?

• Statistical Package for Social Science
• For statistical analyses and data operations
  (connection to database, data clean-up and
  manipulations)
• SAS SPSS

3
ODBC Connections

• Any application like SPSS can access DB by Open
  DataBase Connectivity (ODBC) connection to any DB
  Management System (DBMS), in TWC's case Sybase
• ODBC connection has been set up and tested earlier

4
Data Queries

• File ? Open Database ? New Query
• Database Wizard opens
• Select TWIST ad hoc data source
• Select tabular format
• List of tables
• If vw tables are now shown, click 'View' option
• To select all variables, double click a table
• Click a table and select some variables only
• To remove selected variables, grab variables from
  the right panel and move them to the left
• Click 'Next'

5
Limit Retrieved Cases Selection Criteria

• Criteria 'where' condition in a query
• Connector And, Or
• Logical structure must be verified by correct
  uses of parentheses
• (A gt 2 AND (B 55 OR B 66))
• Correct parentheses should be entered in later
  stage
• Expressions are conditions such as
• Service begin date gt 20061001
• Fund code WIA
• Customer SSN 123121234
• Functions can be used in expression

6
Specifying Date Fields

• Date variables are specified differently for each
  database system
• For SPSS query, specify any date as "yyyymmdd"
  (as a string, it needs double quotation marks)

7
Define Variables

• Normally not needed
• Click 'Next'

8
SQL Query

• Edit WHERE condition in this page
• Select 'Retrieve the data I have selected' option
• If the same query needs to be run today, save the
  query
• Click 'Finish'
• Data Editor opens

9
Data Editor

• Shows Data View (like Excel table)
• Variable View ? data list where you can change
  data type specification
• New versions of SPSS do not download data until
  some operation is requested.
• '???' in table cells means SPSS is looking up the
  table.
• 'SPSS Processor is Ready' in the status bar ? you
  can specify some operation.

10
Data Editor

• To force SPSS to download the whole data, use
  some data operation
• Analyze menu ? Descriptive Statistics ?
  Frequencies
• Select a variable and click the right arrow
• Click OK
• If the data is large, the number of cases
  downloaded is shown in the status bar
• The results are shown in a new window called
  'Output'. This is separate from Data Editor
  window.
• If there is a connection difficulty, the output
  window will open with an error
• Save the data

11
Using SSN Lists

• Build a SSN list in Excel
• Copy to Word, and select the whole table
• Table menu ? Convert ? Table to Text

12
Using SSN Lists

• In Word, Edit menu ? Replace
• Change 'paragraph mark' into
• ' OR ssn '
• Click 'Replace All'
• Select 'No' if asking for search

13
Using SSN Lists

• Result is a desired 'WHERE' condition query
  syntax
• Except the first and last expressions ? edit

Final edited query in Word
14
Using SSN Lists

• Open a new database query in SPSS
• Copy the edited SSN where conditions in the final
  stage of SPSS query
• Cautions
• If SSNs are more than 200, the list has to be
  divided into several parts, and multiple queries
  have to be run. Save each subset of downloaded
  data and combine later
• 'ssn' may have to be changed to your desired
  variable name such as 'participant_ssn' or
  'T14.ssn' (when tables are joined)

15
Using SSN Lists

• Combining datasets in SPSS
• Run multiple queries by saving your query
• File ? Open Database ? Edit Query, and paste the
  section of query with new list of SSNs
• Save downloaded data
• To merge sets, open the first data file, then go
  to Data menu ? Merge Files ? Add Cases
• Specify the second data file to add
• Continue to add the remaining data files
• Save the combined data set

16
Duplicate/Unique Data

• To select unique cases
• First, identify cases by the key variable
• Second, select only one (primary) case in each
  duplicate group
• Identify duplicates
• Go to Data menu ? Identify Duplicate Cases
• Select 'DHS_case_no' and move it to 'Define
  matching cases by' area
• Leave defaults indicator of primary cases with
  the name 'PrimaryLast'
• Click OK

17
Selecting Cases

• Duplicate cases now have 1 (one case) or 0 (all
  other cases) in the newly created 'PrimaryLast'
  variable. ? select only the one with '1'
• Go to Data menu ? Select Cases
• Click 'If condition is satisfied' and click 'IF'
  button
• Select Cases IF ? double-click 'PrimaryLast' to
  move to the right panel and write to read
  PrimaryLast 1

18
Selecting Cases

• You can add other conditions as necessary
• Click 'Continue' ? this goes back to select cases
  dialog
• If this selection is temporary, 'Output' option
  should be 'filter'. To delete unselected cases,
  select 'delete' option
• click 'OK'
• If filtered, unselected cases has cross line in
  the case number field.

19
Export/Import Data (Excel)

• To export data to Excel, simply 'Save As' and
  select 'Excel 97 and later' as the file type.
• To import Excel data
• You can simply open an Excel file by Open ? Data,
  and specifying Excel as file type
• If Excel is currently using the file, SPSS will
  not read the file. Close the Excel first.
• If Excel has variable names in the first row,
  select that option so that SPSS can read the
  names correctly.

20
Cross-Tabulation

• Cross-tabulation is a common analysis that break
  down data by two (or more) variables
• Pivoting tables, drilling down tables
• Analyze menu ? Descriptive Statistics ? Crosstabs
• Rows specify variables that appear as rows
• Columns specify variables that appear as columns
• E.G. Row can be WDA name, Column can be
  participation rate

21
Exercise 1

• TANF Federal
• Get the ad hoc data and present cross tabulation
  analysis for the following measure by staff
• "Choices customer participation rates for all
  staff for the program year 10/1/06 7/1/07"

22
Exercise 2

• Customers with training
• "calculate the success rate (successful job
  placement) among WIA Adult customers who received
  occupational training during PY05"