Working with SAS Data Sets

Process Data by Using the WHERE Clause

Several SAS/IML statements support a WHERE clause that selects observations that satisfy specified criteria. The WHERE clause is supported by the following statements:

The WHERE clause conditionally selects observations that satisfy some criterion. The general form of the WHERE clause is

WHERE variable comparison-op operand;

The arguments to the WHERE clause are as follows:

variable

is a variable in the SAS data set.

comparison-op

is one of the following comparison operators:

<: less than
<=: less than or equal to
=: equal to
>: greater than
>=: greater than or equal to
^=: not equal to
?: contains a given string
^?: does not contain a given string
= :: begins with a given string
= *: sounds like or is spelled like a given string

operand

is a literal value, a matrix name, or an expression in parentheses.

For example, a typical use of the WHERE clause is to subset data:

proc iml;
use Sashelp.Class where(age>14);
read all var {Age Weight} into X;
close Sashelp.Class;
print X[colname={"Age" "Weight"}];

Figure 7.24: Observations That Satisfy a Criterion

X
Age	Weight
15	112.5
15	112
16	150
15	133
15	112

You can also use a WHERE clause in the READ statement. For example, to conduct BY-group processing of all the students in the Sashelp.Class data set, first call the FREQ procedure to find the unique BY groups, and then use a WHERE clause in a DO loop to read observations from each BY group, as shown in the following example:

/* find unique BY combinations of Age and Sex */
proc freq data=Sashelp.Class;
tables Age*Sex / out=freqout
              nocum norow nocol nopercent;
run;

proc iml;
/* read unique BY groups */
use freqout;
read all var {Age Sex};
close freqout;

use Sashelp.Class;            /* open data set for reading */
MeanHeight = j(nrow(Sex), 1); /* allocate vector for results */
do i = 1 to nrow(Age);        /* for each BY group */
   /* read data for the i_th group */
   read all var {Height} where(Sex=(sex[i]) & Age=(age[i]));
   MeanHeight[i] = mean(Height); /* analyze this BY group */
end;
close Sashelp.Class;

print Age Sex MeanHeight[format=4.1];

Figure 7.25: BY-Group Processing

The FREQ Procedure

Total

Age	Sex	MeanHeight
11	F	51.3
11	M	57.5
12	F	58.1
12	M	60.4
13	F	60.9
13	M	62.5
14	F	63.6
14	M	66.3
15	F	64.5
15	M	66.8
16	M	72.0

The operand argument in a WHERE comparison can be a matrix. For the following operators, the WHERE clause succeeds if any of the elements in the matrix satisfy the condition:

=, ?, = :, = *

For the following operators, the WHERE clause succeeds only if all the elements in the matrix satisfy the condition:

^=, ^?, <, <=, >, >=

You can specify logical expressions within the WHERE clause by using the AND (&) and OR (|) operators. If clause is a valid WHERE expression, then you can combine expressions as follows:

	Both conditions satisfied		clause1 & clause2
	Either condition satisfied		clause1 \| clause2

In the WHERE clause, the expression on the left side of a comparison operator refers to values of the data set variables, whereas the expression on the right side is a constant or SAS/IML matrix. Expressions that involve more than one data set variable in a single clause are not supported. For example, you cannot use either of the following expressions:

list all where(weight>height);  /* not supported */
list all where(weight-height>0);/* not supported */