|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: Combining WHERE Conditions into One Statement|
|Example 6: Determining Whether Any Rows Meet the WHERE Condition|
|sysrc=WHERE(table-id<,clause-1<, . . . ,clause-5>>);|
|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 |
Each of the clauses, clause-1 through clause-5, can contain multiple conditions separated by the AND operator. Each clause cannot exceed 200 bytes.
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.
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
To remove all WHERE conditions, use either of the following:
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');
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');
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.
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.
Combine the previous two WHERE conditions into one statement:
rc=where(tableid,'num= '||put(scrnum,5.)||' and name='||quote(scrname));
You can use the ATTRN function with the NLOBSF argument to check for rows that meet a WHERE condition, and then conditionally execute SCL code. 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. Use ATTRN with NLOBSF before fetching the first row of the subset.
tableid=open('mydata', 'i'); rc=where(tableid,'x=1'); if attrn(tableid,'nlobsf')>0 then rc=fetch(tableid);
ATTRC and ATTRN
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.