DATA Step, Macro, Functions and more

delete datasets that are more than one week old

Reply
Super Contributor
Posts: 345

delete datasets that are more than one week old

I have generated many datasets under my library. I need to click one dataset, and then delete. it is too big, so it takes so much time to delete one by one. Can anybody share your code to delete all those datasets that are older than one week, or a certain period of time, say, a month, or a year?

 

Thanks.

Super User
Super User
Posts: 7,996

Re: delete datasets that are more than one week old

Go into your OS Explorer window - say Windows, right click on Start/Explorer.  Sort files by date by clicking date modified, select the files you want by click holding, or using shift.  Press delete.  Your OS will then confirm if you want to delete them.

 

Why are you creating lots of datasets over time and keeping them, that doesn't seem efficient.  Maybe a database or warehouse would be a better long term storage solution?

 

You can do this type of thing in programs as well - assuming you have x commands to system enabled, but its more complicated, can go wrong, and its not really worth the effort.

Super User
Posts: 11,343

Re: delete datasets that are more than one week old

This is one way:

proc sql;
   create table ToDelete as
   select memname
   from dictionary.tables
   where libname='MYLIB'
      AND CRDATE LE '01NOV10'd;
quit;

data _null_;
   set ToDelete end=last;
   if _n_ = 1 then do;
      call Execute("proc datasets library=MYLIB;");
      call execute("delete");
   end;
   call execute(memname);
   if last then do;
      call execute(";"); /* ends the delete clause*/
      call execute("run;quit;");
   end;
run;

I create a dataset of the files to delete. You provide the name of the libray where I put MYLIB. The libname is in uppercase.

 

I used the Creation date for comparison. You may want to use the modification date variable, MODATE instead. The separate set is made so that you can look at it before executing the Data _null_ and the call to Proc datasets to confirm that the listed tables are the ones you want to actually delete.

Super Contributor
Posts: 345

Re: delete datasets that are more than one week old

Thanks. I am using teradata, my lib is A123_LIB

there is no obs.

below is the error messasge:

0 observations read from the data set WORK.TODELETEE

 

proc sql;
 create table ToDeletee as
 select memname
 from dictionary.tables
 where libname="A123_LIB"
  and crdate le "10NOV16"d;
  quit;	

proc sql;
 create table ToDeletee as
 select memname
 from dictionary.tables
 where libname="A123_LIB"
  and crdate le "10NOV16"d;
  quit;




 data _null_;
   set ToDeletee end= last;
   if _n_=1 then do; 
     call Execute("proc datasets library=A123_LIB;");
	 call execute("delete");

	end;


	call execute(memname);
	if last then do;
	   call execute(";"); 
	   call execute("run;quit;");
	
Super User
Posts: 11,343

Re: delete datasets that are more than one week old

I submit for consideration that Teradata tables are not SAS data sets exactly. So you likely need to read the properties from Teradata not the SAS objects in Dictionary.tables. At which I cannot help as I have not access/experience with Teradata.

Super User
Super User
Posts: 7,996

Re: delete datasets that are more than one week old

So its a database.  I would contact your database support team.  Normally they have a policy in place on user's temporary areas.  Any other area on the database should not be used as a dumping ground, and there will be strict controls on what can or can't be done there.  This isn't really a SAS question, but a database one.

Ask a Question
Discussion stats
  • 5 replies
  • 206 views
  • 0 likes
  • 3 in conversation