Increase the length of a variable

[foxrol94]

Hi,

I have many SAS dataset on SPDS server  with billions of records and i would like to increase the lenght of a variable.

I have a variable name PisteAudit with lenght 100 and i would like to increase to 240.

By using

proc sql;
	alter table lib.table_065
	modify PisteAudit char(240);
quit;

it takes several hours to complete. Is there an easier way to do this?

Regards

[Tom]

No.

Increasing the length of a variable in a dataset that is already that large is of no value.

Increasing the length will not make any difference until you make a NEW dataset that contains NEW information.

So just make the new dataset that you want.  You can then define the variable to have the right length before you populate it with values.

[Patrick]

I guess if you really need to increase the length of the variable in order to store new values without truncation then I "fear" what you're doing is how you need to do it.

Assuming there is also security and other stuff defined for your table I'm not so sure if just creating a new table is the way to go with SPDS.

@Ksharp Based on some of your past posts I believe you've got real-live experience with SPDS. Any advice?

[Kurt_Bremser]

Changing the structure of a dataset always requires a complete rewrite. Since you do this in place, both read and write operations happen on the same hardware, which slows the process down even further.

What is the reason for this? All you get is a lot of empty space anyway.

Are the datasets stored with the COMPRESS=YES option? If not, try this, as it will greatly reduce the write I/O, and subsequent reads of the datasets.

[AllanBowe]

There's definitely an easier way, surely not a _faster_ way though.

We wrote a macro to avoid the need for you to produce boilerplate:  https://core.sasjs.io/mp__updatevarlength_8sas.html

The nice thing about the macro is that it will drop and recreate constraints if it's a key variable (it will also avoid the rewrite if you choose the same length)