Preparing data: recode, calculate, select, merge

In brief

On the "Data" page, "Edit data" opens six assistants: Recode, Calculate, Select cases, Merge, Aggregate and Duplicate cases. Each shows a preview and the PSPP command before it is applied. The result goes into a new column; an existing column is overwritten only if that is explicitly chosen. Each step can be taken back in one go with "Undo" in the header. "Add to Commands" puts the command on the "Commands" page.

Missing values

• An expression like a + b is empty as soon as one value is missing. Functions such as MEAN(a, b), SUM, MIN and MAX instead use the values that are present.
• For the mean of several columns one valid value is enough (as in scale building after reliability). Many textbooks recommend a minimum for scales, such as half of the items; it can be set. The standard deviation needs at least two values.
• Values marked as "no answer" count as missing.

Recode

Old values (a value, a range, "no answer", blank) are replaced by new ones; the first matching rule applies. Values without a rule are kept, left blank or set to a value. The preview counts old against new. "Reverse an item" computes new value = lowest + highest − old value, with the same rule as in scale building; the scale comes from the value labels, otherwise from the data, and can be changed. In text columns upper/lower case and leading spaces count; if values differ only in these, Statable points it out.

The "Recode" assistant also has "Form classes": a number column is divided into equally wide classes, classes with equal numbers of people, or classes with your own limits (see "Forming classes").

Calculate

A formula with columns, operators, brackets and the functions LN, EXP, SQRT, ABS and RND, or mean, sum, minimum, maximum and standard deviation across several columns. "Only if …" calculates only for cases where the condition holds; the others stay empty and are counted separately in the preview. If a divisor is 0 the result is empty, also for 0 divided by 0. The preview shows the first ten cases and counts empty results separately: missing inputs on the one hand, calculation errors (division by 0, logarithm of 0 or less, root of a negative number) on the other.

Select cases

Statable adds a selection column: 1 = condition met, 0 = not met, blank = cannot be checked because a value is missing. It can be used as a group or condition; there is deliberately no invisible filter because not every calculation in Statable would respect it. "Keep only these cases" removes the other cases for good (with a confirmation and the number) - including those that cannot be checked. Analyses in the notebook that use these data are then marked as out of date.

Merge

• Append cases: the second file has the same columns and is added below. Columns are matched by name; if one is only in one file, it stays blank for the other cases. A number against text in the same column is blocked.
• Add columns: through an ID (person, class). "One row per ID in both files" or "many to one" (several people per class). Before linking, Statable checks whether IDs occur more than once (blocked, with a list) and counts IDs without a partner on both sides; with "many to one" IDs only in the second file are missing from the result; with "one row per ID in both files" they are added as rows of their own at the end, with the columns of the first file blank there (all cases of both files are kept). Columns with the same name apart from the ID get a new name (suggestion: _2).
• The second file can be SPSS, Stata, CSV or Excel and stays on the device.

Aggregate

Statistics per group: cases in the group, valid values, mean, standard deviation, median, minimum, maximum, sum. Either as new columns on the data (each person gets the value of their group, row order is kept) or as a new project with one row per group; the current project stays unchanged. Cases without a group value form a group of their own. "Cases" counts all cases of the group, "valid values" only the values present in a column; a median without any valid value stays empty.

Duplicate cases

All or the chosen columns are compared (for example the ID). Cases count as equal if they match, ignoring upper/lower case and spaces at the edge (switchable to "exactly equal"); numbers are compared to 15 digits, and é as one or two characters is the same. Whether a missing value equals a missing value can be chosen. Spaces at the start of a text are lost when .sav files are opened. The result is a marker column (0 = first case, 1 = repeat); cases are deleted only on explicit choice with a confirmation.

For specialists

• Commands: RECODE, COMPUTE, IF, SELECT IF, ADD FILES, MATCH FILES, SORT CASES and AGGREGATE (source: PSPP manual). Statable calculates duplicate cases itself because PSPP compares text byte by byte and numbers bit by bit (0.1 + 0.2 ≠ 0.3).
• Why there are pre-checks: RECODE lets ranges and ELSE catch user-missing values (a 9 for "no answer" would become valid); the missing-value rule therefore comes before the value rules. In PSPP 0/0 is 0 (Statable turns it into an empty result), and a text comparison with a blank cell gives "false" instead of "unknown". MATCH FILES silently drops IDs that are only in the second file, pairs duplicate IDs wrongly without notice, links blank IDs with each other, gives no message for unsorted files and silently drops a column of the second file with the same name. AGGREGATE with MODE=ADDVARIABLES forms groups only from consecutive cases on unsorted data, and a median without a valid value is NaN.