Help using Base SAS procedures

To Alter a column definition

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

To Alter a column definition

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


Accepted Solutions
Solution
‎01-06-2012 01:01 PM
Valued Guide
Posts: 2,175

To Alter a column definition

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


All Replies
PROC Star
Posts: 7,363

Re: To Alter a column definition

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 )

Frequent Contributor
Posts: 80

To Alter a column definition

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.

Super Contributor
Posts: 1,636

To Alter a column definition

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;

Frequent Contributor
Posts: 80

To Alter a column definition

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

Contributor QLi
Contributor
Posts: 57

To Alter a column definition

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;

Frequent Contributor
Posts: 80

To Alter a column definition

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?

Super User
Posts: 17,819

Re: To Alter a column definition

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;

Frequent Contributor
Posts: 80

To Alter a column definition

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

Valued Guide
Posts: 765

Re: To Alter a column definition

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

Super User
Posts: 17,819

Re: To Alter a column definition

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;

Super User
Super User
Posts: 6,500

Re: To Alter a column definition

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?

Frequent Contributor
Posts: 80

To Alter a column definition

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 .

Super User
Posts: 17,819

To Alter a column definition

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.

Frequent Contributor
Posts: 80

To Alter a column definition

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.

☑ This topic is SOLVED.

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

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