topic Delete SAS Dataset 3 days prior to today in work library in SAS Programming

Delete SAS Dataset 3 days prior to today in work library

sameer112217 — Fri, 04 May 2018 19:09:57 GMT

Everyday a job runs and it creates a dataset with date extension in the library called final. For example date extension for the first record read this 2018 is the year, 05 is the month and 04 is the day which is yymmddn8. date format

Sample_20180504

Sample_20180503

Sample_20180502

Sample_20180501

Sample_20180430

Sample_20180429

Sample_20180428

I want to create a macro or code which will delete the tables or dataset in the final library which has file extension more than 3 days old. In the above example all the dataset older than 2nd May 2018 extension should be deleted if I run it today which is 4th May 2018

I tried the code but it did not work out

data _null_;
call symput('to_delete',catt('sample_',year(today())-0,
put(intnx('day',today(),-3), z2.)));
run;

Similarly you can put for month but it only deletes one record which is less than 3 that is Sample_20180501. I want to delete all that are older than 3 days old which is till 28th april 2018 record

Re: Delete SAS Dataset 3 days prior to today in work library

novinosrin — Fri, 04 May 2018 20:09:56 GMT

No need for macro, generate proc datasets statements using Call execute reading from sashelp,vtable

-->
if today()-3<=crdate<today() then call execute

Data _null_;
set sashelp.vtable end=last;
if _n_=1 then call execute ('proc datasets lib=work;');
if  today()-3<=datepart(crdate)<today()  then call execute ("delete "||' '||memname||";");
if last then call execute('quit;');
run;

Re: Delete SAS Dataset 3 days prior to today in work library

sameer112217 — Sat, 05 May 2018 02:56:25 GMT

Novinosrin, it will delete dataset which is 3 days older however my job is creating a dataset with an extension

Sample_20180504 . It is 04th may, 2018.The date part is an extension that needs attention for a particular dataset name and not creation date for every dataset

Re: Delete SAS Dataset 3 days prior to today in work library

art297 — Sat, 05 May 2018 14:03:36 GMT

I think that the following will do what you want:

data _null_;
  set sashelp.vtable (where=(libname eq 'WORK'));
  if _n_=1 then pattern_num = PRXPARSE("/_\d{8}$/");
  retain pattern_num;
  position=PRXMATCH(pattern_num,trim(memname));
  if position then do;
    if input(substr(memname,position+1),yymmdd8.) le today()-3 then do;
      forexec=catt('proc delete data=work.',memname,';run;');
      call execute(forexec);
    end;
  end;
run;

Art, CEO, AnalystFinder.com

Re: Delete SAS Dataset 3 days prior to today in work library

Patrick — Sun, 06 May 2018 02:08:41 GMT

@sameer112217

Here another coding option.

/*** create sample data ***/
libname final (work);
data _null_;
  dcl hash h1(dataset:'sashelp.class');
  h1.defineKey('name');
  h1.defineData(all:'y');
  h1.defineDone();
  set sashelp.class(obs=1);
  do date=today()-5 to today();
    h1.output(dataset:cats('final.sample_',put(date,yymmddn8.)));
  end;
run;

/*** delete ds with a date parte older today()-3 ***/
/* create list of ds to be deleted */
%let del_list=;
proc sql noprint;
  select memname into :del_list separated by ' '
  from dictionary.tables
  where libname='FINAL' and memname like 'SAMPLE^_%' escape '^'
    and input(scan(memname,-1,'_'),yymmdd8.)<today()-3
  ;
quit;

/* delete ds in list. */
/* use additional dummy ds name to avoid errors in case &del_list is empty */
proc datasets lib=final nolist nowarn;
  delete &del_list _dummy_123456789;
  run;
quit;