WHERE

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: Combining WHERE Conditions into One Statement
	Example 6: Determining Whether Any 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. 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.

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: Combining WHERE Conditions into One Statement

Combine the previous two WHERE conditions into one statement:

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

Example 6: Determining Whether Any Rows Meet the WHERE Condition

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

See Also

Chapter Contents
Previous
Next
Top of Page