05-13-2015 05:52 AM
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.
I want to extract all the registered tables in library AAA.
* generates the list; But the generation bit is NOT working;
select distinct quote(trim(memname)) into :list separated by ' '
where libname = 'AAA'
order by memname ;
%put list value : &list. ;
* updates metadata for already registered tables;
Any help would be greatly appreciated.
05-14-2015 10:31 AM
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.
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.
05-14-2015 10:41 AM
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.
05-14-2015 11:22 AM
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.
For your purpose (only updating registered tables), there is option in Proc metalib;
UPDATE_RULE = (NOADD);