Deleting 25th dataset in rolling 24 months

[dkcoop]

I'm trying to write a program that will be put on a schedule to run monthly.  It is a simple copy of data from another SQL server to SAS.  I only want 24 months of data in SAS even though the other server has 36 months of data.  The field date_nbr that is the suffix of the datasets is in the form 20211231.

Here's what I have so far:

proc sql noprint;
create table unique as
select distinct date_key from dkc.monthly_data /*this is data on sql server*/
order by date_key descending;
quit;

data unique;
set unique ;
id=_N_ ;
run;
/* we only want 24 months of data */
data _null_;
set unique ;
where id < 25;
call symput('N', put(id, 3.));
run;

%let i = 1;
%macro getdata (dsn);
%do i = 1 %to &N;
data _null_;
set unique ;
call symputx(cats("date_nbr"),put(intnx('month', today(),-&i, 'E'), yymmddn.));
run;

data saslib.ds_&date_nbr;
set &dsn (obs=10);
where date_key = &date_nbr;
%end;
%mend getdata;

%getdata(dkc.monthly_data )
run;

This works great but I need to delete the earliest dataset that will be hanging around from last month's monthly run so that I'm back to 24 months of data.  I'm not sure how to do that and would appreciate some help.

Thanks,

[PaigeMiller]

When you create table UNIQUE, you also know which are the last 24 and which are not in the last 24. So simply have SAS delete the data sets that are not in the last 24 at that point in time, using the values in UNIQUE that are not in the most recent 24. So you have a %GETDATA macro, you also need a %DELETEDATA macro. I'm also sure you can do this without macros using CALL EXECUTE, but if it is working with macros, great.

[Patrick]

Something along the line of below should work.

/* create sample data */
options dlcreatedir;
libname dkc "%sysfunc(pathname(work))/dkc";
data dkc.monthly_data;
  do iter=35 to 0 by -1;
    date_key=input(put(intnx('month',today(),-iter,'e'),yymmddn8.),16.);
    output;
  end;
run;

data work.ds_123 work.ds_20211215;
  some_var=5;
run;

/* &n_months: number of monthly tables to be maintained within SAS */
%let n_months=24;

/* create list of desired month keys based on current date */
data work.want_month_keys;
  do _i=1 to &n_months;
    date_key=input(put(intnx('month',today(),-_i+1,'e'),yymmddn8.),16.);
    output;
  end;
  drop _i;
run;

/* create monthly SAS tables (if not exist) */
%macro create_table(
  date_nbr,
  source_lib=dkc, 
  source_table=monthly_data, 
  target_lib=work, 
  target_tbl_root=ds_
  );
  %if %sysfunc(exist(&target_lib..&target_tbl_root._&date_nbr))=0 %then
    %do;
      proc append 
        base=&target_lib..&target_tbl_root.&date_nbr 
        data=&source_lib..&source_table /*(readbuff=10000)*/
        ;
        where date_key=&date_nbr;
      run;quit;
    %end;
%mend;

data _null_;
  set work.want_month_keys;
  call execute(cats('%create_table(',date_key,')'));
run;


/* delete old monthly tables - only keep the newest &n_months tables */
%let target_lib=work;
%let target_tbl_root=ds;
proc sql;
  create table tbls_in_sas as
  select libname, memname
  from dictionary.tables
  where 
    libname=%upcase("&target_lib") 
    and scan(memname,1,'_')=upcase("&target_tbl_root")
    and countw(memname,'_')=2
    and not missing( input(scan(memname,-1,'_'),yymmdd8.) )
    and input(scan(memname,-1,'_'),yymmdd8.) = intnx('month',input(scan(memname,-1,'_'),yymmdd8.),0,'e')
  order by memname desc
  ;
quit;

data _null_;
  set tbls_in_sas;
  if _n_>&n_months then
    do;
      call execute( cats('proc delete data=',libname,'.',memname,';run;quit;') );
    end;
run;

[Kurt_Bremser]

So you have a bunch of datasets with a name pattern of saslib.ds_YYYYMMDD, and need to delete all datsets before a given date 24 months back from the current date.

See this:

%let cutoff = %sysfunc(intnx(month,%sysfunc(today()),-24,s),yymmddn8.);

proc sql noprint,
select catx('.',libname,memname) into :datasets separated by " "
from dictionary.tables
where libname = 'SASLIB' and memname like 'DS_%' and substr(memname,4,8) lt "&cutoff.";
quit;

proc delete data=&datasets.;
run;

Untested.

[dkcoop]

I ended up learning a few things from all 3 answers so I thank you all very much.  I know this still needs some tweaking but here is what I ended up with:

data _null_;

call symput('to_delete',catt('saslib.ds_', put(intnx('month',today(),-25,'E'), yymmddn.)));

run;

proc delete data=&to_delete;

At some point I want to add in code so that the libname is a macro variable.  I'm thinking I should put some kind of "if exists" code in also.  And the idea of using the unique dataset to compare the datasets I want vs. the datasets I have is something I need to think about more.  Thanks so much.