DATA Step, Macro, Functions and more

macro in libname

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

macro in libname

 

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


Accepted Solutions
Solution
‎04-30-2017 09:34 PM
PROC Star
Posts: 1,760

Re: macro in libname

Posted in reply to arunasaran

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.

 

View solution in original post


All Replies
Valued Guide
Posts: 634

Re: macro in libname

Posted in reply to arunasaran

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.

PROC Star
Posts: 1,760

Re: macro in libname

[ Edited ]
Posted in reply to arunasaran

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.

Valued Guide
Posts: 634

Re: macro in libname

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.

Contributor
Posts: 63

Re: macro in libname

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

Contributor
Posts: 63

Re: macro in libname

Posted in reply to arunasaran
Also my folder has the following files
ab002_201501
ab003_201501
ab007_201501
3 different files with similar naming convention, so I need a code to delete the files older than 24 months from today irrespective of the prefix(ab002_or ab003_ or ab007)
Super User
Posts: 19,842

Re: macro in libname

Posted in reply to arunasaran

arunasaran 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. 

PROC Star
Posts: 1,760

Re: macro in libname

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

Re: macro in libname

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

Super User
Posts: 19,842

Re: macro in libname

Posted in reply to arunasaran

arunasaran 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?

Contributor
Posts: 63

Re: macro in libname

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.
Super User
Posts: 19,842

Re: macro in libname

Posted in reply to arunasaran

arunasaran 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. 

 

Contributor
Posts: 63

Re: macro in libname

nothing was incorrect in ur code, just a change I made
Contributor
Posts: 63

Re: macro in libname

Posted in reply to arunasaran
currently I have files only from 2015march
Super User
Posts: 19,842

Re: macro in libname

Posted in reply to arunasaran

So then you haven't tested it with January yet Smiley Happy

☑ This topic is solved.

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

Discussion stats
  • 23 replies
  • 398 views
  • 3 likes
  • 4 in conversation