Sample 24660: Using an index for BY processing instead of sorting
 |  |  |  |  |  |
Create and use an index for BY-group processing when sorting is not possible or practical.
Note: If you have a single variable on the BY statement, use a simple index. If you have multiple variables in the BY-group, you can use a simple index on the first variable in the BY-group, or you can use a composite index built on the variables on the BY statement in the same order. A composite index does not have to contain all the variables in the BY-group, but at a minimum the first variable of the BY-group must be the same as the first variable in the index. Additional variables in the composite index should match the order of the variables in the BY-group for best performance. If there are multiple BY variables and the composite index does not include all of them, the unnamed variables must be in sorted order or the BY process will fail.
System performance will not be as good as using a sorted data set, but it will allow you to use a BY statement without having a sorted data set.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
/* Create a sample data set */
data one;
do asd=1 to 10;
qwe=ranuni(5432);
output;
end;
run;
/* Sort ONE on the random number QWE. This is to have the data */
/* NOT be in sort order based upon ASD. */ */
proc sort data=one out=two;
by qwe;
run;
title "Original Sorted Order";
proc print;
run;
/* Create an index on a non-sorted variable */
proc datasets library=work nolist;
modify two;
index create asd;
run;
quit;
/* SET the data set BY the index variable */
data three;
set two;
by asd;
run;
title "Reordered by Indexed Variable";
proc print;
run;
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
Original Sorted Order 14:27 Thursday, November 4, 2004 15 Obs asd qwe 1 4 0.05344 2 6 0.11456 3 5 0.14520 4 8 0.14944 5 7 0.46287 6 9 0.48115 7 2 0.52190 8 10 0.58951 9 1 0.71668 10 3 0.84563 Reordered by Indexed Variable 14:27 Thursday, November 4, 2004 16 Obs asd qwe 1 1 0.71668 2 2 0.52190 3 3 0.84563 4 4 0.05344 5 5 0.14520 6 6 0.11456 7 7 0.46287 8 8 0.14944 9 9 0.48115 10 10 0.58951
| Type: | Sample |
| Topic: | SAS Reference ==> DATA Step Data Management ==> Access ==> Indexes |
| Date Modified: | 2021-11-11 09:54:24 |
| Date Created: | 2004-09-30 14:09:02 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | n/a | n/a |