Stata tip #35: Changing missing values to numeric values and vice versa

Data manipulation is an important part of data analysis, which ensures accuracy of the results you get. Stata is one of the best packages available for data manipulation; Stata not only allows you to choose between command-based and menu-based options to manipulate a data set, you can also choose from a wide range of commands to manipulate data according to your specific needs. Below are a list of common commands for data manipulation you should know by now:

generate, replace, recode, clonevar, label, note, drop, keep, encode

In this tip, I want to introduce to you two new commands useful for data manipulation. These two commands are mvencode and mvdecode.

Description: mvencode changes missing values to a specified numeric value for (any number of) variables in your data set; mvdecode works just like the opposite, and changes a specified numeric value (of any number) of variables to missing values.

Let’s learn mvencode and mvdecode in a context – using examples.

NOTE: I am using The Asia Foundation’s A Survey of the Afghan People (2014) data in my examples. If you do not have this data, you can download it from the following below:

http://afghansurvey.asiafoundation.org/download/

Examples:

Suppose you want to get rid of “don’t know” responses in your data set, which are coded 99. Why would you want to do that? Perhaps you want to run regression, correlation, or a ttest, whose result will be biased if you have “don’t know” responses, coded 99, since its variance is meaningless to your analysis.

There is a question (Q57) in A Survey of the Afghan People (2014) asking how much the respondent has sympathy with armed opposition groups. The responses to the question include: “a lot of sympathy,” “some sympathy,” and “no sympathy at all.” But there are some survey respondents who refused to answer this question, or responded “don’t know.” As you see below, responses “refused” and “don’t know” are coded 98 and 99 respectively, and by including these responses to our analysis, we are biasing our results.

So in this case, and other similar cases, which you need to get rid of all 99s, or any other value, a number of commands can be used: replace, recode, and mvdecode. In the following, I explain how they to use each of these codes for our purpose.

Generally replace is used to change contents of a variable. For instance, to change all code 99s from your variable q57 to missing values, replace does it in the following manner:

replace q57=. if q57==99

recode is a very powerful command to use for data manipulation (see Stata tips #3 and #16 for more detailed information). recode changes the values of numeric variables according to rules that you specify. In addition to what replace does, recode allows for more convenient data manipulation, and create label values.

Using recode, to change all value 99s to missing values in variable q57, you will do the following:

recode q57 (99=.)

this command leaves values that do not meet any of the conditions of the rules unchanged, i.e. here any values except 99. In other words, values 1, 2, 3 and 98 will be left unchanged.

Another command that you could use to change numeric values (e.g. 99) to missing is mvdecode. While replace and recode are general purpose commands with their own merits and shortcomings, mvdecode is a command specially designed for changing values to missing. In other words, the only function of mvdecode is to change numeric values to missing values.

To change a numeric value to missing value using mvdecode, you have to type the command followed by variable list, and then followed by a comma and list of numbers in parentheses:

mvdecode q57, mv(99)

this command changes all value 99s in variable q57 to missing values.

If you are interested to change more values, for instance 98 too, you need to add it inside the parentheses beside 99. If you are interested to bring the mentioned change to more than just one variable, you could type more variable names after mvdecode and next to q57. And if you want to change all variables’ 99 and 98 values to missing, you could use shortcut ­_all, which means all variables:

mvdecode q1 q57, mv(98 99)

this command replaces variables q1 and q57’s values 98 and 99 to missing values.

mvdecode _all, mv(98 99)

this command changes values 98 and 99 to missing values in all variables (_all) of your data set.

Now that you are able to change numeric values to missing values using replace, recode and mvdecode, how about changing missing values to a numeric value?

As you might already guessed, commands replace and recode could change missing values to a numeric value too. The procedure is similar to what we have done earlier with changing value 99s in variable q57 to missing values. Now let’s do the opposite, and change the missing values to value 99 using replace and recode:

replace q57=99 if q57==.

recode q57 (.=98)

To change missing values to a numeric value, like what I just did, there is a special command called mvencode. Noticed the similarity between mvencode and mvdecode? They do exactly the opposite of each other, i.e. mvdecode changes numeric values to missing values, while mvencode changes missing values to numeric values, making them inverse twins.

The syntax for mvencode is extremely similar to mvdecode too, but does exactly the opposite of it:

mvencode q57, mv(99)

the above command changes missing values to 99.

You can also use more than one variable with mvencode, but you cannot have more than one numeric value, such as 99, to convert missing values to. You can also use _all if you want the operation to apply on all variables in your data set.

A general tip: as you notice, usually you can use several commands to obtain your goal in Stata. But that does not mean you only should learn one of them. Because, each command or method has their own merits and shortcomings, and learning all these commands will help you perform various tasks, and saves your time.

Stata tip #34: tab1

Command tabulate or tab is perhaps the first and the most useful commands for any Stata user. Command tab displays a table of frequencies and percentages for a variable, when used with one variable. When used with two variables, it produces a two-way table of frequencies distribution. You could add percentages to the two-way table by including column and row percentages.

Recently I came across a command called tab1, which is an offspring of command tabulate, used to create table of frequencies and percentages for more than one variable, in one command. In other words, if you want to perform tabulate command more than once, and type as many line of code as the number of variables, tab1 can save time by doing so with just one line of code.

You could also think of tab1 as a loop – see Stata tip #33 for loops – which repeats command tabulate for a list of variables.

Let’s see some examples now.

NOTE: I am using The Asia Foundation’s A Survey of the Afghan People (2014) data in my examples. If you do not have this data, you can download it from the following below:

http://afghansurvey.asiafoundation.org/download/

Examples:

Suppose you want to see frequency distribution (table of frequencies) of q6a, q6b, q6c, q6d, q6e and q6f questions. The long way is to type five commands for six mentioned variables:

tab q6a

tab q6b

tab q6c

tab q6d

tab q6e

tab q6f

Another way of doing the above 6 commands is to use loops, command foreach (see Stata tip #33):

foreach x in q6a q6b q6c q6d q6e q6f {

tab `x’

}

A shorter and more straightforward command for doing tab multiple times is to use tab1. For tab1, all you have to do is list all the variables you want after tab1, like below:

tab1 q6a q6b q6c q6d q6e q6f

Although loops are usually the most convenient way of performing a single command over a number of variables, tab1 is the easiest way of obtaining table of frequencies and percentages, i.e. looping tab, for a number of variables.

There are some options available with command tab1, as well as tab. The most commons are missing, nolabel, plot and sort. As a general rule in Stata, options come after a comma.

Option missing requests that missing values, if available, be treated like other values in counts, percentages and other statistics (in composite command tab, sum( ) – see Stata tip #5). The command with missing would look like:

tab1 q6a q6b q6c q6d q6e q6f, missing

Option nolabel causes the numeric codes to be displayed rather than the value labels:

tab1 q6a q6b q6c q6d q6e q6f, nolabel

Option plot adds a simple bar chart of relative frequencies, while omitting the percentage and cumulative columns, to the command tab1:

tab1 q6a q6b q6c q6d q6e q6f, plot

And finally, option sort puts the tables in descending order of frequency/percentage.

Note, if interested, there is no limit to the number of options to be used with one command. Therefore, you can use any number of options at once.