DATA Step, Macro, Functions and more

Delete empty rows from a dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Delete empty rows from a dataset

Hi All,

Is there any way to delete a row if all the coulmns in the row has empty values.

Thanks in advance.

Accepted Solutions
Solution
‎08-30-2017 01:44 PM
Regular Contributor
Regular Contributor
Posts: 166

Re: Delete empty rows from a dataset

Posted in reply to kishore415
Hi,
Had read this somewhere.
It should help you...


data work.test;
infile cards missover;
input (a b c) ($1.) x y z;
if missing(coalesceC(of _character_)) and missing(coalesce(of
_numeric_)) then delete;
cards;
abc 1 2 3

def 3 4 5
;;;;
run;
proc print;
run;

View solution in original post


All Replies
Solution
‎08-30-2017 01:44 PM
Regular Contributor
Regular Contributor
Posts: 166

Re: Delete empty rows from a dataset

Posted in reply to kishore415
Hi,
Had read this somewhere.
It should help you...


data work.test;
infile cards missover;
input (a b c) ($1.) x y z;
if missing(coalesceC(of _character_)) and missing(coalesce(of
_numeric_)) then delete;
cards;
abc 1 2 3

def 3 4 5
;;;;
run;
proc print;
run;
Occasional Contributor
Posts: 14

Re: Delete empty rows from a dataset

Posted in reply to kishore415
Thanks NN that helped me a lot..
Valued Guide
Posts: 2,177

Re: Delete empty rows from a dataset

Posted in reply to kishore415
if nmiss( of _numeric_ ) or cats( of _character_ ) =' ' then delete ;

this looks likely to do what is needed,
If there were not enough space in the default holder for cats() results, then you you are certainly not "missing", even if you get a warning.
Super User
Posts: 10,023

Re: Delete empty rows from a dataset

Posted in reply to kishore415
[pre]


data work.test;
infile cards missover;
input (a b c) ($1.) x y z;
cards;
abc 1 2 3

def 3 4 5
;;;;
run;

options missing=' ';
data want;
set test;
if missing(cats(of _all_)) then delete;
run;
[/pre]


Ksharp
Occasional Contributor
Posts: 14

Re: Delete empty rows from a dataset

this code is not removing the empty rows
Super User
Posts: 10,023

Re: Delete empty rows from a dataset

Are you run my code?
I test it ,it is successful under SAS9.2.
The dataset want has no missing row.


Ksharp
Super User
Posts: 10,023

Re: Delete empty rows from a dataset

Or Under Patrick's suggestion.

[pre]

data work.test;
infile cards missover;
input (a b c) ($1.) x y z;
cards;
abc 2 2 3

def 3 4 5

abc 2 2 3
abc 2 2 3
;;;;
run;
%let dsid=%sysfunc(open(test));
%let nvar=%sysfunc(attrn(&dsid,nvar));
%let dsid=%sysfunc(close(&dsid));
data want;
set test;
if cmiss(of _all_) ge &nvar then delete;
run;
[/pre]


Ksharp
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 12248 views
  • 1 like
  • 5 in conversation