topic Re: Aging old obs off a sas dataset in SAS Procedures

Aging old obs off a sas dataset

gbrad — Thu, 19 Feb 2015 17:28:24 GMT

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;

Re: Aging old obs off a sas dataset

SASKiwi — Thu, 19 Feb 2015 18:34:08 GMT

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

Re: Aging old obs off a sas dataset

gbrad — Thu, 19 Feb 2015 21:04:34 GMT

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.

Re: Aging old obs off a sas dataset

SASKiwi — Thu, 19 Feb 2015 21:58:22 GMT

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

Re: Aging old obs off a sas dataset

gbradford — Tue, 10 Mar 2015 13:46:26 GMT

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.

Re: Aging old obs off a sas dataset

data_null__ — Tue, 10 Mar 2015 14:07:34 GMT

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.

Re: Aging old obs off a sas dataset

gbradford — Tue, 10 Mar 2015 15:10:57 GMT

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.

Re: Aging old obs off a sas dataset

data_null__ — Tue, 10 Mar 2015 16:00:52 GMT

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; 
     

Re: Aging old obs off a sas dataset

LinusH — Tue, 10 Mar 2015 16:09:43 GMT

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.

Re: Aging old obs off a sas dataset

data_null__ — Tue, 10 Mar 2015 16:15:35 GMT

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

Re: Aging old obs off a sas dataset

gbradford — Tue, 10 Mar 2015 17:35:53 GMT

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?