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.

Answer 1

1. Add the database connection to your ODBC driver; you will need the name of this connection in your libname,
2. 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;