SAS Data Set Options |
Valid in: | DATA step and PROC steps |
Category: | Data Set Control |
Restriction: | Valid only for a DATA step view |
Syntax |
SPILL=YES | NO |
creates a spill file for non-sequential processing of a DATA step view. This is the default.
does not create a spill file or reduces the size of a spill file.
Details |
When a DATA step view is opened for non-sequential processing, a spill file is created by default. The spill file contains the observations that are generated by a DATA step view. Subsequent requests for data read the observations from the spill file rather than execute the DATA step view again. The spill file is a temporary file in the WORK library.
Non-sequential processing includes the following access methods, which are supported by several SAS statements and procedures. How the SPILL= data set option operates with each of the access methods is described below:
Examples |
This example creates a DATA step view that generates a large amount of random data and uses the UNIVARIATE procedure with a BY statement. The example illustrates the effects of SPILL= with a small number of large BY groups.
With SPILL=YES, all observations that are requested from the DATA step view are written to the spill file. With SPILL=NO, only the observations that are in the current BY group are written to the spill file. The information messages that are produced by this example show that the size of the spill file is reduced with SPILL=NO. However, the time to truncate the spill file for each BY group might add to the overall processing time for the DATA step view.
options msglevel=i; data vw_few_large / view=vw_few_large; drop i; do byval = 'Group A', 'Group B', 'Group C'; do i = 1 to 500000; r = ranuni(4); output; end; end; run; proc univariate data=vw_few_large (spill=yes) noprint; var r; by byval; run; proc univariate data=vw_few_large (spill=no) noprint; var r; by byval; run;
1 options msglevel=i; 2 data vw_few_large / view=vw_few_large; 3 drop i; 4 5 do byval = 'Group A', 'Group B', 'Group C'; 6 do i = 1 to 500000; 7 r = ranuni(4); 8 output; 9 end; 10 end; 11 run; NOTE: DATA STEP view saved on file WORK.VW_FEW_LARGE. NOTE: A stored DATA STEP view cannot run under a different operating system. NOTE: DATA statement used (Total process time): real time 21.57 seconds cpu time 1.31 seconds 12 proc univariate data=vw_few_large (spill=yes) noprint; INFO: View WORK.VW_FEW_LARGE open mode: BY-group rewind. 13 var r; 14 by byval; 15 run; INFO: View WORK.VW_FEW_LARGE opening spill file for output observations. INFO: View WORK.VW_FEW_LARGE deleting spill file. File size was 22506120 bytes. NOTE: View WORK.VW_FEW_LARGE.VIEW used (Total process time): real time 40.68 seconds cpu time 12.71 seconds NOTE: PROCEDURE UNIVARIATE used (Total process time): real time 57.63 seconds cpu time 13.12 seconds 16 17 proc univariate data=vw_few_large (spill=no) noprint; INFO: View WORK.VW_FEW_LARGE open mode: BY-group rewind. 18 var r; 19 by byval; 20 run; INFO: View WORK.VW_FEW_LARGE opening spill file for output observations. INFO: View WORK.VW_FEW_LARGE truncating spill file. File size was 7502040 bytes. NOTE: The above message was for the following by-group: byval=Group A INFO: View WORK.VW_FEW_LARGE truncating spill file. File size was 7534800 bytes. NOTE: The above message was for the following by-group: byval=Group B INFO: View WORK.VW_FEW_LARGE truncating spill file. File size was 7534800 bytes. NOTE: The above message was for the following by-group: byval=Group C INFO: View WORK.VW_FEW_LARGE deleting spill file. File size was 32760 bytes. NOTE: View WORK.VW_FEW_LARGE.VIEW used (Total process time): real time 11.03 seconds cpu time 10.95 seconds NOTE: PROCEDURE UNIVARIATE used (Total process time): real time 11.04 seconds cpu time 10.96 seconds
This example creates a DATA step view that generates a large amount of random data and uses the UNIVARIATE procedure with a BY statement. This example illustrates the effects of SPILL= with a large number of small BY groups.
With SPILL=YES, all observations that are requested from the DATA step view are written to the spill file. With SPILL=NO, only the observations that are in the current BY group are written to the spill file. The information messages that are produced by this example show that the size of the spill file is reduced with SPILL=NO, and with small BY groups, this results in a large disk space savings.
options msglevel=i; data vw_many_small / view=vw_many_small; drop i; do byval = 1 to 100000; do i = 1 to 5; r = ranuni(4); output; end; end; run; proc univariate data=vw_many_small (spill=yes) noprint; var r; by byval; run; proc univariate data=vw_many_small (spill=no) noprint; var r; by byval; run;
1 options msglevel=i;
2 data vw_many_small / view=vw_many_small;
3 drop i;
4
5 do byval = 1 to 100000;
6 do i = 1 to 5;
7 r = ranuni(4);
8 output;
9 end;
10 end;
11 run;
NOTE: DATA STEP view saved on file WORK.VW_MANY_SMALL.
NOTE: A stored DATA STEP view cannot run under a different operating system.
NOTE: DATA statement used (Total process time):
real time 0.56 seconds
cpu time 0.03 seconds
12 proc univariate data=vw_many_small (spill=yes) noprint;
INFO: View WORK.VW_MANY_SMALL open mode: BY-group rewind.
13 var r;
14 by byval;
15 run;
INFO: View WORK.VW_MANY_SMALL opening spill file for output observations.
INFO: View WORK.VW_MANY_SMALL deleting spill file. File size was 8024240 bytes.
NOTE: View WORK.VW_MANY_SMALL.VIEW used (Total process time):
real time 30.73 seconds
cpu time 29.59 seconds
NOTE: PROCEDURE UNIVARIATE used (Total process time):
real time 30.96 seconds
cpu time 29.68 seconds
16
17 proc univariate data=vw_many_small (spill=no) noprint;
INFO: View WORK.VW_MANY_SMALL open mode: BY-group rewind.
18 var r;
19 by byval;
20 run;
INFO: View WORK.VW_MANY_SMALL opening spill file for output observations.
INFO: View WORK.VW_MANY_SMALL truncating spill file. File size was 65504 bytes.
NOTE: The above message was for the following by-group:
byval=410
INFO: View WORK.VW_MANY_SMALL truncating spill file. File size was 65504 bytes.
NOTE: The above message was for the following by-group:
byval=819
INFO: View WORK.VW_MANY_SMALL truncating spill file. File size was 65504 bytes.
NOTE: The above message was for the following by-group:
byval=1229
.
. Deleted many INFO and NOTE messages for BY groups
.
INFO: View WORK.VW_MANY_SMALL truncating spill file. File size was 65504 bytes.
NOTE: The above message was for the following by-group:
byval=99894
INFO: View WORK.VW_MANY_SMALL deleting spill file. File size was 32752 bytes.
NOTE: View WORK.VW_MANY_SMALL.VIEW used (Total process time):
real time 29.43 seconds
cpu time 28.81 seconds
NOTE: PROCEDURE UNIVARIATE used (Total process time):
real time 29.43 seconds
cpu time 28.81 seconds
This example creates a DATA step view that generates a large amount of random data and uses the TRANSPOSE procedure. The example illustrates the effects of SPILL= with a procedure that requires two-pass access processing.
When PROC TRANSPOSE processes a DATA step view, the procedure must make two passes through the observations that the view generates. The first pass counts the number of observations and the second pass performs the transposition. With SPILL=YES, a spill file is created during the first pass, and the second pass reads the observations from the spill file. With SPILL=NO, a spill file is not created--after the first pass, the DATA step view is restarted.
Note that for the first TRANSPOSE procedure, which does not include the SPILL= data set option, even though a spill file is used by default, the informative message about the open mode is not displayed. This action occurs to reduce the amount of messages in the SAS log for users who are not using the SPILL= data set option.
options msglevel=i; data vw_transpose/view=vw_transpose; drop i j; array x[10000]; do i = 1 to 10; do j = 1 to dim(x); x[j] = ranuni(4); end; output; end; run; proc transpose data=vw_transpose out=transposed; run; proc transpose data=vw_transpose(spill=yes) out=transposed; run; proc transpose data=vw_transpose(spill=no) out=transposed; run;
1 options msglevel=i; 2 data vw_transpose/view=vw_transpose; 3 drop i j; 4 array x[10000]; 5 do i = 1 to 10; 6 do j = 1 to dim(x); 7 x[j] = ranuni(4); 8 end; 9 output; 10 end; 11 run; NOTE: DATA STEP view saved on file WORK.VW_TRANSPOSE. NOTE: A stored DATA STEP view cannot run under a different operating system. NOTE: DATA statement used (Total process time): real time 0.68 seconds cpu time 0.18 seconds 12 proc transpose data=vw_transpose out=transposed; 13 run; INFO: View WORK.VW_TRANSPOSE opening spill file for output observations. INFO: View WORK.VW_TRANSPOSE deleting spill file. File size was 880000 bytes. NOTE: View WORK.VW_TRANSPOSE.VIEW used (Total process time): real time 2.37 seconds cpu time 1.17 seconds NOTE: There were 10 observations read from the data set WORK.VW_TRANSPOSE. NOTE: The data set WORK.TRANSPOSED has 10000 observations and 11 variables. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 4.17 seconds cpu time 1.51 seconds 14 proc transpose data=vw_transpose (spill=yes) out=transposed; INFO: View WORK.VW_TRANSPOSE open mode: sequential. 15 run; INFO: View WORK.VW_TRANSPOSE reopen mode: two-pass. INFO: View WORK.VW_TRANSPOSE opening spill file for output observations. INFO: View WORK.VW_TRANSPOSE deleting spill file. File size was 880000 bytes. NOTE: View WORK.VW_TRANSPOSE.VIEW used (Total process time): real time 0.95 seconds cpu time 0.92 seconds NOTE: There were 10 observations read from the data set WORK.VW_TRANSPOSE. NOTE: The data set WORK.TRANSPOSED has 10000 observations and 11 variables. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 1.01 seconds cpu time 0.98 seconds 16 proc transpose data=vw_transpose (spill=no) out=transposed; INFO: View WORK.VW_TRANSPOSE open mode: sequential. 17 run; INFO: View WORK.VW_TRANSPOSE reopen mode: two-pass. INFO: View WORK.VW_TRANSPOSE restarting for another pass through the data. NOTE: View WORK.VW_TRANSPOSE.VIEW used (Total process time): real time 1.34 seconds cpu time 1.32 seconds NOTE: The View WORK.VW_TRANSPOSE was restarted 1 times. The following view statistics only apply to the last view restart. NOTE: There were 10 observations read from the data set WORK.VW_TRANSPOSE. NOTE: The data set WORK.TRANSPOSED has 10000 observations and 11 variables. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 1.42 seconds cpu time 1.40 seconds
See Also |
|
Copyright © 2011 by SAS Institute Inc., Cary, NC, USA. All rights reserved.