Module II: Preparing the Data File

The credibility, precision, and reproducibility of empirical quantitative research depends fundamentally on the quality of data file preparation. In IBM SPSS Statistics, converting raw questionnaire responses, electronic forms, or financial ledgers into an analytically rigorous database requires a structured workflow encompassing metadata definition, systematic data entry, data transformation, multi-file consolidation, and comprehensive data cleaning. Errors introduced during data definition or data entry corrupt all downstream descriptive statistics, correlation matrices, and econometric models. This module examines the technical mechanics of defining variables in SPSS Variable View, entering and manipulating empirical records, importing and converting external spreadsheet datasets, merging files via cases and variables, utilizing variable sets, and executing systematic error screening and data sanitation protocols.

1. Data Architecture & Variable Definition in SPSS

In IBM SPSS Statistics, data files (.sav) do not merely hold raw numerical values; they embed an intricate metadata dictionary that defines the structural characteristics, display formatting, and analytical roles of each measurement column. All variable definition procedures occur within the Variable View tab of the Data Editor window.

1.1 Comprehensive Analysis of the Eleven Variable Properties

• 1. Name: The unique syntax variable identifier. Must adhere strictly to SPSS naming syntax: must start with an alphabetic letter, cannot exceed 64 bytes in length, cannot contain spaces, mathematical operators (+, -, *, /), or punctuation marks, and cannot duplicate SPSS reserved syntax commands (ALL, AND, BY, EQ, GE, GT, LE, LT, NE, NOT, OR, TO, WITH).
• 2. Type: Specifies the data storage format. While SPSS supports several data types, four predominate in commerce research:
  • Numeric: The standard default format for all quantitative data, ratings, codes, and scale items. SPSS processes numeric data with maximum mathematical efficiency.
  • String: Text or alphanumeric characters (e.g., respondent names, open-ended qualitative comments). String variables cannot be included in mathematical or parametric statistical procedures without prior recoding into numeric codes.
  • Date: Standard calendar dates in diverse formats (e.g., dd-mmm-yyyy, mm/dd/yyyy). Used extensively for calculating chronological age, tenure, or holding durations via Date and Time Wizards.
  • Custom Currency: Numeric variables formatted with currency symbols and comma thousand separators.
• 3. Width: Dictates the maximum number of characters or digits permitted for variable entry and storage. Default is typically 8 characters for numeric variables.
• 4. Decimals: Specifies the number of decimal places displayed on screen in Data View and output tables. For integer codes (e.g., Gender: 1, 2; Likert ratings: 1 to 5), decimals should always be set to 0 to maintain clean visual presentation. For continuous financial ratios, stock prices, and percentages, decimals are calibrated to 2 or 4.
• 5. Label: An extended descriptive text string (up to 256 characters) that fully identifies the variable. While the variable Name is brief (e.g., Inv_Horizon), the variable Label provides clear human-readable context (e.g., "Target Investment Horizon in Years"). SPSS automatically prints the Label on all generated frequency tables, ANOVA summaries, and charts.
• 6. Values (Value Labels): The critical mapping mechanism that assigns meaningful verbal text descriptors to discrete numerical codes. For example, assigning 1 = Male and 2 = Female to a categorical variable, or 1 = Strongly Disagree through 5 = Strongly Agree to Likert scale items. Clicking the ellipsis (...) in the Values cell launches the Value Labels dialogue box.
• 7. Missing (User-Defined Missing Values): Instructs SPSS how to handle non-responses, refusals, or inapplicable items. Rather than leaving blank cells (which SPSS treats as System-Missing, represented by a period '.'), researchers specify explicit discrete numerical codes:
  • 9 or 99 = Refused to Answer / Unreported
  • 8 or 98 = Not Applicable / Skipped Question
  • -1 or -99 = Data Corruption / Incomplete Item
  SPSS automatically filters out designated user-missing codes from statistical calculations while maintaining an exact audit count in frequency distributions.
• 8. Columns: Controls the visual display width of the column in pixels within the Data View grid. Does not alter underlying data storage width.
• 9. Align: Adjusts cell text alignment within Data View: Left, Right, or Centered. Numeric variables default to Right-aligned; string variables default to Left-aligned.
• 10. Measure: Declares the Stevens' scale of measurement: Nominal, Ordinal, or Scale (Interval/Ratio). This declaration is vital because modern SPSS dialogue boxes and the Chart Builder use the measurement icon to automatically restrict or permit specific analytical algorithms.
• 11. Role: Assigns the functional analytical role of the variable:
  • Input: Predictor or Independent Variable (IV).
  • Target: Outcome, Criterion, or Dependent Variable (DV).
  • Both: Variable serves simultaneously as input and target (e.g., mediating variables in path models).
  • None: Variable has no pre-assigned role; treated neutrally across all dialogue boxes.

1.2 Master Variable Architecture Reference Matrix

1.3 Operational Shortcuts: Copying Variable Properties

Manually entering identical Value Labels and Missing codes across 40 or 50 Likert scale items is inefficient and error-prone. SPSS provides rapid automation shortcuts:

• Standard Copy-Paste Method: Complete the full definition of the first Likert variable (e.g., Sat_1). Click on its Values cell, press Ctrl + C, highlight the Values cells of all remaining scale variables (Sat_2 through Sat_50), and press Ctrl + V. The complete value label dictionary is copied across all items instantaneously.
• Define Variable Properties Utility: Located at Data -> Define Variable Properties. This interactive dialogue scans existing data columns, lists all unique values encountered, and allows the analyst to enter labels, assign missing values, and change measurement levels simultaneously across dozens of variables in one window.
• Apply Data Dictionary Wizard: Navigating to Data -> Copy Data Properties launches an automated wizard that imports the complete variable definition architecture (names, labels, measurement types, missing codes) from an existing external master SPSS file (.sav) into an unformatted new dataset.

2. Data Entry & File Manipulation Operations

Once the metadata architecture is established, researchers populate the Data View grid with empirical observations. Modifying, restructuring, and derived computation represent standard operational tasks in quantitative analysis.

2.1 Structural File Modifications

• Inserting & Deleting Cases (Rows): To insert an omitted respondent between existing records, right-click on the row number header and select Insert Cases. To remove corrupted or unengaged responses, highlight the target row number and press the Delete key (or right-click and select Clear).
• Inserting & Deleting Variables (Columns): To introduce an omitted survey item, right-click the column header and select Insert Variable. A new column named VAR00001 is created, which can be renamed in Variable View. Highlight and delete columns using Clear.
• Sorting Cases: Navigating to Data -> Sort Cases allows the researcher to sort observations based on one or multiple criteria. For example, sorting cases in ascending order by Respondent_ID, or sorting primarily by Gender (Ascending) and secondarily by Annual_Income (Descending). Sorting is an essential prerequisite before executing file merges and split-file comparisons.

2.2 Splitting Files and Selecting Cases

2.3 Weighting Cases: Data -> Weight Cases

In business research, analysts frequently receive aggregated secondary summary tables rather than individual respondent rows (e.g., a cross-tabulation showing 120 males preferred Zerodha, 80 preferred Groww, 90 females preferred Zerodha, 110 preferred Groww). Entering this in SPSS requires only 4 rows with a frequency variable Freq_Count, and executing Data -> Weight Cases by Freq_Count. SPSS mathematically treats the 4 rows as 400 discrete individual respondents in all downstream Chi-square and frequency analyses.

2.4 Transforming Variables: Recoding into Different Variables

Recoding involves transforming existing data values into new categories or numeric groupings. In empirical commerce research, researchers routinely convert continuous age into generational cohorts, or invert reverse-worded Likert statements.

2.5 Automatic Recode: Converting String Variables to Numeric Codes

When survey datasets contain textual responses in String format (e.g., city names: "Mumbai", "Kochi", "Bangalore", "Delhi"), parametric statistical algorithms cannot process them. Rather than manually typing numerical codes, researchers execute Transform -> Automatic Recode:

• SPSS automatically reads all distinct string values, sorts them alphabetically, and assigns sequential integer codes (1, 2, 3, 4).
• It creates a new numeric variable while automatically populating the Value Labels dictionary using the original string texts as descriptors.

2.6 Deriving Composite Scales: The Compute Variable Command

Psychometric research rarely relies on single individual survey questions. Instead, multiple Likert items measuring the same theoretical construct are aggregated into a single composite summated score using Transform -> Compute Variable.

• Mean Score vs. Summated Total Score: Researchers can compute either the arithmetic mean or the raw sum across construct items. Computing the mean preserves the original 1-to-5 Likert interpretation scale, whereas summating produces a broad continuous score.
• Handling Missing Data during Computation:
  • Using Arithmetic Plus Operator (+): If an expression is written as COMPUTE Total_Score = Item1 + Item2 + Item3, if a respondent skipped even one single item, SPSS assigns a System-Missing value (.) to the entire composite score.
  • Using Statistical Functions (MEAN.n / SUM.n): Writing COMPUTE Mean_Score = MEAN.3(Item1, Item2, Item3, Item4) instructs SPSS to compute the mean provided the respondent answered at least 3 out of the 4 items, mitigating unnecessary case attrition.

3. External Data Ingestion & File Merging Operations

In contemporary business environments, primary empirical data is rarely inputted directly into SPSS keyboard-by-keyboard. Data is routinely gathered via online survey tools (Google Forms, Qualtrics, SurveyMonkey) or extracted from corporate ERP ledgers in Microsoft Excel (.xlsx) or Comma Separated Values (.csv) formats.

3.1 Importing Microsoft Excel Files into SPSS

Navigating to File -> Open -> Data (and setting file type filter to Excel *.xlsx) launches the Excel Import Wizard. Successful importation requires observing critical structural conditions:

• First Row Variable Names: Check the box "Read variable names from first row of data". Ensure the top row of the Excel worksheet contains clean, syntax-compliant column headers without spaces or special characters.
• Worksheet Selection: If the Excel workbook contains multiple tabs, select the exact target sheet from the dropdown menu.
• Range Specification: If the Excel sheet contains decorative summary tables or notes above or beside the data, specify the exact cell coordinates (e.g., A1:AZ500).
• Data Type Homogeneity: Ensure every column in Excel contains homogeneous data types. If a numerical age column contains occasional text entries (such as "twenty-five" or "N/A"), SPSS will convert the entire column into a String variable, corrupting subsequent numerical analysis.

3.2 Merging Datasets: Adding Cases vs. Adding Variables

Large-scale research projects frequently necessitate merging disparate empirical files. SPSS provides two fundamentally distinct merging mechanisms under the Data -> Merge Files menu:

3.3 Managing Large Datasets: Using Variable Sets

When working with institutional corporate datasets containing hundreds of variables (e.g., annual balance sheet metrics spanning 20 years), navigating variable selection lists in dialogue boxes becomes unwieldy. SPSS provides the Variable Sets utility:

• Defining Variable Sets: Navigating to Utilities -> Define Variable Sets allows the researcher to group related variables into custom subsets (e.g., a set named Liquidity_Ratios, another named Demographics, and a third named Governance_Metrics).
• Activating Sets: Navigating to Utilities -> Use Variable Sets allows the analyst to check only the active subsets required for current analysis. All dialogue boxes instantly collapse to show only the selected variables, dramatically accelerating workflow speed and reducing selection errors.

4. Data Screening, Quality Assurance & Data Cleaning

Data cleaning is the rigorous quality assurance process executed prior to conducting any formal hypothesis testing. Even minor data entry errors—such as accidentally typing an age of 240 instead of 24—heavily distort means, variances, and regression coefficients.

4.1 Screening Categorical Variables: Frequency Analysis

For nominal and ordinal variables (Gender, Education, Marital Status), data screening relies on checking absolute frequency distributions:

• Procedure: Navigate to Analyze -> Descriptive Statistics -> Frequencies. Move all categorical variables into the Variable box and execute.
• Out-of-Range Error Detection: Examine the frequency tables. If Gender is coded 1 = Male and 2 = Female, inspect the table for unauthorized values such as 3, 0, or 22 resulting from keyboard entry slip.
• Locating and Correcting Errors: If an invalid code '3' is detected in Gender:
  • Navigate to Data -> Sort Cases and sort cases by Gender to bring the offending case to the top.
  • Alternatively, use Edit -> Find (or press Ctrl + F) while highlighting the Gender column in Data View, searching for the value '3'. Cross-check the original physical paper questionnaire using the Respondent ID and correct the digital entry.

4.2 Screening Continuous Variables: Descriptive Statistics & Explore

For continuous scale variables (Age, Annual Income, Monthly Portfolio Turnover), frequency tables are inefficient because every unique number produces a separate row. Instead, researchers utilize descriptive summary statistics:

• Procedure: Navigate to Analyze -> Descriptive Statistics -> Descriptives (or the powerful Analyze -> Descriptive Statistics -> Explore command).
• Minimum and Maximum Scrutiny: In the descriptive output table, carefully inspect the Minimum and Maximum columns:
  • If Age_Years exhibits a Minimum of 2 or a Maximum of 450, gross data entry errors exist.
  • If 5-point Likert items display values of 0, 6, or 55, out-of-bounds errors must be flagged.
• Mean vs. 5% Trimmed Mean: The Explore procedure calculates both the standard Arithmetic Mean and the 5% Trimmed Mean (which removes the top 5% and bottom 5% extreme values). If the standard mean diverges substantially from the trimmed mean, severe outliers are distorting the central tendency.

4.3 Detecting Duplicate Records

When collecting data via online electronic forms, respondents occasionally submit their survey multiple times due to slow internet connections or accidental double-clicking. SPSS provides an automated audit feature:

• Procedure: Navigate to Data -> Identify Duplicate Cases. Move identifying matching variables (e.g., Email, Phone Hash, or demographic combinations like Gender + Age + Pin Code) into the "Define matching cases by" box.
• Output Indicator: SPSS creates a new binary system variable named PrimaryLast or DuplicateIndicator (where 0 = Duplicate Case and 1 = Primary Unique Case). Researchers can instantly filter or delete all '0' entries using Data -> Select Cases.

4.4 Systematic Missing Data Protocols