Variable name with ampersand in proc sql

[JdrPen]

Hi.

We are switching from a SAS Windows environment to an UNIX one which creates some issues (SAS version is Enterprise Guide 7.1).

One of those is that SAS can't read variables with an ampersand in their name in a proc sql. But, as this variable come from a table in our data warehouse, we are not able to rename directly the variable.

Here is a query where I have this issue.

proc sql noprint;
create table orderheaders as 
	select  a.headerid
		,	a.orderno
		,	a.orderstatus
	from Sales.OrderHdrs a
	left join Sales.ordersSi b 
		on a.headerid = b.headerid
	where 	a.countrycode='IE'
	;
quit;

As you can see, I am not taking in my select part a variable with an ampersand in its name, but I have a variable in 'Sales.ordersSi' called 'S&HRev'.

I tried to :

- Preselect the variables in my left join

- put a keep statement on the table in my left join

- apply the 'VALIDVARNAME=ANY' option

But none of those points changed the situation. Here is the error message I got :

ERROR: Error during in-database processing.
NOTE: Compressing data set WORK.ORDERHEADERS decreased size by 36.39 percent. 
      Compressed is 229 pages; un-compressed would require 360 pages.
ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near 'Sales'.
ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile 
       errors.

But this error message doesn't make any sense to me ...

Is this possible to handle this directly in the proc sql ?

Thank you in advance.

[r_behata]

Enable the below debugging options and see if further details are produced in the log.

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

[JdrPen]

Thanks for your quick answer.

Here is what I have in addition when I apply this option :

SQLSRV: AUTOCOMMIT is NO for connection 34
SQLSRV: AUTOCOMMIT turned ON for connection id 34
 
SQLSRV_1: Prepared: on connection 34
SELECT * FROM  Sales . OrderHdrs 
 
SQLSRV: AUTOCOMMIT is NO for connection 35
SQLSRV: AUTOCOMMIT turned ON for connection id 35
 
SQLSRV_2: Prepared: on connection 35
SELECT * FROM  Sales . ordersSi 
 
SQLSRV: AUTOCOMMIT is NO for connection 36
SQLSRV: AUTOCOMMIT is NO for connection 37
SQLSRV: COMMIT performed on connection 37.
ERROR: Error during in-database 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. 
SQLSRV: AUTOCOMMIT turned ON for connection id 36
 
SQLSRV_3: Prepared: on connection 36
 select a. HeaderID , a. OrderNo , a. OrderStatus from  Sales . OrderHdrs  a left join  Sales . ordersSi  b on 
a. HeaderID  = b. headerid  where a. CountryCode  = 'IE'
 
 
SQLSRV_4: Executed: on connection 36
Prepared statement SQLSRV_3
 
ACCESS ENGINE:  SQL statement was passed to the DBMS for fetching data. 

Not sure it helps ... Do you see something interesting ?

[r_behata]

SAS_SQL:  Unable to convert the query to a DBMS specific SQL statement due to an error.

Apparently, the Implicit conversion is not happening for some reason as suggested by the log.

Try the SQL Pass though as suggested by  @Tom , You need to include the SQL Server syntax in the query which will be passed to the SQL Server. Assuming this query runs fine within SQL Server, this should work fine within SAS too.

[Tom]

It might turn out that the easiest solution is to rebuild the SQL server table and use better names for the variables.  If SAS Access to ODBC is having trouble with that goofy name then I bet that many other ODBC tools will also.  They could perhaps just make a view that changes the name to one that follow normal rational naming conventions.

How do you refer to that variable if you are running code in your SQL Server database without SAS?

Can you get that syntax to work in SAS using pass thru code?

proc sql;
connect using sales ;
select * from connection to sales
( ... put your SQL Server syntax query here ...
);
quit;

[JdrPen]

Thanks Tom for your quick answer.

Actually, for the moment the tables I am accessing are copies of the datawarehouse (so in SAS format).

What I don't understand is why then when I am running a query with those tables, still SAS is using ODBC SQL server connection ( as mentioned in the error message.

I am not used to this SAS environment (SAS viya) so I am a little bit lost. I will see internally how to implement your proposition and how to connect with SAS using pass thru code, even with SAS tables.

I will let you know if I have any update.

Thanks again.

ERROR: Error during in-database processing.
NOTE: Compressing data set WORK.ORDERHEADERS decreased size by 37.83 percent. 
      Compressed is 38782 pages; un-compressed would require 62380 pages.
ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near 'Sales'.
ERROR: ERROR: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]The batch could not be analyzed because of compile 
       errors.
NOTE: Table WORK.ORDERHEADERS created, with 23142652 rows and 13 columns.

[Tom]

You need to provide more information on what you are doing.

Sounds like you are using VIYA and not normal SAS.

It sounds like you think the SALES libref is pointing to SAS datasets, but the messages are showing it is using an ODBC connection to something.  Is VIYA using ODBC to connect to ???

[JdrPen]

Hi.

Yes I am using SAS VIYA. Libref is currently pointing to SAS datasets as we are testing the environment, but in the final workspace it will be connected to datawarehouse table. I will come back to you when I will have more info on if VIYA is using ODBC to connect to. My first guess is yes when I see the log but I try to have more details.

Thanks.