BookmarkSubscribeRSS Feed
Mike_Davis
Fluorite | Level 6

Hello everyone,

when I connect to SQL server with SAS ,I use the code as below.

Could anybody please help me explain what is the defference between sdn and schema?

Thanks!

Mike

libname mydatas odbc

dsn=name1

schema=dbo;

7 REPLIES 7
Mike_Davis
Fluorite | Level 6

seems like dsn(data source name) denotes database's name

schema specify a subfolder under this database(there would be many tables(datasets) under the schema specified folder, these tables(datasets) will be listed in under the libname folder of SAS )

Reeza
Super User

That's a good way to think of it.

In SQL you refer to tables using a two level system schema.table_name and then variables with a three level system, schema.table_name.variable_name

SAS uses different notation so you need to specify your schema in the database connection to allow SAS to appropriately transfer the names.

I've seen the schema used to differentiate different programs that are stored/warehoused in the same db.

Mike_Davis
Fluorite | Level 6

Thank you Sapper,

After the libname statement was finished,I found some of these tables in the SQL server didn't show up in the SAS folder. what cause this happen?

Thanks!

Mike

Reeza
Super User

Are the table names longer than 32 characters?

Alpay
Fluorite | Level 6

If the table names are longer than 32 characters one way they can be accessed is by using SQL Pass Through.

proc sql;

  connect to odbc(dsn=name1);

  create table x as

  select *

  from connection to odbc(

    select *

    from dbo.<sqlservertablenamelongerthan32characters>

  );

  disconnect from odbc;

quit;

Zafer

SAPPER
Calcite | Level 5

Mike,

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#titlepage.htm

Check the link out and it has database specific properties of the connection string. This should help you out!

Thanks

SAPPER.

Ksharp
Super User

Does your table name contain some special character , such as white blank $ # ?

And use libname option reserve_table_names=yes can update and display it.

Ksharp

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
  • 7 replies
  • 5152 views
  • 3 likes
  • 5 in conversation