Coding responses and creating variables

Guide

On this page

Between the questionnaire and the calculator stand two quiet jobs: coding – deciding which number represents each answer, and writing it in the codebook before entering a single cell (Pallant, 2020, Chapter 2); and creating variables – once the file is full and clean, turning the raw columns into the variables the tests need: a scale score from items, age groups from a continuous variable, two categories out of six, dummy variables for regression (Pallant, Chapter 8). This page follows that order. What is already on the site is not repeated here: the file structure and the legend sheet – on the page Standard data template; how to word the question so that coding is almost automatic – in Questionnaire construction; what to do with missing values in the analysis – in Missing values.

1. The codebook – before data entry

Pallant (2020, chapter 2) opens the preparation of the file with a codebook: "a summary of the instructions you will use to convert the information from each participant into a form the program understands" – and this holds for a questionnaire, an experiment and data drawn from existing records. Two decisions in each row: how the variable is defined and labeled, and which number each possible answer receives. Her Table 2.1 has four columns – the variable name in the program, a description, the coding instructions, the level of measurement – and on the site it is exactly the legend sheet of the standard template (five columns: variable name, description, variable type, values and coding, notes; the page Standard data template, Table 1). The difference: in Pallant the codebook is a separate document from which data are entered into SPSS; on the site it is a sheet inside the file, and the calculators read it – the labels appear in the tables, the variable type determines what may be computed, and the downloaded syntax includes the dictionary commands.

The level of measurement – the fourth column: nominal (categories without order), ordinal (ordered categories), scale (continuous scores). Pallant's examples: sex – nominal, "because the numbers are arbitrary"; education – ordinal, "because the numbers represent increasing levels"; age in years – scale; "had I used age categories (18-30, 31-50, 51+), the variable would have been ordinal". On the site: id / nominal / ordinal / scale; and the site convention – a single rating item is ordinal, a multi-item scale score is continuous (the page Variables and levels of measurement).

Variable names. Unique; short ("very long names make the output hard to read"); for scale items – an abbreviation and a number (op1, op2, op3). SPSS rules (Pallant): start with a letter, no spaces, periods or symbols, no reserved words (all, and, by, eq, ge, gt, le, lt, ne, not, or, to, with), up to 64 characters. On the site Hebrew names are accepted (attitude1, attitude_score), with an underscore instead of a space; the syntax the calculators download uses the names as they are. The first variable – ID: "a unique number for each case… write it clearly on the cover page of each questionnaire; if you later find an error in the file, you can go back and find where it happened" (Pallant, Chapter 2). The calculators recognize a column named "id" (or "id") and exclude it from the analysis.

2. Closed questions – from a marked answer to a number

"Every answer must receive a numeric code" (Pallant, 2020, Chapter 2). Some are already numbers (age in years); the rest – sex, marital status – are converted. If the options are numbered on the questionnaire, coding is almost automatic; and if not – "decide on a convention and stick to it: the first answer in the list = 1, the second = 2, and so on down the page". Field (2024, section 4.6.5) points out that in a grouping variable "the codes are arbitrary, because the numbers themselves will not be analyzed" – 0 and 1 are possible, 616 and 11 are possible – but there are conventions that make life easy: control group = 0 and experimental = 1 (Field), yes/no = 1/0, and categories in the order in which they appear in the questionnaire. Table 1 summarizes the types of closed questions and the columns each of them produces.

3. Open-ended questions – from text to categories

"For open-ended questions coding is a little more complicated" (Pallant, 2020, Chapter 2). Her example: "What is the main source of stress in your life at the moment?" – go through the questionnaires and identify recurring themes (work, money, relationship, health, lack of time), record them in the codebook under the variable stress with a number for each theme (work = 1, partner = 2…), and add a code for answers that do not fit any category (other = 99); then for each participant compare the answer with the list and enter the code. Three additions by the site, which are not in Pallant:

The text column stays. The coding is a new column next to the original answer (stress_text, stress_code) – so that it can be recoded when it turns out a category is missing, and so that answers can be quoted in the report. A swelling "other" is a sign that the list is not enough: if 20% of the answers are 99, add categories and go through again. Two coders. Theme coding is judgment, and judgment needs checking: a sample of the answers is given to a second coder with the same codebook, and agreement is measured with intercoder reliability (Cohen's kappa, Krippendorff's alpha); systematic disagreement on a particular category means that its definition in the codebook is not clear – sharpen the definition, not the coder. Counting the categories – on the frequencies page.

4. Missing values and special codes

Field (2024, section 4.6.8): the simple way to record a missing value is an empty cell; and if a numeric code is chosen, it must be an "impossible" value – larger than the maximum of the measure (101, 999, or "666, because missing values are the work of the devil") – because if you choose 9 and some participants really answered 9, they will be treated as missing. In SPSS up to three separate codes can be defined, and so you can remember why a value is missing: 8 = not applicable, 9 = don't know, 99 = did not answer – the program ignores all of them the same way, but the code tells you the reason. On the site the rule is empty cell = missing, with no definitions (the page Standard data template, "Missing values"): a code like 99 in an age column will be counted as an age. So what is done in practice: refusal / did not answer / unintended skip – an empty cell; "not applicable" after a filter question – an empty cell, and the reason is documented in the notes column of the legend ("empty = non-smoker, per question 4"); "don't know" in a knowledge or factual question – this is an answer, not a missing value: its own code (e.g., 9) documented in the legend, which stays in the frequencies and is removed (replaced with an empty cell in a working copy) before a mean, correlation or test. The distinction between legitimate and illegitimate missingness, and why "don't know" is sometimes a finding – on the template page (following Cunningham & Muir, 2023, as cited there); what each method of handling missing values does to the results – in Missing values.

5. From items to a scale score

"Before you can analyze, you need to compute scale scores for each scale in the study. Two steps: 1. reverse every negatively worded item; 2. add up the scores of all the items that make up the scale or subscale" (Pallant, 2020, Chapter 8). The reversal: on a 1-5 scale, the new score is 6 − x (1 ↔ 5, 2 ↔ 4, 3 stays); on a 1-7 scale – 8 − x; on a 0-4 scale – 4 − x. The rule: (maximum + minimum) − x. Three warnings from Pallant: check in the scale manual or the original article which items are reversed and how the score is computed (sum or mean; subscales that may or may not be added) – "it is much easier to do it right the first time than to repeat the analyses"; do this only on a complete file, "because the program does not update the commands when data are added"; and create new variables rather than overwriting – "much safer, and it keeps the original data". On the site all this is done in one click: the page Cronbach's alpha and scale score reads "item" and "reverse" in the legend, reverses, computes alpha and downloads your file with the scale score column (mean or sum, with a threshold for missing values) – and the original columns in place. What is left for you: mark them in the legend, and write in the notes how the score was computed.

6. Grouping a continuous variable and merging categories

Continuous into groups. "For some analyses, or when the distribution is very skewed, the sample can be divided into equal groups by score" – Pallant (2020, chapter 8) divides age into three groups of similar size (Visual Binning, by percentiles), and notes that the same technique gives a median split into two groups; the result is a new variable (agegp3) and the continuous variable stays. Two warnings of her own: "you lose information" – so grouping is for a test that needs groups (analysis of variance, chi-square), not a substitute for a correlation or regression on the continuous variable; and for a clinical scale a threshold from the literature (published cutoff points) is preferable to percentiles. In Excel: a new column with =IF(B2<30,1,IF(B2<45,2,3)), and a row in the legend: age_grp – ordinal – 1 = up to 29; 2 = 30-44; 3 = 45+, and in the notes "derived from age". Merging categories. For a theoretical reason (marital status → "in a relationship / not in a relationship") or after seeing the frequencies – "only two people in the primary school category" – then merge with the adjacent category and relabel ("did not finish high school"); "very important: create a new, additional variable" (Pallant). When it is necessary: chi-square with small expected frequencies (the chi-square page warns), analysis of variance with a group of two participants (analysis of variance drops groups with fewer than two observations), Kruskal-Wallis with fewer than five in a group.

7. Dummy coding – categories in regression

Regression accepts a dichotomous variable as it is (0/1); a variable with three or more categories cannot be entered as one column, because the numbers 1, 2, 3 will be read as order and distances. The solution – dummy coding (Field, 2024, Section 12.3.1), in eight steps: count the groups and subtract 1 – this is the number of dummy variables; create them; choose a baseline group – a control group, or, without a specific hypothesis, the largest group ("with unequal groups it is important that the baseline group be large, so that the estimates are reliable"); the baseline group gets 0 on all the dummy variables; in the first variable 1 for the first group compared with the baseline and 0 for all the rest; and so on; and all the dummy variables enter the model in the same block. Field's example: three treatment groups (none, 15 minutes, 30 minutes) – two dummy variables, "none" as the baseline, and each coefficient is the difference between that group and the baseline. In Excel: a column for each group except the baseline, =IF(C2=2,1,0), and in the legend: educ_postsec – nominal – 1 = post-secondary; 0 = other, in the notes "dummy; baseline = secondary". The regression page accepts a dichotomous variable and warns about a categorical variable with more than two levels; with the dummy variables it computes the model, and the baseline group is stated in the report sentence.

8. From text to number – and back

A column typed in words ("man", "woman"; "Tel Aviv", "Haifa") is data in every respect – but only as categorical. In SPSS it is converted to numbers with Automatic Recode – a new variable, and in the output a summary of the old values and the codes assigned (Pallant, 2020, Chapter 8). On the site there is no need: the calculators recognize a column whose values are mostly text as categorical, and accept it as a grouping variable (t-test, Mann-Whitney, analysis of variance, chi-square, frequencies) – but not as a dependent variable, and not as a predictor. Two traps: inconsistent spelling ("Tel Aviv" / "Tel-Aviv" / "TA") turns one category into three – the template checker shows the list of distinct values; and a number typed with text ("35 years", "about 5") turns a continuous column into text – the checker warns, and you fix it in the copy. In the opposite direction – numbers you want to see as words – do not change the data: write the labels in the legend ("1 = man; 2 = woman"), and they appear in every table, chart and report sentence.

9. Transformations – and what to do instead

"Often, when you check the distribution of a scale, you discover (to your dismay!) that the scores are not normally distributed" – Pallant (2020, chapter 8) lists three ways: abandon the parametric and switch to a nonparametric alternative (Spearman, Mann-Whitney, Wilcoxon, Kruskal-Wallis – all on the site); group into categories – with a skewed depression scale she cuts at the published clinical cutoff points ("normal" / "clinical") and switches to a test for categories, "but you will lose information"; or change the scores mathematically – and on this "there is considerable controversy in the literature, there are enthusiastic supporters and there are opponents, and it varies between disciplines – check articles in your field" (the elaboration – Tabachnick and Fidell, 2013, as cited in Pallant). The site convention, following this reservation: the calculators do not perform transformations; before changing scores, check what the violation actually means (Normality testing – in a large sample it usually does not matter; Outliers and assumptions), and if you decide to anyway – a transformation is a new variable in Excel (=LOG10(B2), =SQRT(B2)), with a row in the legend, and you report it and on which scale the finding is interpreted.

10. Documenting the changes

"When you change variables or create new ones, it is important to record this in the codebook" (Pallant, 2020, Chapter 8) – and in her book every procedure ends with Paste to the syntax window, "a useful tool for keeping a record of every change in the file". On the site, where the work is in Excel, the documentation is in three places: the original column stays and the new one is next to it, with a name that reveals the link (age → age_grp; att2 → att2_rev); a row in the legend for every new variable – type, codes, and in the notes from what and how it was derived ("derived from age: up to 29 / 30-44 / 45+"; "dummy, baseline = secondary"); and the syntax every calculator downloads, which documents what was run and on which variables. The rule above them all, from the template page: the source is locked, the work is in a copy – and every value corrected by hand is recorded: who, when, what and why.

Common mistakes

Entering data before the codebook. And then discovering midway that "strongly agree" is 1 in one questionnaire and 5 in another (Pallant, 2020, Chapter 2).

99 as missing without telling anyone. In SPSS without a user-missing definition, and on the site always – 99 is an age (Field, 2024, section 4.6.8; the template page).

"Don't know" as an empty cell, or as the scale midpoint. An answer with its own code, removed before the mean (section 4).

"1, 3, 5" in one cell for a "check all that apply" question. A column for each option, 0/1 (Table 1).

Reversing an item by overwriting, or forgetting to reverse. A low or negative alpha, a negative item-total correlation; "reverse" in the legend and the alpha page do it in a new column (section 5).

Computing a scale score before the file is complete. And then adding participants without a score (Pallant, Chapter 8).

Grouping age into groups and then computing a correlation with the groups. The information is lost; the correlation – on the continuous variable (section 6).

A categorical variable with 1, 2, 3 directly in regression. The model reads order and distances that do not exist; dummy coding (section 7).

A baseline group of three participants. The estimates of all the dummy variables rely on it (Field, 2024, Section 12.3.1).

Inconsistent spelling in a text column. Three categories instead of one (section 8).

A transformation without reporting it. The finding is interpreted on a scale that is not the original scale (section 9).

Changing the source. No copy, no new column, no row in the legend – and there is no way back (section 10).

Checklist – before data entry and before analysis

References

Field, A. (2024). Discovering statistics using IBM SPSS statistics (6th ed.). SAGE.

Pallant, J. (2020). SPSS survival manual: A step by step guide to data analysis using IBM SPSS (7th ed.). Open University Press.

Tabachnick and Fidell (2013) – as cited in Pallant (2020, chapter 8); Cunningham and Muir (2023) – as cited on the standard data template page. Per the site rule, the reference list includes only sources read in the original: Pallant (2020) – chapters 2 and 8 in full, by chapter of the printed edition; Field (2024) in a reflowable digital copy – sections 4.6.1, 4.6.5, 4.6.8 and 12.3.1, by section.