08-11-2015 02:06 AM
I've googled for a few days and can't get this working. Hopefully this is the correct forum.
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;
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.
08-11-2015 03:44 PM
Try something like this. You don't need to specifiy a DSN, you can define it all in the connection string. Note don't leave any more blanks in the NOPROMPT string than in my example otherwise it won't work.
libname SQLSRVR odbc noprompt = "server=MYSERVER\SQLEXPRESS;DRIVER=SQLServer Native Client 11.0;Trusted Connection=yes" DATABASE = AdventureWorks2012 schema = dbo;
08-12-2015 06:26 AM
Insert a blank between the string and stringdates=yes to avoid the note 49-169.
To test your libname, do
proc datasets library=mylib;
and take a look at the output listing.
08-13-2015 06:06 AM
I have had some success with the Native ODBC SQL Server Driver on SAS 9.3 (On Linux) .
We do not use a Libname Statement for extracting data though.
We use this method to connect to MSSQL 2014 Server databases:
%let connx=(dsn=<myODBC_DSN> user=<MYLOGIN> pwd=xxxxxxx);
connect to odbc&connx;
Create Table Test as
Select * from connection to odbc
(SELECT * FROM [SQLDatabase].[dbo].[MyTable]);
Disconnect from odbc;
This method is dependent on a ODNC.ini DSN entry, so you will need to make sure that you have a proper DSN in your ODBC.ini and ODBCINST.ini files on your Linux Server.