topic Re: Read SQL database files with SAS in SAS Procedures

Read SQL database files with SAS

lizzy28 — Thu, 18 Jun 2015 16:11:53 GMT

Hi everyone,

I am trying to read tables stored in sql server in SAS (configuration has been set up). This is my very first time to read files in the configuration setting. I set the library by the following statement:

libname sql odbc dsn=’sqldb’;

Then I checked what files in the library "sql":

proc sql;

connect to odbc(dsn=’sqldb’);

create table test as select * from connection to odbc (ODBC::SQLTables);

quit;

proc contents data=test;

run;

I can see tables and views in the temporary file ‘test’. But when I tried to set a table based on the file ‘table1’ in the "sql" library, it says there is no such file.

data new;

set sql.table1;

run;

Did I read the file from SQL server in SAS in a right way? Please suggest!

Millions of thanks.

Lizi

Re: Read a file from sql server in SAS

Reeza — Thu, 18 Jun 2015 16:30:43 GMT

Most likely you need schema=dbo in your libname statement, or the appropriate schema:

libname sql odbc dsn=’sqldb’ schema=dbo;

The reason test works is that it uses pass through SQL, not your libname connection.

Re: Read a file from sql server in SAS

lizzy28 — Thu, 18 Jun 2015 16:44:06 GMT

Thanks, Reeza.

Adding schema=dbo does not make it work, although the statement "libname sql odbc dsn=’sqldb’ schema=dbo;" worked fine.

Also, I cannot see any files in the "sql" library" through the SAS explorer. Is it the way supposed to be?

Thanks again!

Re: Read SQL database files with SAS

lizzy28 — Fri, 19 Jun 2015 02:19:25 GMT

Anyone has any other suggestion? Thanks.

Re: Read SQL database files with SAS

Reeza — Fri, 19 Jun 2015 03:01:29 GMT

You should see them under the library.

Are your table names under 32 characters and are you sure your schema is dbo?

There's a bunch of methods for debugging in this document:

http://support.sas.com/techsup/technote/ts765.pdf

Re: Read SQL database files with SAS

SASKiwi — Fri, 19 Jun 2015 03:17:06 GMT

Most likely you need a QUALIFIER or DATABASE specified as well.

You reference an SQL server table like so: [Server].[Database].[Schema].[Table]

Your libname could be libname sql odbc dsn='sqldb' qualifier = xxxx schema = dbo;

If you have access to SQL Server Studio that will tell you what your qualifier/database names are.

Re: Read SQL database files with SAS

lizzy28 — Fri, 19 Jun 2015 14:40:15 GMT

Thanks, adding schema works. I somehow used a wrong schema name.

Thanks a lot!