This condition
if find(memname,'data','i') ge 1 then do;
Is also not the same as your requirement that the name start with DATA. This will also find datasets names XXDATA_YYYYYY. Is that ok? If you want DATA to be first part of the name, like DATAXX_YYYYYY then test if the location it is found is 1 only.
This statement is not needed.
if first.memname;
Since you used PROC SORT with NODUPKEY to reduce the data to one observation per MEMNAME already.
This statement is placed in the wrong location.
if _n_ = 1 then call execute('proc sql;');
If should be before the IF/THEN DO block. Otherwise it will not run when the first dataset is not one that meets that IF condition. The same goes for the
if eof then do;
call execute('quit;');
end;
That should be placed outside of the conditional blocks.
This statement makes no sense at all
call execute (catx(' ', 'where', 'input(scan(memname, -1, '_'),? yymmn6.) < intnx('year', today(), -7, 'b');'));
The data step that is running it is already the dataset that has the variable MEMNAME you appear to want to test. Why are you trying to generate some SQL code to run later? Why not just test the name of the dataset you are already seeing and use that to decide whether or not you need to generate the DROP statement?
I cannot tell if you want to delete dataset that have the YYYYMM stamp on their names that are more than 7 years old or more than 4 years old. Here is code to do it for those that are more than 7 years old.
DATA work.deleted;
SET work.contents(KEEP=LIBNAME MEMNAME) end=eof;
where length(scan(memname, -1, '_')) = 6;
where also find(memname,'data','i') ge 1;
where also not missing(input(scan(memname, -1, '_'),?yymmn6.));
where also input(scan(memname, -1, '_'),?yymmn6.) < intnx('year', today(), -7, 'b');
deletion_time = datetime();
format deletion_time datetime20.;
if _n_ = 1 then call execute('proc sql;');
call execute(catx(' ', 'drop table', catx('.', libname, memname), ';'));
if eof then call execute('quit;');
RUN;
So the where clause is filtering dataset names with:
- Six character suffix after an _
- Have the string DATA in them
- The six character suffix is a valid YYYYMM string
- The year of the suffix is more than seven years ago
Note your test using the INTNX() with the "B" option would be easier to implement if you only read the YEAR from the name.
where also input(scan(memname, -1, '_'),?4.) < year(today())-7 ;
