f



Re: Deleting SAS Data from a SAS DATASET #9

On Fri, 15 Aug 2008 16:09:46 -0500, Mary <mlhoward@AVALON.NET> wrote:

>A view helps on deletes, but I wonder how it affects performance of
querying the data- wouldn't storing the data in 24 different locations
cause a significant slowdown in perfomance upon querying the data versus
having it all in one table that is indexed?  If this data is queryied a
lot but only deleted once a month, the time in querying (which probably is
in peak time) could be much more important than the time in deleting
(which could be run when the computer is not busy, such as nights or
weekends).

hi,
good point! but as shown below, you can make a view of multiple datasets
having the separate indexes, and where seems to be fine with this.
interesting. ran on sas 9.1.3 sp4 on windows.
cheers,
chang

/* test datasets */
data one two;
    set sashelp.class;
    ds = "one";
    output one;
    ds = "two";
    output two;
run;

/* separately index on age */
proc datasets lib=work;
  modify one;
  index delete age;
  index create age;
  modify two;
  index delete age;
  index create age;
quit;

/* make a view of two datasets appended */
proc sql feedback;
  drop view work.oneTwo;
  create view oneTwo as
  select * from one union select * from two
  order by age;
quit;

/* where is utilizing the indexes */
options msglevel=i;
data sixteenAndOlder;
  set oneTwo; /* we are using the view */
  where age >= 16;
run;
/* on log
INFO: Index Age selected for WHERE clause optimization.
INFO: Index Age selected for WHERE clause optimization.
*/
0
chang_y_chung (1975)
8/15/2008 10:03:52 PM
comp.soft-sys.sas 142827 articles. 4 followers. Post Follow

0 Replies
844 Views

Similar Articles

[PageSpeed] 1

Reply: