topic Re: Deleting datasets older than 24 months in SAS Programming

Deleting datasets older than 24 months

SAS_INFO — Tue, 11 Apr 2017 03:47:28 GMT

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

Re: Deleting datasets older than 24 months

Reeza — Tue, 11 Apr 2017 04:03:01 GMT

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.

Re: Deleting datasets older than 24 months

art297 — Tue, 11 Apr 2017 04:37:57 GMT

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

Re: Deleting datasets older than 24 months

SAS_INFO — Tue, 11 Apr 2017 05:25:09 GMT

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

Re: Deleting datasets older than 24 months

SAS_INFO — Tue, 11 Apr 2017 05:52:32 GMT

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.

Re: Deleting datasets older than 24 months

Patrick — Tue, 11 Apr 2017 06:20:46 GMT

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

Re: Deleting datasets older than 24 months

rogerjdeangelis — Tue, 11 Apr 2017 13:31:33 GMT

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;

Re: Deleting datasets older than 24 months

art297 — Tue, 11 Apr 2017 14:05:07 GMT

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

Re: Deleting datasets older than 24 months

ballardw — Tue, 11 Apr 2017 14:47:13 GMT

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

Re: Deleting datasets older than 24 months

SAS_INFO — Thu, 13 Apr 2017 06:26:37 GMT

yes, my datasets are named in such a way.

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

Re: Deleting datasets older than 24 months

SAS_INFO — Thu, 20 Apr 2017 00:25:52 GMT

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

Re: Deleting datasets older than 24 months

Reeza — Thu, 20 Apr 2017 03:31:28 GMT

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?

Re: Deleting datasets older than 24 months

Reeza — Thu, 20 Apr 2017 03:37:37 GMT

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

Re: Deleting datasets older than 24 months

SAS_INFO — Thu, 20 Apr 2017 03:42:22 GMT

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

Re: Deleting datasets older than 24 months

Reeza — Thu, 20 Apr 2017 03:50:36 GMT

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

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.