Re: Deleting SAS Data from a SAS DATASET #4

Summary: PROC DATASETS; AGE statement. + VIEWs

This won't help you delete data from your very big data set, but you
may find this example interesting.

You say you append data monthly to a big data set then when big gets
too big you need to clean out the old.  And that takes a very long

However if you don't physically append but use a view to
append/combine you may find it easier to get rid of the unwanted old

Consider this code. it pushes MonthlyUpdate onto the stack of 24 data
sets and the 24th data set is deleted.  Then all the data sets get
renamed to produce a new group of 24.  You can see from the notes how
the operation works.  The data sets don't have to use a numbered range
M01-M24 I did that for my convenience.

proc datasets library=work;
   age MonthlyUpdate m01-m24;

NOTE: Deleting WORK.M24 (memtype=DATA).
NOTE: Aging the name WORK.M23 to WORK.M24 (memtype=DATA).
NOTE: Aging the name WORK.M22 to WORK.M23 (memtype=DATA).
NOTE: Aging the name WORK.M21 to WORK.M22 (memtype=DATA).
NOTE: Aging the name WORK.M20 to WORK.M21 (memtype=DATA).
NOTE: Aging the name WORK.M19 to WORK.M20 (memtype=DATA).
NOTE: Aging the name WORK.M18 to WORK.M19 (memtype=DATA).
NOTE: Aging the name WORK.M17 to WORK.M18 (memtype=DATA).
NOTE: Aging the name WORK.M16 to WORK.M17 (memtype=DATA).
NOTE: Aging the name WORK.M15 to WORK.M16 (memtype=DATA).
NOTE: Aging the name WORK.M14 to WORK.M15 (memtype=DATA).
NOTE: Aging the name WORK.M13 to WORK.M14 (memtype=DATA).
NOTE: Aging the name WORK.M12 to WORK.M13 (memtype=DATA).
NOTE: Aging the name WORK.M11 to WORK.M12 (memtype=DATA).
NOTE: Aging the name WORK.M10 to WORK.M11 (memtype=DATA).
NOTE: Aging the name WORK.M09 to WORK.M10 (memtype=DATA).
NOTE: Aging the name WORK.M08 to WORK.M09 (memtype=DATA).
NOTE: Aging the name WORK.M07 to WORK.M08 (memtype=DATA).
NOTE: Aging the name WORK.M06 to WORK.M07 (memtype=DATA).
NOTE: Aging the name WORK.M05 to WORK.M06 (memtype=DATA).
NOTE: Aging the name WORK.M04 to WORK.M05 (memtype=DATA).
NOTE: Aging the name WORK.M03 to WORK.M04 (memtype=DATA).
NOTE: Aging the name WORK.M02 to WORK.M03 (memtype=DATA).
NOTE: Aging the name WORK.M01 to WORK.M02 (memtype=DATA).
NOTE: Aging the name WORK.MONTHLYUPDATE to WORK.M01 (memtype=DATA).

Here is a complete example.  Somewhat contrived but I think is covers
most of the relevant points.

/* create example data base 2 years of monthly data */
filename makedata temp;
data _null_;
   file makedata;
   do m = 24 to 1 by -1;
      month = intnx('MONTH',today(),-m,'B');
      format month date9.;
      put 'data m' m z2. ';';
      put +3 'month="' month +(-1)'"d;';
      put +3 'do day = month to intnx("month",month,0,"E");';
      put +6    'output;';
      put +6    'end;';
      put +3 'Format month monyy. day date.;';
      put +3 'run;';
%inc makedata / source2;

/* macro var to make long list of member easier to manage */
%let members = ;
proc sql noprint;
   select memname into :members separated by ' '
      from dictionary.members
         where libname eq 'WORK' and memname eqt 'M';
%put NOTE: MEMBERS=&members;

/* a 2 year view */
data view24 / view=view24;
   set &members open=defer;

/* New data for montly update */
data MonthlyUpdate;
   month = intnx('MONTH',today(),0,'B');
   do day = month to intnx("Month",month,0,"E");
   format month monyy.;

proc datasets library=work;
   age MonthlyUpdate m01-m24;

/* use the view to access the past 24 months of data */
proc print data=view24;

On 8/15/08, SUBSCRIBE SAS-L Chandra Gadde <ddraj2015@gmail.com> wrote:
> Hi All
> I have several SAS datasets that are very very big. (50GB of size). Every
> month, the data is being appended to these datasets. I need to deleted the
> data which is greater than 24 months. What is the best method to do this?
> Please help me.
> I tried PROC SQS and DATA STEP. But these two are taking very long time.
> Data prod.Master_data;
>  set prod.master_date;
>  if snap_dt = "&end_dt"d then delete;
> run;
> proc sql;
> crete table prod.master_date from prod.master_date
> where snap_dt ne "&end_dt"d;
> quit;
iebupdte (1706)
8/15/2008 8:51:25 PM
comp.soft-sys.sas 142827 articles. 4 followers. Post Follow

0 Replies

Similar Articles

[PageSpeed] 0