topic Re: Lag performance over 400 million sorted records in SAS Programming

Lag performance over 400 million sorted records

bentleyj1 — Tue, 23 Oct 2018 21:37:20 GMT

I'm reworking a program that has a data set with about 400 million records sorted by account number with one account number per time period. There are about 300 time periods but each account can be in the data set only maybe 20 times.

We have to process by account_number because we need to derive running totals for each account number across all time periods.
Some derived variables requires lagging. Here's the existing code-. For each record a series of lagged variables are created and then referenced in the code later.

dpd_lag1=lag(dpd); pay_lag1=lag(payment_amount); prin_lag1=lag(payment);
dpd_lag2=lag2(dpd); pay_lag2=lag2(payment_amount); prin_lag2=lag2(payment);
dpd_lag3=lag3(dpd); pay_lag3=lag3(payment_amount); prin_lag3=lag3(payment);
dpd_lag4=lag4(dpd); pay_lag4=lag4(payment_amount); prin_lag4=lag4(payment);
dpd_lag5=lag5(dpd); pay_lag5=lag5(payment_amount); prin_lag5=lag5(payment);

if loan_count=2 then do;
max_dpd=max(dpd,dpd_lag1);
max_dpd_2=dpd_lag1;

        if dq_monthly_payment > 0 then do;
          chrva421=(100*payment)/dq_monthly_payment;
          chrva421p=(100*sum(payment_amount,pay_lag1))/(dq_monthly_payment*2);
       end;
       else do;
          chrva421=.;
          chrva421p=.;
      end;
    end;

else if loan_count=3 then do;
max_dpd=max(dpd,dpd_lag1,dpd_lag2);
max_dpd_2=max(dpd_lag1,dpd_lag2);

     if dq_monthly_payment > 0 then do;
         chrva421=(100*sum(payment,prin_lag1))/(dq_monthly_payment*2);
         chrva421p=(100*sum(payment_amount,pay_lag1,pay_lag2))/(dq_monthly_payment*3);
      end;
    else do;
      chrva421=.;
      chrva421p=.;
    end;
end;

** continues up to loan_count=6 ;

The program takes Hours to run on our server.

My question is, do you think it would be faster to lag the variables 'on the fly' instead of creating them as we're doing here? We'd remove the lag assignment statements and the code would look like this with LAG functions used on-the-fly. (may be parenthesis issues in here.)

if loan_count=2 then do;
max_dpd=max(dpd,lag(dpd));
max_dpd_2=lag(dpd);

       if dq_monthly_payment > 0 then do;
         chrva421=(100*payment)/dq_monthly_payment;
         chrva421p=(100*sum(payment_amount,lag(payment_amount))/(dq_monthly_payment*2);
       end;
       else do;
        chrva421=.;
        chrva421p=.;
      end;
    end;

else if loan_count=3 then do;
max_dpd=max(dpd,lag(dpd),lag2(dpd));
max_dpd_2=max(dpd_lag1,dpd_lag2);

    if dq_monthly_payment > 0 then do;
    chrva421=(100*sum(payment,lag(payment))/(dq_monthly_payment*2);
    chrva421p=(100*sum(payment_amount,lag(payment),lag2(payment))/(dq_monthly_payment*3);
    end;
    else do;
      chrva421=.;
      chrva421p=.;
    end;
end;

I'm also thinking of putting this into a macro do-loop.

Thanks in advance for your thoughts.

John

Re: Lag performance over 400 million sorted records

Reeza — Tue, 23 Oct 2018 21:39:21 GMT

If your data is sorted, what about a temporary array instead? And if you know it's only twenty maximum per ID that's the limit of the size you'd need and SAS will drop them automatically.

Re: Lag performance over 400 million sorted records

bentleyj1 — Tue, 23 Oct 2018 21:51:54 GMT

I've thought of using an array but figured i'd ask for opinions on this solution first. The issue with a _temporary_ array is that the values are retained-- "Temporary data element values are always automatically retained, rather than being reset to missing at the beginning of the next iteration of the DATA step." http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000201956.htm

I need the array to be refreshed/reloaded with each iteration of the DATA step because an account can have more than 5 records. So would anything be gained from an array versus assignment statements?

Re: Lag performance over 400 million sorted records

novinosrin — Tue, 23 Oct 2018 22:02:53 GMT

To counter -The issue with a _temporary_ array is that the values are retained-- "Temporary data element values are always automatically retained, rather than being reset to missing at the beginning of the next iteration of the DATA step."

array temp(9999) _temporary_;

call missing(of temp(*));

at the top of each iteration

Re: Lag performance over 400 million sorted records

bentleyj1 — Tue, 23 Oct 2018 22:05:26 GMT

Brilliant. Thanks. I might go with the temporary array solution... it's got to be faster than adding 15 variables to each record and then dropping them at the end of the step.

Re: Lag performance over 400 million sorted records

novinosrin — Tue, 23 Oct 2018 22:09:28 GMT

Yes Sir. i concur with @Reeza 's idea too as no output buffer is created and the variables are contiguous and so easy to peek/compute. Let us know if you need any further help.

Re: Lag performance over 400 million sorted records

data_null__ — Tue, 23 Oct 2018 22:15:09 GMT

How did you determine the LAG functions are causing the performance issue?

If you are executing the LAG or any function unnecessarily then not doing should improve performance.

Will the LAGs be right with when the are executed as you suggest? i.e. the conditional LAG problem.

Re: Lag performance over 400 million sorted records

Reeza — Tue, 23 Oct 2018 22:25:39 GMT

To me, you'd need to reset it at the beginning of each new ID wouldn't you?If you're only going up to 5 though and that makes sense you can just overwrite the numbers can't you. You can easily set it to missing with CALL MISSING().

Re: Lag performance over 400 million sorted records

ChrisNZ — Wed, 24 Oct 2018 00:58:05 GMT

1. Arrays are a good option to explore

2. Your suggestion

if loan_count=2 then do;
max_dpd=max(dpd,lag(dpd));

would not work. The LAG stack must be updated with each observation. The IF test prevents that.

3. I don't think you need

else do;
        chrva421=.;
        chrva421p=.;
      end;

4. Temporary arrays are much faster than named ones.

Re: Lag performance over 400 million sorted records

Astounding — Wed, 24 Oct 2018 03:17:54 GMT

One more thought ... it could be that the LAG function is slowing the program. Note that these two programs generate identical resrults:

data want1;

set have;

pay_lag1 = lag(payment);

pay_lag2 = lag2(payment);

run;

data want2;

pay_lag2 = pay_lag1;

pay_lag1 = payment;

set have;

retain pay_lag1;

run;

It's less work to do this with numeric variables. With character variables, you would have to set the length first. But it could well be significantly faster than using LAG.

Re: Lag performance over 400 million sorted records

Oligolas — Wed, 24 Oct 2018 06:49:31 GMT

Maybe two thoughts that come to my mind to complement what have been said:

- it looks like you only need to determine the n-1 lag of the current loan_Count if-clause
- You may want to determine if you can make use of the SASFILE Statement, maybe by exploring if you can split up the huge dataset upon performing the operations by time period and account number.

Re: Lag performance over 400 million sorted records

Kurt_Bremser — Wed, 24 Oct 2018 07:42:06 GMT

@Astounding wrote:

One more thought ... it could be that the LAG function is slowing the program. Note that these two programs generate identical resrults:

data want1;

set have;

pay_lag1 = lag(payment);

pay_lag2 = lag2(payment);

run;

data want2;

pay_lag2 = pay_lag1;

pay_lag1 = payment;

set have;

retain pay_lag1;

run;

It's less work to do this with numeric variables. With character variables, you would have to set the length first. But it could well be significantly faster than using LAG.

I just ran a similar test against one of our larger datasets (100+ million obs, ~14 G compressed size):

data ww0.test2 (keep=x1 x2);
set source;
length x1 x2 4;
x1 = invar;
x2 = lag(invar);
run;

data ww0.test3 (keep=x1 x2);
set source;
length x1 x2 4;
retain x2;
x1 = invar;
output;
x2 = invar;
run;

In terms of CPU time, both methods performed similar, with only marginal differences. Outside factors (concurrent load, especially I/O) was much more significant with regards to real time needed.

My personal bottom line: I expect a Base SAS function (and one that's been around as long as lag()) to be optimized as much as can be, and not to be outdone by manually written code that does essentially the same (fill and move a FIFO chain)

Re: Lag performance over 400 million sorted records

s_lassen — Wed, 24 Oct 2018 10:45:30 GMT

I think the temporary array idea can work, and I do not think you have to initialize the array for each account. Just keep track of the number of records you have read from that account, something like

data want;
  array dpd_lag(100) 8 _temporary_;
  array pay_lag(100) 8 _temporary_;
  array prin_lag(100) 8 _temporary_;
  do _N_=1 by 1 until(last.account_number);
    set have;
    by account_number;
    /* assuming that you want to do the calculation for loan_count=1 as well, with no lag, I put the current variables in also */
    dpd_lag(_N_)=dpd;
    pay_lag(_N_)=payment_amount;
    prin_lag(_N_)=payment;
    if loan_count>_N_ then /* would be the same as getting the lag from a previous account */
      error 'Wrong loan count';
    else do;
      lag_index=_N_-loan_count+1;
      /* do the calculation here, using dpd_lag(lag_index) etc. as the lagged vars */
      /* I assume the calculation is basically the same for all loan counts */
      output;
      end;
    end;
run;

Re: Lag performance over 400 million sorted records

ChrisNZ — Wed, 24 Oct 2018 22:25:37 GMT

@Kurt_Bremser

My experience differs, using LAG() takes 40% longer.

data TEST; 
  do PAYMENT = 1 to 1e8;
    output;
   end;
run;

sasfile TEST load;
 
data _null_;        * 7s CPU/Elapse;
  set TEST;
  PAY_LAG1 = lag (PAYMENT);
  PAY_LAG2 = lag2(PAYMENT);
run;

data _null_;        * 5s CPU/Elapse;
  retain PAY_LAG1;
  PAY_LAG2 = PAY_LAG1;
  PAY_LAG1 = PAYMENT;
  set TEST;
run;

sasfile TEST close;

> I expect a Base SAS function (and one that's been around as long as lag()) to be optimized as much as can be

Considering how badly functions perform when used in WHERE clauses compared to IF clauses, my optimism regarding optimisation doesn't reach the levels of yours. 🙂

Re: Lag performance over 400 million sorted records

Kurt_Bremser — Thu, 25 Oct 2018 06:44:32 GMT

Interesting. This is the log when I ran your test on my server:

35         data _null_;        * 7s CPU/Elapse;
36           set TEST;
37           PAY_LAG1 = lag (PAYMENT);
38           PAY_LAG2 = lag2(PAYMENT);
39         run;

NOTE: There were 100000000 observations read from the data set WORK.TEST.
NOTE: DATA statement used (Total process time):
      real time           44.22 seconds
      cpu time            5.75 seconds
      

40         
41         data _null_;        * 5s CPU/Elapse;
2                                                          Das SAS System                           08:26 Thursday, October 25, 2018

42           retain PAY_LAG1;
43           PAY_LAG2 = PAY_LAG1;
44           PAY_LAG1 = PAYMENT;
45           set TEST;
46         run;

NOTE: There were 100000000 observations read from the data set WORK.TEST.
NOTE: DATA statement used (Total process time):
      real time           46.69 seconds
      cpu time            4.88 seconds

SAS 9.4M5, AIX 7.1, 2 POWER8 cores, MEMSIZE 256M (that's why SASFILE won't work).

All this with a rather constant run queue around 8, and one can see that the potential gain in real time is negligible (actually, in this particular test there wasn't one), as the factor I/O is much bigger.

When reducing the obs count to 1e7, sasfile was possible, and I got this:

35         data _null_;        * 7s CPU/Elapse;
36           set TEST;
37           PAY_LAG1 = lag (PAYMENT);
38           PAY_LAG2 = lag2(PAYMENT);
39         run;

NOTE: There were 10000000 observations read from the data set WORK.TEST.
NOTE: DATA statement used (Total process time):
      real time           3.97 seconds
      cpu time            0.54 seconds
      

40         
41         data _null_;        * 5s CPU/Elapse;
42           retain PAY_LAG1;
43           PAY_LAG2 = PAY_LAG1;
44           PAY_LAG1 = PAYMENT;
45           set TEST;
46         run;

NOTE: There were 10000000 observations read from the data set WORK.TEST.
NOTE: DATA statement used (Total process time):
      real time           3.53 seconds
      cpu time            0.44 seconds

Once again, the difference in CPU seconds lies in the 10-15% range.

What environment do you use for SAS?

Re: Lag performance over 400 million sorted records

ChrisNZ — Thu, 25 Oct 2018 07:27:42 GMT

@Kurt_Bremser

I am testing under Windows. I can post the log tomorrow, but there is nothing to be learnt there I think.

>as the factor I/O is much bigger

The idea is to test whether retrieving previous values with LAG() is slower than saving values before they are overwritten by SET.

It may well be that, depending on other parameters, this difference in efficiency makes no difference in various cases.

That is besides the point. That's why I used data _null_ and sasfile : to isolate the process we are measuring.

Once again, the difference in CPU seconds lies in the 10-15% range.

Your numbers show an increase in CPU time when using LAG of 17 and 22%. Not as high as my test (maybe due to the high load you have?), but enough to conclude that LAG is less efficient imho.

MEMSIZE 256M

Oh wow that's low! So much CPU power so little RAM space!

Re: Lag performance over 400 million sorted records

Kurt_Bremser — Thu, 25 Oct 2018 08:23:54 GMT

@ChrisNZ wrote:

@Kurt_Bremser

MEMSIZE 256M

Oh wow that's low! So much CPU power so little RAM space!

That's following a recommendation by SAS themselves. If you allow SAS lots of memory in a heavily multi-user setup, SAS will (in most cases with data steps and "simple" procedures that do not need much memory for themselves) "waste" memory by caching SAS dataset data internally, when the operating system already does that on it's own. In extreme, the OS would then have to page out program pages that actually contain data which is already present in the portion the OS reserves for caching disk data. Double whammy. Restricting SAS memory prevents that and makes the most of the operating system's capabilities in terms of read-ahead, sharing cached data and the like.

So we work with the least memory that's necessary for our codes to work.

At the moment I ran my tests, we had our monthly closure running, so the accountants and actuaries where riding the server hardest, pushing up the run queue and the I/O load.

The batch programs I develop are also meant to run in parallel, so using memory economically is also a must.

Re: Lag performance over 400 million sorted records

Astounding — Thu, 25 Oct 2018 13:05:17 GMT

Remember, in this application there are several variables that need to be lagged 5 times. This might be a more appropriate test of the savings:

data want1;

set have;

pay_lag1 = lag(payment);

pay_lag2 = lag2(payment);

pay_lag3 = lag3(payment);

pay_lag4 = lag4(payment);

pay_lag5 = lag5(payment);

run;

data want2;

pay_lag5 = pay_lag4;

pay_lag4 = pay_lag3;

pay_lag3 = pay_lag2;

pay_lag2 = pay_lag1;

pay_lag1 = payment;

set have;

retain pay_lag1 - pay_lag5;

run;

Yes, I know I don't need to retain PAY_LAG5. But that can only help the program run a hair faster.

Re: Lag performance over 400 million sorted records

Kurt_Bremser — Thu, 25 Oct 2018 13:38:31 GMT

With 5 lags vs. 5 retains, the difference becomes significant. With 3e7 observations I got

      real time           3.83 seconds
      cpu time            2.13 seconds

for the lag() vs.

      real time           2.32 seconds
      cpu time            1.29 seconds

for the retain version. All tests ran from a dataset loaded with sasfile and used in a data _null_ step, so I/O was mostly removed as a factor.

I hereby revoke my former statement about SAS functions being optimized.

I then added a step with a temporary array:

data _null_;
set have;
array pay_lag{5} _temporary_;
do i = 1 to 4;
  pay_lag{i} = pay_lag{i+1};
end;
pay_lag{5} = payment;
run;

and this turned out to be the slowest of all, more than double the CPU time of the retain version.

Who's the culprit here? It turns out to be the do loop, because (after digging out the old C programmer in me who uses #DEFINEs to speed up the compilate)

data _null_;
set have;
array pay_lag{5} _temporary_;
%macro mymac;
%do i = 1 %to 4;
  pay_lag{&i} = pay_lag{%eval(&i+1)};
%end;
%mend;
%mymac
pay_lag{5} = payment;
run;

performed as well as the retain version.

Bottom line: if you engage in optimizing code in this way, creating lots of statements with a macro can save significantly in runtime.

Re: Lag performance over 400 million sorted records

ChrisNZ — Thu, 25 Oct 2018 21:07:57 GMT

@Kurt_Bremser

1. if you engage in optimizing code in this way, creating lots of statements with a macro can save significantly in runtime.

Top notch optimisation research here Kurt. Well done! 🙂

2.SAS will "waste" memory by caching SAS dataset data internally, when the operating system already does that on it's own

- I've never heard of this before. As far as I know, a step allocates virtual memory based on best guess upon starting, requests more as/if needed until the value of ~MEMSIZE is reached, and then uses utility files if that's a feature supported by that step. At the end of the step, all the virtual memory is released.

- The latest value seems to be a more reasonable 2G (it was 512M in version 9.3).