retrieve information for metadata tables

[Sandyliu]

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.

[andreas_lds]

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.

[Patrick]

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

[Sandyliu]

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

[Kurt_Bremser]

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

[BrunoMueller]

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

[Sandyliu]

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?

[ybolduc]

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 🙂

[Sandyliu]

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

[andreas_lds]

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.

[ybolduc]

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