Search code examples
sql-serversasdelete-rowsql-delete

Efficient way to delete specific rows of a SAS dataset on a SQL Server

I want to delete the latest 30 day records from a dataset. Then I have couple of ways to do so.

proc sql;
delete from server.data
where Date >= today() - 30;
quit;

OR

data server.data;
set server.data(where= (Date>= today() - 30));
run;

Which way is better? Or any faster procedures?

Solution

  • The first approach is faster.

    However this approach leaves the deleted observations in place (they are marked as deleted). This causes a difference between the NOBS and NLOBS properties of the table. So if space is a consideration, I'd recommend the second approach (using a macro variable constant in the same manner). This will rebuild the table without those records. Remember to recreate any indexes and constraints, which will be destroyed in the rebuild process..

    EDIT: I had previously suggested that the below would be even faster, however this turned out not to be the case - see Joe's comment in the thread..

    proc sql;
delete from server.data
where Date >= %eval(%sysfunc(today()) - 30);