How to Run Aggregate Data in SPSS Step by Step

When to Use Aggregate Data in SPSS

Aggregate in SPSS groups multiple rows of data into one representative row for each group, or creates an additional aggregated variable in the current file. You usually need this operation when your data is recorded at the transaction, purchase, measurement, or repeated-response level, while your research model requires data at the respondent, store, company, or customer-ID level.

For example, one respondent may appear in three rows because they evaluated three products. If your goal is to compare the average score across customers, you need to aggregate the rows by customer ID. Aggregate can calculate Mean, Sum, Minimum, Maximum, Count, and other statistics for each group.

This operation is different from calculating the average score of a scale. When you calculate the Mean of items such as SAT1, SAT2, and SAT3, you are creating a score for a construct. When you run Aggregate by MaKH, you are changing the level of the data. Check this distinction before running regression or EFA.

If you have just received a survey file, first identify whether it contains primary data or data retrieved from an existing source. The observation level and the meaning of each row must be clear before the Aggregate results can be explained.

Preparing the Data Before Running Aggregate

Before looking for the Aggregate menu, open Variable View and Data View and answer four questions: What does each row represent, what group needs to be aggregated, which variables need to be calculated, and how are missing values coded?

Identify the grouping variable. The grouping variable may be MaKH, MaDN, MaCuaHang, MaLop, or another identifier. Its data type must be consistent. If some IDs contain letters, such as KH001 and KH002, keep the variable as String. If the ID contains only numbers and has no leading zeros, Numeric may be appropriate.

Check the variables to aggregate. Mean and Sum are meaningful only for numeric variables. Likert items must be coded in the same direction before you calculate a score. If an item is reverse-worded, recode it first. For example, with a scale from 1 to 5, the reverse-coded value can be calculated as 6 - original score. Then use the reverse-coded variable in Aggregate.

Check missing values. SPSS may calculate an average from the valid observations, depending on the handling method and the number of missing values. Do not treat a code such as 99 or 999 as an ordinary numeric value, because it can pull the Mean and Sum sharply away from their proper values. If the file uses such missing-value codes, define them in the Missing column or recode them as system-missing before running the procedure.

Check duplicates and data order. Aggregating by group produces different results when a group ID is entered incorrectly, contains trailing spaces, or is assigned two different codes for the same object. Run Analyze > Descriptive Statistics > Frequencies for the grouping variable to detect unusual codes. If you want to preserve the detailed data, save a copy before making changes.

You can also read about what data standardization is if the variables use very different measurement units. Standardization is not a required condition for every Aggregate operation. Use it only when the research question or model requires comparisons across scales with different units.

Steps to Run Aggregate Data in SPSS

The usual way to aggregate data in SPSS is through Data > Aggregate. The steps below use SPSS Statistics and are suitable when you want to create a separate aggregated data file.

Step 1: Open the Aggregate Dialog Box

In Data View, select Data > Aggregate.... The Aggregate Data dialog box opens. In the Break Variable(s) field, enter the variable used to divide the groups, such as MaKH. Break Variable(s) tells SPSS to group rows with the same code together.

If you do not place a variable in Break Variable(s), SPSS calculates the statistic for the entire file as one group. This is a common reason users receive one overall Mean instead of one value for each customer.

Step 2: Select the Variables to Calculate

Move the variable to be aggregated into the Aggregated Variables field. Then select the function by clicking the Function button. For repeated ratings, Mean is usually more suitable than Sum when groups contain different numbers of rows. For the number of transactions, Count or N may be more appropriate.

You can create several aggregated variables in one run. For example, for DoanhThu, create Mean_DTTB using the Mean function and Sum_DT using the Sum function. Use meaningful names because the default names are long and difficult to track when you later include the variables in a regression model.

Step 3: Choose How to Save the Data

In the Save section, you will usually see two choices: create a new data file containing only the aggregated groups, or add the aggregated variables to the current file. If the original data contains multiple rows for each group, creating a new file is usually easier to check. You can still keep the detailed file for comparison.

If you create a new file, give it a clear name such as data_aggregate_khachhang.sav. Do not overwrite the original file during the first run. If you need syntax, you can also read about running SPSS syntax to save all commands and reproduce the procedure.

Step 4: Select the Option to Create a Row Count

Some SPSS versions allow you to create a count of the number of rows in each group, usually through the Number of cases option. This variable is useful for checking how many records each customer has. If one group has only one row while other groups have five rows, review the data before drawing a conclusion.

Step 5: Click OK and Compare the Results

Click OK, then open Data View in the new file. Each group should appear on exactly one row if you selected the option to create aggregated data. Randomly check two or three groups by returning to the original file, filtering by the group ID, and calculating the Mean or Sum yourself for comparison.

If you need to run the procedure several times with different variables, record the variable names, selected functions, and number of groups. This helps you explain the process when your supervisor asks why the number of rows decreased after Aggregate. You can also find related guidance in the SPSS topic and connect this step with later analyses.

Reading the Output

Aggregate does not produce a significance-test table like Coefficients or ANOVA. The main result appears in Data View of the new file, while Output Viewer may show little information when you run the procedure only through the menu. You therefore need to check the new variable names, number of rows, and statistical value for each group.

The table below is illustrative output, not the result of a real study. Assume the original file contains several transactions for each customer, and you aggregate by MaKH to calculate the average DiemHaiLong, the total DoanhThu, and the number of rows in each group.

In the illustrative output, KH001 has three rows in the original file. The group's average satisfaction score is 4.20, while its total revenue is 1,850,000 in the currency used by the file. If you want to keep one row for each customer, you should still retain N_Records during the checking stage so that you can identify groups with too few or too many records.

When Aggregate adds variables to the current file instead of creating a new file, you may see the same aggregated value repeated across several rows belonging to the same group. This is technically normal, but it is risky if you use that file immediately for regression. Repeated rows can cause observations to be counted more than once. Check the number of rows before running the analysis.

If you plan to use an aggregated variable in regression, make sure the dependent and independent variables are at the same level. A customer-level variable should not be directly combined with a transaction-level variable unless you have a clear conversion method. If your study contains multilevel data, you may need a different method rather than using Aggregate alone.

Evaluation Criteria

Aggregate does not have one p-value threshold that determines whether the operation passes or fails. The important criteria are whether the aggregated data fits the research question, whether the groups were identified correctly, and whether the selected statistic matches the type of variable. The following quantitative checks help prevent an incorrect file from moving to the next stage.

The rows in this table are criteria for the next checking stage, not mandatory conditions for the Aggregate command. For example, Aggregate can still run with a small sample size, but the subsequent regression model may lack an adequate basis. Distinguish an operational error from a methodological limitation.

If you aggregate items into one representative scale score, run Analyze > Scale > Reliability Analysis again before using that score. In the dialog box, move the items measuring the same construct into Items, click Statistics, select Scale if item deleted and Item, then read the Reliability Statistics and Item-Total Statistics tables.

How to Write This in Your Thesis

The method section should state the original observation unit, the grouping variable, the aggregation function, and the number of groups remaining after processing. Do not write that SPSS automatically proves the data is representative, because Aggregate only groups and calculates data according to the options you select.

How to write this in your thesis: “The original dataset contained [number of rows] observations at the [data level] level. The study used [grouping variable name] to aggregate the data by [unit], with [variable name 1] calculated using [Mean/Sum/Count] and [variable name 2] calculated using [Mean/Sum/Count]. After checking the group codes and missing values, the dataset contained [number of groups] observations for [next analysis].”

If you want to describe a specific result, you can write: “Group [group code] contained [number of records] records in the original data, with an average [variable name] of [illustrative or actual value].” The values in this paragraph must come from your file. Do not replace them with the illustrative output in this article.

When presenting the results in Chapter 4, place the descriptive table before the model results. If Aggregate reduced the number of rows from [N1] to [N2], explain that the decrease occurred because records were grouped by [grouping variable]. Supervisors often ask about this point as soon as the number of observations in the data table differs from the number of questionnaires collected.

Common Errors When Running Aggregate Data in SPSS

Choosing the wrong grouping variable. If you select a transaction ID instead of a customer ID, each row may remain a separate group. The sign is that the number of rows barely decreases after Aggregate. Open Frequencies and check the number of distinct values in the grouping variable.

Using Sum for a Likert scale. A total score may be appropriate in some designs, but it depends on the number of items or recorded occasions. If groups contain different numbers of rows, Sum is no longer easy to compare across groups. Use Mean when your goal is an average score and report that choice clearly.

Leaving a missing code such as 99. A customer with a score of 99 will make the Mean increase abnormally. Check Minimum, Maximum, and Frequencies before aggregation. For a Likert scale from 1 to 5, a value of 99 is almost certainly an error code or missing-value code that needs to be handled.

Failing to save the original file. If you select Add aggregated variables to the active dataset, the new variables may appear in the open file. Save a copy before running the procedure and label the variables so that original scores and aggregated scores can be distinguished.

Using repeated data to run a model. If the same customer appears in several rows but you treat every row as a respondent, the coefficients and standard errors may be affected. After Aggregate, use Analyze > Descriptive Statistics > Frequencies to check the number of rows and group IDs.

Confusing Aggregate with Compute Variable. Transform > Compute Variable is usually used to calculate a score from variables on the same row. Data > Aggregate calculates values across multiple rows within groups. Choosing the wrong command can produce a result that looks valid but answers a different question.

Read more: what secondary data is, how to handle categorical variables in regression.

Frequently asked questions

Does Aggregate data in SPSS delete the original file

The command does not automatically delete the original file. You can create a new data file or add variables to the current file. For safety, use Save As to create a copy before running the procedure and check the save option in the Aggregate Data dialog box.

When should I use Mean and when should I use Sum

Use Mean when you want to compare the average level across groups and the number of rows may differ between groups. Use Sum when the total amount is meaningful for the research question, such as total revenue or total number of visits. Check whether the number of rows differs substantially between groups before choosing.

Why does the number of rows decrease after running Aggregate

This is the expected result when you select one or more Break Variable(s). Rows with the same group code are combined into one row. If a file decreases from 500 rows to 120 rows, you may have moved from the transaction level to 120 groups, but you still need to check whether any group codes were entered incorrectly or left missing.

Can Aggregate replace calculating Cronbach's Alpha

No. Aggregate calculates statistics by group. Cronbach's Alpha checks the internal consistency of items in a scale. Check reliability first, then calculate a representative score and aggregate by group if the research design requires it.

Can I run Aggregate using SPSS syntax

Yes. You can record the command generated by SPSS or write syntax to reproduce the procedure. Syntax allows you to save the grouping variable, calculation function, and output-file name, which is especially useful when you need to rerun the procedure after cleaning the data. Check the output file in Data View rather than relying only on the fact that the syntax ran without an error.

Your next step is to identify the exact level of every row, back up the .sav file, run Data > Aggregate on a copy, and compare at least three groups with the original data. If you want to run the full analysis on your own file, M4 SPSS and SmartPLS data analysis can continue from your .sav or .csv file.