topic Delete row if all variables empty in SAS Procedures

Delete row if all variables empty

TM — Thu, 15 Sep 2011 13:15:38 GMT

Hi. I have a question that my knowledge of datasteps and arrays can't really handle.

I have some data of the form:

Iden Var1 Var2 Var3 Var4

1 x y

3 y

4 x z

What i hope to make clear is that i have different variables, named anumabb2 to anumabb34, that contain non-numeric text.

What i wan't to delete is the row with iden 2, because all that exact one has all empty variable slots.

I hope it makes sense, and i'm thinking it has a simple solution that im too high on coffee to find for myself!

Thanks

Delete row if all variables empty

art297 — Thu, 15 Sep 2011 13:39:45 GMT

Tim, How about something like:

data want;

set have;

array cvars(*) var1--var4;

if not(cmiss(of cvars(*)) eq dim(cvars));

run;

Re: Delete row if all variables empty

MikeZdeb — Thu, 15 Sep 2011 14:40:28 GMT

Hi ... here's another idea.

data new;

set old;

if ^missing(cats(of anumabb2-anumabb34));

run;

A generic approac to this question is shown below.

The OPTIONS MISSING=' ' is needed if there are

any numeric variables in your data set since the CATS function will add

a period to the concatenated string of values if the default value of

a period is used.

options missing = ' ';

data new;

set old;

if ^missing(cats(of _all_));

run;

options missing = '.';

Re: Delete row if all variables empty

jonam — Thu, 15 Sep 2011 18:52:32 GMT

data INPUT;

iden=1;

var1='x';

var2='y';

var3='';

var4='';

output;

iden=2;

var1='';

var2='';

var3='';

var4='';

output;

iden=3;

var1='';

var2='';

var3='u';

var4='';

output;

iden=4;

var1='';

var2='';

var3='x';

var4='z';

output;

iden=1;

var1='';

var2='y';

var3='';

var4='';

output;

run;

proc sql;

select COMPRESS(name)||" is not null" into :COND SEPARATED BY ' OR '

from sashelp.vcolumn

where memname='TEST' AND TYPE='char'

AND LIBNAME='WORK';

QUIT;

%PUT &COND.;

DATA WANT;

SET INPUT;

WHERE &COND.;

RUN;

Step 1: i'm selecting all the variables that are character as per the requirement into a macro variable . (Using dictionary)

Step 2: using the macro to be used as my where clause condition. Though it looks like two steps, the first steps doesn't access data but dictionary, hence it doesn't take time.

I'm using where clause instead of if to improve the performance. but it works.

Re: Delete row if all variables empty

Ksharp — Fri, 16 Sep 2011 03:06:50 GMT

Art has already given your answer.

data temp;
infile datalines truncover;
input (Iden     Var1     Var2     Var3     Var4 ) ($) ;
datalines;
1            x          y         
2                             
3                                    y
4                         x          z
;
run;
%global nvar list;
proc sql noprint;
 select name ,count(name) 
   into : list separated by ',', : nvar
  from dictionary.columns
   where libname='WORK' and memname='TEMP' and name like 'Var%';
quit;
%put &nvar &list;

data temp;
 set temp;
 if cmiss(of &list) eq &nvar then delete;
run;

Ksharp