Multiple Schemas for Single SAS Library?

[deleted_user]

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?

[ChrisHemedinger]

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

[niki_02468]

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]

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]

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

[deleted_user]

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