Aggregating Data

Contents

  1. Aggregating Data
    1. Calculating higher-level values
      1. Scratch Variables
      2. Leave
      3. Aggregate
        1. Functions
        2. Break Groups
    2. Aggregating Cases
      1. Aggregate
        1. Functions
      2. Transforming Long to Wide

Calculating higher-level values

Scratch Variables

Scratch variables are declared with a preceding hash (#). These variables are deleted on reaching execution, and are never re-initialized. 

compute #total = sum(sale,#total).
compute total = #total.

Leave

The LEAVE command explicitly suppresses re-initialization for a given variable. In all other contexts, these are normal variables with normal behavior. 

compute total = sum(sale,total).
leave total.

Aggregate

The AGGREGATE command can be used to calculate higher-level values.

To calculate the age of the oldest customer from a dataset of sales, try: 

aggregate outfile=* mode=addvariables
  /oldest_customer_age = max(age).

The OUTFILE=* and MODE=ADDVARIABLES subcommands are the default behavior.

Note: if the calculated variables may collide with pre-existing variable names, use the OVERWRITE=YES subcommand.

Functions

See Aggregate Functions.

Break Groups

To calculate a higher-level value for each subgroup, use the /BREAK=VARLIST subcommand. The dataset must be sorted by VARLIST.

If the dataset is already sorted, use the /PRESORTED subcommand to skip re-processing. The /PRESORTED subcommand must precede the /BREAK subcommand.

Aggregating Cases

Aggregate

The AGGREGATE command can be used to create a higher-level dataset.

This is mostly only useful with break groups, whereby a case is generated for each one.

To create a dataset with a row for each region, try: 

dataset define NEW.
aggregate outfile=NEW
  /break=region
  /total_sales=sum(sale).

If the dataset is already sorted, use the /PRESORTED subcommand to skip re-processing. The /PRESORTED subcommand must precede the /BREAK subcommand.

Functions

See Aggregate Functions.

Transforming Long to Wide

The CASESTOVARS command transforms a data set from long to wide format. An index variable identifies the cases that should be aggregated.

The index variable (or multiple index variables) can be either numeric or string, but all cases must have a non-missing value.

Constants should be specified on the /FIXED subcommand. SPSS will automatically inspect variables and warn about any that appear to be constants but aren't specified here.

The remaining variables will be duplicated for each case that is aggregated together. Extraneous variables can and should be dropped to save time. 

casestovars
 /id=ID
 /index=INDEX
 /fixed=INDEX_LEVEL_VARIABLES
 /drop=FOO BAR BAZ.

See a concrete example in the long-to-wide crosswalk.

CategoryRicottone