topic Re: Finding the non-matching the records based on some condition in SAS Programming

Finding the non-matching the records based on some condition

David_Billa — Thu, 10 Oct 2019 12:39:45 GMT

Assuming I'm creating a dataset based on WHERE clause. It means only the data which passed the condition will be filtered and inserted into the dataset which we create. Is there a way to capture the non-matching records in one dataset which doesn't statisfy the condition mentioned in WHERE clause?

e.g. If there are 10 records in HAVE dataset and we're getting only 5 records in WANT dataset due to the WHERE clause and the other 5 records was excluded. I want to capture excluded records in other dataset EXCLUDED.

Re: Finding the non-matching the records based on some condition

Kurt_Bremser — Thu, 10 Oct 2019 12:48:59 GMT

Use a simple condition:

data
  want
  fail
;
set have;
if condition
then output want;
else output fail;
run;

Re: Finding the non-matching the records based on some condition

David_Billa — Thu, 10 Oct 2019 13:03:32 GMT

Any other ways?

Re: Finding the non-matching the records based on some condition

Kurt_Bremser — Thu, 10 Oct 2019 13:18:16 GMT

@David_Billa wrote:
Any other ways?

Sure, but not as simple, so don't waste your time.

Re: Finding the non-matching the records based on some condition

David_Billa — Thu, 10 Oct 2019 14:22:32 GMT

How do you tackle this if I ask you to write the condition in WHERE instead
of IF?

Re: Finding the non-matching the records based on some condition

ballardw — Thu, 10 Oct 2019 14:30:52 GMT

@David_Billa wrote:
How do you tackle this if I ask you to write the condition in WHERE instead
of IF?

Basically you don't.

The WHERE is applied to the input vector. Anything that does not match is excluded and cannot be manipulated in that data step.

You would have to use a second data step with a where clause that is basically the NOT of the one you are using.

Re: Finding the non-matching the records based on some condition

David_Billa — Thu, 10 Oct 2019 14:37:32 GMT

Could you please help me with one example?

Re: Finding the non-matching the records based on some condition

ballardw — Thu, 10 Oct 2019 16:23:04 GMT

@David_Billa wrote:
Could you please help me with one example?

If you mean of two data steps:

data want;
   set have;
   where value in ('a' 'b' 'c');
run;

data want2;
   set have;
   where not (value in ('a' 'b' 'c'));
run;

The NOT operation negates the logical value of the expression.

Re: Finding the non-matching the records based on some condition

David_Billa — Fri, 11 Oct 2019 04:22:06 GMT

It works with one condition and I'm unable to mame it work for more than one condition.

Following code is working correctly:

data want;
   set sashelp.class;
   where not (age in (11,12,13));
run;

where as the below code is not working if I tweak the above code with more than one condition:

6         data want2;
27            set sashelp.class;
28            where not (age in (11,12,13)
29         and weight=112);
30         run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
      WHERE age not in (11, 12, 13) or (weight not = 112);

data want2;
   set sashelp.class;
   where not (age in (11,12,13)
and weight=112);
run;

Log:

Re: Finding the non-matching the records based on some condition

Kurt_Bremser — Fri, 11 Oct 2019 04:47:11 GMT

You need to negate the whole condition. Enclose everything in an additional pair of brackets, and put the not before that.

Or apply de Morgan's laws.

Re: Finding the non-matching the records based on some condition

Patrick — Fri, 11 Oct 2019 05:55:40 GMT

@David_Billa

Even though using the WHERE statement in a SAS data step is syntactically correct, you won't find a lot of experienced SAS users coding this way.

Re: Finding the non-matching the records based on some condition

David_Billa — Fri, 11 Oct 2019 06:33:48 GMT

I tried as syou suggested but it still seem to be not working. Not sure why second condition is resolving it as OR instead of AND

26         data want2;
27            set sashelp.class;
28            where not ((age in (11,12,13)
29         and weight=112));
30         run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
      WHERE age not in (11, 12, 13) or (weight not = 112);
NOTE: The data set WORK.WANT2 has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

Re: Finding the non-matching the records based on some condition

Kurt_Bremser — Fri, 11 Oct 2019 06:58:58 GMT

SAS automatically applies De Morgan's law when it finds a completely negated condition.

not (a and b)

is equal to

(not a) or (not b)

Re: Finding the non-matching the records based on some condition

David_Billa — Fri, 11 Oct 2019 07:09:17 GMT

then may I know how to make it work?

Re: Finding the non-matching the records based on some condition

Kurt_Bremser — Fri, 11 Oct 2019 11:24:13 GMT

@David_Billa wrote:

then may I know how to make it work?

It DOES work.

See this example:

data want1;
set sashelp.class;
where age in (11,12,13) and weight=128;
run;

data want2;
set sashelp.class;
where not (age in (11,12,13) and weight=128);
run;

(I had to change the weight value, so that the first condition selects at least one observation)

Log:

 73         data want1;
 74         set sashelp.class;
 75         where age in (11,12,13) and weight=128;
 76         run;
 
 NOTE: There were 1 observations read from the data set SASHELP.CLASS.
       WHERE age in (11, 12, 13) and (weight=128);
 NOTE: The data set WORK.WANT1 has 1 observations and 5 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 77         
 78         data want2;
 79         set sashelp.class;
 80         where not (age in (11,12,13) and weight=128);
 81         run;
 
 NOTE: There were 18 observations read from the data set SASHELP.CLASS.
       WHERE age not in (11, 12, 13) or (weight not = 128);
 NOTE: The data set WORK.WANT2 has 18 observations and 5 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.02 seconds
       cpu time            0.01 seconds

18 + 1 gives 19, the number of observations in sashelp.class.

Re: Finding the non-matching the records based on some condition

hashman — Sat, 12 Oct 2019 05:49:33 GMT

@David_Billa:

In this situation, using WHERE instead of IF (as @Kurt_Bremser has offered) doesn't win you much. But it can be done in a single step. One way:

data inc exl ;                                                                                                                          
  set sashelp.class (where=(sex eq "M") in=inc)                                                                                         
      sashelp.class (where=(sex ne "M"))                                                                                                
  ;                                                                                                                                     
  if inc then output inc ;                                                                                                              
  else        output exl ;                                                                                                              
run ;

Another way:

data inc exl ;                                                                                                                          
  do until (z_inc) ;                                                                                                                    
    set sashelp.class (where=(sex eq "M")) end = z_inc ;                                                                                
    output inc ;                                                                                                                        
  end ;                                                                                                                                 
  do until (z_exc) ;                                                                                                                    
    set sashelp.class (where=(sex ne "M")) end = z_exc ;                                                                                
    output exl ;                                                                                                                        
  end ;                                                                                                                                 
run ;

I wouldn't recommend either because in both cases you end up reading all input records into the buffer, which defies the purpose of using WHERE vs IF in the first place.

Kind regards

Paul D.

Re: Finding the non-matching the records based on some condition

Kurt_Bremser — Sat, 12 Oct 2019 08:32:31 GMT

@David_Billa Mind that all solutions relying on WHERE involve multiple reads of the input dataset and need considerably more code than the simple if, so they are inefficient both in use of computing resources and programmer's time.