topic Re: questions about SAS ODBC connection to SQL server data in SAS Data Management

questions about SAS ODBC connection to SQL server data

Jade_SAS — Wed, 25 Jan 2017 22:00:00 GMT

Hi All,

I have some questions about SAS ODBC connection to SQL data.

1) When set up the ODBC.ini file, do you have to set up separate entry for every database you want to access?

Or can you do it at server level, so you can access all the databases under that server?

I think we only can set up ODBC.ini file at database level, correct me if I am wrong.

2) When you try to access the SQL data via libname in SAS, can you see all the tables under the libref in SAS libraries?

for example,

LIBNAME test odbc Password=*** User=*** DATASRC=*** schema=sas1;

Should I see all the tables of schema sas1 in library "test" in SAS?

Thank you!

Jade

Re: questions about SAS ODBC connection to SQL server data

SASKiwi — Wed, 25 Jan 2017 23:02:29 GMT

You can set up an ODBC connection at the server level and then use SQL passthru to identify which database, schema and table you want. It would look something like this:

proc sql;
 connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;uid=myusr1;pwd=mypwd1;");
  create table Want  as 
  select * from connection to odbc
  (SELECT *
   FROM [MyDatabase].[MySchema1].[MyTable]
   )
  ;
  disconnect from odbc;
quit;

Re: questions about SAS ODBC connection to SQL server data

Jade_SAS — Thu, 26 Jan 2017 13:22:38 GMT

Thank you SASKiwi!

If I set up the libname at server level,

LIBNAME test odbc Password=*** User=*** DATASRC=***;

Will I be able to see all the datasets under "test" library? Thanks!

Re: questions about SAS ODBC connection to SQL server data

SASKiwi — Thu, 26 Jan 2017 23:13:29 GMT

Unfortunately I don't think so. Your DATASRC definition could include just the server name, but then you will have to add the DATABASE= and SCHEMA= options to your LIBNAME statement for the tables to be visible. Check out these examples:

http://support.sas.com/kb/52/777.html

Also be aware if you have any table names longer than 32 characters, they wont show in SAS - this is a current SAS limitation. You have to use PASSTHRU to read those.

Re: questions about SAS ODBC connection to SQL server data

Jade_SAS — Fri, 27 Jan 2017 21:06:50 GMT

Thank you for the help!