topic Re: macro in libname in SAS Programming

macro in libname

SAS_INFO — Thu, 20 Apr 2017 02:35:17 GMT

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

Re: macro in libname

ArtC — Thu, 20 Apr 2017 05:09:26 GMT

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#n0js70lrkxo6uvn1fl4a5aafnlgt.htm. 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.

Re: macro in libname

ChrisNZ — Thu, 20 Apr 2017 05:29:42 GMT

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.

Re: macro in libname

ArtC — Thu, 20 Apr 2017 06:02:18 GMT

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.

Re: macro in libname

SAS_INFO — Thu, 20 Apr 2017 06:08:13 GMT

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

Re: macro in libname

SAS_INFO — Thu, 20 Apr 2017 06:50:03 GMT

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)

Re: macro in libname

Reeza — Thu, 20 Apr 2017 13:28:14 GMT

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

Re: macro in libname

ChrisNZ — Thu, 20 Apr 2017 23:36:15 GMT

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;

Re: macro in libname

SAS_INFO — Wed, 26 Apr 2017 00:31:05 GMT

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

Re: macro in libname

Reeza — Wed, 26 Apr 2017 00:42:08 GMT

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

Re: macro in libname

SAS_INFO — Wed, 26 Apr 2017 00:53:33 GMT

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.

Re: macro in libname

Reeza — Wed, 26 Apr 2017 00:56:04 GMT

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

Re: macro in libname

SAS_INFO — Wed, 26 Apr 2017 00:58:54 GMT

currently I have files only from 2015march

Re: macro in libname

Reeza — Wed, 26 Apr 2017 01:00:46 GMT

So then you haven't tested it with January yet 🙂

Re: macro in libname

SAS_INFO — Wed, 26 Apr 2017 02:36:01 GMT

NOPE 😞

Re: macro in libname

SAS_INFO — Wed, 26 Apr 2017 03:04:08 GMT

nothing was incorrect in ur code, just a change I made

Re: macro in libname

ChrisNZ — Wed, 26 Apr 2017 03:13:24 GMT

OLDEST_ALLOWED=put(intnx('month',today(),-25),yymmn6.);

Re: macro in libname

SAS_INFO — Thu, 27 Apr 2017 06:31:13 GMT

Hi Reez,

I tried your code,used libname concat, but it deleted only the first libname, location file, and rest dint get deleted.

libname A '/data/test1/a1/DATASET';

libname B '/data/test2/a2/DATASET';

libname C '/data/test3/a3/DATASET';

libname CONCAT (A B C );

%let table=;

data _null_;

OLDEST_ALLOWED='catt(year(today())-2,"_",month(today())-2)';

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;

Re: macro in libname

Reeza — Thu, 27 Apr 2017 12:00:13 GMT

I did say your oldest_allowed was incorrect. Use Chris' version.

Month returns a value between 1 and 12. If it's January your second term will edd up negative.

Re: macro in libname

SAS_INFO — Fri, 28 Apr 2017 04:44:04 GMT

hi Reeza,

I made the change in oldest_allowed. but still its not deleteing satasets.

Please Note, when I executed the code for the first time, it deleted the datasets.

libname A '/data/test1/a1/DATASET';
libname B '/data/test2/a2/DATASET';
libname C '/data/test3/a3/DATASET';

libname CONCAT (A B C );

%let table=;

data _null_;
OLDEST_ALLOWED=put(intnx('month',today(),-25),yymmn6.);

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;