topic Re: Multiple Schemas for Single SAS Library? in SAS Enterprise Guide

Multiple Schemas for Single SAS Library?

deleted_user — Fri, 18 Aug 2006 23:30:26 GMT

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?

Re: Multiple Schemas for Single SAS Library?

ChrisHemedinger — Mon, 21 Aug 2006 19:33:50 GMT

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

Re: Multiple Schemas for Single SAS Library?

deleted_user — Tue, 22 Aug 2006 07:21:13 GMT

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

Re: Multiple Schemas for Single SAS Library?

niki_02468 — Thu, 12 Jan 2023 15:23:44 GMT

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.

Re: Multiple Schemas for Single SAS Library?

Kurt_Bremser — Thu, 12 Jan 2023 17:01:36 GMT

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.

Re: Multiple Schemas for Single SAS Library?

SASKiwi — Thu, 12 Jan 2023 19:51:41 GMT

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