topic Re: where statement and where = data set option in SAS Procedures

where statement and where = data set option

mnew — Wed, 13 Oct 2010 05:49:41 GMT

Greetings:

Does any of your experts know whether a where statement in data step for subsetting is processed differently from where = dataset option, when there is only one input dataset?

Here is the only quote from SAS help that I found.

"The WHERE statement applies to all input data sets, whereas the WHERE= data set option selects observations only from the data set for which it is specified."

My friend also read from somewhere that a where = option is more efficient as it only brings the qualified observations into PDV, while a where statement will read in all the observations then discard the unqualified data. What he mentioned also sounds like the difference between IF and Where statement for subsetting, so I'm a bit confused.

Thank you!

Re: where statement and where = data set option

sbb — Wed, 13 Oct 2010 08:30:25 GMT

The WHERE statement and WHERE= data set option are equivalent, for example, when you have only one dataset referenced on the SET statement (or when using a PROC that accepts WHERE filtering, when loading input data into the PDV. I find the WHERE statement easier to code.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

+"where statement" +where +"data set option" site:sas.com Message was edited by: sbb

Re: where statement and where = data set option

Robert_Bardos — Wed, 13 Oct 2010 09:03:30 GMT

Additionally the WHERE statement can be augmented ( by using WHERE ALSO). A technique that is sometimes (or for some people) easier to read/understand than complex AND constructs.

Sample code snippet:
[pre]
where age gt 12 and age lt 15 ;
where also weight gt 60 and weight lt 80 ;
[/pre]

Re: where statement and where = data set option

Doc_Duke — Wed, 13 Oct 2010 12:36:52 GMT

To follow-up on Scott's post, because the WHERE statement is a "declarative" statement, it is addressed at the data step compile time and would have the same effect as a WHERE =.

A subsetting IF statement is an "executable" statement and it's location in a data step can make a significant difference in how fast a program runs.

See
http://support.sas.com/documentation/cdl/en/lrdict/63026/HTML/default/viewer.htm#a001225397.htm

Doc Muhlbaier
Duke

Re: where statement and where = data set option

mnew — Thu, 14 Oct 2010 22:11:04 GMT

This is very helpful. I often could not find what I was looking for on sas.com. Now I can try this search trick you taught me!

Re: where statement and where = data set option

mnew — Thu, 14 Oct 2010 22:15:12 GMT

First time heard of where also. This is better than putting a group of conditions on a new line (my clumsy way to chop them up). Thank you!

Re: where statement and where = data set option

mnew — Thu, 14 Oct 2010 22:25:55 GMT

Wish I had the web page you recommended handy when I was taking the programming one class. Thanks!