Making an update

[Augusto]

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

[Haikuo]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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 ;