BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Longimanus
Quartz | Level 8

Hi to y'all!  😊 

 

I have a table that resides on a SPD server.  I call it SPD_TABLE in this example.  It has 4 indexes.  3 simple ones and 1 composite that is unique. For the example I call the columns for this unique index  REC_RK and VALID_FROM.  This is the UNIQUE KEY.  I have about 80.000 rows in this table. All the REC_RK's are > 6000000.   So now I want to empty this table.  I write: 

 

proc sql;  delete from libforfun.spd_table where rec_rk ne 1 ;quit; 

 

This take hours if it ends at all (haven't had the patience) and write-locks the table!  What is it that the compiler cannot figure out? 🙄 

 

To avoid this I coded a simple work around: 

1. data bla_bla; set  libforfun.spd_table; where rec_rk eq 1; run;    /*result - an empty table */

2. data libforfun.spd_table; set bla_bla; run;    /*problem solved*/

 

But WHY is the other option not done in a jiffy ??? Where is the bug that I cannot see? 😕

 

Best regards,

Longimanus.  

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Deleting all of the observations from a existing dataset is NOT something you should be doing with SAS.

Why not just make a new dataset with zero observations?

data replace;
  set replace(obs=0);
run;

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

Deleting all of the observations from a existing dataset is NOT something you should be doing with SAS.

Why not just make a new dataset with zero observations?

data replace;
  set replace(obs=0);
run;
SASKiwi
PROC Star

My guess that for every row your SQL deletes, SAS is updating the index. That will definitely be slow. Try dropping indexes, then deleting rows.

Ksharp
Super User
I tested it under PC version sas , have no problem.But yours is under SPDServer ,totally different engine.
Maybe as SASKiwi said the problem is from index . try to drop index before delete.
Kurt_Bremser
Super User

Use your workaround, but don't forget to recreate the indexes (if they are needed anyway on SPD tables). DELETE will only mark the observations as deleted, but not free the space.

Run this for reference:

data cars;
set sashelp.cars;
do i = 1 to 10;
  output;
end;
run;

proc contents data=cars;
run;

proc sql;
delete from cars where origin ne "moon";
quit;

proc contents data=cars;
run;

You will see that the filesize does not change.

Patrick
Opal | Level 21

With SPDS did you consider TRUNCATE? That should be very fast.

Docu here.

 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 5 replies
  • 1061 views
  • 9 likes
  • 6 in conversation