Open In App

SAS | Delete Empty Rows

Last Updated : 30 Jul, 2019
Improve
Improve
Like Article
Like
Save
Share
Report

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.




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;


Output:

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




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

    
    

    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




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

    
    

    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 –



Similar Reads

How to import data into SAS?
Entering Data Directly: You can enter numbers of lines of data directly in SAS program by using a DATALINES statement. The keywords are as follows: DATA: The DATA step always starts with a DATA statement. The purpose of the DATA statement is to tell SAS that you are creating a new data set i.e. outdata. DATA outdata; INPUT: To define the variables
3 min read
How to import External Data from Excel or Text file into SAS Programming?
PROC IMPORT: It is a procedure to import external files into SAS. It automates importing process. There is no need to specify the variable type and variable-length to import an external file. It supports various formats of files such as excel, txt, etc. Importing an Excel File into SAS: The main keywords used in the following program are: OUT: To s
2 min read
How to Create or Modify a Variable in SAS Programming?
This will help you to create or modify a variable. It is common to define a new variable based on the existing variable. Let's create a dataset In the code below, we are creating a dataset named as Example1 which is going to store on WORK(temporary) library. In this dataset, there would be a variable called OldRate which contains a numeric value. T
2 min read
Contains and Between-And operators in SAS Programming
BETWEEN-AND Operator: Between Two Numbers Task 1: Suppose you want to select scores whose values are greater than or equal to 50 and less than or equal to 90. data readin; input name $ Section $ Score; cards; Raj A 80 Atul A 77 Priya B 45 Sandeep A 95 Rahul C 84 Shreya C 44 ; run; data readin1; set readin; where Score between 50 and 90; run; where
1 min read
How to drop variables from a dataset in SAS Programming?
This topic is regarding how to drop variables from a dataset in SAS. It includes various methods to delete variables from data. In SAS, there are two ways to drop variables: DROP = data set option DROP statement Let's start with creating a data set: DATA outdata; INPUT roll_num gender $ class subj1 subj2 subj3; DATALINES; 21 F 6 10 17 20 13 F 6 21
2 min read
Where Statement in SAS Programming
The WHERE statement is a substitute to IF statement when it comes to subsetting a data set. Syntax: WHERE (condition is true) => It refers to subsetting a dataset. Task1 : Suppose you want to select only section A students. You need to filter Section variable equals to A using where clause. data readin; input name $ Section $ Score; cards; Raj A
2 min read
If-Then-Else statement in SAS Programming
Comparison Operators used while using conditional statements. Symbol Mnemonic Meaning = EQ equals ^= or ~= NE not equal > GT greater than < LT less than >= GE greater than or equals <= LE less than or equals in IN selecting multiple values IF statement Syntax: IF (condition is true) => It means subsetting a dataset. Example: Data rea
3 min read
IS MISSING and IS NOT MISSING Operators in SAS Programming
IS MISSING Operator: Selecting Missing Values Task 1: Suppose you want to select only those observations in which students did not fill their section information. data readin; input name $ Section $ Score; cards; Raj A 80 Atul . 77 Priya B 45 Sandeep A 95 Rahul . 84 Shreya . 44 ; run; data readin1; set readin; where Section is missing; run; Output:
1 min read
Like Operator in SAS Programming
LIKE Operator: Pattern Matching The LIKE operator used to select data by comparing the values of a character variable to a specified pattern. It is case sensitive. Task 1: To select all students with a name that starts with the letter S. There are two special characters patterns available for specifying a pattern: percent sign (%) - Wildcard Charac
1 min read
SAS | How to specify a list of Variables?
Suppose you have a list of variables. You don't want to type the name of each variable every time to define them within the function or array. You are looking for a shortcut to accomplish this task. Create a dataset with a list of variables data dummy; input a1 a3 a4 a2 a6$ bs$ a5; cards; 2 1 3 5 aa xy 2 2 5 4 1 ab xz 4 2 7 3 9 ac wx 3 ; run; Outpu
2 min read