topic Re: Fetching data from SQL Table into SAS dataset in SAS Programming

Fetching data from SQL Table into SAS dataset

NancyArora — Tue, 15 Oct 2019 12:23:08 GMT

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.

Re: Fetching data from SQL Table into SAS dataset

Kurt_Bremser — Tue, 15 Oct 2019 13:40:04 GMT

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 (`)

Re: Fetching data from SQL Table into SAS dataset

Tom — Tue, 15 Oct 2019 13:51:28 GMT

Can you find the idiot that created DSN with an & in it and get them to change it something more user friendly?