f



Re: Deleting SAS Data from a SAS DATASET #2

Chandra - this isn't my expertise area - I'm sure others may have better
ideas but I'll make three comments.

If your datasets have a large number of variables, in your datastep
method a WHERE clause will be more efficient - the IF statement causes
each record to be fully read before it can be selected for deletion. A
WHERE clause would only read the single variable in the case of records
that meet the criteria for deletion.

Data prod.Master_date;
  set prod.master_date;
  where snap_dt ne "&end_dt"d;
run;


You probably would be better off changing your data model though - my
first thought would to be to store your data as separate yearly or
monthly files and then access them with a view of the past two years or
24 months.  With each month or year you would delete the oldest file and
change the view one time period forward.

*set up the data;
data time1 time2 time3 time4;
     do i = 1 to 100;
          do t = 1 to 4;
               if t=1 then output time1;
               if t=2 then output time2;
               if t=3 then output time3;
               if t=4 then output time4;
          end;
     end;
run;

*three successive example views;
data filet1 / view=filet1;
    set time1 time2;
run;
data filet2 / view=filet2;
    set time2 time3;
run;
data filet3 / view=filet3;
    set time3 time4;
run;


One more thought - since the data are appended - if the file is not
reordered and you have the record counts from each append - then you can
use firstobs to only read starting with the recent time period.  I would
replace the append with a datastep that skips the appended data from 24
months prior and appends the new data in a single pass:

*set up the data;
data history time2 time3 time4;
     do i = 1 to 100;
          do t = 1 to 4;
               if t=1 then output history;
               if t=2 then output time2;
               if t=3 then output time3;
               if t=4 then output time4;
          end;
     end;
run;

proc append base=history data=time2;
run;

*two example single pass delete/appends;
data history;
do obsnum=101 to last;
   set history time3 point=obsnum nobs=last;
   output;
end;
stop;
run;

data history;
do obsnum=101 to last;
   set history time4 point=obsnum nobs=last;
   output;
end;
stop;
run;

You'll need to store prior record counts and then access them for the
obsnum starting position.

hth

Paul Choate
DDS Data Extraction
(916) 654-2160
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
SUBSCRIBE SAS-L Chandra Gadde
Sent: Friday, August 15, 2008 10:14 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Deleting SAS Data from a SAS DATASET

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;
0
pchoate (2538)
8/15/2008 6:30:16 PM
comp.soft-sys.sas 142827 articles. 4 followers. Post Follow

0 Replies
1208 Views

Similar Articles

[PageSpeed] 38

Reply: