Help using Base SAS procedures

Aging old obs off a sas dataset

Reply
New Contributor
Posts: 2

Aging old obs off a sas dataset

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;                          

Respected Advisor
Posts: 3,001

Re: Aging old obs off a sas dataset

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

New Contributor
Posts: 2

Re: Aging old obs off a sas dataset

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.

Respected Advisor
Posts: 3,001

Re: Aging old obs off a sas dataset

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

Occasional Contributor
Posts: 5

Re: Aging old obs off a sas dataset

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.

Respected Advisor
Posts: 3,773

Re: Aging old obs off a sas dataset

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.

Occasional Contributor
Posts: 5

Re: Aging old obs off a sas dataset

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. 

Respected Advisor
Posts: 3,773

Re: Aging old obs off a sas dataset

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;
    
3-10-2015 10-59-33 AM.png
Esteemed Advisor
Posts: 5,158

Re: Aging old obs off a sas dataset

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 never sleeps
Respected Advisor
Posts: 3,773

Re: Aging old obs off a sas dataset

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

Occasional Contributor
Posts: 5

Re: Aging old obs off a sas dataset

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?   

Ask a Question
Discussion stats
  • 10 replies
  • 239 views
  • 0 likes
  • 5 in conversation