Aging old obs off a sas dataset

[gbrad]

On z/OS 2.1 runing SAS 9.3 I'm attempting to age or delete any obs that are greater than 30 days old off of a sasdb backup file. I've tried using both proc append & proc dataset to accomplish the task, with no positive results.  Here's the code using proc dataset. 

//SASDB     DD DISP=OLD,DSN=STRG.RMMRPT.DSNSIZDT.SASDB              

//BACKUP    DD DISP=OLD,DSN=STRG.RMMRPT.DSNSIZDT.SASDB.BACKUP       

//DELFIL   DD DISP=OLD,DSN=STRG.RMMRPT.DSNSIZDT.SASDB.BACKUP.DELFILE

//SASLIST DD SYSOUT=*                                               

//SYSIN    DD *                                                     

OPTIONS SOURCE SOURCE2;                                            

OPTIONS NOCENTER LS=250;                                           

PROC CONTENTS DATA=BACKUP.DSNSIZDT;                                

PROC CONTENTS DATA=DELFIL.DSNSIZDT;                                

DATA _NULL_ ;                                                      

    CALL SYMPUT ('DT',PUT(TODAY()-30,DATE9.)) ;                     

PROC DATASETS;                                                     

LIBNAME BACKUP 'STRG.RMMRPT.DSNSIZDT.SASDB.BACKUP' DISP=OLD;       

LIBNAME DELFIL 'STRG.RMMRPT.DSNSIZDT.SASDB.BACKUP.DELFILE' DISP=OLD;

APPEND BASE=DELFIL.DSNSIZDT DATA=BACKUP.DSNSIZDT ;

WHERE DATE GT "'&DT'"D;                          

[SASKiwi]

What does "no positive results" mean? What is your program actually doing compared with what you want it to do?

[gbrad]

Thanks for responding.  All the obs read from the backup are being appended to the delfil, instead of the just the ones that are older than 30 days. 

I'm trying to get the where date= clause to work.

THERE WERE 17597253 OBSERVATIONS READ FROM THE DATA SET BACKUP.DSNSIZDT

WHERE DATE>'20JAN2015'D;                                              

17597253 OBSERVATIONS ADDED.                                          

THE DATA SET DELFIL.DSNSIZDT HAS 51563302 OBSERVATIONS AND 8 VARIABLES.

[SASKiwi]

Try APPEND BASE=DELFIL.DSNSIZDT DATA=BACKUP.DSNSIZDT (where = (DATE>'20JAN2015'D));

[gbradford]

Thanks SASKiwi, after a couple of tries of using a symbolic to resolve the date, I got it to work using the syntax you suggested.

[data_null__]

To REMOVE observations from an existing data set you will need to use MODIFY or recreate the data set with the observations removed.

APPEND does not and cannot alter BASE.

Show example data of DELFIL and BACKUP  and I can show you how remove the old records from DELFIL while adding BACKUP.

[gbradford]

I've made some progress since the last post, but am still looking for a way to remove records from a sas db that will eventually get too large to manage.  The large db contains mf tape records from an RMM PDB, here's 1 record of the 72457465 obs in the db.  This record was brought down from the db using put_all to a seq file.

DATE=28JAN2015 RDDSNAME=C5552.D5552IM1.TS555210.DR01.G0183V00 RDDSSIZE=28K DSNAME=C5552.D5552IM1.TS555210.DR01.G0183V00 RVSTATUS=

PERIOD=6 HLQ=C5552 NEXT=D5552IM1.TS555210.DR01.G0183V00 _ERROR_=0 _N_=10255

Thanks. 

[data_null__]

OK I will create the example data and the example code.

data base;
   input date:date9.;
   format date date9.;
   cards;
01MAR1999
02MAR1999
10JAN2015
;;;;
   run;
data append;
   input date:date9.;
   format date date9.;
   cards;
01MAR2015
02MAR2015
10MAR2015
;;;;
   run; 
data base;
   modify base end=eof;
   if date lt '01jan2015'd then remove;
   if eof then do while(not eof2);
      set append end=eof2;
      output;
      end;
   run;
proc print data=base;
   run;
    

[LinusH]

Aging out Base SAS data comes with a cost - recreation of the data set.

The only SAS engine that that offers no resources in dropping old data is SPD Server (dynamic clusters).

External data bases such as DB2/UDB offers similar functionality like partitions.

What you can do if this is a pain is to split your data set by some data algorithm, and keep those data sets together with views on top. The drawback could be reduced query performance.

[data_null__]

I did not recreate BASE.  Recovery of the space left by REMOVEing obs is a different issue.

[gbradford]

Thanks for the response, I appreciate it.  Given that recovery of the space is a different issue, is another option as straight forward as to append the remaining data to a new re-allocated larger base?