BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JohnT
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
JohnT
Quartz | Level 8

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 !

libname core odbc datasrc = 'PostGreSQL30' schema = report_target PRESERVE_TAB_NAMES=yes;

proc contents data=core._all_ ;
run;


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.

View solution in original post

3 REPLIES 3
jakarman
Barite | Level 11

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

---->-- ja karman --<-----
Patrick
Opal | Level 21

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;

JohnT
Quartz | Level 8

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 !

libname core odbc datasrc = 'PostGreSQL30' schema = report_target PRESERVE_TAB_NAMES=yes;

proc contents data=core._all_ ;
run;


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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 8475 views
  • 0 likes
  • 3 in conversation