BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I've created a SAS library that uses SAS/ACCESS to connect to an Oracle database. I've been successfull in referencing the library to a single schema, however, I can't get it work with multiple schemas.

Can someone help?
5 REPLIES 5
ChrisHemedinger
Community Manager
I can only point you toward the SCHEMA= option on the libname statement:

http://support.sas.com/techsup/unotes/SN/002/002733.html
http://support.sas.com/faq/038/FAQ03822.html

A search of SAS Notes for "oracle schema" will yield many other possible hints.

http://support.sas.com/techsup/search/sasnotes.html

Chris
SAS For Dummies 3rd Edition! Check out the new edition, covering SAS 9.4, SAS Viya, and all of the modern ways to use SAS!
niki_02468
Calcite | Level 5

Hi.

The links are not available anymore (it is an old queistion). Could you provide more detailed?

In particular I am interested in solving the following problem. 

I am able to create a SAS library per schema in SQL Server with LIBNAME ODBC...Statements like this:

 

LIBNAME SchemaA

ODBC 

schema="SchemaA"

noprompt="xxx";

LIBNAME SchemaB

ODBC 

shema="SchemaB"

noprompt="xxx";

 

My idea is then to concatinate the created libriries in a SAS library named "database" as follows:

 

libname database (SchemaA SchemaB);

 

However, I have there a problem: Tables with the same names (but different contents) exist in both schemas.

 

How can I solve this issue?

 

PROC DATASET CHANGE is not an option, as I am not allowed to change table names on the SQL server.

 

Kurt_Bremser
Super User

As the SAS system will always use the first appearance of a name in a concatenated library, this won't work. You need to concatenate the tables(datasets) from the separate libraries whenever you use them.

You could write a program that searches for such tables and automatically creates views that do the concatenation.

SASKiwi
PROC Star

@niki_02468 - It is common practice and certainly simpler to define separate SAS libraries for each external database schema. 

deleted_user
Not applicable
Thanks. I'm going to try this out when I get back to the office.

hackathon24-white-horiz.png

Join the 2025 SAS Hackathon!

Calling all data scientists and open-source enthusiasts! Want to solve real problems that impact your company or the world? Register to hack by August 31st!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 3631 views
  • 2 likes
  • 5 in conversation