1. Introduction
This module will explore missing data in SPSS, focusing on numeric missing data. We will describe how to indicate missing data in your raw data files, how missing data are handled in SPSS procedures, and how to handle missing data in a SPSS data transformations. There are two types of missing values in SPSS: 1) system-missing values, and 2) user-defined missing values. We will demonstrate reading data containing each kind of missing value. Both data sets are identical except for the coding of the missing values. For both data sets, suppose we did a reaction time study with 6 subjects, and the subjects reaction time was measured three times.
2. System-missing values
System-missing
values are values automatically recognized as missing by SPSS. You might notice that some of the reaction times are left blank in the data below. That is the accepted way of indicating system missing data in the data set. For example, for subject 2, the second trial is blank. The only way to read raw data with fields left blank is with fixed field input. The values left blank automatically are treated as system-missing values.
Note:
It is possible to hold the missing place with a single dot in the field, but if you do you will get a warning message each time SPSS encounters one of these values. The resulting variable is coded with system-missing values.
DATA LIST FIXED/
id 1 trial1 3-5 (1) trial2 6-8 (1)
trial3 11-13 (1).
BEGIN DATA .
1 1.5 1.4 1.6
2 1.5 1.9
3 2.0 1.6
4 2.2
5 2.1 2.3 2.2
6 1.8 2.0 1.9
END DATA .
LIST .
One reason for missing data might be that the equipment failed for that trial was missing. The result of the list follows, notice that SPSS marks system-missing values with a dot in the listing. There is a dot everywhere in the listing that there was a blank in the data.
ID TRIAL1 TRIAL2 TRIAL3 1 1.5 1.0 1.6 2 1.5 . 1.9 3 . 2.0 1.6 4 . . 2.2 5 2.1 2.0 2.2 6 1.8 2.0 1.9 Number of cases read: 6 Number of cases listed:
3. User-defined missing values
User-defined
missing
values are numeric values that need to be defined as missing for SPSS. You might notice that some of the reaction times are -9 in the data below. You may use any value you choose to stand for a missing value, but be careful that you don’t choose a value for missing that already exists for the variable in the data set. For that reason many people choose negative numbers or large numbers to represent missing values. For example, for subject 2, the second trial is -9. You may read raw data with user-missing values either as fixed field input or as free field input. We will read it as free field input in this example. When defined as such on a missing values command these values of -9 are treated as user-missing values.
DATA LIST FREE/
id trial1 trial2 trial3 .
MISSING VALUES trial1 TO trial3 (-9).
COMPUTE trialr1=trial1.
COMPUTE trialr2=trial2.
COMPUTE trialr3=trial3.
VARIABLE LABELS trial1 "Trial 1 User Miss"
trialr1 "Trial 1 Sys Miss".
BEGIN DATA .
1 1.5 1.4 1.6
2 1.5 -9 1.9
3 -9 2.0 1.6
4 -9 -9 2.2
5 2.1 2.3 2.2
6 1.8 2.0 1.9
END DATA .
LIST .
The compute command is used to create the new variables trialr1 through trialr3, which will contain system-missing values where there were user-defined missing values in the original variables. User-defined missing values on the original variable become system-missing values on the new variables. The result of the list follows, notice that SPSS marks user-missing values with a -9 in the listing. There is a -9 everywhere in the listing that there was a -9 in the data, so the value of the user-defined missing is preserved for the original variables ().
ID TRIAL1 TRIAL2 TRIAL3 TRIALR1 TRIALR2 TRIALR3 1.00 1.50 1.40 1.60 1.50 1.40 1.60 2.00 1.50 -9.00 1.90 1.50 . 1.90 3.00 -9.00 2.00 1.60 . 2.00 1.60 4.00 -9.00 -9.00 2.20 . . 2.20 5.00 2.10 2.30 2.20 2.10 2.30 2.20 6.00 1.80 2.00 1.90 1.80 2.00 1.90 Number of cases read: 6 Number of cases listed: 6
Let’s examine how SPSS handles missing data in analysis commands.
4. How SPSS handles missing data in analysis commands
As a general rule, SPSS analysis commands that perform computations handle missing data by omitting the missing values. (We say analysis commands to indicate that we are not addressing commands like sort.) The way that missing values are eliminated is not always the same among SPSS commands, so let’s us look at some examples. First, use the descriptives command on our data file and see how this command handles the missing values.
DESC
/VAR= trial1 trial2 trial3
trialr1 trialr2 trialr3.
As you see in the output below, descriptives computed the means using four observations for trial1 and trial2 and six observations for trial3. In short, descriptives used all of the valid data and performed the computations on all of the available data. This was also true for the next three variables containing user-missing values.
As you see below, frequencies likewise performed its computations using just the available data. Note that the percentages are computed based on just the total number of non-missing cases. But the missing values do appear in the tables and they are marked as missing. This is true for both types of missing values.
FREQ /VAR= trial1 trialr1 .
It is possible that you might want the valid percentages to be computed on the total number of values, and even report the percentage missing in the table itself. You can request this using the missing=include subcommand on the freq command. This is shown below for trial1 and trialr1.
FREQ /VAR= trial1 trialr1 /MISSING= INCLUDE.
As you see, now the valid percentages are computed out of the total number of observations, and the percentage missing are shown right in the table as well for the variable trial1 which contained user-missing values. For trialr1, the system-missing values are not used to compute percents even with missing=include specified.
The crosstabs command only includes valid (non-missing data) in its tables. Cases containing a missing value for even one of the variables are not included in the table. Note that the percentages are computed based on just the non-missing cases. This is true for both types of missing values.
CROSS /TAB= trial1 BY trial2 / trialr1 BY trialr2.
It is possible that you might want the missing values included in the tables. This is especially true when you are using crosstabs to verify your transformations. You can request this using the missing=include subcommand on the crosstabs command. This is shown below for trial1 and trialr1. Here again, you will only be successful for user-missing values.
CROSS /TAB= trial1 BY trial2 / trialr1 BY trialr2 /MISSING= INCLUDE.
The user-missing values are included in the table for the variable trial1. For trialr1, the system-missing values are not included in the table even with missing=include specified. There is no subcommand that will enable the inclusion of system-missing values in the crosstabs table.
There is no way to get a system missing value to appear in a crosstabs table. The closest you will come is to change the system-missing value to a user-missing value. This can be accomplished with a recode command, as is shown below. The keyword sysmis can be used on the recode command, and it stands for the system-missing value.
RECODE trialr1 trialr2
(SYSMIS=-1) (ELSE=COPY)
INTO trialb1 trialb2 .
MISSING VALUES trialb1 trialb2 (-1).
CROSS
/TAB= trialb1 BY trialb2
/MISSING= INCLUDE.
Let’s look at how corr handles missing data. We would expect that it would do the computations based on the available data, and omit the missing values for each pair of variables. Because two variables are necessary to compute each correlation. Here is an example program.
CORR VAR trial1 trial2 trial3 .
The output of this command is shown below. Note how the missing values were excluded. For each pair of variables, corr used the number of pairs that had valid data. For the pair formed by trial1 and trial2, there were three pairs with valid data. For the pairing of trial1 and trial3 there were four valid pairs, and likewise there were four valid pairs for trial2 and trial3. Since this used all of the valid pairs of data, this is often called pairwise deletion of missing data.
It is possible to specify that the correlations run only on observations that had complete data for all of the variables listed on the var subcommand. You might want the correlations of the reaction times just for the observations that had non-missing data on all of the trials. This is called listwise deletion of missing data meaning that when any of the variables are missing, the entire observation is omitted from the analysis. You can request listwise deletion within corr with the mssing=listwise subcommand, as shown in the example below.
CORR /VAR= trialr1 trialr2 trialr3 /MISSING=LISTWISE.
As you see in the results below, the N for all the simple statistics is the same, 3, which corresponds to the number of cases with complete non-missing data for trial1, trial2 and trial3. Since the N is the same for all of the correlations (i.e., 3), the N is not displayed along with the correlations in SPSS 7.5 and higher.
5. Summary of how missing values are handled in SPSS analysis commands
It is important to understand how SPSS commands used to analyze data treat missing data. To know how any one command handles missing data, you should consult the SPSS manual. Here is a brief overview of how some common SPSS procedures handle missing data.
- DESCRIPTIVES
For each variable, the number of non-missing values are used. You can specify the missing=listwise subcommand to exclude data if there is a missing value on any variable in the list.- FREQUENCIES
By default, missing values are excluded and percentages are based on the number of non-missing values. If you use the missing=listwise subcommand on the frequencies command, the percentages are based on the total number of non-missing and user-missing values and the percentage of user-missing values are reported in the table.- CORRELATIONS
By default, correlations are computed based on the number of pairs with non-missing data (pairwise deletion of missing data). The missing=listwise subcommand can be used on the corr command to request that correlations be computed only on observations with complete valid data for all variables on the var subcommand (listwise deletion of missing data).- REGRESSION
If values of any of the variables on the var subcommand are missing, the entire case is excluded from the analysis (i.e., listwise deletion of missing data). It is possible to further control the treatment of missing data with the missing subcommand and one of the following keywords: pairwise, meansubstitution, or include.- FACTOR
Cases with missing values are deleted listwise, i.e., observations with missing values on any of the variables in the analysis are omitted from the analysis.- ANOVA
Cases with any missing value are excluded from any single complete ANOVA design in which the missing value is encountered. It is possible to specify more than one ANOVA design with a single anova command.- For other commands, see the SPSS manual for information on how missing data are handled.
6. Missing values in assignment expressions
An assignment expression may appear on a compute or an if command. It is important to understand how missing values are handled in assignment statements. Consider the example shown below.
COMPUTE avg = (trial1 + trial2 + trial3) / 3 . COMPUTE avgr = (trialr1 + trialr2 + trialr3) / 3 . LIST /VAR = trial1 TO trial3 avg. LIST /VAR = trialr1 TO trialr3 avgr.
The list below illustrates how missing values are handled in assignment statements. The variable avg is based on the variables trial1 trial2 and trial3, and the variable avgr is based on the variables trialr1 trialr2 and trialr3. If any of the component variables were missing, the value for avg or avgr was set to missing. This means that both were missing for observations 2, 3 and 4.
TRIAL1 TRIAL2 TRIAL3 AVG
1.50 1.40 1.60 1.50
1.50 -9.00 1.90 .
-9.00 2.00 1.60 .
-9.00 -9.00 2.20 .
2.10 2.30 2.20 2.20
1.80 2.00 1.90 1.90
Number of cases read: 6 Number of cases listed: 6
TRIALR1 TRIALR2 TRIALR3 AVGR
1.50 1.40 1.60 1.50
1.50 . 1.90 .
. 2.00 1.60 .
. . 2.20 .
2.10 2.30 2.20 2.20
1.80 2.00 1.90 1.90
Number of cases read: 6 Number of cases listed: 6
Both system-missing and user-defined missing values yield the same results.
As a general rule, computations involving missing values yield missing values, as shown below.
2 + 2 yields 4
2 + . yields .
2 / 2 yields 1
. / 2 yields .
2 * 3 yields 6
2 * . yields .
Whenever you add, subtract, multiply divide etc., values that involve missing data, the result is usually system-missing. An exception is a value that is defined regardless of one of the values, for example zero divided by missing is zero.
In our reaction time experiment, the average reaction time avg is missing for thee out of six cases. We could try just averaging the data for the non-missing trials by using the mean function as shown in the example below.
COMPUTE avg = MEAN(trial1, trial2, trial3) . COMPUTE avgr = MEAN(trialr1, trialr2, trialr3) . LIST /VAR = trial1 TO trial3 avg. LIST /VAR = trialr1 TO trialr3 avgr.
The results below show that avg now contains the average of the non-missing trials, even if there is only one.
TRIAL1 TRIAL2 TRIAL3 AVG
1.50 1.40 1.60 1.50
1.50 -9.00 1.90 1.70
-9.00 2.00 1.60 1.80
-9.00 -9.00 2.20 2.20
2.10 2.30 2.20 2.20
1.80 2.00 1.90 1.90
Number of cases read: 6 Number of cases listed: 6
TRIALR1 TRIALR2 TRIALR3 AVGR
1.50 1.40 1.60 1.50
1.50 . 1.90 1.70
. 2.00 1.60 1.80
. . 2.20 2.20
2.10 2.30 2.20 2.20
1.80 2.00 1.90 1.90
Number of cases read: 6 Number of cases listed: 6
Had there been a large number of trials, say 50 trials,
then it would be annoying to have to type
avg = mean(trial1, trial2, trial3 …. trial50)
Here is a shortcut you could use in this kind of situation
avg = mean(trial1 to trial50)
providing that the trial variables are contiguous in the file.
Also, if we wanted to get the sum of the times instead of the average, then we could just use the sum function instead of the mean function. The syntax of the sum function is just like the mean function, but it returns the sum of the non-missing values.
Finally, you can use the nvalid function to determine the number of non-missing values in a list of variables, as illustrated below.
COMPUTE n = NVALID(trial1, trial2, trial3). LIST /VAR = trial1 TO trial3 avg n.
As you see below, observations 1, 5 and 6 had three valid values, observations 2 and 3 had two valid values, and observation 4 had only one valid value. These results are the same regardless of the type of missing value.
TRIAL1 TRIAL2 TRIAL3 AVG N
1.50 1.40 1.60 1.50 3.00
1.50 -9.00 1.90 1.70 2.00
-9.00 2.00 1.60 1.80 2.00
-9.00 -9.00 2.20 2.20 1.00
2.10 2.30 2.20 2.20 3.00
1.80 2.00 1.90 1.90 3.00
Number of cases read: 6 Number of cases listed: 6
You might feel uncomfortable with the variable avg for observation 4 since it is not really an average at all. We can use the mean.n form of the function to control the number of valid values required to compute a mean.
COMPUTE avg = MEAN.2(trial1 TO trial3). LIST /VAR = trial1 TO trial3 avg n.
The mean.2 function requires at least two valid values for a mean to be calculated. In the output below, you see that avg now contains the average reaction time for the non-missing values, except for observation 4 where the value is assigned to missing because it had only one valid observation.
TRIAL1 TRIAL2 TRIAL3 AVG N
1.50 1.40 1.60 1.50 3.00
1.50 -9.00 1.90 1.70 2.00
-9.00 2.00 1.60 1.80 2.00
-9.00 -9.00 2.20 . 1.00
2.10 2.30 2.20 2.20 3.00
1.80 2.00 1.90 1.90 3.00
7. Missing values in recoding commands
Using IF.
Suppose you wanted to create a dummy variable from trial1 with a cutpoint of 2. We can use the if command to create the variable hit1. The same is true for creating hirt1 from trialr1.
IF trial1 > 2 hit1 = 1.
IF trial1 <= 2 hit1 = 0.
IF trialr1 > 2 hirt1 = 1.
IF trialr1 <= 2 hirt1 = 0.
VARIABLE LABELS hit1 "Tran T1 User Miss"
hirt1 "Tran T1 Sys Miss".
FREQ
/VAR= hit1 hirt1.
The frequencies shows the result of these transformations as they affect the missing values. Both system-missing and user-defined missing values result in correct classification.
Now, suppose you wanted to create a dummy variable from trial1 in combination with trial2 with a cutpoint of two for each. We can use the if command to create the variable hit12. The same is true for creating hirt12 from trialr1 and trialr2.
IF trial1 > 2 and trial2>2 hit12 = 1.
IF not(trial1 > 2 and trial2>2) hit12 = 0.
VARIABLE LABELS hit12 "Tran T1 AND T2".
FREQ
/VAR= hit12 .
LIST
/VAR = trial1 trial2 hit12.
The frequencies and list shows the result of these transformations as they affect the missing values. Both system-missing and user-defined missing values result in the same output, so only the output for user-defined missing values will be shown.
TRIAL1 TRIAL2 HIT12 1.50 1.40 .00 1.50 -9.00 .00 -9.00 2.00 .00 -9.00 -9.00 . 2.10 2.30 1.00 1.80 2.00 .00
There is only one missing value in the created variable hit12, but we know that there are at least two missing values for trial1 alone. If SPSS can resolve the logic based on a single variable, then it will. Since not(trial1 > 2 and trial2>2) is true if either of the conditions is false, this can be resolved. This is the result that most people would prefer.
If you prefer to have the result missing if either of the component variables are missing then that can be accomplished by adding the following if command. As is shown by the results of the frequencies and list commands.
IF MISSING(trial1) OR MISSING(trial2)
hit12 = $SYSMIS.
FREQ
/VAR= hit12 .
LIST
/VAR = trial1 trial2 hit12.
Note that the missing function is evaluated as true if the variable in the argument contains any kind of missing value. If you are exclusively concerned with system-missing values you may want to use the sysmis function. Consider the following results. Any missing value for one of the component variables results in a missing for hit12 .
TRIAL1 TRIAL2 HIT12 1.50 1.40 .00 1.50 -9.00 . -9.00 2.00 . -9.00 -9.00 . 2.10 2.30 1.00 1.80 2.00 .00
Using RECODE
The recode command can be used to accomplish the dummy coding task discussed at the beginning of the section. Once again, suppose you wanted to create a dummy variable from trial1 with a cutpoint of 2. We can use the recode command to create the variable hit1. The same is true for creating hirt1 from trialr1. However, this command functions differently with respect to system-missing and user-defined missing values.
RECODE trial1 (LO THRU 2=0)
(2 THRU HI=1) INTO hi2t1.
RECODE trialr1 (LO THRU 2=0)
(2 THRU HI=1) INTO hi2rt1.
VARIABLE LABELS hi2t1 "Tran T1 User Miss"
hi2rt1 "Tran T1 Sys Miss".
FREQ
/VAR= hi2t1 hi2rt1.
The frequencies shows the result of these transformations as they affect the missing values. The answer is correct with respect to system-missing values and incorrect with respect to user-missing values. The user-defined missing values are classified according to their value, as if they were not missing.
Now we can examine recode with the else keyword. This affects both system-missing and user-defined missing values the same, but unfortunately neither are correct. The else keyword will include both types of missing values, and mis-classify them.
RECODE trial1 (LO THRU 2=0)
(ELSE=1) INTO hi3t1.
RECODE trialr1 (LO THRU 2=0)
(ELSE=1) INTO hi3rt1.
VARIABLE LABELS hi3t1 "Tran T1 User Miss"
hi3rt1 "Tran T1 Sys Miss".
FREQ
/VAR= hi3t1 hi3rt1.
The frequencies result follows.
If we add the (missing=sysmis) to the recode the problem is alleviated for system-missing , but not for user-defined missing values.
RECODE trial1 (LO THRU 2=0) (MISSING=SYSMIS)
(ELSE=1) INTO hi4t1.
RECODE trialr1 (LO THRU 2=0) (MISSING=SYSMIS)
(ELSE=1) INTO hi4rt1.
VARIABLE LABELS hi4t1 "Tran T1 User Miss"
hi4rt1 "Tran T1 Sys Miss".
FREQ
/VAR= hi4t1 hi4rt1.
The frequencies result follows.
Changing the order of (missing=sysmis) and (lo thru 2=0) alleviates the problem for user-defined missing too.
RECODE trial1 (MISSING=SYSMIS) (LO THRU 2=0)
(ELSE=1) INTO hi5t1.
RECODE trialr1 (MISSING=SYSMIS) (LO THRU 2=0)
(ELSE=1) INTO hi5rt1.
VARIABLE LABELS hi5t1 "Tran T1 User Miss"
hi5rt1 "Tran T1 Sys Miss".
FREQ
/VAR= hi5t1 hi5rt1.
The frequencies result follows.
8. Problems to look out for
- When creating or recoding variables, it is always good practice to test the resulting variables, especially for missing values.
- The type of missing value can make a difference in recode results, and in most cases sytem missing values are more likely to yield the correct results.
- Be aware how the use of and and or work with the if command, as the expression is evaluated, and if a logical conclusion can be made, it will be made. This happens even though one or more of the component variables may be missing.
9. For more information
- See Subsetting data in SPSS for information about subsetting data with variables that are missing.
- For more information about missing values, see the SPSS Command Syntax Reference Guide .