BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.
9 REPLIES 9
BrunoSilva
Quartz | Level 8
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
yonib
SAS Employee
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;
Peter_C
Rhodochrosite | Level 12
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
deleted_user
Not applicable
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.
deleted_user
Not applicable
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.
Patrick
Opal | Level 21
The values in the dictionary tables are upper case:....libname='TEMP'.... and it will work.
deleted_user
Not applicable
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Patrick
Opal | Level 21
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2459 views
  • 0 likes
  • 6 in conversation