Add index to a CAS table which is already indexed

smm662002 — Tue, 08 Apr 2025 09:31:27 GMT

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

Re: Add index to a CAS table which is already indexed

ChrisNZ — Wed, 09 Apr 2025 06:44:26 GMT

Moved to SAS Viya community

topic Re: Add index to a CAS table which is already indexed in SAS Viya

Add index to a CAS table which is already indexed

Re: Add index to a CAS table which is already indexed