Help using Base SAS procedures

Delete row if all variables empty

Reply
Occasional Contributor TM
Occasional Contributor
Posts: 7

Delete row if all variables empty

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         

2                             

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

TM

PROC Star
Posts: 7,492

Delete row if all variables empty

Tim,  How about something like:

data want;

  set have;

  array cvars(*) var1--var4;

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

run;

Valued Guide
Posts: 765

Re: Delete row if all variables empty

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 = '.';

Contributor
Posts: 29

Re: Delete row if all variables empty

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. 

Super User
Posts: 10,046

Re: Delete row if all variables empty

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

Ask a Question
Discussion stats
  • 4 replies
  • 2319 views
  • 0 likes
  • 5 in conversation