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
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.
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.
Hi ,
So if i do
order by code ,yy then i dont get the error
but from table has cc variable in it
Thanks,
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.