How to detect missing values in a data file by SAS?

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;

1. Number of missing values vs. number of non missing values in each variable

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;

Variable N NMiss
———————-
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/missing;
run;

2. Number of missing values in each observation

We can also look at the number of missing values in each observation. For example, we can use SAS CMISS() function to store the number of missing values from both numeric and character variables in each observation.

data test1;
set test;
miss_n = cmiss(of landval -- season);
run;

proc print data = test1;
run;

3. Distribution of missing values

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;

Now we see that there are two observations with no missing values, one observation with one missing value in variable season, and so on.

• This discussion was modified 3 years, 9 months ago by  Datura.
• This discussion was modified 3 years, 9 months ago by  Datura.