DATA Step, Macro, Functions and more

Get Table Attributes from metadata with metadata_* functions

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Get Table Attributes from metadata with metadata_* functions

Hi all.

I'm building a custom SCD where an important part is the initialization of the Dimension table, if it doesn't exist already.

What i thought was a simple activation in the New Tranformation Wizard is after all a huge dificulty, at least from where i stand so far.

The problem is i can't seem to find all attributes needed to build that table structure. Attributes like constraints info can't be activated in the wizard and i'm not fiding them through metadata browsing.

The path i'm following is:

1- Browse the library object (duplicate table name can be found in different librarys).

2- Browse the table object.

3- Browse the table attributes.

I can find lot of information about the table in it's attributes (Columns object, indexes object, etc) but not the constraints.

The code i got so far is in attachment.

Any references to online material would be very apreciated. I already found some but with few detail.

A direct answear is also appreciated or an easier alternative. Smiley Happy

Nevertheless, metadata browsing through code is an item i'm very interested in learning more about.

Regards,

PAzevedo.

Attachment
Attachment

Accepted Solutions
Solution
‎01-23-2012 08:18 AM
Contributor
Posts: 36

Re: Get Table Attributes from metadata with metadata_* functions

Yes i was.

I'll attach the full code, take what suits you.

Any doubts just ask.

Regards.

View solution in original post

Attachment

All Replies
SAS Employee
Posts: 27

Get Table Attributes from metadata with metadata_* functions

In the Metadata Browser, you can see that column constraints (such as "IsNullable") are attributes of the column object.  Have you tried to programmatically browse the attributes of the column object?   It seems you are pretty close with your code.

Contributor
Posts: 36

Get Table Attributes from metadata with metadata_* functions

Hi Daryl

I finally got to the null constraints along with other usefull info into being able to build the dataset structure.

Your short words about atributes gave me the direction i was missing.

At some point i had to stop looking for associations and associated objects and start browsing the atributes.

It was only yesterday that i reaserched about coded metadata browsing for the first time so i'm still a bit green on the matter.

Next i need to get the rest of my dataset info (Primary Key (PK), Foreign Keys (FK), Unique Keys (UK) and Indexes).

This is where i'm standing now:

1 - I found both Index and Unique Key Objects

2 - I know what variables form the indexes and the PK (the DB tables do not have FKs nor UKs so i'm leaving that acquiring of knowledge for latter challenges)

3- I'm removing from the indexes the one that is owned by the PK Constraint by merging the two datasets although i'm sure there is an atribute classifying if the index bellongs to a constraint.

4- I still can't find the order of the variables in de Indexes nor in the PK.

Any help on items 3 and 4?

I'm going on vacation for two weeks and probably wont be able to come here for that time.

Regards,

PAzevedo.

PS: I added the code for getting Constraints and Atributes to the first post. Hope it helps anyone struggling with the same issues i am.

SAS Employee
Posts: 27

Get Table Attributes from metadata with metadata_* functions

For #4, I don't see any attributes that indicate order.  But just as a test, I defined a compound index on a table and looked at the table metadata in the Metadata Browser.  In this view, the columns are associated to the index in the order that I assigned them.  So perhaps you could use your loop variables (i,j,k, etc.) for ordinal position.  That's just a guess on my part.

I'm not sure what you mean by #3.  The columns associated to an index do have the attributes "IsDiscrete", "IsNullable", etc.  Maybe that gets you what you need.

Contributor
Posts: 36

Get Table Attributes from metadata with metadata_* functions

Daryl wrote:

For #4, I don't see any attributes that indicate order.  But just as a test, I defined a compound index on a table and looked at the table metadata in the Metadata Browser.  In this view, the columns are associated to the index in the order that I assigned them.  So perhaps you could use your loop variables (i,j,k, etc.) for ordinal position.  That's just a guess on my part.

I'm not sure what you mean by #3.  The columns associated to an index do have the attributes "IsDiscrete", "IsNullable", etc.  Maybe that gets you what you need.


Now that you mentioned it, the listing of columns in the atributes and constraints datasets i get in the second script is in the order they were defined in the metadata so probably the same happens with the indexes and PK constraint. I'll look into it but i believe it was a very nice guess.

By #3 i mean that i get two datasets from my Index and PK search, one with the indexes, including the one generated by the PK constraint, and their respective columns and another dataset with the PK Contraint columns. In order to find which one of the indexes in the table indexes is owned by the constraint i'm merging those two datasets and excluding the index whose columns match the PK Contraint columns.

What i'm wondering is if that info (about the index belloging to a PK contraint) is anywhere in the Index metadata although i believe the previous solution solves my problem.

Frequent Contributor
Posts: 85

Get Table Attributes from metadata with metadata_* functions

Were u able to find the correct solution for Indexes and Primary Keys ??? If yes, can u plz post code here ??? Even I need something like this

Solution
‎01-23-2012 08:18 AM
Contributor
Posts: 36

Re: Get Table Attributes from metadata with metadata_* functions

Yes i was.

I'll attach the full code, take what suits you.

Any doubts just ask.

Regards.

Attachment
Frequent Contributor
Posts: 85

Get Table Attributes from metadata with metadata_* functions

Hi PAZevedo.. I tried this code and it is running absolutely fine..but I am getting confused for the library traversal. I want the output of all tables in a library and not a single table.. with this code I can get indexes,keys and attributes of only one table but I want  same information for all tables. I understood this code about 50 percent and finally got confused because of many do while conditions.. can u provide direction to me ???

Contributor
Posts: 36

Re: Get Table Attributes from metadata with metadata_* functions

When you are browsing trough the library table objects you just have to remove the condition where you select the "etls_table" so that every table info in the library is retrived. Also you need to add the tabname variable to the output keep statements.

Replace

if nassobj > 0 & tabname="&etls_table" then do;

with

if nassobj > 0 then do;

and Your data statement should look like this:

data isNullConstraints(keep=tabname columnname) attributes(keep=tabname columnname atrname atrvalue) indexes(keep=tabname columnname idxname) keys(keep=tabname columnname keyname isPrimary);

You have to make some changes in the rest of the code, now that you have more than one table represented in each properties datasets. Adding the tabname variable to the by statements should do the trick.

PA.

Frequent Contributor
Posts: 85

Get Table Attributes from metadata with metadata_* functions

Thanks a lot buddy... I will make changes in code and will update if I get stuck anywhere..

Frequent Contributor
Posts: 85

Get Table Attributes from metadata with metadata_* functions

I followed your code and was able to get solution. Below is the code if somebody is interested

data tables_registered;

length liburi dataname $100;

keep liburi dataname;

liburi='';

i=1;

do until (i>rc);

rc=metadata_getnasn("omsobjSmiley FrustratedASLibrary?@Libref='D_RISDMA'","Tables",i,liburi);

prc=metadata_getattr(liburi,"Name",dataname);

output;

i+1;

end;

run;

Contributor
Posts: 36

Re: Get Table Attributes from metadata with metadata_* functions

I didn't realize you were only searching for the list of the tables.

Anyway, your code gave me some ideas to make mine simpler. I updated the code attached in the previous post.

I'm using the keyword "name" instead of "libref" for my search as the "etls_lib" is an automatic global variable that DIS jobs have representing the target table lib.

PA.

Super User
Super User
Posts: 6,502

Re: Get Table Attributes from metadata with metadata_* functions

That code seems like a lot of work.  Doesn't the system surface the data as a SAS view that you can just query?  Similar to the DICTIONARY views in Base SAS?

Contributor
Posts: 36

Get Table Attributes from metadata with metadata_* functions

The information about table attributes can be found in several sashelp views(VTABLE, VTABCON, VINDEX) if the table already physically exists.

Since the purpose of my macro is to create the table structure that approach doesn't suit me.

There might be a way of getting that info into some sashelp view but i don't know how.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 1460 views
  • 6 likes
  • 4 in conversation