Deleting datasets older than 24 months

[SAS_INFO]

Hi All,

In a library , i have datasets for the past 24 months, i need to develop a piece of code that deletes the dataaset if it is older than 24 months.

for ex my datasets are named as follows

ab_2015jan

.

.

.

ab_2017jan

ab_2017feb

ab_2017mar

[Reeza]

1. Get list of all datasets in library. Use SASHELP.VTABLE

2. Extract date component to calculate date

3. Use proc SQL to store dataset names into macro variable filtering by date

4. Use PROC datasets to delete datasets. 

[SAS_INFO]

hi Reeza,

thank you for your reply.

I have a doubt in the 3rd step you mentioned.
I cannot filter by date as this process needs to be automated, so I cannot manually keyin the date in the filter, would you please share someother way

[Reeza]

---

@SAS_INFO wrote:

hi Reeza,

thank you for your reply.

I have a doubt in the 3rd step you mentioned.
I cannot filter by date as this process needs to be automated, so I cannot manually keyin the date in the filter, would you please share someother way

---

That part in bold is incorrect, you can use the macro variable TODAY() to get todays date. Then using the INTCK or INTNX you can then decide which dates are more than 24 months old. Use SCAN() and INPUT() to extract the date from the filename. 

[art297]

Here is one way:

data _null_;
  call symput('to_delete',catt('ab_',year(today())-2,put(today(), monname3.)));
run;

proc delete data=work.&to_delete.;
run;

Of course, you'll have to change 'work' to the name of your library.

Art, CEO, AnalystFinder.com

[SAS_INFO]

hi,

The step ,  year(today())-2 will delete all the datasets of 2015, but i need to do it month wise,

ex, after starting  april 2017, the code should delete march2015 dataset.

[Patrick]

@SAS_INFO

In a data step (or PROC SQL if you must) extract the date string from the table name, convert this date string to a SAS Date value and then use SAS calendarfunctions like INTNX() to determine which dates are older than two years based on todays date.

[rogerjdeangelis · Accepted solution]

Deleting datasets older than 24 months

inspired by this post
https://goo.gl/z3BvtY
https://communities.sas.com/t5/Base-SAS-Programming/Deleting-datasets-older-than-24-months/m-p/348963


HAVE
====

These SAS datasets in my work library

Member Name                             dte
-------------------------------------------
ONE                               11APR2017
POWER_ZIPCODES                    22APR2011 * greater than 24 months
SAMTF_FAC_4                       05APR2016
THREE                             11APR2017
TWO                               11APR2017
ZIP5ETHNIC                        24APR2011 * greater than 24 months


WANT (To delete SAS datsets greater then 24 months)
===================================================

Delete these from work library

Member Name                             dte
-------------------------------------------
POWER_ZIPCODES                    22APR2011 * greater than 24 months
ZIP5ETHNIC                        24APR2011 * greater than 24 months


WORKING CODE
============

        proc sql
          intck("month",datepart(crdate),today()) > 24
        Dosubl
          delete &dsns ;

FULL SOLUTIONS

*                _              _       _
 _ __ ___   __ _| | _____    __| | __ _| |_ __ _
| '_ ` _ \ / _` | |/ / _ \  / _` |/ _` | __/ _` |
| | | | | | (_| |   <  __/ | (_| | (_| | || (_| |
|_| |_| |_|\__,_|_|\_\___|  \__,_|\__,_|\__\__,_|

;


proc datasets lib=work kill;
run;quit;

data one two three;
  set sashelp.class;
run;quit;

* older daatsets;
x "cd d:\lup";
x "move power_zipcodes.sas7bdat E:\saswork\wrk\_TD3624_BEAST_";
x "move zip5ethnic.sas7bdat E:\saswork\wrk\_TD3624_BEAST_";
x cd c:\utl;

*          _       _   _
 ___  ___ | |_   _| |_(_) ___  _ __
/ __|/ _ \| | | | | __| |/ _ \| '_ \
\__ \ (_) | | |_| | |_| | (_) | | | |
|___/\___/|_|\__,_|\__|_|\___/|_| |_|

;

%symdel dsns rc sqlobs / nowarn;
data _null_;

 if _n_=0 then do;

    rc=%sysfunc(dosubl('
       proc sql;
          select
             memname
          into
             :dsns separated by " "
          from
             sashelp.vtable
          where
                libname="WORK"
            and  typemem="DATA"
            and  intck("month",datepart(crdate),today()) > 24
       ;quit;
    '));

  end;

  if (&sqlobs = 0) then do;
    put "** proc sql failed with  &sqlobs obs ** ";
    stop;
  end;

  rc=dosubl('
     proc datasets lib=work mt=data;
       delete &dsns ;
     run;quit;
  ');

  if rc ne 0 then do;
    put "** proc datsets failed **" rc=;
    stop;
  end;

 stop;

run;quit;

[art297]

You didn't look at the macro variable the code produced. If you ran it today it would try to delete just one file, namely:

one with the name: al_2015apr, namely 24 months ago. However, if you want it to delete the month prior to that, instead, use:

data _null_;
  call symput('to_delete',catt('ab_',year(today())-2,
   put(intnx('month',today(),-1), monname3.)));
run;

%put &to_delete.;

proc delete data=work.&to_delete.;
run;

Of course you don't need the %put statement there, I just included it so that you could see the name of the file that would be deleted.

Art, CEO, AnalystFinder.com

[ballardw]

<Pedantic mode:on> Your request is not to delete datasets older than 24 months, it is to delete data sets with a name element that is 24 months prior (assumed to today). Quite a different thing.

I can create a data TODAY that is named ab_2015jan. The data set would not be 24 months old.

<Pedantic mode:off>

[SAS_INFO]

yes, my datasets are named in such a way.

the code should delete the datset which are 24 months older(by name)

[SAS_INFO]

Hi all,

i need to develop a  global macro that checks nearly 12 libraries 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:

develoment

      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 and delete datasets only the Dataset folder .

the code should delete all the datasets whose name is 24 months older

[Reeza]

There was a solution provided that you marked as correct but it looks like you've added to the question. Can you clarify what the current situation is and where you're having issues?

[SAS_INFO]

its a new request added to my previous question.

waht i asked was to delete datasets within a library whose name is older than 24 months,

now i need to do the same across 13 folders

for ex:

this is my folder structure:

/data/DEV/&iterm/DATASET

the &iterm should get replaced with 13 folder names,

like crc,dbt,mer,online,tbt,yrc,lop,kyt,ded,fsw,fgt,kjh,mnb

i am trying to create the macro variable using iterative do loop :

%let name_list= crc,dbt,mer,online,tbt,yrc,lop,kyt,ded,fsw,fgt,kjh,mnb;

%macro aruna;

%local i next_name;

%let i=1;

%do %while (%scan(&name_list, &i) ne );

%let next_name = %scan(&name_list, &i);

%let i = %eval(&i + 1);

%end;

%mend aruna;

but the code is not working.

Once this is resolved, i need to develop another code which should be able to delete all datasets which name is 24 months old

[Reeza]

Change your process then, the steps I illustrated still apply. 

But for the first step, instead use a different way to get all the subfolders, the documentation covers this:

http://support.sas.com/documentation/cdl/en/mcrolref/69726/HTML/default/viewer.htm#n0js70lrkxo6uvn1f...

You could either use the data from the macro above to process each folder separately or change it to delete datasets directly. If you have X command this could be a single macro entirely, modifying the sample above.

1. Parse out date from file name/path

2. Determine if its more than 24 months

3. Execute SYSEXEC command with file path, if more than 24 months.