How to get a list of registered tables of a SAS MDS library

Reply
Contributor
Posts: 21

How to get a list of registered tables of a SAS MDS library

Hi All

Our source tables are all in Oracle. We register table in libraries. I want to generate a list of already registered tables, so that when the data refresh happens I could update the SAS Metadata.

For Example

I want to extract all the registered tables in library AAA.

* generates the list; But the generation bit is NOT working;

sql noprint;

select distinct quote(trim(memname)) into :list separated by ' '

from dictionary.tables

where libname = 'AAA'

order by memname ;

quit ;

%put list value : &list. ;

* updates metadata for already registered tables;

proc metalib;

   omr (library="AAA");

   update_rule(delete);

   select &list.;

   report (type=detail);

run;

Any help would be greatly appreciated.

Thanks

Abhi

Respected Advisor
Posts: 3,124

Re: How to get a list of registered tables of a SAS MDS library

From what I can see, you have several issues, let's tackle it one by one.

1. If your purpose your list is to update 'registered' tables metadata, then a list may not be necessary.  Unless for some reason you don't want to synchronize some of the tables, I don't see why you can't skip the 'generating list' step.

proc metalib;

  omr (library="AAA");

  update_rule(delete);

select &list;


  report (type=detail);

run;

2. If you do need a table list for some reason, unless your library is pre-assigned, you need do a libname statement to assign it before you can utilize dictionary.tables.

3. There are some special metadata functions that can help you to obtain almost anything from metadata repository, the example in following link can get all of your metadata libraries, which I use for a batch job to synchronize/update metadata.

SAS(R) 9.3 Language Interfaces to Metadata

Good luck,

Haikuo

Contributor
Posts: 21

Re: How to get a list of registered tables of a SAS MDS library

Hi There

The Source data schema has 100s of garbage tables, like load, load10022015, etc. The syntax you have given will register all the tables in the schema.

For example, I want to register only 4 tables A, B, C, & D. And I dont want to hard code it. That why I am generating a list first & then updating the metadata.

Hope it makes sense.

Thanks

Respected Advisor
Posts: 3,124

Re: How to get a list of registered tables of a SAS MDS library

Then it will be my answer 2. You need to assign it first,

libname aaa meta library=aaa metaout=datareg;

then dictionary.tables. This will ONLY give you the registered table names.

Good luck,

Haikuo

Update:

For your purpose (only updating registered tables), there is option  in Proc metalib;

UPDATE_RULE = (NOADD);

that will only update registered tables, I think you can use it along with 'delete', but I am not sure.
Update_2:
update_rule = (delete noadd);
works.

Ask a Question
Discussion stats
  • 3 replies
  • 425 views
  • 0 likes
  • 2 in conversation