topic To Alter a column definition in SAS Procedures

To Alter a column definition

Augusto — Thu, 05 Jan 2012 16:30:30 GMT

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

Re: To Alter a column definition

art297 — Thu, 05 Jan 2012 16:37:33 GMT

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 )

To Alter a column definition

Augusto — Thu, 05 Jan 2012 16:54:46 GMT

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.

To Alter a column definition

Linlin — Thu, 05 Jan 2012 17:01:40 GMT

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;

To Alter a column definition

Augusto — Thu, 05 Jan 2012 17:14:58 GMT

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!!!

To Alter a column definition

QLi — Thu, 05 Jan 2012 17:49:46 GMT

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;

To Alter a column definition

Augusto — Thu, 05 Jan 2012 17:56:58 GMT

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?

Re: To Alter a column definition

Reeza — Thu, 05 Jan 2012 18:02:42 GMT

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;

To Alter a column definition

Augusto — Thu, 05 Jan 2012 18:38:04 GMT

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

Re: To Alter a column definition

Tom — Thu, 05 Jan 2012 18:50:42 GMT

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?

Re: To Alter a column definition

MikeZdeb — Thu, 05 Jan 2012 18:57:03 GMT

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

Re: To Alter a column definition

Reeza — Thu, 05 Jan 2012 18:59:44 GMT

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;

To Alter a column definition

Augusto — Thu, 05 Jan 2012 19:25:53 GMT

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 .

To Alter a column definition

Reeza — Thu, 05 Jan 2012 19:36:47 GMT

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.

To Alter a column definition

Augusto — Thu, 05 Jan 2012 20:01:58 GMT

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.

Re: To Alter a column definition

Patrick — Thu, 05 Jan 2012 22:03:30 GMT

When re-creating the table make sure to use "options compress=yes;"

Network throughput and Disk I/O are very often the bottleneck and I've seen it more than once that using character compression for a table can speed up things quite a bit - especially if there are a lot of character variables with "empty space".

To Alter a column definition

Peter_C — Fri, 06 Jan 2012 18:01:08 GMT

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

To Alter a column definition

Augusto — Fri, 06 Jan 2012 22:14:21 GMT

Hi Peter. I hadn't thought about that. You are sure I still have a way out. This was very useful indeed.

Thanks all.