BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chennupriya
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

View solution in original post

4 REPLIES 4
LinusH
Tourmaline | Level 20
It's pretty clear.
You have yy and code in your select clause, not cc.
Order by is for ouput, not input processing.
Data never sleeps
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

chennupriya
Quartz | Level 8

Hi ,

So if i do

order by code ,yy then i dont get the error

 

but from table has cc variable in it

 

 

 

 

Thanks,

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4055 views
  • 0 likes
  • 3 in conversation