Hi,
I'm trying to connect to a PostgreSQL database using the LIBNAME statement.
This is what I'm using:
libname core odbc datasrc = 'PostGreSQL30' schema = Public;
This connects successfully according to the log, but I have no idea what the tables are.
When I connect to a Microsoft SQL database I use schema = dbo and in Enterprise Guide, the library is expandable and I can see the tables and I know what to query/interogate.
The obvious answer is to ask the DBA as to what tables are available, but he's not here. Is there a way that I can setup the library such that I can see the contents? I would want this feature even if the DBA could give me a listing of all the tables available.
We are on Enterprise Guide 5.1 (5.100.0.12269) Hot fix 7 (32-bit), and SAS 9.3 (TS1M2), Rev. 930_12w41, on Windows Server 2008 64 bit R2 SP1.
Thanks.
Hi
Here's my log:
15 libname core odbc datasrc = 'PostGreSQL30' schema = report_target;
NOTE: Libref CORE was successfully assigned as follows:
Engine: ODBC
Physical Name: PostGreSQL30
16
17 proc contents data=core._all_;
18 run;NOTE: Due to the PRESERVE_TAB_NAMES=NO libname option setting, 9 table(s) have not been displayed/returned.
WARNING: No matching members in directory.
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.14 seconds
cpu time 0.00 seconds
I get nothing in the results tab when I run this.
Having said that, this is the first time I've paid attention to the log.
I've successfully done it !
And now I can see the datasets in the CORE library. I wonder why I never noticed the hint in the log when I was originally trying to do this.
Thanks.
It are not the tables in a DBMS you are refering to but to containers, the schemas, that contain those tables.
Within SAS I do not knon an option to see the schema-s.
One excpeption was the ODBC interface missing the schema name as using SAS/Share. As SAS/SHare is SAS is not useful info.
There are a lot of developers tools like SQL developer that will show the Schema-names when being autorized
I might be missing something but if you say that the libname as such executes successfully then the following code will show you all tables available via schema "Public" (which could be none).
proc contents data=core._all_;
run;
Hi
Here's my log:
15 libname core odbc datasrc = 'PostGreSQL30' schema = report_target;
NOTE: Libref CORE was successfully assigned as follows:
Engine: ODBC
Physical Name: PostGreSQL30
16
17 proc contents data=core._all_;
18 run;NOTE: Due to the PRESERVE_TAB_NAMES=NO libname option setting, 9 table(s) have not been displayed/returned.
WARNING: No matching members in directory.
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.14 seconds
cpu time 0.00 seconds
I get nothing in the results tab when I run this.
Having said that, this is the first time I've paid attention to the log.
I've successfully done it !
And now I can see the datasets in the CORE library. I wonder why I never noticed the hint in the log when I was originally trying to do this.
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.