Delete SAS Dataset 3 days prior to today in work library

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Delete SAS Dataset 3 days prior to today in work library

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

 

 

 


Accepted Solutions
Solution
‎05-19-2018 10:35 AM
Respected Advisor
Posts: 4,736

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

Posted in reply to sameer112217

@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;

 

View solution in original post


All Replies
PROC Star
Posts: 1,804

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

[ Edited ]
Posted in reply to sameer112217

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;
Contributor
Posts: 58

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

Posted in reply to sameer112217
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
PROC Star
Posts: 8,164

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

Posted in reply to sameer112217

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

 

Solution
‎05-19-2018 10:35 AM
Respected Advisor
Posts: 4,736

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

Posted in reply to sameer112217

@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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 186 views
  • 5 likes
  • 4 in conversation