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
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.
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;
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!
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.
Thank you for the help!
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.