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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.