topic Re: Retrieve table metadata info for a given library in SAS Programming

Retrieve table metadata info for a given library

figureItOut — Wed, 19 Jul 2023 16:15:01 GMT

Hello,

I found some SAS code that retrieves all of the libraries and some of their associated SAS metadata. I would like to expand this code to retrieve each library's table metadata. It seems like for each library a loop is needed to find the number of tables in the library, then for each table retrieve the description, the folder and so forth.

Any suggestions?

Thank you.

Re: Retrieve table metadata info for a given library

Reeza — Wed, 19 Jul 2023 16:26:06 GMT

Quick check - you've looked at sashelp.vtable and sashelp.vcolumn views?

Re: Retrieve table metadata info for a given library

ballardw — Wed, 19 Jul 2023 16:43:11 GMT

JOIN between the appropriate tables should work in SQL instead of looping.

One question is which "table metadata" that you want.

Re: Retrieve table metadata info for a given library

figureItOut — Wed, 19 Jul 2023 17:21:27 GMT

Thanks Reeza but the sashelp.vtable and sashelp.vcolum (and associated dictionaries) do not contain SAS metadata info.

Re: Retrieve table metadata info for a given library

figureItOut — Wed, 19 Jul 2023 17:34:29 GMT

The table metadata I want, to start with, is the Name, Description, Location, and actual physical table name. As shown in SAS Management Console.

Re: Retrieve table metadata info for a given library

SASKiwi — Wed, 19 Jul 2023 22:17:45 GMT

Try this:

proc sql;
  create table Tables as
  select  T.libname
         ,T.memname
         ,T.memlabel
		 ,L.path
		 ,L.sysdesc
  from dictionary.tables as T,
       dictionary.libnames as L
  where upcase(memname) = 'ODSSTYLE'
  and T.libname = 'STPSAMP' 
  and T.libname = L.libname
  ;
quit;

Re: Retrieve table metadata info for a given library

Patrick — Thu, 20 Jul 2023 03:19:24 GMT

@Reeza , @SASKiwi , @ballardw

I believe the code and screenshot the OP shared makes it obvious that this is about querying SAS metadata and SAS Metadata Tables and not the SAS dictionary.

@figureItOut : Yes, you would need to implement some sort of loop logic per metadata library definition. I haven't checked but would assume tables are associations for libraries.

I normally use the SAS Windows Manager and then metabrowse to figure out how things "hang together" in SAS Metadata.