topic Re: deleting observations older than 6 years from all the tables from different libs in SAS Programming

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

sathya66 — Fri, 23 Mar 2018 10:53:41 GMT

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.

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

Kurt_Bremser — Fri, 23 Mar 2018 12:40:13 GMT

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;

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

sathya66 — Fri, 23 Mar 2018 12:56:50 GMT

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

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

Kurt_Bremser — Fri, 23 Mar 2018 13:06:32 GMT

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;

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

sathya66 — Fri, 23 Mar 2018 13:48:23 GMT

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;

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

sathya66 — Fri, 23 Mar 2018 13:59:39 GMT

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

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

Kurt_Bremser — Fri, 23 Mar 2018 14:02:44 GMT

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.

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

Kurt_Bremser — Fri, 23 Mar 2018 14:12:08 GMT

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

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

sathya66 — Fri, 23 Mar 2018 14:18:32 GMT

Thank you. Now I am clear.