Delete obs efficiently

qkaiwei — Mon, 18 Mar 2013 12:40:09 GMT

I create a dataset named T with an index date,

data t(index=(date));

do i=1 to 1000000;

date=intnx('day','01jan2010'd,i);

x=1;y=2222222;z=33333;

output;

end;

format date yymmdd10.;

run;

The following two pieces of codes are to delete obs efficiently using indexes, but after submitting, I find the size of physicial file of table T don't change(perhaps called logically delete).

The reason why I use the following is that they do not create a new copy of the data set and delete indexes, save time of re-creating, but if logically delete, the table size will become bigger an bigger, and the IO time will increase rapidly.

How to balance?

data t;

modify t;

if date<='01feb3050'd then remove t;

run;

proc sql;

delete from t where date<='01feb3050'd;

quit;

Re: Delete obs efficiently

LinusH — Mon, 18 Mar 2013 12:59:44 GMT

Yes, the deletes as logical.

If you wish to save space, use the REUSE= data set option (only valid with COMPRESS=YES for some reason...?).

Be aware that the table can be fragmented and less efficient for querying.

topic Re: Delete obs efficiently in SAS Programming

Delete obs efficiently

Re: Delete obs efficiently