Datasets deletion based on period in dataset name

Question

I have a library that have tables with name as "tablename_YYYYMM".I want to write a script which loops through all the folders on the disk and deletes tables older than 4 years. Table deletion should be done on the date implied by the filename (dataset name)and individual querying of the dataset is not required. the deleted dataset name and deletion time is stored. I have attached the tables structure. I have the below code but it didn't worked as expected. Image of table structure

PROC CONTENTS DATA=WORK._ALL_ OUT=contents NOPRINT; RUN;
 
 proc sort data=work.contents;
 by memname;
 run;
 
 DATA work.deleted;
 length libname $15. time 8;
 format time datetime20.;
 RETAIN libname memname time;
 SET contents(KEEP=LIBNAME MEMNAME) end=eof;
 by memname;
 where length(scan(memname, -1, '_')) = 6 and  input(scan(memname, -1, '_') || '01',?yymmdd8.) > intnx('year', today(), -10, 's');
 if first.memname;
    time = datetime();
 if _n_ = 1 then call execute('proc sql;');
 call execute(catx(' ', 'drop table', catx('.', libname, memname), ';'));
 if eof then do;
 call execute('quit;');
 
 
 end;
 
 RUN;

Answer 1

You're on the right track. I would do it slightly differently, see this approach.

*make fake data;
data demo_201501;
    set sashelp.class;

data random_201803;
    set sashelp.class;

data help_202004;
    set sashelp.class;
run;

*get list of tables;

data _table_list;
    set sashelp.vtable;
    where libname='WORK' and substr(memname, 1, 1) ne "_";
    cutoff_date=put(intnx('year', today(), -4, 'b'), yymmn6.);
    date_file=scan(memname, 2, "_");

    if date_file < cutoff_date;
    keep libname memname nobs cutoff_date date_file;
run;

*delete via proc datasets;
data _null_;
    set _table_list end=eof;

    if _n_=1 then
        do;
            call execute('proc datasets lib=work nolist nodetails;
delete');
        end;
    call execute(" "||memname||" ");

    if eof then
        call execute(";run;quit;");
run;

Regarding your code specifically, your WHERE condition is incorrect.

The INPUT function isn't correctly parsing the date and you're using 10 years not 4 as specified in your question. The comparison is also backwards, you're keeping files that are more recent rather than ones that are older.

From your code, this seems to work:

 DATA work.deleted;
 length libname $15. time 8;
 format time datetime20.;
 RETAIN libname memname time;
 SET contents(KEEP=LIBNAME MEMNAME) end=eof;
 by memname;

 
 where length(scan(memname, -1, '_')) = 6 and  input(scan(memname, -1, '_'),? yymmn6.) < intnx('year', today(), -4, 'b');
  if first.memname; 
    time = datetime();
 if _n_ = 1 then call execute('proc sql;');
 call execute(catx(' ', 'drop table', catx('.', libname, memname), ';'));
 if eof then do;
 call execute('quit;');
 
 
 end;
 
 RUN;

EDIT: conditionally doing this for different types of data using the IF. Basically makes sure the _TABLE_LIST data set has the list of tables you want to drop and you can modify the IF statement.

data _table_list;
    set sashelp.vtable;
    where libname='WORK' and substr(memname, 1, 1) ne "_";
    cutoff_date_data=put(intnx('year', today(), -4, 'b'), yymmn6.);
    cutoff_date_rest=put(intnx('year', today(), -4, 'b'), yymmn6.);
    date_file=scan(memname, 2, "_");

    if (scan(memname, 1, "_") = "DATA" and date_file < cutoff_date_data) 
    OR (scan(memname, 1, "_") ne "DATA" date_file < cutoff_date_rest);

    keep libname memname nobs cutoff_date date_file;
run;