topic Re: How can I better Delete Many Rows that meet many conditions in SAS Procedures

How can I better Delete Many Rows that meet many conditions

gorba004 — Fri, 19 Apr 2019 19:05:13 GMT

I am working with a dataset that has 110,000 observations and 170 variables. I am trying to delete all the observations that are 0 in all of around 30 variables. They must be 0 in all of the 30 variables. This is what I have done so far:

DATA new_set;

SET old_set (WHERE= (var1<>0 AND var2<>0 AND var3<>0 AND var4<>0 AND....var30<>0));

RUN;

The variables are not in actually numbered 1-30, are not all in a row, and are sparsely scattered about the other 170 in the dataset.

Is there an easier or more efficient way to do this?

I am using SAS 9.4 (Unicode)

Re: How can I better Delete Many Rows that meet many conditions

novinosrin — Fri, 19 Apr 2019 19:08:50 GMT

data have;
input var1-var3;
cards;
0 0 0
0 1 0
0 1 2
;


data want;
set have;
k=compress(cats(of var1-var3),'0');
if missing(k) then delete;
drop k;
run;

Re: How can I better Delete Many Rows that meet many conditions

SASKiwi — Fri, 19 Apr 2019 22:14:01 GMT

The 30 variables appear to be numeric so this should work:

if sum(of var1-var30) = 0 then delete;

Re: How can I better Delete Many Rows that meet many conditions

ballardw — Fri, 19 Apr 2019 23:02:42 GMT

@SASKiwi wrote:

The 30 variables appear to be numeric so this should work:
if sum(of var1-var30) = 0 then delete;

I would hesitate to use that alone. Since the OP is attempting to use <> as not equal there may actually be negative values. So if there is a -1 and a +1 the sum would be 0 . Sum=0 and Range=0 might be sufficient though.

@gorba004

You do not want to use <> if looking to do a "not equal", use one of: NE ^=

Examine this code and the result:

data example;
   x= 5;
   y1 = x <> 2;
   y2 = x <> 7;
   z =  x ne 2;
run;

The <> returns the larger of the two values. The x ne 2 returns a 1 for true.

And while SAS will treat any number not 0 or missing as "True" you may get very unexpected results from comparisons if you use <> instead of NE if you intend "not equal" for the comparison.

Re: How can I better Delete Many Rows that meet many conditions

novinosrin — Fri, 19 Apr 2019 23:07:00 GMT

That's exactly what i was wondering even a champion like Sir @SASKiwi who taught me effective boolean expressions can get "jetlagged" on way to dallas? 🙂

Re: How can I better Delete Many Rows that meet many conditions

SASKiwi — Fri, 19 Apr 2019 23:08:08 GMT

@ballardw - good point. If data is all greater than or equal to zero it should be OK.

Re: How can I better Delete Many Rows that meet many conditions

SASKiwi — Fri, 19 Apr 2019 23:10:15 GMT

@novinosrin - quite likely. I've got a 3 hour followed by a 15.5 hour flight to contend with....

Re: How can I better Delete Many Rows that meet many conditions

novinosrin — Fri, 19 Apr 2019 23:15:28 GMT

Have a safe trip and great fun sir. Pass on my regards to our coomunity folks in SGF. Cheers!

Re: How can I better Delete Many Rows that meet many conditions

ballardw — Fri, 19 Apr 2019 23:33:41 GMT

@SASKiwi wrote:

@ballardw - good point. If data is all greater than or equal to zero it should be OK.

Care to guess how much time I spent diagnosing a very similar issue years ago where a very small number of records had that offsetting sum behavior? Though in the case I had it was a combination of 4 variables where one negative value just happened to match the total of 3 positive...

Re: How can I better Delete Many Rows that meet many conditions

FreelanceReinh — Fri, 19 Apr 2019 23:42:29 GMT

Hello @gorba004 and welcome to the SAS Support Communities!

First of all, please note that your suggested WHERE condition is inconsistent with your requirement: You would need to replace "AND" with "OR" to correct this. (The "<>" works like "NE" in a WHERE condition, but I still wouldn't use it for the reason @ballardw has explained.)

As has been suggested, you can use functions with an OF variable-list argument to simplify the condition, where variable-list may be a list of arbitrary variable names (it's not limited to numbered range lists etc.). If you need more than one function, you can use an array to avoid duplicating the long list.

Example:

data new_set;
array x[*] list your variables here;
set old_set;
if not(nmiss(of x[*])=sumabs(of x[*])=0);
run;

Re: How can I better Delete Many Rows that meet many conditions

novinosrin — Fri, 19 Apr 2019 23:55:20 GMT

if not(nmiss(of x[*])=sumabs(of x[*])=0);

Super slick

Re: How can I better Delete Many Rows that meet many conditions

Shmuel — Sat, 20 Apr 2019 01:46:11 GMT

Using array makes life easier:

data want;
 set have;
      array varx var1-var30;
      do i=1 to dim(varx);
           if varx(i) <> 0 then do; output; leave; end;
      end;
      drop i ;
run;

Re: How can I better Delete Many Rows that meet many conditions

KachiM — Sat, 20 Apr 2019 06:12:13 GMT

You want to delete a row of variables each having a value of zero. Don't you want to delete Columns having zeros?

For instance,

data have;
input var1-var3;
cards;
0 0 0
0 1 0
0 1 2
;
run;

If you delete the first row, you will have the entire column (VAR1) with zeros. Do you want VAR1 to be kept?

Re: How can I better Delete Many Rows that meet many conditions

Ksharp — Sat, 20 Apr 2019 10:59:55 GMT

data have;
input var1-var3;
cards;
0 0 0
0 1 0
0 1 2
;


data want;
set have;
array x{*} var1-var3;
if min(of x{*})=0 and max(of x{*})=0 then delete;
run;

Re: How can I better Delete Many Rows that meet many conditions

gorba004 — Sat, 20 Apr 2019 20:47:22 GMT

Thanks so much, this worked like a charm