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;

Hi
You could use a 'modify' statement (data step). This statement does
the changes in place and needs therefore only have of the space, BUT:
there is a risk of corrupting your data; look it up in the manual.

I also think that some re-designing could solve some problems and
improve performance. I assume you're loading kind of a data mart with
a history of 24 months.

What you could do (just an idea):
- Create a SAS file per month (eg: prod.master_date&yyyymm)
- Create a SAS view for the most recent 24 SAS files (prod.master).
- Create an index over the date field.

Advantages:
- Loading of new data will be much faster (create new file with
current month, re-create view, delete SAS file with data older 24
months).
- As the SAS view has the same name like previously the SAS data file
your users won't even get that something changed (assuming they only
read the data).
- Queries using the index will perform much faster.
- SAS powerusers can also be told the new data organisation and they
can query the monthly files directly
- Smaller files are also much better to handle for backup & recovery
processes.

HTH
Patrick