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,

 

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
rogerjdeangelis
Barite | Level 11
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

14 REPLIES 14
Reeza
Super User

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

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
Super User

@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
Opal | Level 21

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

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
Opal | Level 21

@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
Barite | Level 11
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
Opal | Level 21

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
Super User

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

yes, my datasets are named in such a way.

 

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

SAS_INFO
Quartz | Level 8

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
Super User

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

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
Super User

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. 

 

 

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
  • 14 replies
  • 4363 views
  • 4 likes
  • 6 in conversation