I have a SQL Server Table with special character in its name. For eg: John&Smith_pvt. I want to fetch data from this table into a SAS data set using PROC SQL but I'm getting error and error states " ERROR: CLI describe error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near '&'. : [SAS][ODBC
SQL Server Wire Protocol driver][Microsoft SQL Server]Statement(s) could not be prepared."
Code I'm using:
proc sql;
connect to sqlsvr (dsn="John&Smith_pvt" user=&uid pass=&password);
CREATE TABLE Product AS
SELECT * FROM connection to sqlsvr
(select
SUM(UNITS) AS UNITS
from John&Smith_pvt
);
quit;
Solutions I've tried:
1) %let tb_nm = %nrstr(John&Smith);, however macro variable %put &tb_nm is resolving correctly but it is not resolving again in my above mentioned table name.
2) I have also tried libname options, Preserve_tab_name = Yes . eg: libname abc sqlsvr dsn="ProductUnits" uid=&uid. pwd=&password schema= DBO; but no luck error is same as above mentioned.
Shout out to SAS expertise..!!
Could you please help me out in resolving this error?
Thank you in advance.
From https://docs.microsoft.com/en-us/sql/odbc/microsoft/table-name-limitations?view=sql-server-ver15 (first result of google search for "ms sql server valid table names"):
Table names can contain any valid characters (for example, spaces). If table names contain any characters except letters, numbers, and underscores, the name must be delimited by enclosing it in back quotes (`)
Can you find the idiot that created DSN with an & in it and get them to change it something more user friendly?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.