DATA Step, Macro, Functions and more

Deleting datasets older than 24 months

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Deleting datasets older than 24 months

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

 

 


Accepted Solutions
Solution
‎04-12-2017 07:57 PM
Valued Guide
Posts: 505

Re: Deleting datasets older than 24 months

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;

View solution in original post


All Replies
Super User
Posts: 17,750

Re: Deleting datasets older than 24 months

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. 

Contributor
Posts: 60

Re: Deleting datasets older than 24 months

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

Super User
Posts: 17,750

Re: Deleting datasets older than 24 months


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

PROC Star
Posts: 7,356

Re: Deleting datasets older than 24 months

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

 

Contributor
Posts: 60

Re: Deleting datasets older than 24 months

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.

 

Respected Advisor
Posts: 3,887

Re: Deleting datasets older than 24 months

@arunasaran

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.

Solution
‎04-12-2017 07:57 PM
Valued Guide
Posts: 505

Re: Deleting datasets older than 24 months

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;
PROC Star
Posts: 7,356

Re: Deleting datasets older than 24 months

[ Edited ]

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

 

Super User
Posts: 10,466

Re: Deleting datasets older than 24 months

<Pedantic modeSmiley Surprisedn> 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 modeSmiley Surprisedff>

 

Contributor
Posts: 60

Re: Deleting datasets older than 24 months

yes, my datasets are named in such a way.

 

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

Contributor
Posts: 60

Re: Deleting datasets older than 24 months

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

 

Super User
Posts: 17,750

Re: Deleting datasets older than 24 months

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?

 

 

 

Contributor
Posts: 60

Re: Deleting datasets older than 24 months

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

 

Super User
Posts: 17,750

Re: Deleting datasets older than 24 months

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. 

 

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 276 views
  • 4 likes
  • 6 in conversation