When writing SCL code in SAS/AF^® 9.2, any WHERE clauses that reference functions will execute slowly. In fact, the more functions that you reference in a WHERE clause, the worse the performance will be.

Here are some examples that show how functions might be used in a WHERE clause:

The use of the CATS function in this statement causes problems:

    rc = where(dsid,"name=cats('11','22','33')");

For this particular function, there are a couple of workarounds:

• The following workaround executes the CATS function in a separate step from the WHERE clause.

whc = cats('11','22','33'); rc = where(dsid,"name='"||whc||"'");

• The following workaround replaces the CATS function with the concatenation operator "||".

rc = where(dsid,"name='11'||'22'||'33'");

Other situations have no workaround. In the following example, the WHERE clause references a column to which functions are applied:

    rc = where(dsid,"name = upcase(firstname)");

In this example, NAME and FIRSTNAME are columns in the table referenced by DSID. If this WHERE clause is executed many times in a single SAS/AF session, then performance is likely to suffer.

Note that only execution of the WHERE clauses is slow. The rest of the SAS/AF application will perform normally.

Operating System and Release Information

A fix for this issue for Base SAS 9.21_M3 is available at: