BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
okaka23
Calcite | Level 5

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
SASJedi
SAS Super FREQ

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):

 

SASJedi_0-1655488554801.png

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

SASJedi_1-1655488708837.png

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:

SASJedi_2-1655488839138.png

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:

SASJedi_4-1655489850403.png

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

SASJedi_3-1655489743235.png

 

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

SASJedi_5-1655489971628.png

And violá!

SASJedi_6-1655490012054.png


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:
SASJedi_7-1655490112050.png

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.

Check out my Jedi SAS Tricks for SAS Users

View solution in original post

11 REPLIES 11
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
okaka23
Calcite | Level 5

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. 

PaigeMiller
Diamond | Level 26

Please answer the question I asked:

 

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

--
Paige Miller
okaka23
Calcite | Level 5

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.

PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
Reeza
Super User

@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. 

 

 

novinosrin
Tourmaline | Level 20

 

proc contents data=yourlibname._all_ nods;

run;

 

Jim_Ogilvie
Obsidian | Level 7

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.

pavel21234165
Calcite | Level 5
thanks for answaring the guy without asking useless specific questions, helped a lot.
Jim_Ogilvie
Obsidian | Level 7

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.

SASJedi
SAS Super FREQ

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):

 

SASJedi_0-1655488554801.png

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

SASJedi_1-1655488708837.png

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:

SASJedi_2-1655488839138.png

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:

SASJedi_4-1655489850403.png

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

SASJedi_3-1655489743235.png

 

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

SASJedi_5-1655489971628.png

And violá!

SASJedi_6-1655490012054.png


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:
SASJedi_7-1655490112050.png

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.

Check out my Jedi SAS Tricks for SAS Users

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 11 replies
  • 55179 views
  • 14 likes
  • 7 in conversation