topic CREATE TABLE AS SELECT in SAS Procedures

CREATE TABLE AS SELECT

DavidCaliman — Tue, 06 Jan 2015 18:31:41 GMT

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.

Re: CREATE TABLE AS SELECT

snoopy369 — Tue, 06 Jan 2015 19:07:11 GMT

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

Re: CREATE TABLE AS SELECT

Ksharp — Wed, 07 Jan 2015 05:51:04 GMT

The following could work ?

proc sql;

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

Re: CREATE TABLE AS SELECT

CTorres — Wed, 07 Jan 2015 16:18:30 GMT

Yes, it works. See below:

proc sql noprint;

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

quit;

CTorres

Re: CREATE TABLE AS SELECT

DavidCaliman — Wed, 07 Jan 2015 18:01:55 GMT

I used the following and it works:

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

select y;

run;