SAS libname syntax to connect to SQL Server via ODBC

Question

I've googled for a few days and can't get this working.

I'm using SQL 2014 and the adventureworks database.

I've got SAS 9.3.

I've tried different ODBC settings, as in setting a default database, using Windows login, or SQL login. Different drivers. Still nothing, used complete and noprompt:

libname mylib odbc complete='TrustedConnection=True;DSN=test2;SERVER=MYSERVER\SQLEXPRESS;DATABASE=AdventureWorks2012;'stringdates=yes;
proc print data=mylib.Person;

Error message:

NOTE: Libref MYLIB was successfully assigned as follows: Engine: ODBC Physical Name: test2

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended.

NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE PRINT used (Total process time): real time 5.48 seconds cpu time 0.34 seconds

700 proc print data=mylib.Person; ERROR: File MYLIB.Person.DATA does not exist

I've tried with Person.Person and also in the connection string using schema=Person.

However if I use the following it works. I put the default database in the ODBC:

proc sql;
   connect to odbc as mycon
   (required = "dsn=awlt32;uid=sa;password=mypassword");
   select *
   from connection to mycon
      (select *
      from Person.Person);

disconnect from mycon;
quit;

I know this is a super simple question but I can't seem to get it working, no matter what example code I find. Thanks in advance.

NOTE 49-169 should disappear if you put a blank before stringdates=yes — Dirk Horsten
– Dirk Horsten, Commented Aug 11, 2015 at 6:44
Any idea with the:700 proc print data=mylib.Person; ERROR: File MYLIB.Person.DATA does not exist — doorlord
– doorlord, Commented Aug 11, 2015 at 7:50
If you run proc contents data=mylib._all_; do you see any tables? I don't know adventureworks. Could it be you need to add a schema name to the libname statement? — Quentin
– Quentin, Commented Aug 11, 2015 at 9:22
It looks like your quotation mark ends too early? Stringdate should be in quotes and semicolon after the connection string. Also try adding schema=dbo and note that table names are case sensitive. — Reeza
– Reeza, Commented Aug 11, 2015 at 11:26
Got it working. schema=Person is correct, but I had never tried it in the libname statement but outside of the " " connection string. opps. All fixed. Thanks all. — doorlord
– doorlord, Commented Aug 11, 2015 at 17:50

momo1644 · Accepted Answer · 2018-05-04 12:50:48Z

2

Add the database connection to your ODBC driver; you will need the name of this connection in your libname,
Use the libname below and update the datasrc, sql user and password

LIBNAME mylib ODBC DATASRC=name from step1 SCHEMA=dbo USER=sql user PASSWORD="xxx" ;

proc datasets lib=mylib ;
quit;

edited May 4, 2018 at 12:50

answered May 1, 2018 at 21:11

momo1644

1,80412 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SAS libname syntax to connect to SQL Server via ODBC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related