How to Remove Rows with Missing Values in SAS


You can use the following basic syntax to remove rows with missing values from a dataset in SAS:

data new_data;
    set my_data;
    if cmiss(of _all_) then delete;
run;

This particular example creates a new dataset called new_data where any rows with missing values from the original dataset called my_data have been deleted.

The following example shows how to use this syntax in practice.

Example: Remove Rows with Missing Values in SAS

Suppose we have the following dataset in SAS that contains information about various basketball teams:

/*create dataset*/
data my_data;
    input team $ points assists;
    datalines;
Mavs 113 22
Pacers 95 .
Cavs . .
Lakers 114 20
Heat 123 39
Kings . 22
Raptors 105 11
Hawks 95 25
Magic 103 26
Spurs 119 .
;
run;

/*view dataset*/
proc print data=my_data;

Notice that there are several rows with missing values.

We can use the following code to create a new dataset in which we remove all rows from the existing dataset that have missing values in any column:

/*create new dataset that removes rows with missing values from existing dataset*/
data new_data;
    set my_data;
    if cmiss(of _all_) then delete;
run;

/*view new dataset*/
proc print data=new_data;

We can see that all rows with missing values have been removed from the dataset.

Note #1: The argument _all_ within the CMISS function specifies that SAS should look for missing values in all columns for each row.

Note #2: You can find the complete documentation for the CMISS function here.

Additional Resources

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

How to Remove Duplicates in SAS
How to Count Missing Values in SAS
How to Replace Missing Values with Zero in SAS

Leave a Reply

Your email address will not be published. Required fields are marked *