SAS | Delete Empty Rows

Generally, when we import data from external sources such as Excel/CSV files, it loads additional rows that are totally blank. Sometimes empty values in the database also affect the desired output so it’s necessary to check missing cases and perform operations accordingly

Example:
Input: The sample dataset looks like below have four variables – 1 character and 3 numeric. It would be used further in the example to demonstrate how to remove empty rows.

Name Phys Chem Maths
Shubhash 70 68 66
Samar 55 85
Ashutosh 54 78 89
Varun 50 96 85
Pratiksha 68 93

Create a SAS dataset
The below defined code is a sample dataset to perform delete empty operation.

filter_none

edit
close

play_arrow

link
brightness_4
code

data outdata;
LENGTH name $12.;
input name $ phys chem maths ;
infile datalines missover;
datalines;Shubhash 70 68 66
    samar 55 . 85 
    ashutosh 54 78 89 
    varun 50 96 85 
    pratiksha . 68 93 
;run;

chevron_right


Output:

  • Method I: Removes complete row where all variables having blank/missing values
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    OPTIONS missing = ' ';
    data readin;
        SET outdata;
        IF missing(cats(of _all_)) 
        THEN 
          DELETE;
    run;

    chevron_right

    
    

    Note:

    • The MISSING= system option is used to display the missing values as a single space rather than as the default period (.) options missing = ‘ ‘;
    • The CATS function concatenates the values. It also removes leading and trailing blanks. cats(of _all_) – Concatenate all the variables
    • missing(cats(of _all_)) – Identifies all the rows in which missing values exist in all the variables.

    Output:

  • Method II: Removes only that rows where any of the variable has missing/blank values
    filter_none

    edit
    close

    play_arrow

    link
    brightness_4
    code

    data readin;
        SET outdata; 
        IF cmiss(of _character_) 
        OR nmiss(of _numeric_) > 0
         THEN 
          DELETE;
    run;

    chevron_right

    
    

    In this case, we are using the OR operator to check if any of the variables have missing values. It returns 4 observations. Check out the output below –




My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.