Help using Base SAS procedures

CREATE TABLE AS SELECT

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

CREATE TABLE AS SELECT

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.


Accepted Solutions
Solution
‎01-06-2015 02:07 PM
Regular Contributor
Posts: 244

Re: CREATE TABLE AS SELECT

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

View solution in original post


All Replies
Solution
‎01-06-2015 02:07 PM
Regular Contributor
Posts: 244

Re: CREATE TABLE AS SELECT

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

Super User
Posts: 9,682

Re: CREATE TABLE AS SELECT

The following could work ?

proc sql;

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

Regular Contributor
Posts: 180

Re: CREATE TABLE AS SELECT

Yes, it works. See below:

proc sql noprint;

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

quit;

CTorres

Contributor
Posts: 58

Re: CREATE TABLE AS SELECT

I used the following and it works:

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

select y;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 488 views
  • 3 likes
  • 4 in conversation