SAS Component Language Dictionary

LOCATEC and LOCATEN

Search a SAS table for a row that contains a specified value

Category:

SAS Table

Syntax
Details
Examples
	Example 1: Using the LOCATEC Function
	Example 2: Using the LOCATEN Function
See Also

Syntax

rc=LOCATEC(table-id,col-num,cval<,sort<,direction>>);

rc=LOCATEN(table-id, col-num,nval <,sort<,direction>>);

rc

contains information about the search:

>0	the number of rows read before a match is found
0	no row with a matching value was found

Type: Numeric

table-id

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

Type: Numeric

col-num

is the number of the column to search for. This number can be returned by the VARNUM function. If the number is invalid, the program halts and sends a message to the log.

Type: Numeric

cval

is the character value for LOCATEC to search for. If cval is not a character value, the program halts and sends a message to the log.

Type: Character

nval

is the numeric value for LOCATEN to search for. If nval is not a numeric value, the program halts and sends a message to the log.

Type: Numeric

sort

indicates whether the SAS table is sorted:

'A'	The table is sorted in ascending order.
'D'	The table is sorted in descending order.
'U'	The table is not sorted. (This is the default.)

Type: Character

direction

specifies the direction in which to search the SAS table:

'A'	searches all rows, starting with the first row. (This is the default unless the table is opened in 'IS' mode.)
'B'	searches from the previous row backward.
'F'	searches from the next row forward. (This is the default if the table is opened in 'IS' mode.)

Type: Character

Details

LOCATEC and LOCATEN do not search for partial values. For LOCATEC, preceding blanks are part of cval but trailing blanks are not. Therefore, you can facilitate searching for LOCATEC by using the LEFT function to left-justify character values.

LOCATEC and LOCATEN search all rows, starting with the first row by default and skipping rows marked for deletion. When a WHERE clause is active, these functions search the rows that meet the WHERE condition for a match. If a matching row is found, it is loaded into the Table Data Vector (TDV). Otherwise, the current row remains in the TDV.

LOCATEC and LOCATEN return the number of rows read before a match is found. This number may not correspond to the row number where the match is found because these functions skip deleted rows. Moreover, if a WHERE clause is active, they read only the that meet the WHERE condition, and they may include appended rows that meet the WHERE condition. Also, if direction is supplied, the number returned is the number of rows read from the previous row where the search began. By default, the search direction is forward, starting with the first row in the table.

If the table is sorted, then specifying 'A' or 'D' for sort uses the more efficient binary search algorithm. Perform a binary search only when you have member-level access so that no one else can be editing the table concurrently. With a binary search, LOCATEC and LOCATEN make assumptions about how the data is sorted, and they assume that they can identify the first and last rows. If the table is being edited concurrently, rows could be appended so that the table is no longer in sorted order. As a result, the binary search might not find the correct values.

Examples

Example 1: Using the LOCATEC Function

Locate a customer named SMITH in the PAYROLL table. The table is opened with a table-id of TABLEID and is sorted by NAME. The customer's name is specified in the CUSTOMER column.

customer='SMITH';
rc=locatec(tableid,varnum(tableid,'name'),customer,'a');
if (rc=0) then _msg_=
   'There is no customer named '||customer||'.';
else do;
      ...more SCL statements...
     end;
return;

Example 2: Using the LOCATEN Function

Locate a house whose price is $94,000 in the SASUSER.HOUSES table, which is opened with a table-id of HOUSEID. The price is specified in the window variable PRICE.

houseid=open('sasuser.houses');
price=94000;
rc=locaten(houseid,varnum(houseid,'price'),price);
if (rc=0) then
   _msg_='No house is priced at '||
          putn(price,'dollar9.2')||'.';
else do;
   rows=curobs(houseid);
   _msg_=
      'The specified price was found in row '||rows;
   end;
return;