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
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;
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.