BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SAS_INFO
Quartz | Level 8

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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

23 REPLIES 23
ArtC
Rhodochrosite | Level 12

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.

ChrisNZ
Tourmaline | Level 20

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.

ArtC
Rhodochrosite | Level 12

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.

SAS_INFO
Quartz | Level 8

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
Quartz | Level 8
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)
Reeza
Super User

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

ChrisNZ
Tourmaline | Level 20

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;
SAS_INFO
Quartz | Level 8

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

Reeza
Super User

@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
Quartz | Level 8
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.
Reeza
Super User

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

 

SAS_INFO
Quartz | Level 8
nothing was incorrect in ur code, just a change I made
SAS_INFO
Quartz | Level 8
currently I have files only from 2015march
Reeza
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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