topic Adding to an Existing Index in SAS Programming

Adding to an Existing Index

A_SAS_Man — Tue, 23 Apr 2019 15:21:30 GMT

I have a data set such as below:

data test;
 input c1 c2 c3 c4;
 datalines;
 1 3 5 2
 3 5 1 7
 2 6 8 9
 1 2 7 3
 6 1 9 1
 9 1 3 6
 4 1 1 7
 ;
 run;

I have indexed it as follows:

data test2 (index=(c1
c2
));
set test;
run;

Suppose I want to index on an additional column at a later time, is there a way to add an index for c3, without deleting or re-indexing the columns c1 and c2? For my actual purposes, re-indexing columns c1 and c2 is very expensive as it is a very large data set. Obviously if you do the below you lose your indexes to 1 and 2:

data test2 (index=(c3
));
set test2;
run;

And if you do the following you re-index over all 3 columns (very computationally expensive):

data test2 (index=(c1
c2
c3
));
set test;
run;

Is there anyway to get the results of indexing over all 3 columns, without having to redo the indexes on 1 and 2?

Re: Adding to an Existing Index

AllanBowe — Tue, 23 Apr 2019 15:36:27 GMT

Yes, there are 2 ways in fact - one with

proc sql;

and another using

proc datasets

You cannot modify an existing index, but you can delete one and add another. Here is the syntax for creating a new, simple index with both approaches:

data test(index=(c1 c2));
input c1 c2 c3 c4;
datalines;
1 3 5 2
3 5 1 7
2 6 8 9
1 2 7 3
6 1 9 1
9 1 3 6
4 1 1 7
;
run;
/* add index with proc sql; */
proc sql;
create index c3 on test(c3);

/* add index with proc datasets */
Proc Datasets library = work nolist;
modify test;
index create c4;
quit;

proc sql;
describe table test;

The describe statement 'describes' the table (including indexes) in the log, and will show:

create table WORK.TEST( bufsize=65536 )

(
c1 num,
c2 num,
c3 num,
c4 num
);
create index c4 on WORK.TEST(c4);
create index c3 on WORK.TEST(c3);
create index c1 on WORK.TEST(c1);
create index c2 on WORK.TEST(c2);