Hello,
The tests have been done on VIYA LTS 2023.03 and VIYA LTS 2024.09
I have tried to add a new index to a promoted table which has already other indexes (the reason why a new index has to be added at a later moment rather than from the beginning is because new requirements and also the table is quite large and I try to avoid delete it and recreate it)
For the tests I have used the table sashelp.cars which has been added to casuser and promoted. The first index has been added to variable "make" and a new table has been created and promoted. table.columnInfo has been used to check if the index has been applied (please see code below).
proc cas; table.index / table={caslib='casuser',name="cars"}, casOut={name="cars_idx2", caslib='casuser',indexVars={"make"},promote=True};
table.columnInfo /table={name="cars_idx2",caslib="casuser"};
quit;
Later I have tried to add a new index to variable "type" to the promoted table casuser.cars_idx2. (the code is below)
proc cas; table.index / table={caslib='casuser',name="cars_idx2"}, casOut={name="cars_idx2", caslib='casuser', indexVars={"type"} ,replace=True}; table.columnInfo / table={name="cars_idx2", caslib="casuser"}; table.tableInfo / caslib="casuser", name="cars_idx2"; quit;
From table.columnInfo we can see that the index has been applied, but, looking at table.tableInfo results we can see that the scope of table cars_idx2 is no longer global but session scope. The output of table.columnInfo and table.tableInfo is abovew. A similar behavior was presented in the article http://proc-x.com/2020/12/append-and-replace-records-in-a-cas-table/ where manipulating promoted cas table with data steps can leads to duplication of data. But in this scenario it seems that even a cas action can lead to duplicated data having local and global scope. I hoped that using "replace=True" would help.
Can I apply a new index to an existing promoted indexed table without having to delete and recreate that table?
Thank you for your comments.
Regards,
smm662002
... View more
