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?
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);
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);
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: