DATA Step, Macro, Functions and more

Auto deleting old tables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Auto deleting old tables

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

 

 


Accepted Solutions
Solution
‎05-17-2018 03:57 AM
Valued Guide
Posts: 540

Re: Auto deleting old tables

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

View solution in original post


All Replies
Super User
Super User
Posts: 9,840

Re: Auto deleting old tables

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.

Occasional Contributor
Posts: 17

Re: Auto deleting old tables

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.

Solution
‎05-17-2018 03:57 AM
Valued Guide
Posts: 540

Re: Auto deleting old tables

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 72 views
  • 0 likes
  • 3 in conversation