Sometimes, a data set may have “holes” in it, that is, missing values. Some statistical procedures such as regression analysis will not work as well, or at all, on a data set with missing values. The observations with missing values have to be deleted or the missing values have to be substituted in order for a statistical procedure to produce meaningful results. Most statistical programs (including SAS, SPSS, and Stata) will automatically remove such cases from any analysis you run (without deleting the cases from the dataset). This is why the “n” often varies from analysis to analysis, even if the dataset is the same. Different variables have different amounts of missing data and hence, changing the variables in a model changes the number of cases with complete data on all the variables in the model. Because the software drops cases with missing values for us, it is very easy to “forget” about missing data entirely. However, the presence of missing data can influence our results, especially when a dataset or even a single variable, has a high percentage of values missing. Thus it is always a good idea to check a dataset for missing data, and to think about how the missing data may influence our analyses. This page shows a few methods of looking at missing values in a dataset, this information can be used to make better informed decisions about how to handle the missing values.
Before we begin, we need some data with missing values, the code below inputs a small dataset into Stata, and then displays that data. In a small dataset, like the one below, it is very easy to look at the raw data and see where values are missing. However, when datasets are large, we need a more systematic way to examine our dataset for missing values. Below we show you some ways to do that, using the data below as an example.
clear
input landval improval totval salepric saltoapr
30000 64831 94831 118500 1.25
30000 50765 80765 93900 .
46651 18573 65224 . 1.16
45990 91402 . 184000 1.34
42394 . 40575 168000 1.43
. 3351 51102 169000 1.12
63596 2182 65778 . 1.26
56658 53806 10464 255000 1.21
51428 72451 . . 1.18
93200 . 4321 422000 1.04
76125 78172 54297 290000 1.14
. 61934 16294 237000 1.10
65376 34458 . 286500 1.43
42400 . 57446 . .
40800 92606 33406 168000 1.26
end
list
+---------------------------------------------------+
| landval improval totval salepric saltoapr |
|---------------------------------------------------|
1. | 30000 64831 94831 118500 1.25 |
2. | 30000 50765 80765 93900 . |
3. | 46651 18573 65224 . 1.16 |
4. | 45990 91402 . 184000 1.34 |
5. | 42394 . 40575 168000 1.43 |
|---------------------------------------------------|
6. | . 3351 51102 169000 1.12 |
7. | 63596 2182 65778 . 1.26 |
8. | 56658 53806 10464 255000 1.21 |
9. | 51428 72451 . . 1.18 |
10. | 93200 . 4321 422000 1.04 |
|---------------------------------------------------|
11. | 76125 78172 54297 290000 1.14 |
12. | . 61934 16294 237000 1.1 |
13. | 65376 34458 . 286500 1.43 |
14. | 42400 . 57446 . . |
15. | 40800 92606 33406 168000 1.26 |
+---------------------------------------------------+
1. Number of missing values vs. number of non missing values
The first thing we are going to do is determine which variables have a lot of missing values. We have created a small Stata program called mdesc that counts the number of missing values in both numeric and character variables. You can download mdesc from within Stata by typing search mdesc (see How can I use the search command to search for programs and get additional help? for more information about using search).
Then you can run mdesc for one or more variables as illustrated below.
mdesc
Variable Missing Total Missing/Total
------------------------------------------------------------
landval 2 15 .133333
improval 3 15 .2
totval 3 15 .2
salepric 4 15 .266667
saltoapr 2 15 .133333
Now we know the number of missing values in each variable. For instance, variable salepric has four missing values and saltoapr has two missing values.
2. Obtaining the number of missing values per observation
We can also look at the distribution of missing values across observations. The code below creates a variable called nmis that gives the number of missing values for each observation. The function rmiss2() used here is an extension to the egen function rmiss(). It counts the number of missing values in the varlist. rmiss2() accepts both string and numeric variables. (Stata’s rmiss() only accepts numeric variables.) You can download rmiss2() over the internet from within Stata by typing search rmiss2 (see How can I use the search command to search for programs and get additional help? for more information about using search).
egen nmis=rmiss2(landval improval totval salepric saltoapr)
Below we tabulate the variable we just created. Looking at the frequency table we know that there are four observations with no missing values, nine observations with one missing values, one observation with two missing values and one observation with three missing values.
tab nmis
nmis | Freq. Percent Cum.
------------+-----------------------------------
0 | 4 26.67 26.67
1 | 9 60.00 86.67
2 | 1 6.67 93.33
3 | 1 6.67 100.00
------------+-----------------------------------
Total | 15 100.00
3. Patterns of missing values
We can also look at the patterns of missing values. You can download mvpatterns over the internet from within Stata by typing search mvpatterns (see How can I use the search command to search for programs and get additional help? for more information about using search). The command mvpatterns produces output for all variables in the dataset, for missing data patterns across a subset of variables, a variable list can be included, for example, mvpatterns landval improval totval.
The output produced by mvpatterns is shown below. The first table lists the variables, their storage type (type), the number of observations (obs), the number of missing values (mv), and the variable label if the variables has one. The second table contains the information on the pattern of missing values. The first block of columns in the output shows the patterns of missing data. Within the block, each variable is represented by a column, a “+” indicates that values of that variable are present in a given missing data pattern, a “.” indicates that they are missing. The columns follow the same order as the variable list in the first table, so that the first column in the output below represents landval, the second improval, and so on. The missing data patterns are listed in descending frequency, here the most common missing data pattern is complete data (“+++++”). The table also shows the number of missing values in that pattern (_mv), and the number of cases with that missing data pattern (_freq). Based on the information in the second table we know that there are four observations with no missing values, two cases missing on just the variable salepric, and one observation with missing values on improval, salepric and saltoapr.
mvpatterns Variable | type obs mv variable label -------------+----------------------------------- landval | float 13 2 improval | float 12 3 totval | float 12 3 salepric | float 11 4 saltoapr | float 13 2 ------------------------------------------------- Patterns of missing values +------------------------+ | _pattern _mv _freq | |------------------------| | +++++ 0 4 | | +++.+ 1 2 | | ++.++ 1 2 | | +.+++ 1 2 | | .++++ 1 2 | |------------------------| | ++++. 1 1 | | ++..+ 2 1 | | +.+.. 3 1 | +------------------------+
4. When all the variables of interest are numeric
The examples above all work regardless of whether the variables of interest (i.e. the variables whose missing data patterns you want to examine) are numeric or string. When all of the variables you wish to check for missing values are numeric we can use a program called misschk to simplify the steps of examining the missing data in our dataset. (Note: numeric variables include those with value labels that are strings, as long as the actual values of the variables are stored as numbers.) You can download misschk from within Stata by typing search misschk (see How can I use the search command to search for programs and get additional help? for more information about using search).
Below is the command for misschk. We have listed all five of the variables in our dataset in the variable list after the misschk command. However, we could have just left the list of variables blank (i.e. used only misschk , gen(miss) instead), if we had, misschk would have run using all the variables in our dataset. The variable list is only necessary if we want to run misschk on only some of the variables in our dataset. The gen(miss) option tells misschk that we want it to create two new variables, both of which start with “miss”. These two variables will be named misspattern and missnumber. The variable misspattern indicates which of the missing data patterns each case follows. The variable missnumber indicates the number of missing values for each case.
misschk landval improval totval salepric saltoapr, gen(miss)
The output for misschk consists of three tables. The first table lists the number of missing values, as well as percent missing for each variable, this is similar to the table produced by mdesc in part 1 above. This table also contains a column labeled “#” which assigns each variable a number that is used to identify the variable later on in the output. The second table shows the distribution of missing values. The pattern of missingness is described using the variable numbers from the first table, and underscores (“_”). The numbers indicate which variables are missing in that pattern, the underscores represent non-missing observations. For example, from the second table we see that two cases have missing values on variable 1 (landval), but complete data on all other variables, and that one case is missing data on variables 2, 4, and 5. The bottom row shows that four cases are not missing any values at all (all underscores). This table shows the same information generated in part three above, but in a slightly different format. The missing data pattern for each case is described in the variable misspattern. Finally, the third table shows the distribution of the number of missing values per case. This is the same information discussed above in part 2. The number of variables each case is missing is also contained in the variable missnumber.
Variables examined for missing values
# Variable # Missing % Missing
--------------------------------------------
1 landval 2 13.3
2 improval 3 20.0
3 totval 3 20.0
4 salepric 4 26.7
5 saltoapr 2 13.3
Missing for |
which |
variables? | Freq. Percent Cum.
------------+-----------------------------------
1____ | 2 13.33 13.33
_2_45 | 1 6.67 20.00
_2___ | 2 13.33 33.33
__34_ | 1 6.67 40.00
__3__ | 2 13.33 53.33
___4_ | 2 13.33 66.67
____5 | 1 6.67 73.33
_____ | 4 26.67 100.00
------------+-----------------------------------
Total | 15 100.00
Missing for |
how many |
variables? | Freq. Percent Cum.
------------+-----------------------------------
0 | 4 26.67 26.67
1 | 9 60.00 86.67
2 | 1 6.67 93.33
3 | 1 6.67 100.00
------------+-----------------------------------
Total | 15 100.00