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? 😕
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.