Date:         Fri, 6 Jan 2006 12:01:56 -0500
Reply-To:     "Rickards, Clinton (GE Consumer Finance)"
              <clinton.rickards@GE.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Rickards, Clinton (GE Consumer Finance)"
              <clinton.rickards@GE.COM>
Subject:      Re: Simultaneous append and prune a large data set
Comments: To: diskin@alum.rpi.edu
Content-Type: text/plain; charset="iso-8859-1"

Dennis,
 
Interesting thought. If I had a naming convention for the data sets, I could recreate the view with each daily job. Something like 
 

proc sql;
  create view lib.full as
  select * 
  from lib.day20060106, lib.day20060105, lib.day20060104, ... , lib.day20050706
 ;
quit;

 
The trick is getting the list of data sets, which is available in a dictionary table...
 
thanks!!!
 
Clint

-----Original Message-----
From: Dennis Diskin [mailto:ddiskin@gmail.com]
Sent: Friday, January 06, 2006 11:38 AM
To: Rickards, Clinton (GE Consumer Finance)
Cc: SAS-L@listserv.uga.edu
Subject: Re: Simultaneous append and prune a large data set


Hi Clint, 
 
The only alternative to this problem that I've seen (and it wasn't in SAS) is to keep each day's transactions in a separate dataset and just append them for processing, possibly with a view. This does preclude or complicate some types of access. 
 
HTH,
Dennis Diskin

 
On 1/6/06, Rickards, Clinton (GE Consumer Finance) < clinton.rickards@ge.com> wrote: 

All,

I think I know the answer to this but I'm hoping for someone can give me some alternatives.

I have a job I inherited which maintains a large data set (220+ GB). The goal is to keep a rolling 180 days of data. The job extracts the current day's data into a temp data set and PROC APPENDs that data to the full data set. A data step then rewrites the full data set, using a where clause to prune data more than 180 days old. Here is the essence of the program: 

       %let keepdate = "06jul2005"d;  <<<< recalculated daily...
       proc append base=lib.full data=work.daily;
       run;

       data lib.full;
         set lib.full (where=(tran_date < &keepdate)); 
       run;

The problem is that in the data step, SAS is making a second copy of lib.full, consuming all available disk space. We are working with our sys admins to clear space and to allocate more disk space.

My basic understanding has always been that there is no way to avoid making a copy of the full data set, either into WORK or into another library. Our work space averages 270-300GB free, big enough but close given other users on the system. I am considering modifying the program to look like this: 

       %let keepdate = "06jul2005"d;  <<<< recalculated daily...
       libname otherlib "in our saswork directory";
       data otherlib.days179;
         set lib.full (where=(tran_date < &keepdate)); 
       run;

       proc datasets lib=lib;
         delete full;
       run;

       data lib.full;
         set otherlib.days179
              work.daily ;
       run;

Any other alternatives or thoughts? 

TIA,

Clint