BookmarkSubscribeRSS Feed
Augusto
Obsidian | Level 7

Hello everyone,

There is a process that deletes tables over 6 months of inactivity. To solve this issue I need to update all tables from a libname (GPA) that are not modified over 6 months ago. The update can be just an append with "0" zero observation or other better way, because I only need to have those table updated. Someone can write a code to me? Tks

14 REPLIES 14
Haikuo
Onyx | Level 15

Upon what the process to decide 6 month of inactivity? One simple solution could be in-situ copy its own, such as:

data have;

set have;

run;

Then you will find the "modate" value is updated in dictionary.tables. Appending 0 records using proc append such as;
proc append base=have data=have(obs=0);

run;

doesn't seem to work.

Regards,

Haikuo

Augusto
Obsidian | Level 7

Hello Haikuo. Thanks but I need to update and not recreate them, because them can be very large, because of this i thought to use the proc append "0" with zero observation.

I thought something like this below.(I made comments where i had trouble);

1) list all tables from the GPA libname

proc sql noprint;

     select cats(libname,'.',memname) into: nm_tables separated by ',' from dictionary.tables

          where libname eq 'GPA'  and modate  less then 6 months ago using intck (i had trouble to compare the modate with datetime using the intck);

quit;

2) i need such a loop process to make a proc append with ZERO observation in each table to just update them. Consider that you can have as 1 as 1000 tables into this libname and also each one has your own layout.

Maybe could be solved using the Macro facility

SASKiwi
PROC Star

If I understand you correctly you want to remove all rows from tables that were last modified more than 6 months ago?

If so a simple and efficient way to remove all rows is this:

data MyTable;

  set MyTable (obs = 0);

run;

As you suggest if you are doing many tables a macro is a good option.

Augusto
Obsidian | Level 7

No SASKiwi,

To be more clearly. In my job we have a process that is executed by TI System that deletes (drop) old tables (tables that are not used, inactive, not modified) for more than 6 months. This process is executed everyday.

I have a process that use some historical tables from a specific libname (GPA).

I just need to update those tables, i don't want to recreate them, delete rows neither make inserts.. I thought to make an append (with "0" observation) because is faster and will changes nothing. Thus, I can have those tables updated.

I just need to know how to do that.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

So the question is, why are you trying to break the process?  Surely the TI (whatever that is) process is there for a reason, if that reason is no longer valid then remove or change it.  If you want to run outputs based on data, then that would be snapshotting, i.e. taking a copy of the data at that specific timepoint.  I don't know your industry, however at least in my role, if we run outputs then we need to be able to re-create those at any time in the future hence we take snapshots of the data at given timepoints.  It sounds like you have more of a process issue than a technical one.

Kurt_Bremser
Super User

So you just need to set the modification date of the files so that the system does not automatically archive/delete them?

Why make it so complicated, every OS has a utility to do that from the command line. On UNIX it is called touch, in Windows you use copy, as described here: http://superuser.com/questions/10426/windows-equivalent-of-the-linux-command-touch

So you only need to find the files that need touching, build the physical filenames and then do a call system with the correct command.

One nice sideeffect: the internally stored modification date that SAS shows in "Properties" is not affected.

Augusto
Obsidian | Level 7

KurtBremser the command Copy will make a copy of the file, so I do not want make a copy because I will not have suficiente disk space

Kurt_Bremser
Super User

You're right, that will also take unnecessary time.

Solution #1: Get rid of the microcrap and start working with a real operating system that has all the useful stuff built in. Not to be taken that seriously, but give it a thought.

Solution #2: Install a Windows version of the touch utility, usually from GNU

Solution #3:

data have1;

set have (obs=0);

run;

proc append data=have1 base=have;

run;

proc delete data=have1;

run;

This has successfully changed the modification date (internally and externally though), and append does not rewrite the whole file.

Augusto
Obsidian | Level 7

Thanks everyone for participating, i just didn't want to take time writing logical code, I though it could be simple, but not.

I'll be very grateful if someone just help me with this below.

1) List all tables from the "GPA" LIBNAME with modate less than 6 (Six months ago) - Please, consider the time to compare is always the actual time because the process will be run daily, thus use datetime or today().

Regards

Augusto

Kurt_Bremser
Super User

You find that in dictionary.tables and use the intnx function.

proc sql;

create table gpa as

select memname from dictionary.tables

where libname = 'GPA' and modate > intnx('month',date(),6); * or did you mean earlier than six months, then it is <;

quit;

Augusto
Obsidian | Level 7

Hello,

Now, i just need to make an iteratively proc append to insert "ZERO" observation into each one table listed.

%macro havetb;
%do i = 1 %to 5;
data have&i.;
x = 1;
run;
%end;
%mend;
%havetb;

proc sql;

create table total_have

as select

cats(libname,'.',memname) as nm_tables, modate

from dictionary.tables

where libname eq 'WORK' and modate gt intnx('month',date(),6) and memtype eq 'DATA';

quit;

/* I just need to fix the code below (its getting in looping....) - Its was just my logic, if someone has another way that works just post.*/

/*creates the macro*/

%macro append(want);
     proc append base = &want. data = &want. (obs=0); run;

%mend append;

/*making the append for each table*/

data _null_;

set total_have end = eof;

do until(eof);

     %append(nm_tables);

end;

run;

Unfortunatelly its not working...

Astounding
PROC Star

It looks like you could apply straightforward techniques to this problem.  For a single data set:

data zero;

stop;

set have;

run;

proc append base=have data=zero;

run;

If that works for a single table, it should be easy enough to use macro language to apply the same techniques to a list of table names:

%macro append_zero (nm_tables);

   %local i next_table;

   %do i = 1 %to %sysfunc(countw(&nm_tables));

        %let next_table = %scan(&nm_tables, &i, %str( ));

        data zero;

        stop;

        set &next_table;

        run;
        proc append data=zero base=&next_table;

        run;

   %end;

%mend append_zero;

The usual steps apply.  Get the program working for a single table, then apply macro language to repeat the program for a list of tables.

Good luck.

Augusto
Obsidian | Level 7

Hello Astounding thanks for your help... It will be an automatic process, in reality will be like this...

I real I have 100 or more tables from one libname (GPA), thus will need update (those with more than 6 months with no updates), thus, as I don't know how many table could contain, will be necessary  to use this macro for all tables available, you know? I mean, to make the macro language run reapeat automaticly until the last table.

"I want to create this process to run wherever I want with no changes".

So how to make this macro automaticly run (call) for all tables that could exist in one specific libname.

Obs. . sorry for my english...

Tom
Super User Tom
Super User

The PROC APPEND trick can be reduced to just appending zero observations from the dataset to itself.

proc append base=x data=x(obs=0);
run;

You can query the table DICTIONARY.TABLES ( or the view SASHELP.VTABLE) to check the MODATE variable to find those that have not been modified in a while and use that list to generate the code to "touch" them.  I am not sure if the MODATE in the SAS metadata is actual datetime value that your archive/delete utility is using, but it should be close enough.

Personally I find it easiest to do this using a data step to write the generated code to a text file that is later run via a %INCLUDE statement.  That way I can easily debug the step that generates the file until I get the syntax right.

Try something like this:

libname GPA 'path to GPA library';

filename code temp;


data _null_;

  set sashelp.vtable ;

  where libname='GPA'

    and modate <= intnx('dtmonth',datetime(),-5)

  ;

  file code ;

  dsname = catx('.',libname,memname);

  put 'proc append base=' dsname 'data=' dsname '(obs=0);run;' ;

run;

%include code / source2 ;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 14 replies
  • 1346 views
  • 6 likes
  • 7 in conversation