Table of Contents
To select observations which are not null in SAS, the WHERE clause is used. This clause is used to specify conditions that the observations must meet in order to be included in the output dataset. The condition is specified as the variable (or variables) being not equal to a missing value, which is indicated by the keyword ‘missing’. This can be done by writing WHERE variable NE MISSING. This will select only observations that have a value for the specified variable and exclude observations that are missing values.
You can use the following basic syntax to select observations in a dataset in SAS where a certain column value is not null:
/*select only rows where var1 is not null*/
proc sql;
select *
from my_data1
where not missing(var1);
quit;
The following example shows how to use this syntax in practice.
Example: Select Observations Which are Not Null in SAS
Suppose we have the following dataset in SAS:
/*create dataset*/
data my_data1;
input team $ points;
datalines;
A 15
B .
C 22
D 19
E 29
F .
G 40
H 35
;
run;
/*view dataset*/
proc print data=my_data1;
Notice that there are some null values in the points column.
We can use the following code to select all of the rows where the value in the points column is not null:
/*select only rows where points is not blank*/
proc sql;
select *
from my_data1
where not missing(points);
quit;
Notice that only the rows where the value in the points column is not null are returned.
Note that you could also use the count() function in proc sql to count the number of observations where the value in the points column is not null:
/*count rows where points is not blank*/
proc sql;
select count(*)
from my_data1
where not missing(points);
quit;
This tells us that 6 observations in the dataset have a value that is not null in the points column.