DATA Step, Macro, Functions and more

deleting observations older than 6 years from all the tables from different libs

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

deleting observations older than 6 years from all the tables from different libs

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


Accepted Solutions
Solution
‎03-23-2018 09:17 AM
Super User
Posts: 9,550

Re: deleting observations older than 6 years from all the tables from different libs

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎03-23-2018 09:17 AM
Super User
Posts: 9,550

Re: deleting observations older than 6 years from all the tables from different libs

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 64

Re: deleting observations older than 6 years from all the tables from different libs

Posted in reply to KurtBremser
Hi ,
Thank you .it is working . This is for a history data for each table.
what about the history tables. need to delete the history tables also from the libraries.

I have tables like April2009_ext
April2010,etc (each table has date stamp on them) from different libs.
Thanks,
SS
Super User
Posts: 9,550

Re: deleting observations older than 6 years from all the tables from different libs

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 64

Re: deleting observations older than 6 years from all the tables from different libs

Posted in reply to KurtBremser

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;
Super User
Posts: 9,550

Re: deleting observations older than 6 years from all the tables from different libs

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 64

Re: deleting observations older than 6 years from all the tables from different libs

Posted in reply to KurtBremser
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

Super User
Posts: 9,550

Re: deleting observations older than 6 years from all the tables from different libs


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 64

Re: deleting observations older than 6 years from all the tables from different libs

Posted in reply to KurtBremser
Thank you. Now I am clear.
☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 130 views
  • 0 likes
  • 2 in conversation