topic Re: SAS Warning Message in SAS Enterprise Guide

SAS Warning Message

chennupriya — Mon, 13 Jun 2016 15:03:05 GMT

Hi ,

I am using

proc sql;

create table pp as

select substr(cc,2,2) as yy,

code

from bb

order by code ,cc

;

quit;

And i get below warning what should i do if i dont want to get this message . Can anyone help

NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
ODBC: AUTOCOMMIT is NO for connection 6
SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error.
ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing.
SAS_SQL: Unable to convert the query to a DBMS specific SQL statement due to an error.
ACCESS ENGINE: SQL statement was not passed to the DBMS, SAS will do the processing.
ODBC: AUTOCOMMIT turned ON for connection id 6

Re: SAS Warning Message

LinusH — Mon, 13 Jun 2016 15:23:48 GMT

It's pretty clear.
You have yy and code in your select clause, not cc.
Order by is for ouput, not input processing.

Re: SAS Warning Message

RW9 — Mon, 13 Jun 2016 15:25:18 GMT

Can you provide your full code, the error is telling you your code will be executed in SAS rather than in the database, which indicates at some point you are using a database - MS Access possibly. MS Access does not support a substr function.

Also the note you get is because CC is not a variable in the select - it is used, but not selected.

Re: SAS Warning Message

chennupriya — Mon, 13 Jun 2016 15:34:33 GMT

Hi ,

So if i do

order by code ,yy then i dont get the error

but from table has cc variable in it

Thanks,

Re: SAS Warning Message

RW9 — Mon, 13 Jun 2016 15:44:07 GMT

Order by operates on the end result of the statements - i.e. after the select anf from have operated, at which point the available columns are YY and CODE.