NHANES Dietary Web Tutorial: Review Data & Create New Variables: Task 1

Task 1: How to Identify and Recode Missing Data

Here are the steps for identifying and recoding missing data:

Identify missing and unavailable values
Recode unavailable values as missing
Evaluate extent of missing data

Step 1: Identify Missing and Unavailable Values

In this step, you will use the PROC MEANS procedure to check for missing, minimum, and maximum values of continuous variables, and the PROC FREQ procedure to look at the frequency distribution of categorical variables in your analytic dataset. The output from these procedures provides the number and frequency of missing values for each variable listed in the procedure statement.

IMPORTANT NOTE

The following examples will use the PROC MEANS and PROC FREQ procedures on the same set of variables without distinguishing continuous and categorical variables. However, if you use the PROC FREQ procedure on a continuous variable with many values, the output could be extensive.

In the examples below, you will check for missing values as well and minimum and maximum values for the osteoporosis variables used in the sample "Supplement" program.

Program to Identify Missing and Unavailable Values By Means

*----------------------------------------------------------------;
* Use the PROC MEANS procedure to determine the number of        ;
* observations (N), the number of missing observations (Nmiss), ;
* minimum values (min), and maximum values (max) for the         ;
* selected variables. Use the WHERE statement to select the INT ;
* sample weight and to select females who are 20 years of age    ;
* and older. Use the VAR statement to list the variables of     ;
* interest.                                                      ;
*----------------------------------------------------------------;

proc means data =DEMOOST N Nmiss min max ;
where WTINT2YR > 0 and RIAGENDR= 2 and RIDAGEYR >= 20 ;
var OSQ060 OSQ070; run ;

Program to Identify Missing and Unavailable Values By Frequency

*-----------------------------------------------------------------;
* Use the PROC FREQ procedure to determine the frequency of each ;
* value of the variables listed. Use the WHERE statement to      ;
* select the INT sample weight and to select females who are 20   ;
* years of age and older. Use the TABLES statement to list the   ;
* variables of interest. Use the list missing statement option   ;
* to display the missing values.                                  ;
*-----------------------------------------------------------------;

proc freq data =DEMOOST;
where WTINT2YR > 0 and RIAGENDR= 2 and RIDAGEYR >= 20 ;
tables OSQ060 OSQ070/ list missing ;
run ;

Output of Programs

Click here to view program output and highlights

The MEANS Procedure

Variable Label N Miss Minimum Maximum

-----------------------------------------------------------------------------------------------

OSQ060 Ever told had osteoporosis/brittle bones 2622 1 1.0000000 9.0000000

OSQ070 Ever treated for osteoporosis 320 2303 1.0000000 9.0000000

----------------------------------------------------------------------------------------------

The FREQ Procedure

Ever told had osteoporosis/brittle bones

Cumulative Cumulative

OSQ060 Frequency Percent Frequency Percent

----------------------------------------------------------------

. 1 0.04 1 0.04

Yes 320 12.20 321 12.24

No 2292 87.38 2613 99.62

9 10 0.38 2623 100.00

Ever treated for osteoporosis

Cumulative Cumulative

OSQ070 Frequency Percent Frequency Percent

----------------------------------------------------------------

. 2303 87.80 2303 87.80

Yes 232 8.84 2535 96.65

No 85 3.24 2620 99.89

9 3 0.11 2623 100.00

Highlighted items from the PROC MEANS and PROC FREQ output:

The column labeled “N” shows the number of observations with data. For example, 2622 women aged 20 years and older have data for the variable OSQ060.
The column labeled “N Miss” indicates the number of observations without data.
Each response value of a variable has a corresponding frequency (check the codebook to determine the definition for each value). In this example, the variable OSQ060, labeled “Ever told had osteoporosis/brittle bones,” has four possible response values labeled “.” (missing), “1,” “2,” and “9.”
• The column labeled “Frequency” indicates the frequency with which a particular response value occurs in the dataset. In this example, the variable OSQ060 has one missing observation, 320 observations with a value of “1” indicating a “Yes” response, 2,292 observations with a value of “2” indicating a “No” response, and 10 observations with a value of “9” indicating a “don’t know” response.
The column labeled “Percent” indicates the percentage for which each value of the variable accounts, out of the total. The “1” and “2” response values of OSQ060 account for 12.20% and 87.38% of the total, respectively.
Note for the variable OSQ060, labeled as “Ever told has osteoporosis/brittle bones,” 10 observations have a value of “9.” These represent the frequency of “don’t know” responses that were obtained for this question. These observations will need to be recoded as missing, which will be covered in the next step.

Click here to close program output and highlights

Step 2: Recode Unavailable Values as Missing

To recode missing data, assign missing values one variable at a time using an IF…THEN statement, as demonstrated in the excerpt of the "Supplement" program below.

Program to Assign Missing Values One Variable at a Time

*-------------------------------------------------------------;
* Recode DONT KNOW responses to missing for OS1060 and OSQ070 ;
*-------------------------------------------------------------;

data DEMOOST;
    set DEMOOST;
    if OSQ060= 9 then OSQ060= . ;
    if OSQ070= 9 then OSQ070= . ;
run ;

Step 3: Evaluate Extent of Missing Data

In this step you will use the PROC FREQ procedure to ensure that the variables were recoded correctly in the previous step. This example is from the "Supplement" program.

Program to Check the Extent of Missing Data

*-------------------------------------------------------------------;
* Use the PROC FREQ procedure to determine the frequency of each    ;
* value of the variables listed. Use the WHERE statement to select ;
* the INT sample weight and to select females who are 20 years of   ;
* age and older. Use the TABLES statement to list the variables of ;
* interest. Use the list missing statement option to display the   ;
* missing values.                                                   ;
*-------------------------------------------------------------------;

proc freq data =DEMOOST;
where WTINT2YR > 0 and RIAGENDR= 2 and RIDAGEYR >= 20 ;
tables OSQ060 OSQ070/ list missing ;
run ;

Output of Program

Click here to view program output and highlights

Check skip pattern for osteoporosis questionnaire

The FREQ Procedure

Ever told had osteoporosis/brittle bones

                                          Cumulative    Cumulative
       OSQ060    Frequency     Percent     Frequency      Percent
       ----------------------------------------------------------------
       .               11        0.42            11         0.42
       Yes             320       12.20           331        12.62
       No             2292       87.38          2623       100.00

Ever treated for osteoporosis

Cumulative Cumulative
OSQ070 Frequency Percent Frequency Percent

----------------------------------------------------------------

. 2306 87.91 2306 87.91

Yes 232 8.84 2538 96.76
No 85 3.24 2623 100.00

Highlighted items from the PROC FREQ output for recoding missing values:

In this example, the variable OSQ060, labeled as “Ever told had osteoporosis/brittle bones” now has only three response values instead of the original four observed before recoding — the “9” value is no longer present. Also note that there are now a total of 11 missing values (instead of one originally).
Review of this output indicates that the “9” values have been successfully recoded and are now classified as missing (.).

Click here to close program output and highlights

Close Window to return to module page.

Task 1: How to Identify and Recode Missing Data

Step 1: Identify Missing and Unavailable Values

Program to Identify Missing and Unavailable Values By Means

Sample Code

Program to Identify Missing and Unavailable Values By Frequency

Sample Code

Output of Programs

Step 2: Recode Unavailable Values as Missing

Program to Assign Missing Values One Variable at a Time

Sample Code

Program to Check the Extent of Missing Data

Sample Code

Output of Program