BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sathya66
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

8 REPLIES 8
Kurt_Bremser
Super User

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;
sathya66
Barite | Level 11
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
Kurt_Bremser
Super User

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;
sathya66
Barite | Level 11

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

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.

sathya66
Barite | Level 11
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

Kurt_Bremser
Super User

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

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
  • 8 replies
  • 1220 views
  • 0 likes
  • 2 in conversation