Connect SAS to a Microsoft SQL Server database

Question

I'd like to connect SAS to a Microsoft SQL Server database but I didn't succeed.

I already have a database connection using a NT authentication.

For instance, when I want to connect R (the statistical sofware) to the db, I do that

ch_db <- odbcConnect("sql_nt")

But when I do that in SAS, it doesn't work :

LIBNAME sql ODBC DSN=’sql_nt’;

I have this error message :

ERROR: Libname SQL is not assigned. ERROR: Error in the LIBNAME statement. ERROR 22-7: Invalid option name SQL_NT.

I probably do a stupid mistake, but I don't see it.

Answer 1

In SAS to make a ODBC CONNECTION TO SQL SERVER; First make a User DSN using Windows ODBC Data Source Administrator. I use the SQL Server Native Client and the defaults.

THEN IN SAS EXECUTE THE FOLLOWING STATEMENT

libname mySasLib odbc datasrc='myUserDSN';

ALTERNATIVELY, from the SAS Explorer window GUI, choose New to invoke the New Library Dialog.

Note the DSN sources on your machine will be listed in the Data Source dropdown.

User ID, Password, and Options fields are optional and are left blank for Windows Integrated Security.

;

SUBSEQUENTLY--to get the power of SQL pass-through--here is the syntax for creating a virtual view in Work; this is an incredible performance boost for my situation.

proc sql;
connect to ODBC as mycon (datasrc='myUserDSN');
create view one as
select colA, colB from connection to mycon
(select colA, colB from tableInDataSrc order by colA);
disconnect from mycon;
quit;

Then something like:

proc univariate data=one;
by colA;
histogram colB;
run;

Answer 2

Take a look at this page: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001355231.htm

Specifically, I think you should try it this way:

libname mydblib odbc user=testuser password=testpass datasrc=mydatasource;

Typically you should provide user name and password when connecting to a SQL server. I assume you've verified that the ODBC connection was set up correctly (such as testing it with R).

Edit from comments: If you're using NT authentication, then follow the instructions here: (from http://support.sas.com/techsup/technote/ts802.pdf )

libname odbclib odbc noprompt="dsn=sql_NT;Trusted_Connection=yes" 
schema=DBO; 

I suspect the old style of just dsn="stuff" doesn't work on newer versions - though I only use OLEDB so I'm not 100% sure.

Answer 3

I have tried to connect using both proc sql statements and the libname format. The latter seems more user friendly, here is one example:

  LIBNAME testLib ODBC DSN='sqlserver' user=userID pw=xxxxxxxx schema=dbo;
  title 'Testing Libname Option';
  proc contents data= testLib.mytable;
  proc print data=testLib.mytable(obs=10); 

UserID and PWD need to come from your ODBC connection setup.

Answer 4

I am a SAS newbie and wasted quite a lot of time trying to create a 'user DSN' . Thankfully, I checked with admin team in my company. One of the SAS admin replied "DSNs are configured on SAS server side " She requested for 'MSSQL database and SAS server name' and created DSN for me.

Once connection was established i used below query

(P.S : I used windows authentication)

---

LIBNAME sql odbc = 'xxxxxx' ;
LIBNAME myfolder 'xxxxxxxxxxxxxxxx' ;

    proc sql;
        create table sql.target_table as select * from myfolder.mydata ;
    quit;

---