Using SAS Tables

Controlling Access to SAS Table Rows in SCL Programs

For many applications, you may want an SCL program to read from a SAS table only the rows that meet a set of search conditions. For example, if you have a SAS table that contains sales records, you may want to read just the subset of records for which the sales are greater than $300,000 but less than $600,000. To do this, you can use WHERE clause processing, which is a set of conditions that rows must meet in order to be processed. In WHERE clause processing, you can use either permanent or temporary WHERE clauses.

Permanently Subsetting Data

A permanent WHERE clause applies a set of search conditions that remain in effect until the SAS table is closed. You might use a permanent WHERE clause to improve the efficiency of a program by reading only a subset of the rows in a SAS table. You might also want to use a permanent WHERE clause in applications when you want to limit the SAS table rows that are accessible, or visible, to users. For example, if you are working with a large SAS table, users may not need access to all the rows to use your application. Or, for security reasons, you may want to restrict access to a set of rows that meet certain conditions.

SCL provides several features that enable you to subset a SAS table based on specified search conditions. To apply a permanent WHERE clause to a SAS table, you can use the SAS data set option WHERE= with the OPEN function. For example, the following WHERE clause selects only the records for which the sales are greater than $300,000 but less than $600,000:

   /* Open the SAS table and display a  */
   /* subset of the SAS table rows      */
salesid=open
   ("sample.testdata(where=((sales > 300000)"||
                  "and (sales < 600000)))",'i');

You can also use the WHERE= option in SCL with the FSEDIT and FSVIEW routines.

Temporarily Subsetting Data

In addition to restricting access to SAS table rows, you may want to enable users to subset the accessible records even further. In this case, you can use the WHERE function to apply a temporary WHERE clause. A temporary WHERE clause applies a set of search conditions that can be modified or cancelled by subsequent SCL statements. For example, you could apply a temporary WHERE clause like the following:

rc=where(dsid,'SSN='||ssn);

When a SAS table is indexed, you can use the SETKEY function for subsetting. For example, if a SAS table is indexed on the column SSN, you could use:

rc=setkey(dsid,'SSN','eq');

Searching with WHERE versus LOCATEC or LOCATEN

You can search efficiently with the WHERE function if you are working with a large SAS table that is indexed by the column or columns for which you are searching. It is also appropriate to use the WHERE function when you are using an expression that involves several columns to locate rows.

However, you can use LOCATEC or LOCATEN to find a row when one or more of the following conditions are met:

The SAS table is small.
You are searching for one row that meets a single search condition (for example, the row that contains a particular name).
You are looking for one row that meets a single search condition in a large SAS table, if the SAS table is sorted by the column for which you are searching, and if you are using the more efficient binary search. See the following section for more information.

Searching Efficiently

By default, LOCATEC and LOCATEN search a SAS table sequentially. However, a sequential search is not always the most efficient way to locate a particular row, especially if your SAS table has been sorted. If a SAS table has already been sorted by the column for which you want to search, you can specify a faster, more efficient binary search. For a binary search, use an additional optional argument with LOCATEC or LOCATEN to specify the order in which the SAS table has been sorted (A for ascending order or D for descending order). For example, assuming that the SAS table MYSCHOOL.CLASS has been sorted in ascending order by NAME, you can use the following statements to perform a binary search:

dsid=open('myschool.class');
vnum=varnum(dsid,'name');
sname='Gail';
val=locatec(dsid,vnum,sname,'a');

Undoing WHERE Clauses

WHERE clauses impose certain restrictions on other SCL functions that manipulate data. Therefore, in some cases, you may need to undo a WHERE clause in an SCL program before using other functions. When you specify a WHERE clause, the WHERE conditions replace the conditions that were specified in the previous WHERE clause. However, you can augment a WHERE condition with the ALSO keyword. For example, the following WHERE clause adds the condition of "age greater than 15" to an existing WHERE clause:

rc=where(dsid,'also age > 15');

To undo the condition that was added by the ALSO keyword, you could use the following statement:

rc=where(dsid,'undo');

To undo (or delete) a temporary WHERE clause, use the WHERE function and specify only the SAS table identifier argument. This process undoes all temporary WHERE clauses that are currently in effect.

Top of Page