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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User

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.

lizzy28
Quartz | Level 8

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!

lizzy28
Quartz | Level 8

Anyone has any other suggestion? Thanks.

Reeza
Super User

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

lizzy28
Quartz | Level 8

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

Thanks a lot!

SASKiwi
PROC Star

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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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