Code for Deleting 2 years old zip files from a folder using sas

[ambadi007]

Dear Experts,

Could you please give a macro code for deleting old files (2 year old ) from a folder that runs automatically in every year starting .

The code . I am using SAS EG to run my code .

Manesh

[Kurt_Bremser]

Do you have the creation date somehow coded into filenames, or must that be retrieved from the operating system?

[ambadi007]

Hi ,

The file name is like the below

SFRE_BIL_SIT_20160812_134317_PAM_FILES1.zip

in the file 20160812   is the year month and day of the file

[Kurt_Bremser]

So, let's assume you want to delete a file that is 2 years old today from a directory and has the date somewhere in the filename:

- store the directory name in a macro variable (this will help in later wrapping your code in a macro)

(everything from here in a data _null_ step)

- store the value of the macro variable in a data step variable

- use put(intnx('year',date,-2,'same'),yymmddn8.) to get a date string in your notation

- use the filename() function to assign a fileref with the directory name

- use the dopen() function to open the directory; assign the result to a data step variable

- use the dnum() function on that datastep variable to retrieve the number of members in the directory

- in a do loop from 1 to number of members, iterate through all the files: use the dread() function to retrieve the filename of a file

- if that file name matches your requirement (contains the date string created earlier), use the filename() function again to create a fileref for the zip file, and then use fdelete() to delete that file

- after the do loop, use fclose() to close the directory

[ambadi007]

Hi ,

Iam trying with the below code and I could able to delete the files which is in zip format but I need to know how we can filter to delete only the 2 year old files . The file name will be SFRE_BIL_SIT_20160812_134317_PAM_FILES1.zip

when doing the filtering for deleting the 2 year old fiIes I  need to concentrate only the year and month part for the file to do he deletion because the day part will be changing . Please find below the code

options mlogic;
    %macro delete_all_zip_files_in_folder(folder);
       filename filelist "&folder";
       data _null_;
          dir_id = dopen('filelist');
          total_members = dnum(dir_id);
          do i = 1 to total_members;  
             member_name = dread(dir_id,i);
             if scan(lowcase(member_name),2,'.')='zip' then do; 
              file_id = mopen(dir_id,member_name,'i',0);
              if file_id > 0 then do; 
                freadrc = fread(file_id);
                rc = fclose(file_id);
                rc = filename('delete',member_name,,,'filelist');
                rc = fdelete('delete');
             end;
             rc = fclose(file_id);
          end;
          end;
          rc = dclose(dir_id);
       run;
    %mend;


	%delete_all_zip_files_in_folder(C:\Users\UCS1MKP\Desktop\test)

Please give me a solution for this

thanks

[Ksharp]

It is more like OS Admin 's task.
Can you use X statement ? That is the most convenient way .


 %macro delete_all_zip_files_in_folder(folder);
       filename filelist "&folder";
       data _null_;
          dir_id = dopen('filelist');
          total_members = dnum(dir_id);
          do i = 1 to total_members;  
             member_name = dread(dir_id,i);
             if scan(lowcase(member_name),2,'.')='zip' then do; 


              ......if member_name like  'SFRE_BIL_SIT_20160812_134317_PAM_FILES1' then do;...............................
                rc = filename('delete',member_name,,,'filelist');
                rc = fdelete('delete');
              end;


             end;
             rc = fclose(file_id);
          end;

[ambadi007]

Hi Thanks for your reply .

But here I am getting exactly what i need to get with  the code I have provided except the deletion of the particular files which I need to focus on older than 2 years , The file name would like

'SFRE_BIL_SIT_20160812_134317_PAM_FILES1'

And here I need to extract only the Year part and delete if it today - 2 year . Only that I need to include the with the below macro

options mlogic;
    %macro delete_all_zip_files_in_folder(folder);
       filename filelist "&folder";
       data _null_;
          dir_id = dopen('filelist');
          total_members = dnum(dir_id);
          do i = 1 to total_members;  
             member_name = dread(dir_id,i);
             if scan(lowcase(member_name),2,'.')='zip' then do; 
              file_id = mopen(dir_id,member_name,'i',0);
              if file_id > 0 then do; 
                freadrc = fread(file_id);
                rc = fclose(file_id);
                rc = filename('delete',member_name,,,'filelist');
                rc = fdelete('delete');
             end;
             rc = fclose(file_id);
          end;
          end;
          rc = dclose(dir_id);
       run;
    %mend;


	%delete_all_zip_files_in_folder(C:\Users\UCS1MKP\Desktop\test)

[Kurt_Bremser]

The basic logic for the year would look like that:

options mlogic;
    %macro delete_all_zip_files_in_folder(folder);
       filename filelist "&folder";
       data _null_;
          cutoff_date = put(year(date())-2,4.);
          dir_id = dopen('filelist');
          total_members = dnum(dir_id);
          do i = 1 to total_members;  
             member_name = dread(dir_id,i);
             if scan(lowcase(member_name),2,'.')='zip'
             and index(scan(lowcase(member_name),1,'.'),cutoff_date) > 0
             then do; 
              file_id = mopen(dir_id,member_name,'i',0);
              if file_id > 0 then do; 
                freadrc = fread(file_id);
                rc = fclose(file_id);
                rc = filename('delete',member_name,,,'filelist');
                rc = fdelete('delete');
             end;
             rc = fclose(file_id);
          end;
          end;
          rc = dclose(dir_id);
       run;
    %mend;


	%delete_all_zip_files_in_folder(C:\Users\UCS1MKP\Desktop\test)

[ambadi007]

HI Experts ,

Regarding the file deletion I have used the below code but now worked . The file name is SFRE_BIL_SIT_20140822_134317_PAM_FILES1.zip

Here only I need to concentrate the year and month part to make it delete . Nut somehow it is not working.

options mlogic;
    %macro delete_all_zip_files_in_folder(folder);
       filename filelist "&folder";
       data _null_;
	      dir_id = dopen('filelist');
          total_members = dnum(dir_id);
          do i = 1 to total_members;  
           member_name = dread(dir_id,i);

		datestring = scan(member_name,4,'_');
		
		month = input(substr(datestring,5,2),best.);
		day = input(substr(datestring,7,2),best.);
		year = input(substr(datestring,1,4),best.);
		date = mdy(month, day, year);
			 if intnx('year', today(),-2) > date then do;
              file_id = mopen(dir_id,member_name,'i',0);
              if file_id > 0 then do; 
                freadrc = fread(file_id);
                rc = fclose(file_id);
                rc = filename('delete',member_name,,,'filelist');
                rc = fdelete('delete');
             end;
             rc = fclose(file_id);
          end;
          end;
          rc = dclose(dir_id);
       run;
    %mend;


	%delete_all_zip_files_in_folder(C:\Users\UCS1MKP\Desktop\test)

[Kurt_Bremser]

Define "not working".

Do you get an error, are files not found, .... ?

[ambadi007]

NO That was wondering there was no error and messages like that 🙂

[Kurt_Bremser]

And what does or does not happen?

[ambadi007 · Accepted solution]

Hi Kurt,

I have successfully deleted the files which are having 2 years old and the code I have made a small change I am attaching the code with this , it may be usefull if somebody got this kind of scenarios. Thank you once gain for the help and support for tackling this issue..

options mlogic;
    %macro delete_all_zip_files_in_folder(folder);
       filename filelist "&folder";
       data _null_;
	      dir_id = dopen('filelist');
          total_members = dnum(dir_id);
          do i = 1 to total_members;  
           member_name = dread(dir_id,i);

		datestring = scan(member_name,4,'_');
		
		month = input(substr(datestring,5,2),best.);
		day = input(substr(datestring,5,2),best.);
		year = input(substr(datestring,1,4),best.);
		date = mdy(month, day, year);
			 if intnx('year', today(),-2,'S') > date then do;
              file_id = mopen(dir_id,member_name,'i',0);
              if file_id > 0 then do; 
                freadrc = fread(file_id);
                rc = fclose(file_id);
                rc = filename('delete',member_name,,,'filelist');
                rc = fdelete('delete');
             end;
             rc = fclose(file_id);
          end;
          end;
          rc = dclose(dir_id);
       run;
    %mend;


	%delete_all_zip_files_in_folder(C:\Users\UCS1MKP\Desktop\test)

[RW9]

To be honest with you, file management is not the purpose of SAS (which is for processing and analyzing data).  This topic comes under system administration.  There should be a plan in place to remove older files, achive them if necessary etc.  Whilst you could write a program in SAS to do that, you could run into problems, say if the files are locked out, or your code fails or something else.  I would really advise you to just just build a procdure document (maybe with your IT group), that at a fixed timepoint file cleanups will happen.  It maybe those files can be moved out of direct access storage (i.e. you don't see them, but they don't get removed), and I am sure your IT group will already have some of this to hand.