topic Re: Indices - Need to Reapply to Dataset? in SAS Programming

Indices - Need to Reapply to Dataset?

shl007 — Mon, 07 Aug 2023 01:41:38 GMT

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?

Re: Indices - Need to Reapply to Dataset?

SASKiwi — Mon, 07 Aug 2023 02:53:24 GMT

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

Re: Indices - Need to Reapply to Dataset?

mkeintz — Mon, 07 Aug 2023 03:10:34 GMT

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.

Re: Indices - Need to Reapply to Dataset?

SASKiwi — Mon, 07 Aug 2023 03:35:39 GMT

@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.

Re: Indices - Need to Reapply to Dataset?

Patrick — Mon, 07 Aug 2023 03:42:52 GMT

@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?

Re: Indices - Need to Reapply to Dataset?

Tom — Mon, 07 Aug 2023 14:17:11 GMT

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;

Re: Indices - Need to Reapply to Dataset?

shl007 — Tue, 08 Aug 2023 21:15:51 GMT

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!

Re: Indices - Need to Reapply to Dataset?

shl007 — Tue, 08 Aug 2023 21:16:40 GMT

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