where vs. if

[clq]

I know that where can be used in both DATA and PROC step, whereas if can only be used in DATA step. However, what is the difference between where and if? 

[VDD]

The where statement can be used in procedures to subset data.  The if statement cannot be used in procedures.

The where statement is more efficient than the if statement. It tells SAS not to read all observations from the data set.

[ChrisNZ]

@VDD The where statement is more efficient than the if statement. It tells SAS not to read all observations from the data set.

Not always. When functions are used, IF is faster than WHERE.

This has to do with functions being optimised to access PDV data, but not so fast when reading from the read buffer.

If someone has more details I'd be curious to know more actually.

So   if A=1;   is slower than where A=1;

But   if round(A)=1;   is faster than  where round(A)=1;

data TEST;
  do I=1 to 1e8;
    output;
  end;
run;
   
sasfile TEST load;
                                    
data _null_; set TEST; if          I =0; run;  *  4 seconds;
data _null_; set TEST; where       I =0; run;  *  2 seconds;
data _null_; set TEST; if    round(I)=0; run;  *  5 seconds;
data _null_; set TEST; where round(I)=0; run;  * 11 seconds;

sasfile TEST close;

[Reeza]

http://www2.sas.com/proceedings/sugi31/238-31.pdf

And many, many more:

https://www.lexjansen.com/search/searchresults.php?q=where%20vs%20if

---

@clq wrote:

I know that where can be used in both DATA and PROC step, whereas if can only be used in DATA step. However, what is the difference between where and if? 

---

[Kurt_Bremser]

Also, if you have multiple datasets coming in (merge), a where statement may not be possible:

data sexes;
set sashelp.class;
keep name sex;
run;

data others;
set sashelp.class (drop=sex);
run;

data want;
merge
  others
  sexes
;
by name;
where age = 16;
run;

but the if will work:

data want;
merge
  others
  sexes
;
by name;
if age = 16;
run;

If you need to build a combined condition using variables from multiple datasets that are not present in all, then the subsetting if statement is the only option.

[ballardw]

WHERE statements or data set option can only reference variables that appear in the incoming data set(s), If can use newly created variables.

Example: The first data step generates an error, the second doesn't.

data junk;
   set sashelp.class;
   ns= catt(name,sex);
   where ns in ('AlfredM' 'CarolF');
run;

data junk2;
   set sashelp.class;
   ns= catt(name,sex);
   if ns in ('AlfredM' 'CarolF');
run;

[hak18]

When SAS reads the row of a data into PDV, two variables will be created i.e. the FIRST.variable and LAST.variable.
These variables are not in the input table . Therefore, you can't use the WHERE statement to subset rows of variables which are not yet
in the input data. Instead, you can use the subsetting IF statement.