SCL provides a set of
functions that are useful for locating and fetching the required auxiliary
table rows (observations) in a data-concurrent SCL application. However,
you should use caution with these functions in applications that access
shared data. The return code, which is obtained directly from the
called function or from the SYSRC function, must be checked to ensure
that a lock was obtained on the row or that an update was successful.
The return value, which is generated by the macro invocation %SYSRC(_SWNOUPD),
is generated when a fetch or update function fails to lock or update
the row because it is locked by another application.
The FETCHOBS table function is useful when the row number can serve
as the row identifier. Remember that the FETCHOBS function accepts
a relative row number by default. That number might or might not equate
to the physical row number. If you can delete rows in the auxiliary
table, you probably want to use the ABS option in the FETCHOBS function
for absolute row numbering.
The
LOCATEC and LOCATEN table functions can be useful for finding rows
in small tables when the data can remain sorted by a unique identifier
(column) and a binary search is specified. However, due to the overhead
of searching with these SCL functions, it is better to use the WHERE
and FETCH functions to find the rows. In a shared-data environment,
when you use the LOCATEC and LOCATEN functions to find rows, each
row must be requested from the server and transmitted to the client's
SAS session.
The SYSRC function must be queried for warnings when the LOCATEC
and LOCATEN functions find a row because these functions only return
a return code of 0 for either condition: row found or row not found.
For more information about the LOCATEC and LOCATEN functions, see
SAS Component Language: Reference. The following SCL
program example checks whether the located row is locked by another
task:
gotrec=locatec(data-set-id,var-num,search-string,
sort-order);
if (gotrec<=0) then do;
/* Handle row not found */
end;
else if (sysrc()=%sysrc(_swnoupd)) then do;
/* Handle row locked */
end;Note: The LOCATEC and LOCATEN functions
cannot perform binary searches on compressed tables, SAS data views,
or SAS data files that have deleted rows.
The more
general and, usually, more efficient way to find a row is to use the
WHERE function followed by a FETCH function call. The WHERE clause
is evaluated in the server's SAS session, and only the row that needs
the specifications in the WHERE clause is transmitted to the client's
SAS session.
If the WHERE clause
does not find the specified row, the FETCH function returns a −1
return code, which indicates that the end of the table has been reached.
If the WHERE clause is cleared by issuing a null WHERE function call,
the next FETCH call that the application issues fetches the first
row in the table. The FETCH call, not the WHERE clause, locks the
row (if possible). Notice that the WHERE function returns a harmless
warning, %SYSRC(_SWWREP), when the WHERE clause is replaced.
The
DATALISTC and DATALISTN selection-list functions help a client to
select a valid row. These functions actually fetch the entire selected
row into the Table Data Vector (DDV) and lock the row (if possible).
Because these functions do not return a system return code, the SYSRC
function must be queried for warnings. The DATALISTC and DATALISTN
functions might cause the entire SAS table to be read, which means
that each row that is read is transferred individually from the server
to the client SAS session.