BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Augusto
Obsidian | Level 7

Hi people,

I've got a small question.

How to alter a column definition on SAS with no needs to create or overwrite the table

Like in SQL using alter table command?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Peter_C
Rhodochrosite | Level 12

Augusto

as you know, you are in a difficult position. You cannot increase the width for new values in $30 column, without rewriting the whole table, except....

If you "close" the old table and start writing new rows to a new table of the same structure, which has that column defined as $50.

When analysing, read both "old" and "new" through a view (just make sure that the wider definition is found first on the concatenation.

PROC SQL might provide the most "transparent" join of the tables (as it would allow an sql query optimiser to reach through an sql view, to any indexes defined for "old" and "new". This is not possible through a data step view).

hope this is useful

peterC    

View solution in original post

17 REPLIES 17
art297
Opal | Level 21

What are you trying to change?  In SAS, you have the alter table statement in proc sql (see: http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001395096.htm ), as well as proc datasets ( http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a000247753.htm )

Augusto
Obsidian | Level 7

Hi art

I would like to use the data step statement instead of proc sql.  To change the column size that has already been defined.

Linlin
Lapis Lazuli | Level 10

data have;

input dates;

informat dates mmddyy10.;

format dates date9.;

cards;

12/1/2011

1/1/2012

;

proc print;

title before change;

run;

proc datasets library=WORK nolist;

modify have;

format dates mmddyy10.;

proc print;

title after change;run;

Augusto
Obsidian | Level 7

Hi Linlin,

In fact, what i'm looking for is to change the definition, because a column in a table has  been defined as character length $30. but i need to change to $40. And i would like to know if is possible to use data step  instead of proc sql but without creating or overwriting tables?  Maybe using proc datasets but not sure.

if not, is ok!!!

QLi
Fluorite | Level 6 QLi
Fluorite | Level 6

Proc Datasets is hard to change column's length. But you can try data step by length statement;

Data want;

length var1 $ 40;

set have;

run;

Augusto
Obsidian | Level 7

I believe not be possible to change the column's length using data step without the needs of creating a new table or  overwrting the same table. am I sure?

Reeza
Super User

Yes, I think you're correct. It doesn't appear possible to change the length of a variable without recreating the table. Changing the table structure could change the data if you reduced the length so this makes some sense.

PROC SQL or PROC DATASETS can be used to change the format but not the length. I thought the modify statement within a datastep might work, but I couldn't get it to work either (though that doesn't mean it can't be).

You can use a data step and specify the length before you the SET statement as in QLi's code to change the length.

EDIT:

NOTE in SAS DOCs that say it is a fixed attribute and cannot be changed via proc datasets:

http://support.sas.com/documentation/cdl/en/basess/58133/HTML/default/viewer.htm#a001397456.htm

*Via PROC SQL;

data class;

    set sashelp.class;

run;

proc sql;

alter table work.class

    modify sex format=$5.;

quit;

proc contents data=class;

run;

*Via a datastep;

data class;

    length sex $5.;

    set class;

run;

proc contents data=class;

run;

Augusto
Obsidian | Level 7

Even with proc sql is not possible to change the length?

MikeZdeb
Rhodochrosite | Level 12

hi ... yes with SQL, but you still have to read/rewrite the data set

anything that modifies the data set content rather than just the header (attributes such as variable names, formats, labels) requires a rewrite

data x;

retain name "mike";

run;

title 'ORIGINAL';

proc contents data=x;

ods select variables;

run;

proc sql;

create table x as select name length=10 from x;

quit;

title 'MODIFIED';

proc contents data=x;

ods select variables;

run;

ORIGINAL

Alphabetic List of Variables and Attributes

#    Variable    Type    Len

1    name        Char      4

MODIFIED

Alphabetic List of Variables and Attributes

#    Variable    Type    Len

1    name        Char     10

Reeza
Super User

You can with PROC SQL. I think it just recreates the dataset though or at least read that somewhere.

data class;

    set sashelp.class;

run;

proc sql;

alter table work.class

    modify sex char(5);

quit;

proc contents data=class;

run;

Tom
Super User Tom
Super User

You cannot change the length of a variable without recreating the dataset.

But unless you want to modify the data there is no need to change the length so what is the issue about recreating the dataset?

Augusto
Obsidian | Level 7

For one reason.

There is a table with 250GB of space that has a column with length $30. It took a long time to be created, Now we realized that we need alter it to length $50 (there are some fields to increase the size and other to get small).  So I wouldn't like to recreate this table.

But  It seems that  there is no other way .

Reeza
Super User

If you change the length on the table the information in the fields won't change, you should be able to drop the old column and add a new one with the correct defn and update that. 

Not sure about the efficiencies of that but someone else may know.

Augusto
Obsidian | Level 7

Hi Reeza, I know that ("changing the length on the table the information in the fields won't change").

The length $50. will be used for new values, because the source that we get this information has new values with greater then length $30. Unfortunatelly we defined our column with length $30.

But Ok.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 17 replies
  • 15981 views
  • 7 likes
  • 9 in conversation