BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
PAzevedo
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
PAzevedo
Fluorite | Level 6

Yes i was.

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

Any doubts just ask.

Regards.

View solution in original post

13 REPLIES 13
Daryl
SAS Employee

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.

PAzevedo
Fluorite | Level 6

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.

Daryl
SAS Employee

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.

PAzevedo
Fluorite | Level 6

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.

forumsguy
Fluorite | Level 6

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

PAzevedo
Fluorite | Level 6

Yes i was.

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

Any doubts just ask.

Regards.

forumsguy
Fluorite | Level 6

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

PAzevedo
Fluorite | Level 6

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.

forumsguy
Fluorite | Level 6

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

forumsguy
Fluorite | Level 6

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("omsobj:SASLibrary?@Libref='D_RISDMA'","Tables",i,liburi);

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

output;

i+1;

end;

run;

PAzevedo
Fluorite | Level 6

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.

Tom
Super User Tom
Super User

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?

PAzevedo
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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