BookmarkSubscribeRSS Feed
CharlotteCain
Quartz | Level 8

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

CharlotteCain
Quartz | Level 8

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 myselfSmiley Happy

Sorry and Thanks for the patience,

Charlotte

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Loko
Barite | Level 11

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)

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 858 views
  • 6 likes
  • 3 in conversation