Prev: Log File with Color in Server
Next: path analysis with categorical variables
From: SASsyGirl on 25 Mar 2010 11:27
I have created a dataset in DI studio (in metadata) and loaded it with
data. I now want to add a unique index. I add the unique index to
the dataset and then I have tried to load duplicate rows and it allows
the rows to be appended (I"m using a loader step to append to the
dataset). When I view the statistics on the dataset, clearly it is
NOT creating a unique index however I do see it in the metadata
properties via the DI Studio GUI. Do I need to delete all the data
and then create the index or do I need to recreate the dataset with
the index or is there something I'm missing on the Loader step
or ???? Please help.
From: Patrick on 26 Mar 2010 22:12
It would be important in general to know what DI version you're using.
There was quite a lot of improvement from SAS9.1 to SAS9.2.

You wouldn't want the index re-created every single time you're
loading data into target. Therefore I don't think you're missing
something in the table loader.

What happens is:
As the underlying SAS dataset already exists it doesn't get re-created/
modified even though the metadata has changed and is no more in synch.
As far as I know none of the existing DI transforms checks for this
out-of-synch condition and then modifies the underlying table.

You could of course delete the SAS table and then run your DI job. The
table would get created using the latest metadata definitions.

Another way could be:
Go into the code created by the table loader and there go to the part
for creating a table if it doesn't exist. In this code section will be
the generated PROC DATASETS code creating the indexes. Just copy this
code as a whole and run it in a code window. This should add all the
indexes as defined in metadata.
No harm should be done if you run this code for already existing
indexes. I think it throws an error as there is an already a existing
index but still will create the remaining missing indexes.

If the index already exists and you want to modify/delete it then you
have to write your own piece of code (PROC DATASETS) for deleting an
index (contents will show you the name of the index). Modify is
easiest done by first deleting and then re-creating (using the
generated code).


To bring existing tables into synch with already changed metadata can
be quite annoying. As I'm currently working in a project where a lot
of such changes happened (new/modified rows and indexes) I ended up
writing some code which queries the metadata and then creates the
necessary SAS code for modifying the tables (still a draft version
only good enough for me, can't give it away yet).

In SAS9.2 (DIS 4.2): One can first change the underlying SAS table and
then just re-synch table metadata with the SAS table. That at least is
now a built-in DIS function.

HTH
Patrick
 | 
Pages: 1
Prev: Log File with Color in Server
Next: path analysis with categorical variables