Hi,
as part of deletion routine. I want to delete the observations( older than 6 years) from all the tables from different libs. below code works but I am not sure how to use in all programmes. is there any easy method if we tweet a macro. it will automatically delete the observations (older than 6 years).
%macro test(application);
if &application ge intnx('year' ,today(),-6,'s');
%mend test;
data test;
set test1;
%test(date);
run;
thanks for the help.
SS
If you change the macro so that it contains all the code:
%macro reduce(libname,memname,testvar);
data &libname..&memname._red;
set &libname..&memname;
if &testvar ge intnx('year' ,today(),-6,'s');
run;
%mend test;
/* typical call: */
%reduce(work,test,date);
you can now call it automated from a dataset that contains the necessary values.
data _null_;
set control;
call execute('%reduce(' !! trim(libname) !! ',' !! trim(memname) !! ',' !! trim(varname) !! ');');
run;
If you change the macro so that it contains all the code:
%macro reduce(libname,memname,testvar);
data &libname..&memname._red;
set &libname..&memname;
if &testvar ge intnx('year' ,today(),-6,'s');
run;
%mend test;
/* typical call: */
%reduce(work,test,date);
you can now call it automated from a dataset that contains the necessary values.
data _null_;
set control;
call execute('%reduce(' !! trim(libname) !! ',' !! trim(memname) !! ',' !! trim(varname) !! ');');
run;
You can retrieve a list of datasets from dictionary.tables (in proc sql). With a suitable where condition, you will get all those you want to delete.
This can then be used to dynamically delete datasets:
data _null_;
set datasets_to_delete end=eof;
if _n_ = 1 then call execute('proc delete data=');
call execute(' ' !! trim(libname) !! '.' !! trim(memname));
if eof then call execute('; run;');
run;
Hi,
this is not working with your logic above or I am I wrong to apply what you said in this post
proc sql;
create table a as
select
memname
/* into*/
/* :dsns separated by " "*/
from
sashelp.vtable
where
libname="work"
and typemem="DATA"
and intck("month",datepart(crdate),today()) >1
;quit;
I guess you will always have a hard time finding datasets in your WORK that are older than a month, unless you have a single SAS session up permanently. Also keep in mind that libnames in dictionary tables are always uppercase.
Try this for starters:
proc sql;
create table a as
select
memname
from dictionary.tables
where
libname="SASUSER"
and typemem="DATA"
and intck("month",datepart(crdate),today()) > 1
;
quit;
sashelp.vtable is a view on dictionary.tables, and is usually slower to use than dictionary.tables, as there SQL can optimize the where condition.
you can now call it automated from a dataset that contains the necessary values.
data _null_;
set control;
call execute('%reduce(' !! trim(libname) !! ',' !! trim(memname) !! ',' !! trim(varname) !! ');');
run;
and also above logic is not working for me. What will control table have? one example please.
Thanks
@sathya66 wrote:
you can now call it automated from a dataset that contains the necessary values. data _null_; set control; call execute('%reduce(' !! trim(libname) !! ',' !! trim(memname) !! ',' !! trim(varname) !! ');'); run;
and also above logic is not working for me. What will control table have? one example please.
Thanks
It's a dataset that contains libname, memname and variable name for all datasets/variables that you want to have corrected.
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 25. 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.