BookmarkSubscribeRSS Feed
thetoddflatline
Calcite | Level 5

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.

4 REPLIES 4
SASKiwi
PROC Star

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;

thetoddflatline
Calcite | Level 5

Thank you very much!

Kurt_Bremser
Super User

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.

Lenvdb
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 4732 views
  • 1 like
  • 4 in conversation