SAS: How to Select Observations Which are Not Null


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.

Additional Resources

The following tutorials explain how to perform other common tasks in SAS:

How to Normalize Data in SAS
How to Rename Variables in SAS
How to Remove Duplicates in SAS
How to Replace Missing Values with Zero in SAS

Leave a Reply

Your email address will not be published.