Help using Base SAS procedures

Read SQL database files with SAS

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 98
Accepted Solution

Read SQL database files with SAS

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:Smiley FrustratedQLTables);                                                              

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


Accepted Solutions
Solution
‎06-18-2015 12:30 PM
Super User
Posts: 19,770

Re: Read a file from sql server in SAS

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.

View solution in original post


All Replies
Solution
‎06-18-2015 12:30 PM
Super User
Posts: 19,770

Re: Read a file from sql server in SAS

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.

Frequent Contributor
Posts: 98

Re: Read a file from sql server in SAS

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!

Frequent Contributor
Posts: 98

Re: Read SQL database files with SAS

Anyone has any other suggestion? Thanks.

Super User
Posts: 19,770

Re: Read SQL database files with SAS

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

Frequent Contributor
Posts: 98

Re: Read SQL database files with SAS

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

Thanks a lot!

Super User
Posts: 3,250

Re: Read SQL database files with SAS

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.

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 1310 views
  • 3 likes
  • 3 in conversation