BookmarkSubscribeRSS Feed
foxrol94
Fluorite | Level 6

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

4 REPLIES 4
Tom
Super User Tom
Super User

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
Opal | Level 21

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
Super User

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
Barite | Level 11

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)

/Allan
SAS Challenges - SASensei
MacroCore library for app developers
SAS networking events (BeLux, Germany, UK&I)

Data Workflows, Data Contracts, Data Lineage, Drag & drop excel EUCs to SAS 9 & Viya - Data Controller
DevOps and AppDev on SAS 9 / Viya / Base SAS - SASjs

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 718 views
  • 0 likes
  • 5 in conversation