Parallel Processing versus Multithreading

Parallel processing broadly represents processes that run simultaneously across multiple threads, cores, or processors. Multithreading is a specific type of parallel processing in which multiple threads are executed simultaneously to perform discrete tasks concurrently or divide and conquer a single task. Base SAS includes multithreading capabilities in several procedures to maximize performance and resource utilization. The “Support for Parallel Processing” chapter of SAS 9.4 Language Reference Concepts: Third Edition, lists the following SAS procedures as supporting multithreading, starting with SAS versions 9:²

• SORT
• SQL
• MEANS
• REPORT
• SUMMARY
• TABULATE

SAS practitioners have limited control over threading on these multithreaded procedures by specifying the THREADS system option, by specifying the THREADS procedure option, or by increasing the value of the CPUCOUNT system option. SAS practitioners unfortunately cannot harness multithreading when building their own SAS software, unlike object-oriented programming (OOP) languages. Notwithstanding this limitation of Base SAS software, SAS practitioners can benefit by understanding how multithreading increases performance and by replicating aspects of these techniques using parallel processing.

For example, when the SORT procedure runs with multithreading enabled, SAS divides the initial data set into numerous smaller data sets, sorts these in parallel, and finally joins the sorted subsets into a composite, sorted data set. Figure 9.1 depicts the modest performance improvement that the SORT procedure achieves through multithreading on two CPUs. On systems that were tested, increasing CPUCOUNT beyond two CPUs failed to increase performance further.

Multithreading facilitates the optimization of threads by the SAS application by evenly distributing threads across processors. Moreover, because only one instance of the SAS application is running, system resource overhead is minimized. Notwithstanding, SAS practitioners can improve performance of the SORT procedure by running multiple procedures in parallel. For example, the following code simulates parallel processing of the SORT procedure for the PERM.Mydata data set, which has 1,000 observations:

* first sort module to run in parallel;
proc sort data=perm.mydata (firstobs=1 obs=500) out=sort1;
   by num1;
run;
* second sort module to run in parallel;
proc sort data=perm.mydata (firstobs=501 obs=1000) out=sort2;
   by num1;
run;
data sorted;
   set sort1 sort2;
   by num1;
run;

An initial process (not shown) would determine how many parallel processes to run (based on file size or observation count), after which two (or more) SAS sessions could be spawned with the SYSTASK statement to perform the sorting. After the batch jobs had completed, a final DATA step in the original SAS program would rejoin the two sorted halves for a faster solution than the native SORT procedure.

Two primary methods exist for implementating parallel processing in SAS software. The first, as described previously, uses an engine (AKA controller or driver) to spawn multiple SAS sessions to execute external code in parallel. For example, the values for FIRSTOBS, OBS, and the output data set name for each SORT procedure would be dynamically encoded so that external code (not shown) could be called with SYSTASK and executed. The second method (demonstrated in the following section) uses software that is actually designed to have two or more instances run simultaneously so that they can work together to accomplish some shared task (like sorting) faster.

Distributed SORT-SORT-MERGE

The SORT-SORT-MERGE represents the common task of joining two data sets by some unique key or keys. The process can be accomplished using the SQL procedure or hash object but is often performed by two successive SORT procedures followed by a DATA step that joins the two data sets:

proc sort data=temp1;
   by num1;
run;
proc sort data=temp2;
   by num1;
run;
data sorted;
   merge temp1 temp2;
   by num1;
run;

This code creates a false dependency, described in the “False Dependencies of Process” section in chapter 7, “Execution Efficiency,” because the sorts are not dependent on each other yet are performed in series rather than parallel. Especially where the two data sets are equivalent in size and take roughly the same amount of time to sort, the speed of the SORT-SORT-MERGE can be increased by performing the two sorts in parallel. To coordinate this, SAS software should be written that is dynamically capable of sorting either Temp1 or Temp2 based on whichever data set has not been sorted. Thus, when two instances of the software are executed simultaneously, one will sort Temp1 and the other—detecting that Temp1 has already commenced its sort—will sort Temp2. When both SORT procedures complete, one of the software instances will perform the merge while the other patiently waits or exits. A similar technique is demonstrated in the “Executing Software in Parallel” section in chapter 7, “Execution Efficiency,” to perform independent MEANS and FREQ procedures in parallel.

The following code initializes the PERM.Data1 and PERM.Data2 data sets—each of which contains 100 million observations—that will be individually sorted and subsequently merged:

libname perm 'C:perm';
data perm.data1 (drop=i);
   length num1 8 char1 $10;
   do i=1 to 100000000;
      num1=round(rand('uniform')*1000);
      char1='data1';
      output;
      end;
run;
data perm.data2 (drop=i);
   length num1 8 char2 $10;
   do i=1 to 100000000;
      num1=round(rand('uniform')*1000);
      char2='data2';
      output;
      end;
run;

The following code should be saved as C:permdistributed.sas. The program can be run in series in a single SAS session, which will perform the typical SORT-SORT-MERGE in succession. For faster performance, however, the software can be opened in two different SAS sessions and run simultaneously. The two SORT procedures will perform in parallel, after which one of the sessions will merge the two data sets into the composite PERM.Merged:

libname perm 'C:perm';
%include 'C:permlockitdown.sas';
%macro control_create();
%if %length(&sysparm)>0 %then %do;
   data _null_;
      call sleep(&sysparm,1);
   run;
   %end;
%if %sysfunc(exist(perm.control))=0 %then %do;
   %put CONTROL_CREATE;
   data perm.control;
      length process $20 start 8 stop 8 jobid 8;
      format process $20. start datetime17. stop datetime17. jobid 8.;
   run;
   %end;
%mend;
%macro control_update(process=, start=, stop=);
data perm.control;
   set perm.control end=eof;
%if %length(&stop)=0 %then %do;
   output;
   if eof then do;
   process="&process";
   start=&start;
   stop=.;
   jobid=&SYSJOBID;
   output;
   end;
   %end;
%else %do;
   if process="&process" and start=&start then stop=&stop;
   %end;
   if missing(process) then delete;
   run;
&lockclr;
%mend;
%macro sort(dsn=);
%let start=%sysfunc(datetime());
%control_update(process=SORT &dsn, start=&start);
proc sort data=&dsn;
   by num1;
run;
%lockitdown(lockfile=perm.control, sec=1, max=5, type=W, canbemissing=N);
%control_update(process=SORT &dsn, start=&start, stop=%sysfunc(datetime()));
%mend;
%macro merge();
%let start=%sysfunc(datetime());
%control_update(process=MERGE, start=&start);
data perm.merged;
   merge perm.data1 perm.data2;
   by num1;
run;
%lockitdown(lockfile=perm.control, sec=1, max=5, type=W, canbemissing=N);
%control_update(process=MERGE, start=&start, stop=%sysfunc(datetime()));
%mend;
%macro engine();
%let sort1=;
%let sort2=;
%let merge=;
%control_create();
%do %until(&sort1=COMPLETE and &sort2=COMPLETE and &merge=COMPLETE);
   %lockitdown(lockfile=perm.control, sec=1, max=5, type=W,    canbemissing=N);
   data control_temp;
      set perm.control;
      if process='SORT PERM.DATA1' and not missing(start) then do;
         if missing(stop) then call symput('sort1','IN PROGRESS'),
         else call symput('sort1','COMPLETE'),
         end;
      else if process='SORT PERM.DATA2' and not missing(start) then do;
         if missing(stop) then call symput('sort2','IN PROGRESS'),
         else call symput('sort2','COMPLETE'),
         end;
      else if process='MERGE' and not missing(start) then do;
         if missing(stop) then call symput('merge','IN PROGRESS'),
         else call symput('merge','COMPLETE'),
         end;
   run;
   %if %length(&sort1)=0 %then %sort(dsn=PERM.DATA1);
   %else %if %length(&sort2)=0 %then %sort(dsn=PERM.DATA2);
   %else %if &sort1=COMPLETE and &sort2=COMPLETE and %length(&merge)=0     %then %merge;
   %else %if &sort1ˆ=COMPLETE or &sort2ˆ=COMPLETE or    &mergeˆ=COMPLETE %then %do;
      &lockclr;
      data _null_;
         call sleep(5,1);
         put 'Waiting 5 seconds';
      run;
      %end;
   %else &lockclr;
   %end;
%mend;
%engine;

The control table (PERM.Control) depicted in Table 9.1 demonstrates the performance improvement: each data set was sorted in approximately four minutes in parallel, after which the %MERGE macro merged the two data sets. Note that the JOBID variable indicates that two separate jobs were running.

Another example of distributed processing is found in the “Executing Software in Parallel” section in chapter 7, “Execution Efficiency.” Whether the SYSTASK statement is used to spawn separate SAS sessions or a control table is used to broker and coordinate separate SAS sessions that are operating concurrently, parallel processing can provide significantly faster processing.

Diminished Performance

As demonstrated in Figures 9.1 and 9.2, when data volume increases, performance generally will slow as additional system resources are consumed to handle the additional load. Performance typically decreases in a predictable, often linear path correlated most significantly with file size. This increased execution time can cause software to fall short of performance requirements. For example, if an ETL process is required by a customer to complete in 55 minutes, as data volume increases over the software lifespan, execution time may increase until it fails to meet this performance objective.

In many cases, performance requirements are predicated upon temporal or other limitations. For example, the requirement that software execute in less than 55 minutes may exist because the ETL program is intended to be run every hour. Thus, failure to complete in a timely manner might cause dependent processes to fail or might delay or hamper the start of the subsequent hourly execution. In a robust, fault-tolerant system, while runtime errors will not be produced by these failures, business value can be diminished or eliminated when dependent processes cannot execute. This phenomenon is discussed more in the “Functional Failures” section.

As data volume increases, or when it's expected to increase in the future, it's important for developers and other stakeholders to understand customer intent regarding software execution time limits. For example, requiring that software must complete in 55 minutes or less is very different from requiring that software must produce output every 55 minutes. In the first example, the customer clearly specifies that from the moment data are received, they should be extracted, transformed, and loaded and ready for use in 55 minutes. However, if the customer only wants data updated once per hour, it may be acceptable to create a program flow that takes 90 minutes, but that is run in parallel and staggered to still achieve output once per hour.

Figure 9.3 demonstrates two interpretations of the 55-minute requirement. On the left, the phase-gate approach is shown across a four-hour time period, demonstrating the requirement that the software complete in 55 minutes or less without the ability to overlap any processes. On the right, an overlapping schedule is demonstrated in which two copies of the software are able to run for 90 minutes, but because one starts on even hours and one starts on odd hours, they collectively produce results once an hour. In this example, the extract module is able to overlap with the load module to facilitate parallel processing and a control table or other mechanism would be required to coordinate the concurrent activities.

Parallel execution and other distributed processing models are a primary method to reduce execution time of software as data volume increases. Another benefit of the overlapping execution method noted in Figure 9.3 is the tendency to process smaller data sets, which reduces execution time. For example, an ordinary serialized ETL process that completed in 90 minutes would only ingest incoming data every 90 minutes. However, because the parallel version ingests data every 60 minutes, smaller chunks are being processed each time, thus reducing overall execution time.

Inefficiency Elbow

The inefficiency elbow represents a specific type of performance failure that occurs as resources are depleted due to increased data volume. Because execution time is the most commonly assessed performance metric, the elbow is typically first noticed when software performance drastically slows due to a relatively nominal increase in data volume. Figure 9.4 demonstrates the inefficiency elbow within the SAS University Edition, observed when execution times for the SORT procedure are measured in 100,000 observation increments from 100,000 to 4.5 million observations. Execution time takes only 36 seconds at 4.2 million observations, 98 seconds at 4.3 million observations, and 4 hours 21 minutes by 4.5 million observations!

The inefficiency elbow depicts the relative efficiency of a process as it scales to increasing data volume, indicating relatively efficient performance to the left of the elbow and relatively inefficient performance to the right. This is to say that efficient performance does not necessarily denote that the code in the underlying process is coded as efficiently as possible, only that as data volume scales, the software less efficiently utilizes resources until it fails outright.

To better understand what occurs as the efficiency elbow is reached, Table 9.2 demonstrates abridged UNIX FULLSTIMER performance metrics, including real time, CPU time, memory, OS memory, switch counts, page faults, involuntary context switches, and block input operations. SAS documentation contains more information about these specific metrics.³ Metrics surrounding the inefficiency elbow (from 3.5 to 4.5 million observations) demonstrate the substantial increase in I/O processing as SAS shifts from RAM to virtual memory, and finally the later jump in CPU usage.

As Figure 9.2 demonstrates, although the inefficiency elbow is not reached until around 4.2 million observations (as defined by the change in execution time), I/O metrics presaged defeat of the SORT procedure several iterations earlier. For example, both page faults and block input operations had consistently been 0 until 3.7 million observations, when both began increasing. Given this information, a quality assurance process monitoring these metrics could have detected levels above some threshold and subsequently terminated the process. This technique is described in the following section, “Data Governors.”

Without understanding the contributing factor that increased data volume can play in inefficiency, stakeholders may mistakenly attribute process slowness to network or other infrastructure issues. Worse yet, stakeholders may accept reduced performance as the status quo and their penance for big data, rather than investigating the cause of performance deficiencies. In reality, several courses of action can either move or remove the inefficiency elbow, the first of which begins with stress testing to determine what volume of data will induce inefficiency or failure in specific DATA steps, SAS procedures, and other processes. Without proper stress testing, discussed in the “Stress Testing” section in chapter 16, “Testability,” the inefficiency elbow will be a very unwelcome surprise when it's encountered.

To reduce the effects of the inefficiency elbow, stakeholders have several programmatic and nonprogrammatic solutions that entail more efficient use of resources, more distributed use of resources, or procurement of additional system resources:

• More efficient software—The inefficiency elbow denotes a change in relative efficiency; thus, it's possible that software is not truly operating efficiently even to the left of the elbow. For example, in Figure 9.4, it's possible that a WHERE statement in the SORT procedure—if appropriate—would reduce the number of observations read, thus reducing I/O consumption and shifting the elbow to the right.
• Increased infrastructure—Increased infrastructure can include increasing the hardware components as well as installing distributed SAS solutions such as SAS Grid Manager or third-party solutions like Teradata or Hadoop. By either increasing system resources or increasing the efficiency with which those resources can be utilized, the inefficiency elbow is shifted to the right, allowing more voluminous data to be processed with efficiency.
• Distributed software—As demonstrated in Figure 9.3, when processes can overlap or be run in parallel, the resource burden exhibited by a single SAS session can be distributed across multiple sessions and processors. Moreover, when this parallel design distributes data volume across multiple sessions, this doesn't alter the inefficiency elbow, but does decrease the position of execution time on the graph since fewer observations are processed through each independent SAS session.
• Data governors—A data governor is a quality control device that restricts data injects based on some criteria such as file size. For example, if a certain process is stress tested and demonstrates relative inefficiency when processing data sets larger than 1 GB, a data governor placed at 1 GB would block or subset files (for later or parallel processing) to prevent inefficiency.

Figure 9.5 demonstrates the effect of these methods on software performance and the inefficiency elbow.

SAS literature is replete with examples of how to design SAS software that is faster and more efficient, so these best practices are not discussed in this text. Infrastructure is discussed briefly in the “Distributed SAS Solutions” section, but this also lies outside of the focus of this text. Parallel processing and distributed solutions are discussed throughout chapter 7, “Execution Efficiency,” and data governors are discussed in the following section.

Data Governors

Just as a governor in a vehicle is installed to limit speed beyond some established threshold, a data governor can limit the velocity or, more typically, the volume of data for specific processes. Data governors can be implemented immediately before the inefficiency elbow, as demonstrated in Figure 9.5, or anywhere else to ensure a process does not begin to function inefficiently or fail. While data volume is the primary factor affecting performance, other factors can also lead to more substantial variability. For example, if other memory-hogging processes are already running, performance or functional failure might be reached at a significantly lower data volume because resources are more constrained. The “Contrasting the SAS University Edition” section in chapter 10, “Portability,” also demonstrates the influence that nonprogrammatic factors such as SAS interface have on the inefficiency elbow.

A data governor must first determine the file size of the data set. The following %HOWBIG macro obtains the file size in megabytes from the DICTIONARY.Tables data set. Note that a generic return code—the global macro variable &HOWBIGRC—is generated to facilitate future exception handling. However, robust production software would need to implement additional controls—for example, to validate that a data set exists and is not exclusively locked by another process, either of which would cause the SQL procedure to fail:

%macro howbig(lib=, dsn=);
%let syscc=0;
%global howbigRC;
%global filesize;
%global nobs;
%let howbigRC=GENERAL FAILURE;
proc sql noprint;
   select filesize format=15.
      into :filesize
      from dictionary.tables
      where libname=%upcase("&LIB") and memname=%upcase("&DSN");
   select nobs format=15.
      into :nobs
      from dictionary.tables
      where libname=%upcase("&LIB") and memname=%upcase("&DSN");
   quit;
run;
%if %eval(&syscc>0) %then %do;
   %let howbigRC=&syscc;
   %let filesize=;
   %let nobs=;
   %end;
%else %do;
   %let filesize=%sysevalf(&filesize/1048576); *convert bytes to MB;
   %let nobs=%sysevalf(&nobs);
   %end;
%if &syscc=0 %then %let howbigRC=;
%put filesize: &filesize;
%put observations: &nobs;
%mend;

In this example, the inefficiency elbow was shown to be around 1GB, so to account for some variability, the data governor will be set at 0.9 GB through conditional logic similar to the following code:

%let governor=%sysevalf(1024*.9); * in MB;
%if %sysevalf(&filesize>&governor) %then %do;
   * subset the data here or perform other busines logic;
   %end;
* run process on full data set or data subset;

If a 1.25 GB data set is encountered by the governor, business logic could specify that the first 0.9 GB of the data set be processed initially and the last 0.35 GB be processed either in series—that is, after the first batch completes—or immediately as a parallel process. One facile way to implement a data governor is through FIRSTOBS and OBS statements that can be used to subset the data before it is even read, efficiently eliminating unnecessary I/O processing. Because FIRSTOBS and OBS are dependent on observation number while data governors and other performance quality controls should be driven by file size, a conversion to observation count is necessary.

In a separate example, the following code demonstrates the creation of the 1 million-observation PERM.Mydata data set which, when interrogated through the %HOWBIG macro, is shown to be 7.875 MB:

libname perm 'c:perm';
data perm.mydata (drop=i);
   length num1 8;
   do i=1 to 1000000;
      num1=round(rand('uniform')*10);
      output;
      end;
run;

Suppose that stress testing has indicated that efficiency begins to decrease shortly after 6 MB (apparently this process has time-traveled back to 1984), indicating that the governor should be installed at 6 MB. The following macro %SETGOVERNOR calls the %HOWBIG macro to determine the file size and observation count and, if the file size exceeds the 6 MB limit, then the value to be used in the OBS option (to ensure a file size equal to or less than 6 MB) will be initialized in the &GOV macro variable.

* determines file size and provides the observation number for OBS;
%macro setgovernor(lib=, dsn=, govMB=);
%let syscc=0;
%global setgovernorRC;
%global governor;
%local gov;
%let setgovernorRC=GENERAL FAILURE;
%let governor=;
%howbig(lib=&lib, dsn=&dsn);
%if %length(&howbigRC)>0 %then %do;
   %let setgovernorRC=HOWBIG failure: &howbigRC;
   %return;
   %end;
%if %sysevalf(&filesize>&govMB) %then %do;
   %let gov=%sysfunc(round(%sysevalf(&nobs * &govMB / &filesize)));
   %let governor=(firstobs=1 obs=&gov);
   %end;
%if &syscc=0 %then %let setgovernorRC=;
%mend;

The following code invokes the %SETGOVERNOR macro to determine how many observations should be read into the Test data set. Because the file size 7.875 MB is greater than the 6 MB governor, the OBS statement (codified in the &GOVERNOR macro variable) specifies that only 761,905 observations (or 1,000,000 × 6 / 7.875) should be read into the Test data set.

%setgovernor(lib=perm, dsn=mydata, govMB=6);
data test;
   set perm.mydata &governor;
run;

To test the accuracy of this math, the %HOWBIG macro can subsequently be run on the Test data set, which produces the following output, indicating that the data set is indeed now only 6 MB and equal to the established governor.

%howbig(lib=work, dsn=test);
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
filesize: 6
observations: 761905

In this example, accuracy of the subset data set size is guaranteed because the data set was created through a standardized process, thus all observations are of equal size. In more variable data sets or especially in heteroscedastic data sets in which the file size of each observation is correlated with the observation number itself (e.g., values are more likely to be blank in the first half of the data set than the second half), this method will be less accurate. In many situations, however, this type of data governance provides an effective means to limit input into some process so that it does not incur performance or functional failures due to high data volume. And, depending on business logic, the remaining observations that were excluded could be executed in parallel or in series following the completion of the first process.

Data governors are most effective when inter-observation comparisons are not required. For example, if the DATA step had included by-level processing, utilized the RETAIN or LAG functions, or otherwise required interaction between observations, the data set could not have been split so easily. In some instances, it's possible to take the last observation from the first file and replicate this in the second file, thus providing continuity between the data set chunks. In other cases, such as a data governor placed on the FREQ procedure, the output from each respective FREQ procedure would need to be saved via the OUT statement and subsequently combined to produce a composite frequency table. Notwithstanding these complexities, where resources are limited and inefficiency overshadows big data processing, data governors can be a cost-effective, programmatic solution to scaling to big data.

As discussed, the %HOWBIG macro does not detect specific exceptions that could occur, such as a missing or exclusively locked data set that would cause the SQL procedure to fail. However, it includes basic exception handling to detect general failures via the &SYSCC global macro variable. The return code &HOWBIGRC is initialized to GENERAL FAILURE and is only set to a missing value (i.e., no failure present) if no warnings or runtime errors are detected. The %SETGOVERNOR macro follows an identical paradigm but, because it calls %HOWBIG as a child process, %SETGOVERNOR must dynamically handle failures that may have occurred in %HOWBIG through exception inheritance. Inheritance is required to achieve robustness in modular software design and is introduced in the “Exception Inheritance” section in chapter 6, “Robustness.”

Functional Failures

As data volume continues to increase and as system resources continue to be depleted, functional failure can become more likely; SAS software can terminate with a runtime error because it runs out of CPU cycles or memory. In some instances, the inefficiency elbow will have first been reached, indicating that performance will have slowed substantially as the process became less efficient. In other cases, the inefficiency elbow may not exist but a performance failure will occur because the execution time exceeds thresholds established in requirements documentation. Still, in other cases, some processes fail with runtime errors with no prior performance indicators (aside from FULLSTIMER metrics that may not have been monitored).

Figure 9.6 demonstrates performance of the SORT procedure when the volume of data is varied from 100,000 to 5 million observations. In a development environment that espouses software development life cycle (SDLC) principles, load testing would have demonstrated performance of the procedure at the predicted load of 2 million observations. However, realizing that data volume might increase over time, additional stress testing would have tested increasingly higher volumes of data to determine where performance and functional failures begin to occur.

If an inefficiency elbow is demonstrated through stress testing, performance requirements for execution time and resource consumption should be established below that point. To ensure that this threshold is never crossed, a data governor can be implemented to prevent both performance failures and functional failures from occurring when exceptionally large data sets are encountered. Figure 9.6 demonstrates the placement of these elements to support efficient performance.

Even with adequate technical requirements, robust quality control measures such as data governors, and attentive monitoring of system resources, SAS processes can still fail due to resource consumption while processing big data. For this reason, production software should espouse fault-tolerant programming within an exception handling framework that monitors all completed processes for warnings and runtime errors that can signal functional failures due to resource constraints. The use of return codes and global macro variable error codes such as &SYSCC and &SYSERR that facilitate failing safe are discussed extensively in chapter 3, “Communication,” and chapter 6, “Robustness.”

Velocity as an Indicator

During planning and design, at least a rough conception of eventual data volume should be obtainable. In many data analytic development environments, the data are already held so velocity is zero. For example, clinical trials software might be developed to analyze a study sample that has already been collected and to which data are not being added. In other cases, however, data accumulate at some velocity, which can be useful in predicting future data volume. For example, clinical trials software might be developed to analyze a longitudinal study that is expected to continue to grow in size for five years. Knowledge of data velocity can be used to establish load testing parameters to determine if performance requirements will be adequate throughout the life of the software. If an initial data set has 10,000 observations and is expected to grow by 10,000 observations each year thereafter, performance requirements would need to expect file sizes of 50,000 to 60,000 observations.

Modest file sizes such as this likely would not incur any noticeable performance decreases, so neither data volume nor velocity would be of significant concern in this example. But in environments where data volume can be staggering, data velocity should be used to facilitate accurate load and stress testing at accurate data volume levels. This information can be critical to stakeholders, helping them predict when software or hardware upgrades will be needed to maintain equivalent performance.

To continue the example demonstrated in Figure 9.6, the current data volume of some SAS processes is 2 million observations. If stakeholders further determine that the volume will grow by approximately 1 million observations per year, then they can predict that the process will still be operating efficiently and within the stated performance requirement of two hours' execution time. However, if data velocity instead accelerates, performance or functional failures could result as data volume increases and the software takes longer and longer to run. This underscores the importance of establishing expected software lifespan during planning and design phases of the SDLC.

The ability to predict future software performance is beneficial to Agile software development environments in which stakeholders aim to release software incrementally through rapid development that delivers small chunks of business value. An initial SAS process that meets performance requirements could be developed quickly and released, with the understanding that subsequent incremental releases will improve this performance to ensure that performance requirements will be met in the future as data volume continues to increase. Progressive software requirements to facilitate increasing the performance of software over time are discussed in the “Progressive Execution Time” section in chapter 7, “Execution Efficiency.”

The role of data volume and velocity in supporting effective Agile development is demonstrated in Figure 9.7. In this example, an initial solution is released in the first iteration that meets the two-hour performance requirement but that would begin to fail to meet this requirement when the data volume increased to approximately 2.5 million observations. The software is refactored in the second iteration to produce faster performance, which shifts the predicted performance function (as estimated through load and stress testing). After refactoring a second time in the third iteration, the software performance increases to a level that will meet the performance requirements to 5 million observations, giving stakeholders ample time to decide how to handle that scalability challenge when it is encountered. Thus, Agile development enables SAS practitioners to produce a solution quickly that meets immediate requirements while allowing developers to keep an eye toward the future and eventual performance requirements that software will need to meet to scale to larger data. This forward-facing awareness is the objective of software refactoring and extensibility, discussed in the “From Reusability to Extensibility” sections in chapter 18, “Extensibility.”

Invalid Business Logic

Invalid business logic essentially amounts to errors that SAS practitioners make in software design when they fail to predict implications of higher data volume. The failure patterns are endless but one example occurs when observation counters exceed predicted limits. For example, the following code simulates an ingestion process (with increasing data volume) followed by a later process:

libname perm 'c:perm';
* simulates data ingestion;
data perm.mydata;
   length num1 8;
   do i=1 to 1000;
      num1=round(rand('uniform')*10);
      output;
      end;
run;
* simulates later data process;
filename outfile 'c:permmydata_output.txt';
data write;
   set perm.mydata;
   file outfile;
   put @1 i @4 num1;
run;

The output demonstrates that the second process will operate effectively when the data set contains fewer than 100 observations, but when this threshold is crossed, the data no longer are delimited by a space and are indistinguishable. At 1,000 observations, a functional failure occurs when the data are overwritten:

1  4
2  1
3  4
...
9  1
10 7
11 6
...
99 0
1008
1013

Logic errors such as this are easily remedied but can be difficult to detect. However, if load testing and stress testing are not performed or fail to detect underlying software defects, they can go unnoticed indefinitely.

SAS Application Thresholds

Programmatic limitations exist within the SAS Base language just as in any software language. For example, variable naming conventions specify acceptable alphanumeric characters that can appear in a SAS variable name, and maximum name lengths are specified for SAS variables, macro variables, libraries, and other elements. Under the SAS 9.4 variable naming standard (V7), both data set variable names and macro variable names are limited to 32 characters in length. Thus, failures could result if variable names were being dynamically created through macro language, if the name included an incrementing number, and if that number caused the length of the variable to exceed 32 characters. Many of these SAS thresholds are detailed in SAS^® 9.4 Language Reference: Concepts: Fifth Edition.⁴

Other SAS language limitations, however, are published nowhere and are discovered solely through trial and error—that is, when software has been performing admirably, but suddenly and inexplicably fails. For example, a commonly cited method to determine the number of observations in a data set is to utilize the COUNT statement in the SQL procedure to create a macro variable. The following output demonstrates that the PERM.Mydata data set has 1,000 observations:

proc sql noprint;
   select count(*)
   into :nobs
   from perm.mydata;
quit;
%put OBS: &nobs;
OBS: 1000

While this method lacks robustness (because the SQL procedure fails with a runtime error if the data set does not exist, is exclusively locked, or contains no variables), it can be a valuable tool when these exceptions are detected and controlled through exception handling. But as data volume increases, another exception can occur when the default SAS variable format shifts from standard to scientific notation. Thus, 10 million is represented in standard notation as 10000000, while 100 million is represented in scientific notation as 1E8:

%put OBS: &nobs; * for 10 million observations;
OBS: 10000000
%put OBS: &nobs; * for 100 million observations;
OBS: 1E8

This notational shift can destroy SAS conditional logic and other processes, creating aberrant results. For example, if %SYSEVALF is not used to compare the resultant value, then operational comparisons are character—not numeric—which causes scientific notation to yield incorrect results:

* equivalent values in standard and scientific notation;
%let nobs_stand=100000000;
%let nobs_sci=1E8;
%macro test(val=);
%if &nobs_stand<9 %then %put standard notation: 100000000 is less than &val;
%else %put standard notation: 100000000 is NOT less than &val;
%if &nobs_sci<9 %then %put scientific notation: 1E8 is less &val;
%else %put scientific notation: 1E8 is NOT less than &val;
%mend;
%test(val=9);
standard notation: 100000000 is NOT less than 9
scientific notation: 1E8 is less 9

1E8 is not less than 9, but when a character comparison is made, the fallacy is demonstrated because SAS only assesses that the first character, 1, is less than 9. The %EVAL function is used to force numeric comparisons in the macro language and typically works—that is, until scientific notation is encountered:

%macro test(val=);
%if %eval(&nobs_stand<9) %then %put standard notation: 100000000 is less than &val;
%else %put standard notation: 100000000 is NOT less than &val;
%if %eval(&nobs_sci<9) %then %put scientific notation: 1E8 is less &val;
%else %put scientific notation: 1E8 is NOT less than &val;
%mend;
%test(val=9);
standard notation: 100000000 is NOT less than 9
scientific notation: 1E8 is less 9

The scientific notation unfortunately fails again, but now for a different reason. The %EVAL function does not recognize scientific notation and thus views this as an attempt to convert a character value to a number. This is demonstrated in the following output that results when the %EVAL function is used to print standard and scientific notation:

%put standard: %eval(&nobs_stand);
standard: 100000000
%put scientific: %eval(&nobs_sci);
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: 1E8
scientific:

This failure is overcome by substituting the %EVAL function with the %SYSEVALF function, which does interpret scientific notation. Now, as expected, both standard and scientific notation are accurately read and can be operationally evaluated:

%macro test(val=);
%if %sysevalf(&nobs_stand<9) %then %put standard notation: 100000000 is less than &val;
%else %put standard notation: 100000000 is NOT less than &val;
%if %sysevalf(&nobs_sci<9) %then %put scientific notation: 1E8 is less &val;
%else %put scientific notation: 1E8 is NOT less than &val;
%mend;
%test(val=9);
standard notation: 100000000 is NOT less than 9
scientific notation: 1E8 is NOT less than 9

While %SYSEVALF is not always required and can be replaced by %EVAL for many operations, as data sets increase in volume, %SYSEVALF may be required to ensure that scientific notation is accurately assessed. Another possible solution is to add FORMAT=15. to the SELECT statement in the SQL procedure, as demonstrated previously in the “Data Governors” section. The duality of these two solutions is discussed further in the “Risk versus Failure” section in chapter 1, “Introduction.”