A performance issue might occur when you query a database management system (DBMS) table via a LIBNAME engine and the following conditions exist in your SQL procedure query:

• You are selecting two like-named variables where the shorter of the two named variables is listed first.
• You are assigning a label to both variables.
• You are summarizing a third variable.
• The two like-named variables appear on the GROUP BY clause.
• The second variable is not being renamed.
• The two like-named variables are both from the same table listed on the FROM clause.

The following code illustrates one case that might produce slow performance:

The slow performance is caused by an incorrect query being passed to the database where the first variable is listed twice in the GROUP BY clause rather than the two different variables appearing in the GROUP BY clause. This behavior results in a database error because most databases require that variables that appear in a SELECT clause must also appear in the GROUP BY clause when the query includes a summary function. Because of the error, SAS^® software might attempt to pass a second query in which it removes the summarization. Instead of the DBMS performing the processing, SAS reads in the DBMS table and the summarization in performed by SAS. This behavior often results in slower performance due to the extra data being read into SAS.

To work around the issue, remove the label modifiers from the query and add them in a subsequent step.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for this issue for Base SAS 9.4_M6 is available at: