topic Re: Deleting SAS Datasets older than 3 days. in SAS Enterprise Guide

Deleting SAS Datasets older than 3 days.

deleted_user — Wed, 03 Dec 2008 21:00:34 GMT

Hi,
I am writing a SAS program to delete the datasets older than 3 days

In my SAS library, I have files older than 5 days. So whenever I run my program, it has to check the dir to see if there are files older than 3 days. If so, delete the files.

Can somebody provide the SAS code inorder to do the above logic.

Thank you.

Re: Deleting SAS Datasets older than 3 days.

BrunoSilva — Wed, 03 Dec 2008 22:38:09 GMT

Hi,
I hope the above code helps you.
in the example o look for datasets in the libname SASUSER that have been created more than 3 days ago and delete them.

filename delcode temp;

data _null_;
file delcode;
set sashelp.vtable end=eof;
where libname eq 'SASUSER' and memtype eq 'DATA' and datepart(crdate)<(today()-5) ;

if _n_ eq 1 then do;
put 'proc datasets library=' libname ';';
put ' delete ';
end;

put memname;

if eof then do;
put '; run; quit;';
end;

run;

%include delcode;

Cheers,
Bruno Silva

Re: Deleting SAS Datasets older than 3 days.

yonib — Thu, 04 Dec 2008 10:05:56 GMT

You can try this example:
but notice: in filteer (where expresion) you can choose any libarary you wants!!!

proc sql;
create view temp as
select *
,datepart(crdate) as date
from dictionary.tables
where libname='WORK' and calculated date>intnx('day',today(),-3);
quit;

Re: Deleting SAS Datasets older than 3 days.

Peter_C — Thu, 04 Dec 2008 14:05:44 GMT

just to introduce another idea:
Although the datepart() function has achieved what was called for, it isn't really neccessary.
But that's OK here, because for such small data volumes, performance is not an issue.
However, where data base volumes are significant, for testing a datetime value against a date constant, just extend the constant with 0 time information. Rather than applying a datepart() function on every row, extending the constant happens only once. For example[pre]proc sql ;
create table vtable as select * from sashelp.vtable
where libname = 'WANTED'
& modate lt "%sysfunc( sum( '&sysdate'd, -3), date9):0:0:0"dt
;
quit ;[/pre]

PeterC

Re: Deleting SAS Datasets older than 3 days.

deleted_user — Thu, 04 Dec 2008 17:26:59 GMT

Thanks for your response.

Here is the code I am using as you suggested. But it is giving 0 rows even though the datasets are there in the directory.

proc sql ;
select * from sashelp.vtable
where libname='temp' & modate lt "%sysfunc( sum( '&sysdate'd, -1), date9):0:0:0"dt;
quit

Can you please suggest is something wrong in the code.

Re: Deleting SAS Datasets older than 3 days.

deleted_user — Fri, 05 Dec 2008 04:48:35 GMT

I am using SAS 9. I am not sure whether dictionary.tables && sashelp.vtable will work in SAS 9. Because when I hit the below queries, I am getting the 0 rows, even though the library are having files.

proc sql;
select memname from dictionary.tables where libname='temp';
quit;
run;

proc sql;
select memname from sashelp.vtable
where libname='temp';
quit;
run;

Is there any other option in SAS, where I can find out the 2 days older SAS datasets files and delete it. I was browsing many sites , but nothing worked so far.

Can somebody suggest on this.

Re: Deleting SAS Datasets older than 3 days.

Patrick — Fri, 05 Dec 2008 09:31:11 GMT

The values in the dictionary tables are upper case:....libname='TEMP'.... and it will work.

Re: Deleting SAS Datasets older than 3 days.

deleted_user — Sat, 06 Dec 2008 16:51:07 GMT

Thanks for your response.

After making it Uppercase, it is working fine. The query is giving the results based on the criteria.

Some times, the SAS library will not be having files to delete. In that case program should exit by giving mesg as no files in the log.
But the query I am using is failing.

%macro process_start;
% global oldfiles;
%let oldfiles=;
%mend process_start;

proc sql NOPRINT;
select memname into :oldfiles separated by ' '
from dictionary.tables where libname='TEMP' and today() - datepart(modate) > 2;
quit;
run;

if there are no records the oldfiles variable is not getting created. That is the reason the next steps are failing as below:

WARNING: Apparent symbolic reference OLDFILES not resolved.
ERROR 22-322: Expecting a name

can you tell me, if the query is returning zero records in that case how the oldfiles will keep the value 0. So that I can put IF condition.

Please suggest.

Re: Deleting SAS Datasets older than 3 days.

sbb — Sun, 07 Dec 2008 14:21:09 GMT

Your %GLOBAL has a blank as in "% GLOBAL".

Also consider checking &SQLOBS from the PROC SQL code (inside a macro using %IF), and you should only run your delete code when you have candidates to delete.

Scott Barry
SBBWorks, Inc.

Re: Deleting SAS Datasets older than 3 days.

Patrick — Tue, 09 Dec 2008 05:20:55 GMT

Hi FUN

Following this thread I suddenly remembered that I have something in my box which needed only a bit of change to cover your requirements.

Have a look at the code below.

I changed your 'modate' part in the clause to 'coalesce(modate,crdate)' as I saw in the dictionary table that there are entries with a blank modate.

HTH
Patrick

%macro CleanUpLib(CleanupLib=);
options mprint;
%local dropmems;

proc sql noprint nowarn;
select catx('.',libname,memname) into :dropmems separated by ','
from dictionary.tables
where libname =%upcase("&CleanupLib") and memtype="DATA"
and today()-datepart(coalesce(modate,crdate)) > 2;
%if &dropmems ne %then %do;
%put ************ Tables will be dropped ******;
%put Dropmems=&dropmems;
drop table &dropmems;
%end;
%else %do;
%put ************ No tables to be dropped ******;
%end;
quit;
run;
%mend;

%CleanUpLib(CleanupLib=MyLib) Message was edited by: Patrick