Indices - Need to Reapply to Dataset?

[shl007]

When rows are appended to a dataset through proc append, I'm finding any indices formerly on that dataset need to be reapplied. Is this result correct/working by design?

[SASKiwi]

Whenever you append rows to a SAS dataset, indexes also need to be updated. It is by design.

[mkeintz]

Interesting.

According to the paragraph Updating an Indexed Data File in  Understanding SAS Indexes,

Each time that values in an indexed data file are added, modified, or deleted, SAS automatically updates the index. The following activities affect an index as indicated:

which doesn't mention PROC APPEND.  So I suspect the index is not automatically updated when using proc append, but only when the MODIFY statement is used in a DATA step to add/change/delete observations.

[SASKiwi · Accepted solution]

@mkeintz - From the link you provided:

Appending Data to an Indexed Data File

SAS provides performance improvements when appending a data file to an indexed data file. SAS suspends index updates until all observations are added, and then updates the index with data from the newly added observations. See the APPEND statement in the DATASETS procedure in Base SAS Procedures Guide.

This makes it clear indexes are updated once all rows are added (rather than doing it for each row added) which no doubt greatly improves performance.

[Patrick]

---

@shl007 wrote:

When rows are appended to a dataset through proc append, I'm finding any indices formerly on that dataset need to be reapplied. Is this result correct/working by design?

---

@shl007 According to the docu @SASKiwi refers to the indexes should get updated when using Proc Append. What makes you believe that this is not the case?

[shl007]

I had done some testing where I had thought the indices weren't sticking, but now I see the indices ARE sticking. Thanks for the clarification!

[Tom]

Are you using SAS datasets?  Or are you appending data into some external database like Oracle?

It should work fine for SAS datasets. Try this example:

data class(index=(age));
  set sashelp.class;
  where sex='M';
run;
proc append data=sashelp.class(where=(sex='F')) base=class;
run;

proc print data=class;
  by age;
run;

[shl007]

I'm doing it using SAS datasets. Thanks for the confirmation!