BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jade_SAS
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

4 REPLIES 4
SASKiwi
PROC Star

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;
Jade_SAS
Pyrite | Level 9

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!

SASKiwi
PROC Star

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.

Jade_SAS
Pyrite | Level 9

Thank you for the help!

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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