SAS Component Language Dictionary

WHERE

Applies a WHERE clause to a SAS table

Category:

SAS Table

Syntax
Details
Examples
	Example 1: Applying a Compound WHERE Clause
	Example 2: Applying a WHERE Clause in Separate SCL Statements
	Example 3: Using a Numeric Variable in a WHERE Clause
	Example 4: Using a Character Variable in a WHERE Clause
	Example 5: Specifying Multiple WHERE Conditions
	Example 6: Determining Whether Any Rows Meet the WHERE Condition
	Example 7: Determining How Many Rows Meet the WHERE Condition
See Also

Syntax

sysrc=WHERE(table-id<,clause-1<, . . . ,clause-5>>);

sysrc

contains the return code for the operation:

0	successful
>0	not successful
0<	the operation was completed, but a warning or a note was generated. If the row is locked, it is still fetched (read in), but a sysrc of _SWNOUPD is returned.

Type: Numeric

table-id

is the identifier that was assigned when the table was opened. If table-id is invalid, the program halts.

Type: Numeric

clause-1, . . . , clause-5

is the condition for the search, expressed as a WHERE clause but without the keyword WHERE. The arguments clause-1, clause-2, clause-3, clause-4, and clause-5 are treated as multiple lines of the WHERE clause. Each of the clauses, clause-1 through clause-5, can contain multiple conditions separated by the AND operator.

If the clause starts with the keyword ALSO, then the new WHERE clause is considered to be a subclause of the current WHERE clause.

Specifying no clauses undoes all current temporary WHERE clauses that have been applied to the SAS table.

Type: Character

Details

The WHERE function may take advantage of indexing. The syntax of the WHERE clause is the same as for the WHERE statement in Base SAS software. Any WHERE clause that is applied by the WHERE function is only temporary and is considered to be a subclause of any WHERE clause that was issued when the table was opened. To apply a permanent WHERE clause to a SAS table, use the WHERE= data set option following the table name in OPEN.

The WHERE clause subsets the rows to which you have access. You must then issue a function such as FETCH or FETCHOBS to read rows from the subset. When a WHERE clause is active, FETCHOBS fetches the specified row by counting only rows that meet the WHERE condition.

To create views with more complicated WHERE clauses, use the SQL procedure or the SUBMIT CONTINUE SQL statement.

To remove only the last WHERE condition, use

rc=WHERE(tableid,'undo');

To remove all WHERE conditions, use either of the following:

rc=WHERE(tableid);
rc=WHERE(tableid,'clear');

Examples

Example 1: Applying a Compound WHERE Clause

Apply a WHERE clause to the SAS table MYDATA, which subsets the table into only those rows in which column X is equal to 1 and column Z is less than 0:

tableid=open('mydata','i');'
rc=where(tableid,'x=1 and z<0');

You can separate the WHERE clause into two clauses as follows. This is equivalent to the previous example.

tableid=open('mydata','i');
rc=where(tableid,'x=1','and z<0');

Example 2: Applying a WHERE Clause in Separate SCL Statements

Instead of using one WHERE clause, you can separate the WHERE clause into two statements. The following statements are equivalent to Example 1:

tableid=open('mydata','i');
rc=where(tableid,'x=1');
    ...more SCL statements...
rc=where(tableid,'also z<0');

Example 3: Using a Numeric Variable in a WHERE Clause

You can pass values from SCL variables to the WHERE clause. Subset the table referenced by TABLEID, based on the value that was entered for the numeric variable SCRNUM.

rc=where(tableid,'num= '||put(scrnum,5.));

Example 4: Using a Character Variable in a WHERE Clause

To subset a table based on a character value, you can use the quote function to return the quoted value. (Otherwise, you must use double quotation marks around the WHERE condition, because the quoted value itself must be enclosed in quotation marks.) Subset the table referenced by TABLEID based on the value that was entered for the character column SCRNAME. Use the QUOTE function to return the quoted value.

rc=where(tableid,'name= '||quote(scrname));

Example 5: Specifying Multiple WHERE Conditions

Combine the previous two WHERE conditions into one statement:

rc=where(tableid,'num= '||put(scrnum,5.)||'and 
   name='||quote(scrname));

Specify an additional condition, region=4 :

rc=where(tableid,'num='||put(scrnum,5.)||'and
   name='||quote(scrname),'and region=4');

Example 6: Determining Whether Any Rows Meet the WHERE Condition

You can attempt to fetch a row to determine if any rows meet a WHERE condition. Apply a WHERE clause to the SAS table MYDATA, which subsets the table into only those rows in which the column X is equal to 1. Fetch the first row and check the return code from the fetch. If there are no rows in the subset, the return code to the fetch will be -1.

tableid=open('mydata', 'i');
rc=where(tableid,'x=1');
if fetch(tableid) = -1 then
   _msg_ = 'There are no rows in the WHERE subset';

Example 7: Determining How Many Rows Meet the WHERE Condition

You can use the ATTRN function with the NLOBSF argument to determine how many rows meet a WHERE condition. Apply a WHERE clause to the SAS table MYDATA, which subsets the table into only those rows in which the column X is equal to 1.

tableid=open('mydata', 'i');
rc=where(tableid,'x=1');
numrows=attrn(tableid,'nlobsf');
put numrows=;

CAUTION:: Use NLOBSF with caution.
Passing NLOBSF to ATTRN requires the engine to read every row from the table that matches the WHERE clause. Based on the file type and size, this can be a time-consuming process.