BookmarkSubscribeRSS Feed
gbrad
Calcite | Level 5

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;                          

10 REPLIES 10
SASKiwi
PROC Star

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

gbrad
Calcite | Level 5

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
PROC Star

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

gbradford
Calcite | Level 5

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__
Jade | Level 19

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
Calcite | Level 5

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__
Jade | Level 19

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
LinusH
Tourmaline | Level 20

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
data_null__
Jade | Level 19

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

gbradford
Calcite | Level 5

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?   

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 961 views
  • 0 likes
  • 5 in conversation