Hi all,
i need to develop a global macro that checks nearly 12 folders under the DEVELOPMENT folder and if it finds any dataset name older than 24 months (note: not create date, but the name of the dataset eg: ab002_2015apr, gh005_2015mar) it should delete
folder:
DEVELOPMENT
chq
1 abc
2 vbg
3 dataset
4 jil
mer
1 abc
2 vbg
3 dataset
4 jil
crc
1 abc
2 vbg
3 dataset
4 jil
under Development i have 13 folders and under each folder there are 4 sub folders , and i need to serch the DATASET folder which has SAS datasets stored and delete datasets nmes, which are older than 24 months
the code should delete all the datasets whose name is 24 months older
OK. I saw your second version, after
OLDEST_ALLOWED='catt(year(today())-2,"_",month(today())-2)';
Anyway you can run my latest program as is and see that it does what you want.
Modify it to suit your needs.
You need a macro that will recursively crawl through your directory (and subdirectories). You can find an example of such a macro in the 9.4 macro language reference manual http://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#n0js70lrkxo6uvn1f.... A somewhat more sophisticated version of this macro can be found in my SAS Global Forum paper http://support.sas.com/resources/papers/proceedings17/0835-2017.pdf.
It might be easier to concatenate all the paths into one libname and just scan this one library over and over until all matching tables are deleted.
Also, you really should consider using 201501 instead of 2015jan. This makes this kind of job easier, and automatically sorts the table list in a meaningful order in the SAS (or OS) explorer.
If possible using a concatenated libref as suggested by @ChrisNZ will simplify greatly and reduce the need for the recursive macro. Duplicate file names in subfolders could be problematic.
Assuming that you cannot change the naming convention of the files, unfortunately an all too common reality, the following macro will delete a file from a provided location (fileref).
%macro Fdel(fileref=, filename=);
%local dd yy mon filedate rc tref;
%let tref=temref;
%* assume file name is in the form of:
%* xxxdd_YYYYmon.sas7bdat;
%let dd = %substr(&filename,4,2);
%let yy = %substr(&filename,7,4);
%let mon = %substr(&filename,11,3);
%let filedate = %eval(%sysfunc(inputn(&mon&yy,monyy7.)) + &dd - 1);
%if %sysfunc(intnx(month,&filedate,24,s)) lt %sysfunc(date()) %then %do;
%let rc = %sysfunc(filename(tref,%sysfunc(pathname(&fileref))\&filename));
%let rc = %sysfunc(fdelete(temref));
%let rc = %sysfunc(filename(temref));
%end;
%mend fdel;
filename myloc 'c:\temp';
%fdel(fileref=myloc, filename= ab002_2015apr.sas7bdat)
%fdel(fileref=myloc, filename= gh005_2015mar.sas7bdat)
%fdel(fileref=myloc, filename= gh015_2016nov.sas7bdat)
I have made some assumptions about your file naming conventions - adjust as needed.
hi,
Unfortunately i annot concatenate all paths in to one file name, as all the folders are different channels of business.
But i can cahange the naming convention of the datasets, to ab002_201501 instead of ab002_2015jan
@SAS_INFO wrote:
hi,
Unfortunately i annot concatenate all paths in to one file name, as all the folders are different channels of business.
But i can cahange the naming convention of the datasets, to ab002_201501 instead of ab002_2015jan
This us not a request to combine the directory.
A SAS library can point to multiple folders, so if the subfolder names are consistent a single libname could reference all folders at once. Then you can revert to the original solution.
Like this?
libname A 'path1';
libname B 'path2';
libname C 'path3';
libname D 'path4';
libname CONCAT (A B C D);
%let table=;
data _null_;
OLDEST_ALLOWED='201503';
do until(symget('table')=' ');
RC=dosubl( 'data _null_;'
||'call symputx("table"," ");'
||'set SASHELP.VSTABLE; '
||'where LIBNAME="CONCAT" and '
||'trim(MEMNAME) like "%20____" and '
||'substr(MEMNAME, length(MEMNAME)-5,6) <' ||quote(OLDEST_ALLOWED)
||';call symputx("table",MEMNAME); stop; run;');
if lengthn(symget('table')) then
CR=dosubl( 'proc delete data=CONCAT.'||symget('table')||';run;');
end;
run;
Hi ,
Instead of passing the oldest allowed parameter , i have made a slight modification , as below.
Please correctme if am wrong.
Oldest_allowed=catt(year(today())-2,"_",month(today())-2);
@SAS_INFO wrote:
Hi ,
Instead of passing the oldest allowed parameter , i have made a slight modification , as below.
Please correctme if am wrong.
Oldest_allowed=catt(year(today())-2,"_",month(today())-2);
You've made a mistake. What if it's January?
@SAS_INFO wrote:
Ideally the code needs to delete the file which is 24 months older and to be in safer side we are deleting any dataset older than 25 months.
so the code runs every month and delete the files which are 25 months older.
What does that have to do with the code you proposed and what I said was incorrect? Try running your code with a date in January and examine the output yourself.
So then you haven't tested it with January yet 🙂
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.