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
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;
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.
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
@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.
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
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.
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.
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;
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
<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>
yes, my datasets are named in such a way.
the code should delete the datset which are 24 months older(by name)
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
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?
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
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:
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: