topic Re: list all the tables in the library in SAS Programming

list all the tables in the library

okaka23 — Tue, 18 Dec 2018 15:55:36 GMT

hello,

I have my library assigned but can only see a few tables. I think there is a OPTIONS statement you can use to list all the tables in the library, however, I cannot recall it.

Thanks.

Re: list all the tables in the library

PaigeMiller — Tue, 18 Dec 2018 15:57:03 GMT

If you have issued the LIBNAME statement properly, it should show all the tables in the Library.

Where exactly are you looking at the list? Please be very specific, or show us a screen capture.

Re: list all the tables in the library

novinosrin — Tue, 18 Dec 2018 16:01:37 GMT

proc contents data=yourlibname._all_ nods;

run;

Re: list all the tables in the library

okaka23 — Tue, 18 Dec 2018 16:03:31 GMT

I know I have access to these invisible tables since I test it with a simple Data step and it has an output. But these tables are just not showing up in the library.

Re: list all the tables in the library

PaigeMiller — Tue, 18 Dec 2018 16:09:04 GMT

Please answer the question I asked:

Where exactly are you looking at the list? Please be very specific, or show us a screen capture.

Re: list all the tables in the library

okaka23 — Tue, 18 Dec 2018 16:19:07 GMT

I am using SAS EG, Library A is assigned, I have access to all the tables under Library A say table1, table2, table3 ---> table10. However, when you expand Library A, it only shows table1 and table 2 under library A.

But I did a test:

Data test;

set A.table 3 (obs=3);

run;

it works. So my question is how to make table3--->table10 show up in the library A.

Thanks.

Re: list all the tables in the library

PaigeMiller — Tue, 18 Dec 2018 16:44:45 GMT

As far as I know, if you have 10 tables in a library, you should see all 10. There is no option to set to change this, it should always show you all 10.

So, are you sure you don't have to scroll down to see the rest of the tables?

Can you show us a screen capture that shows the library with only 2 tables, and in the same screen capture, show us the EG LOG window where you run the code that for table3 so we can see that it works?

Re: list all the tables in the library

Reeza — Tue, 18 Dec 2018 23:30:41 GMT

@okaka23 wrote:

I am using SAS EG, Library A is assigned, I have access to all the tables under Library A say table1, table2, table3 ---> table10. However, when you expand Library A, it only shows table1 and table 2 under library A.

But I did a test:

Data test;

set A.table 3 (obs=3);

run;

it works. So my question is how to make table3--->table10 show up in the library A.

Thanks.

What defines work? What does the log show? I suspect you're not supposed to have access so this is something you need to discuss with your SAS administrator.

Re: list all the tables in the library

Jim_Ogilvie — Wed, 18 Mar 2020 15:59:48 GMT

Hi,

Just saw this, whilst search for something related.

The most common reason for a library to be showing as empty in the explorer, even though you can query the tables in code, is that the "schema" option in the libname statement has not been set correctly of has been ommitted.

schema='dbo' worked for the MS SQL Server libraries I worked with previously.

Oracle is tricky, so you'll need to talk to you DBA's.

Re: list all the tables in the library

pavel21234165 — Tue, 18 Aug 2020 08:49:48 GMT

thanks for answaring the guy without asking useless specific questions, helped a lot.

Re: list all the tables in the library

Jim_Ogilvie — Tue, 18 Aug 2020 09:20:15 GMT

Happy to help.

As another practical example, an Oracle database I had to work with the other day required:

schema=scheme

In the libname statement.

Re: list all the tables in the library

SASJedi — Fri, 17 Jun 2022 18:36:34 GMT

So, as I understand the problem, you submitted a properly coded LIBNAME statement and are attempting to view the tables in that library using one of the typical SAS GUIs, such as the Explorer window in the SAS windowing environment (like PC SAS), the Servers Library tree in Enterprise Guide, or the Libraries pane in SAS Studio. Here are a few reasons the tables you expect to see may not appear in the display:
1. I'm using a SAS/ACCESS Interface LIBNAME statement to access database tables.
You may not have specified the correct SCHEMA, or the credentials you used to authenticate to the database don't have SELECT permissions for the tables you want to access. For example, student submits this code:

libname db oracle path="oracle.mydomain.net:1521/oracle1"
        user="student" pw="Metadata0";

And sees these tables in the library (example from Enterprise Guide 7.15):

So, student2 wants access to those tables too, and submits this code:

libname db oracle path="oracle.mydomain.net:1521/oracle1"
        user="student2" pw="Metadata0";

But student2 sees these tables in the library instead of those seen by student.

This is because the ORACLE libname engine connects to your personal schema when you don't specify SCHEMA=. So student2 now specifies the student schema like this:

libname db oracle path="oracle.mydomain.net:1521/oracle1"
        schema=student user="student2" pw="Metadata0";

But sees only the CARS table:

Because student2 has only been granted SELECT permissions on the CARS table in the student schema, student2 won't be able to see any of the other tables in that schema.

2. I'm using SAS libraries located in folders I know I have access to. Consider this code:

libname one "C:\temp\one";
data one.fish;
   set sashelp.fish;
run;

The LOG looks good:

25         data one.fish;
26            set sashelp.fish;
27         run;

NOTE: There were 159 observations read from the data set SASHELP.FISH.
NOTE: The data set ONE.FISH has 159 observations and 7 variables.

If I am running PC SAS, I see the table in my one library right away:

But if I'm working in Enterprise Guide (7.x or earlier, and early versions of SAS Studio, too) I don't see it!

These earlier clients required you to refresh the libraries before you could see newly created tables:

And violá!

You can always check the contents of a library programmatically to verify you are seeing everything in the GUI window. You might be surprised at what you find there 😉
My WORK library looks like this:

But I get a whole lot more information when I use PROC CONTENTS:

proc contents data=work._all_ nods;
run;

And the results show a lot of other things in that library besides my tables:

#	Name	Member Type	File Size	Last Modified
1	REGSTRY	ITEMSTOR	13KB	06/17/2022 12:36:31
2	SASGOPT	CATALOG	5KB	06/17/2022 12:36:40
3	SASMAC3	CATALOG	13KB	06/17/2022 14:23:09
4	SASMACR	CATALOG	89KB	06/17/2022 12:37:31
5	TEST	DATA	192KB	06/17/2022 12:36:40
6	_PRODSAVAIL	DATA	128KB	06/17/2022 12:36:32

Many thanks to all who pitched in to provide clarification and pieces of the answer, especially @PaigeMiller, @novinosrin, and @Jim_Ogilvie I've done my best to consolidate, expand, and organize everything here for future SAS searchers.