11-21-2016 10:09 AM
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?
11-21-2016 10:19 AM
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.
11-21-2016 10:23 AM
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.
11-21-2016 10:54 AM
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;");
11-21-2016 11:38 AM
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.
11-21-2016 12:02 PM
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.