01-13-2015 04:39 AM
Hi, I have a need to clear or in other words delete tables from a library based on a condition. The condition is that some/all of the data-sets needs to be deleted from a library over a period of PREVIOUS 5 days from a given date..
Let's say i have a library named eg with datasets data1-data50. I want to delete all of it in certain cases and keep some of it in other cases.
To make it more precise for understanding, if the date is 12/25/2014, I'd need to delete 25th, 24th, 23rd,22nd and the 21st datasets(that is minus 5 days from 25th)
To keep the 25th datasets and delete only the previous 4 days 24th, 23rd,22nd and the 21st datasets.
I'd like it dynamic as there is a certain possibility of changing the condition in future, which is the number of days to delete currently being 5 to 3 or 8 or whatever number.
01-13-2015 05:00 AM
Well, below is working code which does what you ask. However I really wouldn't recommend you do this. Why would you want to have a table for each date? You lose a lot of functionality within SAS processing (by group processing), and you create complications for yourself. I would suggest creating a base table, with a column for date. Into this table one each day you append your data setting date to the date for that bit of data. You can then do by group processing on it, easily remove data if you want with an if statement in a datastep, keep audit trail on it etc.
data have1 have2 have3 have4 have5;
set sashelp.vtable (where=(libname="WORK" and substr(memname,1,4)="HAVE"));
if input(substr(memname,5),best.) >= remove_to then call execute('proc datasets library=work nolist nowarn; delete '||strip(memname)||'; quit;');
01-13-2015 05:50 AM
First of all, Thank you so much for your super quick response and help. I think I made a silly mistake in my question and I am sincerely sorry for that. Forgive me!.Basically, just like your example i have N number of datasets in a folder. As part of data retention exercise, I just want to only retain 3 or 4 days data from a given a date.
For example, for today, today- 3 days data is what i want to keep. And, for tomorrow, tomorrow-3 days data, and for the day after, day after tomorrow-3days data and it goes on incremental.
I have a feeling that I have explained better this time as I personally have understood the requirement better myself
Sorry and Thanks for the patience,
01-13-2015 06:05 AM
No need to remove then, just do:
have1, have2, have3 This is what we have day1.
New data received:
data have1; set have2; run;
data have2; set have3; run;
data have2; set new_date; run;
That way you only keep the latest 3. However I would still advise not going down this route.
01-13-2015 05:43 AM
This macro is a starting point. there are some notes in the log about the invalid argument of the INPUT function, but there will cause no harm.
/*prepare some data*/
data a_20131212 a_20131213 a_20131214 a_20140112;
%macro delt(lib, inpdate, daysbehinddel);
%let refdate=%sysfunc(intnx(day,&inpdate, %eval((-1)*&daysbehinddel)));
proc sql noprint;
select memname into :deltables separated by " " from dictionary.tables
and input(scan(memname,2,"_"),yymmdd8.) lt %eval(&refdate) and
proc datasets library=&lib nolist;
/*example of calling the macro*/