topic Re: Delete empty rows from a dataset in SAS Programming

Delete empty rows from a dataset

kishore415 — Thu, 16 Jun 2011 11:19:03 GMT

Hi All,

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

Thanks in advance.

Re: Delete empty rows from a dataset

NN — Thu, 16 Jun 2011 11:37:33 GMT

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;

Re: Delete empty rows from a dataset

kishore415 — Thu, 16 Jun 2011 11:47:06 GMT

Thanks NN that helped me a lot..

Re: Delete empty rows from a dataset

Peter_C — Thu, 16 Jun 2011 17:00:14 GMT

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.

Re: Delete empty rows from a dataset

Ksharp — Mon, 20 Jun 2011 05:58:12 GMT

[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

Re: Delete empty rows from a dataset

Ankitsas — Mon, 20 Jun 2011 06:20:42 GMT

this code is not removing the empty rows

Re: Delete empty rows from a dataset

Ksharp — Mon, 20 Jun 2011 10:09:17 GMT

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

Ksharp

Re: Delete empty rows from a dataset

Ksharp — Tue, 21 Jun 2011 01:27:42 GMT

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