SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

questions about SAS ODBC connection to SQL server data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

questions about SAS ODBC connection to SQL server data

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


Accepted Solutions
Solution
‎01-27-2017 04:05 PM
Super User
Posts: 3,102

Re: questions about SAS ODBC connection to SQL server data

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.

View solution in original post


All Replies
Super User
Posts: 3,102

Re: questions about SAS ODBC connection to SQL server data

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;
Frequent Contributor
Posts: 111

Re: questions about SAS ODBC connection to SQL server data

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!

Solution
‎01-27-2017 04:05 PM
Super User
Posts: 3,102

Re: questions about SAS ODBC connection to SQL server data

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.

Frequent Contributor
Posts: 111

Re: questions about SAS ODBC connection to SQL server data

Thank you for the help!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 479 views
  • 0 likes
  • 2 in conversation