02-19-2015 12:28 PM
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.)) ;
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;
02-19-2015 04:04 PM
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
17597253 OBSERVATIONS ADDED.
THE DATA SET DELFIL.DSNSIZDT HAS 51563302 OBSERVATIONS AND 8 VARIABLES.
03-10-2015 10:07 AM
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.
03-10-2015 11:10 AM
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
03-10-2015 12:00 PM
OK I will create the example data and the example code.
03-10-2015 12:09 PM
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.
03-10-2015 01:35 PM
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?