topic Increase the length of a variable in SAS Programming

Increase the length of a variable

foxrol94 — Wed, 29 Sep 2021 03:16:10 GMT

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

Re: Increase the length of a variable

Tom — Wed, 29 Sep 2021 04:43:31 GMT

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.

Re: Increase the length of a variable

Patrick — Wed, 29 Sep 2021 09:04:35 GMT

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?

Re: Increase the length of a variable

Kurt_Bremser — Wed, 29 Sep 2021 08:47:46 GMT

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.

Re: Increase the length of a variable

AllanBowe — Wed, 29 Sep 2021 14:10:49 GMT

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)