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.
Nevertheless, metadata browsing through code is an item i'm very interested in learning more about.
Regards,
PAzevedo.
Yes i was.
I'll attach the full code, take what suits you.
Any doubts just ask.
Regards.
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.
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.
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.
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.
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
Yes i was.
I'll attach the full code, take what suits you.
Any doubts just ask.
Regards.
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 ???
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.
Thanks a lot buddy... I will make changes in code and will update if I get stuck anywhere..
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;
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.
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.