|
|
|
||||
|
|
|||||
Sometimes, a data set may have "holes" in them, i.e., missing values and we may want to know the number of missing values of all the variables and the distribution of the missing values. We will use the following data set as our example data set.
data test;
input landval improval totval salepric saltoapr city $6. season $8.;
datalines;
30000 64831 94831 118500 1.25 A spring
30000 50765 80765 93900 . winter
46651 18573 65224 . 1.16 B
45990 91402 . 184000 1.34 C winter
42394 . 40575 168000 1.43
. 3351 51102 169000 1.12 D winter
63596 2182 65778 . 1.26 E spring
56658 53806 10464 255000 1.21
51428 72451 . . 1.18 F spring
93200 . 4321 422000 1.04
76125 78172 54297 290000 1.14 G winter
. 61934 16294 237000 1.10 H spring
65376 34458 . 286500 1.43 winter
42400 . 57446 . . K
40800 92606 33406 168000 1.26 S
;
run;
The first thing we are going to look at the variables that have a lot of missing values. For numerical variables, we use proc means with the options n and nmiss.
proc means data = test n nmiss;
var _numeric_;
run;
N
Variable N Miss
----------------------
LANDVAL 13 2
IMPROVAL 12 3
TOTVAL 12 3
SALEPRIC 11 4
SALTOAPR 13 2
For character variables, we can use proc freq to display the number of missing values in each variable.
proc freq data = test; tables city season ; run;Cumulative Cumulative city Frequency Percent Frequency Percent --------------------------------------------------------- A 1 10.00 1 10.00 B 1 10.00 2 20.00 C 1 10.00 3 30.00 D 1 10.00 4 40.00 E 1 10.00 5 50.00 F 1 10.00 6 60.00 G 1 10.00 7 70.00 H 1 10.00 8 80.00 K 1 10.00 9 90.00 S 1 10.00 10 100.00 Frequency Missing = 5 Cumulative Cumulative season Frequency Percent Frequency Percent ----------------------------------------------------------- spring 4 44.44 4 44.44 winter 5 55.56 9 100.00 Frequency Missing = 6
We can also look at the number of missing values in each observation. For example, we can use SAS function nmiss to store the number of missing values from all the numeric variables in each observation. An array of all the character variables is created for counting the missing values in all the character variables.
data test1;
set test;
miss_num = nmiss(of landval -- saltoapr);
array mychar(*) $ _character_;
miss_char = 0;
do i = 1 to dim(mychar);
if mychar(i) ="" then miss_char=miss_char+1;
end;
drop i;
run;
proc print data = test1;
run;
miss_
Obs landval improval totval salepric saltoapr city season miss_num char
1 30000 64831 94831 118500 1.25 A spring 0 0
2 30000 50765 80765 93900 . winter 1 1
3 46651 18573 65224 . 1.16 B 1 1
4 45990 91402 . 184000 1.34 C winter 1 0
5 42394 . 40575 168000 1.43 1 2
6 . 3351 51102 169000 1.12 D winter 1 0
7 63596 2182 65778 . 1.26 E spring 1 0
8 56658 53806 10464 255000 1.21 0 2
9 51428 72451 . . 1.18 F spring 2 0
10 93200 . 4321 422000 1.04 1 2
11 76125 78172 54297 290000 1.14 G winter 0 0
12 . 61934 16294 237000 1.10 H spring 1 0
13 65376 34458 . 286500 1.43 winter 1 1
14 42400 . 57446 . . K 3 1
15 40800 92606 33406 168000 1.26 S 0 1
We can also look at the patterns of missing values. We can recode each variable into a dummy variable such that 1 is missing and 0 is nonmissing. Then we use the proc freq with statement tables with option list to compute the frequency for each pattern of missing data.
data miss_pattern (drop=i); set test; array mynum(*) _numeric_; do i=1 to dim(mynum); if mynum(i) =. then mynum{i}=1; else mynum(i)=0; end; array mychar(*) $ _character_; do i=1 to dim(mychar); if mychar(i) ="" then mychar{i}=1; else mychar(i)=0; end; run; proc freq data=miss_pattern; tables landval*improval*totval*salepric*saltoapr*city*season /list; run;landval improval totval salepric saltoapr city season --------------------------------------------------------------------------- 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 1 1 0 0 0 0 1 1 0 0 0 0 1 0 0 0 0 0 0 1 0 0 1 0 0 1 0 0 0 0 0 0 1 0 0 1 0 0 0 1 1 0 0 0 0 1 0 0 0 1 1 0 1 0 1 1 0 1 1 0 0 0 0 0 0 Cumulative Cumulative Frequency Percent Frequency Percent ------------------------------------------------- 2 13.33 2 13.33 1 6.67 3 20.00 1 6.67 4 26.67 1 6.67 5 33.33 1 6.67 6 40.00 1 6.67 7 46.67 1 6.67 8 53.33 1 6.67 9 60.00 1 6.67 10 66.67 2 13.33 12 80.00 1 6.67 13 86.67 2 13.33 15 100.00
Now we see that there are two observations with no missing values, one observation with one missing value in variable season, and so on.
UCLA Researchers are invited to our Statistical Consulting Services
We recommend others to our list of Other Resources for Statistical Computing Help
These pages are Copyrighted (c) by UCLA Academic Technology Services