06-23-2014 10:49 AM
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
06-23-2014 11:08 AM
Upon what the process to decide 6 month of inactivity? One simple solution could be in-situ copy its own, such as:
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);
doesn't seem to work.
06-23-2014 11:05 PM
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);
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
06-24-2014 12:07 AM
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:
set MyTable (obs = 0);
As you suggest if you are doing many tables a macro is a good option.
06-24-2014 07:28 AM
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.
06-24-2014 07:56 AM
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.
06-24-2014 02:47 AM
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.
06-24-2014 08:03 AM
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
set have (obs=0);
proc append data=have1 base=have;
proc delete data=have1;
This has successfully changed the modification date (internally and externally though), and append does not rewrite the whole file.
06-24-2014 09:09 AM
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().
06-25-2014 02:41 AM
You find that in dictionary.tables and use the intnx function.
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 <;
07-01-2014 03:15 PM
Now, i just need to make an iteratively proc append to insert "ZERO" observation into each one table listed.
%do i = 1 %to 5;
x = 1;
create table total_have
cats(libname,'.',memname) as nm_tables, modate
where libname eq 'WORK' and modate gt intnx('month',date(),6) and memtype eq 'DATA';
/* 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*/
proc append base = &want. data = &want. (obs=0); run;
/*making the append for each table*/
set total_have end = eof;
Unfortunatelly its not working...
07-01-2014 03:28 PM
It looks like you could apply straightforward techniques to this problem. For a single data set:
proc append base=have data=zero;
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( ));
proc append data=zero base=&next_table;
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.
07-01-2014 05:11 PM
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...
07-01-2014 08:28 PM
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);
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;
set sashelp.vtable ;
and modate <= intnx('dtmonth',datetime(),-5)
file code ;
dsname = catx('.',libname,memname);
put 'proc append base=' dsname 'data=' dsname '(obs=0);run;' ;
%include code / source2 ;