Does SAS always create a NEW table when a column is added? (or just when the no. of columns change + or -)

Reply
Contributor
Posts: 26

Does SAS always create a NEW table when a column is added? (or just when the no. of columns change + or -)

Hi Guru's out there!

I am trying to add a new column to a HUGE table (Observations 1.374.040.744 - yes, the no of obs, not the size! ) with a proc sql and an 'alter table' statement. My program run 5o minutes and then:

ERROR: The disk is full or the quota has been exceeded.

WARNING: The path /opt/app/sas92/configdir/Lev2/SASApp/Data/bisdata/dds/dds_spd_d1 is full or

the quota has been exceeded

ERROR: The disk is full or the quota has been exceeded.

WARNING: The path /opt/app/sas92/configdir/Lev2/SASApp/Data/bisdata/dds/dds_spd_meta1 is full

or the quota has been exceeded

ERROR: dawritp: Write failed:

File=/opt/app/sas92/configdir/Lev2/SASApp/Data/bisdata/dds/dds_spd_meta1/$6060793.mdf.0.0.0.spds

9 Reason=The table or an index was not created because of insufficient disk space

errno=-2134906863

ERROR: Error writing data to member credit_card_transactions. Reason=The table or an index was

not created because of insufficient disk space

ERROR: The table or an index was not created because of insufficient disk space.

ERROR: The table DDS_SPD.CREDIT_CARD_TRANSACTIONS was not altered due to an error condition.

ERROR: hpoepla: plstop failed: Member=credit_card_transactions Reason=The table or an index was

not created because of insufficient disk space

So there it is. Is there a way where SAS not creates a new table (with the no. of variables) or it it time I have to persuade my manager to buy more disk space? (to be honest I have no idea how to check my quota - I have administator rights so I guess that's NOT the problem).

I hope you "guys" out there can help. Regards from sunny Copenhagen!

Super User
Posts: 9,681

Re: Does SAS always create a NEW table when a column is added? (or just when the no. of columns change + or -)

Did you try to use data step to add a variable ? SQL usually is executed in memory . while data step is based on disk storage .

Xia Keshan

Contributor
Posts: 26

Re: Does SAS always create a NEW table when a column is added? (or just when the no. of columns change + or -)

No I did not. Never tried it really. I look into that right away ....

Super User
Super User
Posts: 7,401

Re: Does SAS always create a NEW table when a column is added? (or just when the no. of columns change + or -)

When you execute code SAS writes temporary datasets to memory/disk as it processes internally.  What I would imagine you are coming up against is that, where behind the scenes SAS is processing the whole of the data and writing large temporary files.  Maybe consider splitting a table of that size up a bit using relational methodology.

Super User
Posts: 9,681

Re: Does SAS always create a NEW table when a column is added? (or just when the no. of columns change + or -)

Maybe you should try. Once I copy a table having 5,000,000,000 + obs from Oracle into SAS which cost me five whole days . you could use LS or DF command to check the change of disk storage, as long as you have enough disk I think you will get that new column at last.

Contributor
Posts: 26

Re: Does SAS always create a NEW table when a column is added? (or just when the no. of columns change + or -)

Hi Keshan! I'm back.

What concerns me is that when I use a datastep I'd also create a new table. Lets say I have a data FOR_FUN set with 3 vars: Var_1, Var_2 and Var_3. I'd like to add Var_4.

In a Data Step I would do the following:

FOR_FUN_NEW;

   SET FOR_FUN,

     lengt Var_4 8;

     Va4_4 = .;

run;

Then deleting the old table, renaming the new one (and the rest ... building of indexes, constraints, etc). I was certain there was som procedure other than proc sql but I didn't find any. How would YOU do this?

Super User
Posts: 9,681

Re: Does SAS always create a NEW table when a column is added? (or just when the no. of columns change + or -)

Hi. You can override the original dataset and keep index constraints ............. But you should make a copy of original dataset to avoid an error.

another proc ? I can get it to use proc append, but I think that is not efficient either.

Valued Guide
Posts: 3,208

Re: Does SAS always create a NEW table when a column is added? (or just when the no. of columns change + or -)

Hmm seen this question recently somewhere.
You need to think what is happening at the low level storage using the IO blocks. 
One of the first often is a header describing the record-layout. Within a RDBMS this is often done as a schema (logical)
As the data will be stored in the storageblock containing the records, every storage block is containing a number of records.
The Storage blocks are having an ordered number. Within a RDBMS this the physical part definition of a table.

The adding/changing of rows is easy with this, but adding a column will need an update to all storageblocks.
When a record doesn't fit at the original storageblock smart decisions must be made. A RDBMS coud use overflow area's to keep index-pointers untouched. 

--> With this common lay-out you will have to rewrite all data when adding/removing a column.     

The solution for changing a SPDS table would be offloading or copying it to an other location needing at least the same size of your current data.

Are there exceptions to this approach? Could be possible when the storage organization is not done as records but with columns. ALTER TABLE (vertica)

You will have another challenge as adding/changing rows will become problematic.

---->-- ja karman --<-----
Valued Guide
Posts: 3,208

Re: Does SAS always create a NEW table when a column is added? (or just when the no. of columns change + or -)

You will not find a way changing columns is needing a change at the basic storage concepts. Strange as you are not going to research that
Rows/records however can be changed (sas datasets) as you can use a transaction dataset with mod in place.
Indeed proc append will change datasets .... adding rows as that are the basic storage concepts.  Not suited for columns

---->-- ja karman --<-----
Contributor
Posts: 26

Re: Does SAS always create a NEW table when a column is added? (or just when the no. of columns change + or -)

Beste Jaap!

I see you're Dutch dus we zouden dit voort kunnen zetten in het Nederlands ... but then we would not pass your valubale tips to the rest of the World. We stick to English. You mention it's strange I am not going to research the basic storage concepts. Maybe so but I'm a programmer who has to meet many deadlines. I have a colleague waiting to test a program using the table in question so I was hoping for a quick (and maybe dirty) hint how to do this. For me the only difference between rows/records is something related to the platform I work on. I worked on the AS/400 for many years (Now iSeries or Power-i (or whatever IBM decides next) and there I always talked about records. Now it's rows or observations. I am not really sure if you think rows = records = observations or if you think they are different.

I think I will look at your advice to get the entire SPDS table to another location where I do have place for 2x the size of this table.  One for the copied table and one for the new one SAS created when I run the proc sql with the alter table statement ... Your advice in your second message might be the golden tip ("with mod in place") but I do not really know what that is. Obviously not thàt experienced ...

Valued Guide
Posts: 3,208

Re: Does SAS always create a NEW table when a column is added? (or just when the no. of columns change + or -)

Longimanus, I have some experience maintaining a RDBMS aside of the "System Programmer" s/360 MVS. I see rows record observations as all of the same kind.
Copying t another location you are needing just 1x of the same size with some spare. More is always better.

SAS(R) 9.3 Statements: Reference ( The modify dataset datastep approach). Lesser known option for SAS datasets.
remembering SAS/FSP and knowing SAS/Share is giving normal SQL behavior using ODBC access (OLTP) with alter/update records that update in place is possible. As the SPDS is designed for fast analytics and not as a OLTP dbms that record update in place will be probably missing, Never mind, changing a record-layout is the nasty process to solve. Unload/Load (Copy to another location and Replace back) is the common way for that.  

With SPDS you could load to a new production location. Testting that first, when ok switch your libname defs.

By that you are saving in the need for an additional copy/backup.
   

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 10 replies
  • 561 views
  • 3 likes
  • 4 in conversation