SQL allows the SAS programmer to write concise code and offers many advantages over the DATA step, however becoming over reliant on SQL can lead to problems. One issue we regularly see is the following message in the SAS log:

NOTE: The query as specified involves ordering by an item that doesn’t appear in its SELECT clause.

A quick google search of this message returns several thousand hits suggesting it’s a common problem.

Some clients may find it acceptable to leave this message in the log, whereas others may require that it is removed. This message is most commonly caused by the user attempting to sort a dataset using a variable which will not appear in the final dataset or attempting to create a macro list from a specific variable but to order it using the values of another variable. In both instances the program should function as expected however the unsightly note will be present in the log.

Below we will look at how to avoid this message in  each of these instances:

Here is an example of some code which attempts to sort a dataset using code not present in the final dataset, this code would create the ordered output dataset as expected however “The query as specified …” note would be present in the log:

PROC SQL;
  CREATE TABLE class  AS
  SELECT name 
  FROM sashelp.class
  ORDER BY sex;
QUIT;

An efficient and concise way to prevent the NOTE being written to the log would be to use Data Set Options on the output table as follows:

PROC SQL;
  CREATE TABLE class(DROP=sex)  AS
  SELECT name, sex 
  FROM sashelp.class
  ORDER BY sex;
QUIT;

Here is an example of the some code which attempts to create a space separated macro list NAMELIST holding the values of the NAME variable ordered by the SEX variable. Again this code would work as expected, however the NOTE would be present in the log.

PROC SQL;
  SELECT name INTO :namelist SEPARATED BY " "
  FROM sashelp.class
  ORDER BY sex;
QUIT;

An efficient way to avoide this would be to first sort the data and then to use a DATA Step to create the list instead, for example using code similar to the below:

PROC SORT DATA = sashelp.class OUT = class;
  BY sex;
RUN;

DATA _NULL_;
  LENGTH retain_var $1000;
  RETAIN retain_var ' ';
  SET class end=eof;
  IF _n_ = 1 THEN retain_var = STRIP(name);
  ELSE retain_var = STRIP(retain_var)||' '||STRIP(name);
  IF eof THEN CALL symput('namelist', retain_var);
RUN;

Please contact us if you have any SAS issues that you would like us to feature.