CREATE TABLE AS SELECT

[DavidCaliman]

When i use the command below, a new table is generated, however the sas index file is not generated.

proc sql;

create table x (compress=char) as select * from y;

** y has a index file

Is there some command to create a table from another, generating the index file?

I have some tables that are becoming very large because of deletes operations.

SAS has not command to rebuild a table, so I need to do this recreation once a week automatically, and should not fail if you create a new field in the table, for example.

[snoopy369 · Accepted solution]

First off, you can just rebuild the index, of course.  If you're deleting significant numbers of records, you probably should do that anyway. 

Secondly, PROC COPY will copy indexes AND will remove empty space.  However, in addition to the warning that preserving indexes isn't great when a lot of rows are being removed, it also requires you to copy from one library to another.  Perhaps there's a way around that, I'm not sure.

I assume your concern here is that the index file itself may have been changed?  Otherwise this is fairly straightforward (ie, option 1).

-Joe

[Ksharp]

The following could work ?

proc sql;

create table x (compress=char index=xx) as select * from y;

[CTorres]

Yes, it works. See below:

proc sql noprint;

  create table class(compress=char index=(name)) as select * from sashelp.class;

quit;

CTorres

[DavidCaliman]

I used the following and it works:

proc copy in=lib1 out=lib2 CONSTRAINT=YES INDEX=YES;

select y;

run;