topic Re: Removing tables/data-sets based on a condition? in SAS Programming

Removing tables/data-sets based on a condition?

CharlotteCain — Tue, 13 Jan 2015 09:39:39 GMT

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.

Scenario1:

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)

Scenario2:

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.

Many thanks,

Charlotte

Re: Removing tables/data-sets based on a condition?

RW9 — Tue, 13 Jan 2015 10:00:42 GMT

Hi,

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.

%let start_date='01jan2015'd;
%let to_date='03jan2015'd;

data have1 have2 have3 have4 have5;
set sashelp.class;
run;

data tmp;
set sashelp.vtable (where=(libname="WORK" and substr(memname,1,4)="HAVE"));
remove_to=&to_date.-&start_date.;
if input(substr(memname,5),best.) >= remove_to then call execute('proc datasets library=work nolist nowarn; delete '||strip(memname)||'; quit;');
run;

Re: Removing tables/data-sets based on a condition?

Loko — Tue, 13 Jan 2015 10:43:50 GMT

Hello,

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;
set sashelp.class;
run;

%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
where not(missing(input(scan(memname,2,"_"),yymmdd8.)))
and input(scan(memname,2,"_"),yymmdd8.) lt %eval(&refdate) and
upcase(libname)=upcase("&lib");
quit;

%put &deltables;

proc datasets library=&lib nolist;
delete &deltables;
quit;

%mend delt;

/*example of calling the macro*/

%delt(work,"01JAN2014"d,5)

Re: Removing tables/data-sets based on a condition?

CharlotteCain — Tue, 13 Jan 2015 10:50:16 GMT

Hi RW,

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,

Charlotte

Re: Removing tables/data-sets based on a condition?

RW9 — Tue, 13 Jan 2015 11:05:18 GMT

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.