topic Re: Some tables missing from the SAS dictionary tables in SAS Data Management

Some tables missing from the SAS dictionary tables

Anne_A — Tue, 16 May 2023 14:42:33 GMT

Hi all,

I want to use the SAS dictionary tables (SASHELP views) to retrieve some specific information but some tables present in the environment (I can access them when browsing the servers directory structure - I'm using EGuide for this) are not included in the dictionary tables.

Would anyone have an idea where the problem could be?

Many TIA

Cheers

Anne.

Re: Some tables missing from the SAS dictionary tables

Reeza — Tue, 16 May 2023 14:52:47 GMT

Tables or libraries? Are some from a server, ie connection to SQL server and are any over 32 characters in length?
Are any possibly views instead of data?
Check the view list as well as tables.

Re: Some tables missing from the SAS dictionary tables

AllanBowe — Tue, 16 May 2023 16:31:39 GMT

the dictionary tables will only give you info for datasets that are assigned to a library in the current session

so the answer would be, to assign a library for each directory to extract the metadata

Re: Some tables missing from the SAS dictionary tables

Anne_A — Mon, 22 May 2023 06:44:04 GMT

Thank you for your answers @Reeza and @AllanBowe . These are not views nor libraries, are plain SAS datasets (no SQL connection) and their name is less than 32 characters long. If I programmatically assign the library the table is indeed found, but what I do not understand is why I can see the (originally assigned) library in the EGuide Server/Libraries treeview but do not have the tables in the SASHELP views.

I've noticed that some icons in the treeview are white while others are yellow. Right-clicking on the libraries I can assign the white ones and unassign the yellow ones, so indeed @AllanBowe the "issue" is a matter of (un)assigned libraries. I am still puzzled by this (new to me) concept of "unassigned" libraries which are accessible through the treeview hence somehow "known but not assigned" - so far I had known about assigned and unassigned libraries, but not of anything in between 🙂. Does any of you know how SAS determines which are which?

Many thanks in advance (again)!

Anne.

Re: Some tables missing from the SAS dictionary tables

Kurt_Bremser — Mon, 22 May 2023 06:52:56 GMT

Library and dataset names must be uppercase when searching for them in the DICTIONARY tables.

Re: Some tables missing from the SAS dictionary tables

Anne_A — Mon, 22 May 2023 08:35:17 GMT

Thank you @Kurt_Bremser , that had been done properly 🙂.

Re: Some tables missing from the SAS dictionary tables

AllanBowe — Mon, 22 May 2023 09:25:30 GMT

Hi Anne - you are seeing pre-assigned (in metadata) libraries in EG. These won't be visible in SASHELP or DICTIONARY tables until they are assigned.

More info here: https://documentation.sas.com/doc/en/bicdc/9.4/bidsag/p1ac6c9mrz0gn2n1spvpkf55ttyk.htm

For info, these libraries CAN be explored without needing to assign them using our free tool: https://datacontroller.io

/Allan

Re: Some tables missing from the SAS dictionary tables

Kurt_Bremser — Mon, 22 May 2023 09:31:21 GMT

My second approach would then be to test such a dataset directly.

data _null_
set lib.dataset (obs=10);
run;

and look at the log.

If you have a dataset file with a incorrect name (uppercase characters), the listing in the EG navigation pane will show the, but they won't be accessible.

This would also cause ERROR messages when you use the DICTIONARY tables in code.

Re: Some tables missing from the SAS dictionary tables

Quentin — Mon, 22 May 2023 12:49:50 GMT

A white library viewed in EG is unassigned, meaning it cannot be used in the SAS session. But the library definition is defined in SAS metadata, so EG can see that the library object(?) exists. When you right-click it you assign it and it turns yellow. The assigned library can be used in your SAS session. From the docs:

Assigning Libraries

When you view a SAS server in SAS Enterprise Guide or SAS Enterprise Guide Explorer, you see a list of libraries on the selected server. The list includes both libraries that are assigned (identified with a yellow icon) and libraries that are unassigned (identified with a white icon).

Assigned libraries are libraries that are pre-assigned or assigned in an autoexec program. Unassigned libraries are libraries that have been defined in metadata but have not yet been assigned or accessed. To assign an unassigned library, right-click on the library and select Assign. If the library definition was created by SAS Enterprise Guide Explorer, the library is assigned using the method that was specified in the library definition. Otherwise, the library is assigned using the META engine in its default mode, which means that the library is Read-Only. The library contents also match the metadata definition, so physical tables that are not defined in metadata are not displayed. Furthermore, tables and columns are not displayed if the user is not authorized to see that data.

https://documentation.sas.com/doc/en/bicdc/9.4/bidaag/n14hxpmvrmifi3n1wi1imvu9n5h8.htm

You can think about a white library as being similar to having a LIBNAME statement in your code, but you haven't executed the the statement yet. Then when you execute the LIBNAME statement, it creates the library.

Then benefit of having a library defined in the metadata (by an admin) but NOT assigned is that it gives SAS programmers/users an easy way to assign the library themselves, but avoids the problem of having 100s of automatically pre-assigned libraries which would flood your dictionary tables with unneeded data and generally slow things down.

Re: Some tables missing from the SAS dictionary tables

Anne_A — Tue, 23 May 2023 12:20:47 GMT

Thank you @Quentin 🙂! I wonder, is there a programmatic way to assign all libraries (so that, for example, some code using the SAS dictionary tables could be properly tested in EGuide)?

Many thanks in advance!

Cheers

Anne.

Re: Some tables missing from the SAS dictionary tables

Quentin — Tue, 23 May 2023 13:24:34 GMT

@Anne_A wrote:

Thank you @Quentin 🙂! I wonder, is there a programmatic way to assign all libraries (so that, for example, some code using the SAS dictionary tables could be properly tested in EGuide)?

Yes, probably.

Sorry, I can't do much better than that. : )

The library definitions are stored in metadata (for a SAS 9 / EBI server), so you should be able to crawl through the metadata to get a list of all of the libraries that are defined ("registered") and then once you have that list, you could generate and execute a libname statement to assign each one (using macro language or whatever).

I've always found crawling through metadata to be a hassle. See for example https://communities.sas.com/t5/Administration-and-Deployment/Library-Extract-metadata/td-p/676885 and https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lrmeta/p1k9zipe59ha2an1pq34gu143lay.htm .

It's possible if you search papers user group papers at lexjansen.com, someone will have already shared code for this.

Re: Some tables missing from the SAS dictionary tables

AllanBowe — Tue, 23 May 2023 15:33:42 GMT

Definitely - it's done in the DC Data Catalog generator, and the macros are open source:

mm_getlibs - fetch all meta libraries: https://core.sasjs.io/mm__getlibs_8sas_source.html
mm_assignlib - assign lib using META engine: https://core.sasjs.io/mm__assignlib_8sas.html

If you need to assign a DIRECT libname engine, that's also (mostly) possible:

mm_assigndirectlib - assign lib using NATIVE engine: https://core.sasjs.io/mm__assigndirectlib_8sas.html

Let me know if you need any additional libname engines supported in that last one, or feel free to raise a PR!

/Allan

Re: Some tables missing from the SAS dictionary tables

Anne_A — Fri, 26 May 2023 09:57:23 GMT

AWESOME! Thank you very much @AllanBowe 😊!