Help using Base SAS procedures

SAS libname MS SQL server ODBC

Reply
New Contributor
Posts: 2

SAS libname MS SQL server ODBC

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;

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.

Super User
Posts: 3,261

Re: SAS libname MS SQL server ODBC

Posted in reply to thetoddflatline

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;

New Contributor
Posts: 2

Re: SAS libname MS SQL server ODBC

Thank you very much!

Super User
Posts: 7,868

Re: SAS libname MS SQL server ODBC

Posted in reply to thetoddflatline

Insert a blank between the string and stringdates=yes to avoid the note 49-169.

To test your libname, do

proc datasets library=mylib;

quit;

and take a look at the output listing.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 49

Re: SAS libname MS SQL server ODBC

Posted in reply to KurtBremser

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);

Proc SQL

connect to odbc&connx;

Create Table Test as

Select * from connection to odbc

(SELECT * FROM [SQLDatabase].[dbo].[MyTable]);

Disconnect from odbc;

Quit;

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.

Ask a Question
Discussion stats
  • 4 replies
  • 1846 views
  • 1 like
  • 4 in conversation