DATA Step, Macro, Functions and more

Difference between IF and WHERE statements

Reply
Occasional Contributor
Posts: 18

Difference between IF and WHERE statements

i want know, what is the difference between these two programs.

1. data c;

    set a;

   where city = "london";

  run;

2. data c;

set a;

if city = "london";

run;

 

PROC Star
Posts: 392

Re: Difference between IF and WHERE statements

In your example I would use option 1 as it can utilize an index on the city variable in dataset a (if such an index exists and would be beneficial). There are several articles on the differences between where and if and with more complex scenarios. A nice short one to get you started is SAS Usage Note 24286: When do I use a WHERE statement instead of an IF statement to subset a data se...

 

Personally I use where where I can and if if I have to Smiley Happy

Occasional Contributor
Posts: 18

Re: Difference between IF and WHERE statements

if both the programs are executing. then, why to use both.

PROC Star
Posts: 555

Re: Difference between IF and WHERE statements

Lets assume that city is some variable that you yourself have calculated further uo in your datastep. Then example 1 will not work, since city does not exist in the dataset in your set statement. Example 2 however will work, since this is a subsetting if statement.

PROC Star
Posts: 555

Re: Difference between IF and WHERE statements

In example 1 you utilize that dataset a contains the variable city.

 

You are not sure about that in example 2 where you are using a socalled "subsetting if statement". This means that you reach the implied output and return statements at the end of your datastep if that condition is true Smiley Happy

Occasional Contributor
Posts: 18

Re: Difference between IF and WHERE statements

if we use 'where statement'. then, it we read only one observation (where city = "london"Smiley Wink, and if we use 'subsetting if statement'. then, it will read all the observations and gives the result.

PROC Star
Posts: 555

Re: Difference between IF and WHERE statements

If I understand your post correctly, you want to use BOTH the where statement and the subsetting if statement. This is not a good idea. 

 

My rule of thumb:

 

If the variable city exists in your input dataset: Use where statement

 

If you created the variable city yourself: Use the Subsetting if statement.

 

When in doubt: Use the subsetting if statement Smiley Happy

Super User
Posts: 5,260

Re: Difference between IF and WHERE statements

A developer shouldn't be in doubt. Man Wink

WHERE is always preferred because of better performance.

IF should only be used for subsetting if WHERE isn't possible to use.

Data never sleeps
Super User
Posts: 9,691

Re: Difference between IF and WHERE statements

WHERE-- filter obs before it enter PDV.

IF -- filter obs after it enter PDV.

Ask a Question
Discussion stats
  • 8 replies
  • 349 views
  • 7 likes
  • 5 in conversation