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.
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
);
Can you include your LIBNAME statements and/or CONNECT statements because it will help us sort this out?
Best wishes,
Jeff
edited. thanks!
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
);
This was it. man I'm dumb. This is how the "APPS" one looks. I knew this and missed it. thanks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.