How to update dataset and change the column's length

[Mike_Davis]

Hello all,

if I want upate tabel work.one for its column x and y as below,and want update their length,but there is error happend when I use length= statement in proc sql .

could anyone tell me how to change variable's length when using update in sql procedure?

Thanks!

Mike

data work.one;

length x y $5;

x='aaa';

y='bbb';

run;

proc sql noprint;

update work.one

set x= catx('|',x,'abcdefghijklmn') length x=$200

     ,y= catx('|',y"abcdefghilklmn") length y=$200

  ;

quit;

[shivas]

Hi,

Try this..hope it helps

proc sql;

create table want as

select catx('|',x,'abcdefghijklmn') as x length =  200,catx('|',y,"abcdefghilklmn") as y length = 200 from one;

  quit;

Thanks,

Shiva

[Doc_Duke]

you can't change the length in an update statement, you need to use a create statemen.  Update may change in place, create builds a new table.

[Ksharp]

As Doc@Duke said, you need another statement to change the length of columns.

data work.one;
length x y $5;
x='aaa';
y='bbb';
run;

proc sql noprint;
alter table work.one modify x char(200),y char(200);


update work.one
set x= catx('|',x,'abcdefghijklmn') 
     ,y= catx('|',y,"abcdefghilklmn")
  ;
quit;

Ksharp