topic Re: Auto deleting old tables in SAS Programming

Auto deleting old tables

dincooo — Wed, 16 May 2018 12:50:04 GMT

Hi experts,

I have daily populated SAS tables under a library.

These tables are in the format like that: (date is in the end)

table_x20180516

table_x20180515

table_x20180514

.....

table_xy20180516

table_xy20180515

.....

I want to write a script that runs at the server and deletes today()-10 dated tables.

How can i write that kind of script.

Thank you very much,

Best Regards

Onur

Re: Auto deleting old tables

RW9 — Wed, 16 May 2018 13:08:05 GMT

I would ask your IT to setup a daily job to do this, rather than writing SAS code to do it.

But then I would have all that data in one dataset, with a variable date in the data, that way I only have one dataset containing my data, and then I really have a very simple life of filter out the data I want to remove by:

data want;
  set have;
  where file_date >= today()-10;
run;

One dataset, with the data in variables is so much easier to work with than splitting data up into multiple files, and having data items in filenames.

Re: Auto deleting old tables

dincooo — Wed, 16 May 2018 13:29:53 GMT

Thank you for your answer, but the thing is that these tables are populated by a batch code. I dont have the chance to edit these codes so I have to find a way to delete today()-10 date tables.

Re: Auto deleting old tables

jklaverstijn — Wed, 16 May 2018 16:08:27 GMT

If it has to be done in SAS code then that's possible. This would be a way to do it:

%let lib=MYLIB;
data tables;
	set sashelp.vtable(where=(libname="&lib"));
	if index(memname, '2018');
	memdate = substr(memname, length(memname)-8+1); /* Take last 8 positions as the date */
	if today() - input(memdate, yymmdd8.) > 10 then do; /* Older than 10 days */
		call execute ("proc delete data=&lib.." || trim(memname) || '; run;');		
	end;
run;

You would probably want to refine this (testing only the proper members are taken into account) but is is meant as a start.

Hope this help,

-- Jan