topic Re: SAS Reading Schema Name as Libref and saying it's not assigned in SAS Programming

SAS Reading Schema Name as Libref and saying it's not assigned

Yokodaspacewale — Wed, 18 Sep 2019 19:21:09 GMT

Version 7.1

Using Proc Sql from an oracle connection, I am unable to run code in SAS against a particular schema and only that schema in the DB.

examples ~~(excluding the PROC SQL and connection strings)~~:

Path text:

libname temp "/sas_env/empl/sm/My_Name";
%let databasepath2="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxx)(PORT=xxxx))
(CONNECT_DATA=(SERVICE_NAME=xxxx)))";

Schema from path causing issue:

Proc sql;

connect to oracle (user=xxxxx password='xxxxx' path=&databasepath2);

create table FCST as

select * from DM.items;
Quit;

returns a Libref not assigned error

DM is a legitimate schema name in the DB and works in every other query application I have

schema from same path not causing issue:

Proc sql;

connect to oracle (user=xxxxx password='xxxxx' path=&databasepath2);

create table FCST as

select * from APPS.Some_Other_Table_In_This_DB;
Quit;

"APPS" is a schema from the same connection and this works fine

Any way around this, or just an explanation of what's happening here would be appreciated.

Re: SAS Reading Schema Name as Libref and saying it's not assigned

JBailey — Wed, 18 Sep 2019 19:04:13 GMT

Hi @Yokodaspacewale

Can you include your LIBNAME statements and/or CONNECT statements because it will help us sort this out?

Best wishes,
Jeff

Re: SAS Reading Schema Name as Libref and saying it's not assigned

Tom — Wed, 18 Sep 2019 19:14:31 GMT

You are passing those queries to the database to run? Or are you submitting them directly as SAS statements?

In SAS syntax the first query is asking to select the dataset ITEMS from the location library that the libref DM points to.

If you want to push code into your external database use something like:

proc sql;
connect to oracle ..... ;
select * from connection to oracle
(select * from DM.items
);

Re: SAS Reading Schema Name as Libref and saying it's not assigned

Yokodaspacewale — Wed, 18 Sep 2019 19:22:53 GMT

edited. thanks!

Re: SAS Reading Schema Name as Libref and saying it's not assigned

Yokodaspacewale — Wed, 18 Sep 2019 19:25:32 GMT

This was it. man I'm dumb. This is how the "APPS" one looks. I knew this and missed it. thanks!