DATA Step, Macro, Functions and more

retrieve information for metadata tables

Reply
New Contributor
Posts: 4

retrieve information for metadata tables

Hi All!

 

I am working on retrieving a list of tables and related information(e.g. source table) that were registered in Metadata.

I tried to use %mdsecds() to make this come true, but the related information is not enough to meet my requirements.

Is there any methods to locate where the metadata tables originally come from, but not the location in the SAS folder?

Thank you for your attention. I really appreciate any help you can provide.

 

Valued Guide
Posts: 629

Re: retrieve information for metadata tables

The metabrowse-utility - found in display manager - is useful to see what's stored in metadata at all. You could use the metadata-functions to extract the information. I don't have an example at hand, but you will find code explaining how to get user-data from metadata with those functions, you will have to adept object names etc.

 

Respected Advisor
Posts: 4,797

Re: retrieve information for metadata tables

@Sandyliu

You can query metadata using SAS code - but it can become involved. If you look into the actual code of the %mdsec macro then you might get the idea.

 

For what's missing: Can you please be more specific? What is it exactly that %mdsec doesn't give you?

New Contributor
Posts: 4

Re: retrieve information for metadata tables

@Patrick

 

Sorry for my unclear expression.

The missing data is the original location(library) of the metadata tables before they are registered.
For example: Suppose a table named【Demo】, stored in a pre-assigned sas library【LIB】, were registered in a sas folder【Shared folder】. Although %mdsec macro can give me a location of the sas folder【Shared folder 】, what I need is the original location【LIB】 of the table.

Is there any sas code to extract that kind of data?

 

 

Super User
Posts: 10,574

Re: retrieve information for metadata tables

Are you actually looking for the result of the pathname() function?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
SAS Super FREQ
Posts: 830

Re: retrieve information for metadata tables

If you have the name of a table within the metadata like: "/folder/subfolder/tablename(Table)" there is a macro as described in

http://support.sas.com/kb/37/386.html to read out the name as libref.tableName

New Contributor
Posts: 4

Re: retrieve information for metadata tables

Posted in reply to Bruno_SAS

Thank you for your post.

That’s a solution when there is only one table name required to search for its library name.

However, I am still seeking a more efficient way to help me to read out all the library names for a long list of registered tables.

I tried to write a loop macro(please check the attachment), basically using the macro that described in  http://support.sas.com/kb/37/386.html , but it doesn’t work.

Further more, I found another problem that some registered tables are not appearing in the SAS folder, but displayed in the Management Console. Is there any solutions or suggestions?

Contributor
Posts: 36

Re: retrieve information for metadata tables

What do you mean exactly by where the table/library comes from? I've worked a lot with the metadata data step functions so if it's stored in the metadata I should be able to show you how to extract it Smiley Happy

New Contributor
Posts: 4

Re: retrieve information for metadata tables

@ybolduc 

Thank you for your reply.

 

I mean the association between library and registered table.

 

In generally, after we registering a dataset stored in a library with a SAS folder, we can find that registered table displaying in the SAS folder. What happen if the registered table lost its association with its original library, for instance, its physical table or library has been deleted ? The registered table will leave in SAS folder as trash, because users will not be able to open and use it anymore. To classify and manage the registered tables, I am trying to list up all the registered tables and find out their original libraries. At present, I am facing the following 2 problems that really need your help.

1, how to detect the existence of registered tables in enterprise guide

  Be based on my knowledge, I found that

  If a registered table had a connection with a library(even if lost the connection in recently),

  then it still can be found in a SAS folder in EG.

  If a registered table had never ever been assigned to a library, then it will not appear in EG.

2, If the registered table appeared in enterprise guide, then look up its library.

  Is there any methods can solve my problems. BTW, if my explanation makes you confused, please let me know.

 

 

Valued Guide
Posts: 629

Re: retrieve information for metadata tables

You could use proc metalib with update_rule=delete to clean-up orphaned references to deleted tables in one library. So you need a way to get the names of all libraries and call the proc for each one. The names can be extracted from metadata by using the functions in mentioned in my earlier post.

 

Contributor
Posts: 36

Re: retrieve information for metadata tables

Hi Sandy,

 

     I'm not certain about the specifics of SAS EG, I'm more of a display manager kind of users. But I'm used to work with the metadata. So if I was to look for a registered library that is not associated with a Schema or a Library, I would run the following code and filter the output table based on "Assoc_Count = 0":

 

data work.MetaTablesAssocs;
	length TableURI $60. AssocURI $60.;
	TableURI = '';
	count = metadata_getnobj("OMSOBJ:DataTable?@Id contains '.'", 1, TableURI);
	put count=;
	do i = 1 to count;
		rc = metadata_getnobj("OMSOBJ:DataTable?@Id contains '.'", i, TableURI);
		Assoc_Count = metadata_getnasn(TableURI, 'TablePackage', 1, AssocURI);
		output;
	end;

	keep TableURI Assoc_Count;
run;

See here for reference: http://support.sas.com/documentation/cdl/en/omamodref/67417/HTML/default/viewer.htm#datatable.htm

 

Thanks

Ask a Question
Discussion stats
  • 10 replies
  • 282 views
  • 1 like
  • 6 in conversation