Working with SAS Data Sets

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

The arguments to the WHERE clause are as follows:

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"}];

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];

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:

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 */