topic Re: Max per loan in last 7 days in SAS Programming

Max per loan in last 7 days

Ronein — Sun, 07 Sep 2025 07:05:38 GMT

Hello
Let's say I have a data set in long structure. for each customer ID +loan need to calculate max offer in last 7 days prior to loan date
What is the way to do it?
For example for customer 111 there us one loan and max 7 days before is 22000( offer in 01jul is not relevant because more than 7 days).
For customer 222 loan 13000 max offer is 40000 and for loan 35000 max is 28000 ( Offer 40000 is not taking into account because more than 7 days prior loan)

In desire table will have 3 rows because have 3 loans.

What is way yo do it??

Data have;
Format date ddmmyy10.;
Input custid date :date9. loansAmnt offerAmnt;
Cards;
111 01JUL2025 . 50000
111 03jul2025 . 22000
111 08jul2025 . 19000
111 09jul2025 5000 .
222 01jul2025 . 40000
222 03jul2025 . 28000
222 04jul2025 13000 .
222 08jul2025 . 27000
223 09jul2025 35000 .
;
Run:

Re: Max per loan in last 7 days

Kurt_Bremser — Sun, 07 Sep 2025 10:00:15 GMT

Use a date-indexed array to keep the offer amounts:

data want;
set have;
by custid;
array ams [1:30000] _temporary_;
if first.custid
then do _date = 1 to 30000;
  ams[_date] = 0;
end;
if offeramnt ne . then ams[date] = offeramnt;
if loansamnt ne .
then do _date = date - 7 to date;
  put _date ams[_date];
  maxamnt = max(maxamnt,ams[_date]);
end;
if loansamnt;
drop _date offeramnt;
run;

Re: Max per loan in last 7 days

Kurt_Bremser — Sun, 07 Sep 2025 10:30:25 GMT

PS if you want a non-zero value for the last observation, custid should be 222 there, not 223.

Re: Max per loan in last 7 days

mkeintz — Sun, 07 Sep 2025 20:49:58 GMT

Here's a data step that effectively maintains a moving window of offer amounts for the prior 7 days.
With each obs, first weed out stale (over 7 days prior) data.

Then just take the maximum of that small array.

Then update the arrays with the new obs

data have;
  format date ddmmyy10.;
  input custid date :date9. loansAmnt offerAmnt;
cards;
111 01JUL2025 .     50000
111 03jul2025 .     22000
111 08jul2025 .     19000
111 09jul2025 5000  .
222 01jul2025 .     40000
222 03jul2025 .     28000
222 04jul2025 13000 .
222 08jul2025 .     27000
223 09jul2025 35000 .
run;
data want (drop=_:);
  set have;
  by custid;

  array tempdat{0:6} _temporary_; /*Observed dates within prior 7 days*/
  array tempval{0:6} _temporary_; /*Observed offers within prior 7 days*/  

  /* Weed stale data first */
  if first.custid=1 then call missing(of tempval{*},of tempdat{*});
  else do while (.< min(of tempdat{*}) < date-7);    
    _d=mod(min(of tempdat{*}),7);
    call missing(tempval{_d},tempdat{_d});
  end;

  /* Get the maximum */
  if n(of tempdat{*})>0 then max_prior_7days=max(of tempval{*});

  /*Update the arrays with current obs */
  _d=mod(date,7);   
  tempdat{_d}=date;
  tempval{_d}=offeramnt;
run;

Ignore the previously sumitted code below, which didn't weed before taking the maximum:

~~data want (drop=_:);~~

~~set have; by custid;~~

~~array tempdat{0:6} _temporary_; /*Observed dates within prior 7 days*/~~

~~array tempval{0:6} _temporary_; /*Observed offers within prior 7 days*/~~

~~/* Get the maximum, when there is a 7-day window available */~~

~~if first.custid=0 and dif(date)<=7 then max_prior_7days=max(of tempval{*});~~

~~else call missing(of tempval{*},of tempdat{*});~~

~~/*Update the arrays with current obs */~~

~~_d=mod(date,7); tempdat{_d}=date;~~

~~tempval{_d}=offeramnt;~~

~~/*Weed out stale data */~~

~~do while (min(of tempdat{*})<date-7);~~

~~_d=mod(min(of tempdat{*}),7);~~

~~call missing(tempval{_d},tempdat{_d});~~

~~end;~~

~~run;~~

Two points:

The temporary arrays are indexed from 0 to 6, effectively representing a day-of-week index. So the data in the arrays are not ordered strictly chronologically, but order doesn't matter for getting the maximum. The task is to be sure to eliminate stale values.
The DIF(x) function is x-lag(x).

Re: Max per loan in last 7 days

Ronein — Mon, 08 Sep 2025 06:24:54 GMT

Why array have 6 arguments?

Re: Max per loan in last 7 days

Ronein — Mon, 08 Sep 2025 06:25:43 GMT

Why array have 30000 arguments? How dud you know to choose this number ?

Re: Max per loan in last 7 days

Kurt_Bremser — Mon, 08 Sep 2025 07:30:10 GMT

As I said, the array is date-indexed. Now, with your knowledge about SAS dates, think which dates a range of 1 to 30000 covers.

The array needs to span the range of dates present in your data, or more.

Re: Max per loan in last 7 days

mkeintz — Mon, 08 Sep 2025 11:08:19 GMT

@Ronein wrote:
Why array have 30000 arguments? How dud you know to choose this number ?

If you are using an array from 0 to 30000, it covers Jan 1, 1960 through 19feb2024. You can customize the date range per below (say for 01jan2009 through 31dec2020):

%let begdate=01jan2009 ;
%let enddate=31dec2020 ;

data;
  * other sas code ;
  array history {%sysevalf("&begdate"d):%sysevalf("&enddate"d)} _temporary_ ;
  * other sas code ;
run;

Re: Max per loan in last 7 days

PaigeMiller — Mon, 08 Sep 2025 11:17:16 GMT

Two simple steps

Find date of loan and find those offers within 7 days of loan (much simpler data step than those shown by others)
Find max of offers within last 7 days (PROC SUMMARY);

/* step 1 */
data intermediate;
    merge have(where=(loansamnt=.))  have(where=(loansamnt>.) drop=offeramnt rename=(date=loandate));
    by custid;
    delta_date=loandate-date;
run;

/* step 2 */
proc summary data=intermediate nway;
    where delta_date between 0 and 7 or missing(delta_date);
    class custid;
    var offeramnt;
    output out=max_offer max=max_offer;
run;

Re: Max per loan in last 7 days

dxiao2017 — Mon, 08 Sep 2025 11:51:34 GMT

Hi @Ronein , I think the major or first problem for solve this question is, create one more date column for your loan date. You cannot put the date for loans and date for offer in only one date column, this is not good, because both dates will be needed for calculating and further analysis. Put two different kind of date in one column will make date calculation difficult and cause trouble in future data analysis, regardless it is business or health data. Therefore, I suggest that create a loandate and an offerdate column for your dataset first, and the problem will become much easier by using whatever procs with an calcualtion such as offerdate-loandate<=7 days; statement.

Re: Max per loan in last 7 days

Kurt_Bremser — Mon, 08 Sep 2025 12:14:15 GMT

Your calculation is off by quite some years. 30000 days is equivalent to ~ 82 years (30000 / 365.25 = 82.1355), so the end date must be in the 2040's, not in 2024.

Re: Max per loan in last 7 days

Ronein — Mon, 08 Sep 2025 13:55:46 GMT

Can you show full code how to create desired output using 2 dare columns?

Re: Max per loan in last 7 days

dxiao2017 — Tue, 09 Sep 2025 15:36:47 GMT

Hi @Ronein , this is what I come up with today, I have not figure out the final solution (the three row table with max offer amount within 7 days). A question is what is the custid for the last row, is it 222 or 223? I assume it is 222, i.e., custid 222 has two loans. The modified dataset and code is as follows:

Data have;
Format date ddmmyy10.;
Input custid date :date9. loansAmnt offerAmnt;
Cards;
111 01JUL2025 . 50000
111 03jul2025 . 22000
111 08jul2025 . 19000
111 09jul2025 5000 .
222 01jul2025 . 40000
222 03jul2025 . 28000
222 04jul2025 13000 .
222 08jul2025 . 27000
222 09jul2025 35000 .
;
Run;
proc print data=have;run;
data have1;
   set have;
   if loansamnt=. then offerdate=date;
      else if loansamnt^=. then loandate=date;
   format offerdate loandate date9.;
run;
proc print data=have1;run;

Re: Max per loan in last 7 days

dxiao2017 — Tue, 09 Sep 2025 15:50:24 GMT

Hi @Ronein , (continued from my last thread) if custid 222 has two loans (i.e., that custid has two load dates), then two loan date columns (instead of one loan date column) need to be created, that consumes more steps, which means your original dataset although looks good, but in fact it is far, far away from ready for analysis, because you need to create three date/time columns from one date column and match them back into the original dataset you have. Here is the next two steps which create two loan date columns (and then, next, one may use proc sql to join the tables to produce a final analysis dataset that is really ready for further data analysis):

proc sql;
create table have2 as
select custid,
       loandate
   from have1
   where loandate^=.;
select * from have2;
quit;
proc transpose data=have2 out=have3;
   by custid;
   var loandate;
run;
proc print data=have3;run;

Re: Max per loan in last 7 days

mkeintz — Wed, 10 Sep 2025 03:19:02 GMT

@Ronein wrote:
Why array have 6 arguments?

The array does NOT have six elements, it has seven elements, where the lower bound is zero (not one) and the upper bound is six. When you divide the date value by 7 and keep the remainder, you will end up with 0, 1, ..., 5, or 6. So each day of the week can trivially be placed in exactly one element of the array.

Re: Max per loan in last 7 days

dxiao2017 — Thu, 11 Sep 2025 15:42:29 GMT

Hi @Ronein , in fact, two loans amount columns also need to created to produce a final analysis dataset that is ready for further analysis. I have created this final analysis dataset today, the code and output tables are as follows (and I will do the date calculation and produce the final report table later on):

Data have;
Format date ddmmyy10.;
Input custid date :date9. loansAmnt offerAmnt;
Cards;
111 01JUL2025 . 50000
111 03jul2025 . 22000
111 08jul2025 . 19000
111 09jul2025 5000 .
222 01jul2025 . 40000
222 03jul2025 . 28000
222 04jul2025 13000 .
222 08jul2025 . 27000
222 09jul2025 35000 .
;
Run;
proc print data=have;title "have";run;
/*create two loan date columns*/
data have1;
   set have;
   if loansamnt=. then offerdate=date;
      else if loansamnt^=. then loandate=date;
   format offerdate loandate date9.;
run;
proc print data=have1;title "have1";run;
proc sql;
create table have2 as
select custid,
       loandate
   from have1
   where loandate^=.;
title "have2";
select * from have2;
quit;
proc transpose data=have2 
               out=have3(drop=_name_
                         rename=(col1=loandate1
                                 col2=loandate2)
                         );
   by custid;
   var loandate;
run;
proc print data=have3;title "have3";run;
/*create two loans amount columns*/
proc sql;
create table have4 as
select custid,
       loansamnt
   from have1
   where loansamnt^=.;
title "have4";
select * from have4;
quit;
proc transpose data=have4
               out=have5(drop=_name_
                          rename=(col1=loansamnt1
                                  col2=loansamnt2)
                         );
   by custid;
   var loansamnt;
run;
proc print data=have5;title "have5";run;
/*produce final analysis dataset that
is ready for data analysis*/
proc sql;
create table want1 as
select h1.custid,
       h1.offerdate,
       h1.offeramnt,
       h3.loandate1,
       h5.loansamnt1,
       h3.loandate2,
       h5.loansamnt2
   from have1 as h1 left join
        have3 as h3
   on h1.custid=h3.custid left join
      have5 as h5
   on h1.custid=h5.custid
   where h1.offeramnt^=. and
         h1.offerdate^=.
   order by 1,2;
select * from want1;
quit;

The final analysis dataset (that is ready for data analysis per your request) look like this:

Re: Max per loan in last 7 days

dxiao2017 — Mon, 15 Sep 2025 12:48:10 GMT

Hi @Ronein ,have you come up with your final answer? I have my solution here (and this is continued from my last thread and step, which was create analysis dataset), today the final step is using proc sql union to produce the final report. I think the code in my last thread(analysis dateset) and this thread(final report) could be the answer to your question. 😀Please kindly let me know if I answer your question, thanks! The code and output are as follows:

/*create separate dataset for
loan1 and loan2, and calculate date
and select rows according to requirement*/
proc sql;
create table want2 as
select custid,
       offerdate,
       offeramnt,
       loandate1,
       loansamnt1,
       intnx('day',loandate1,-7)
          as loand1calc format=date9.
   from want1;
select * from want2;
quit;
proc sql;
create table want3 as
select custid,
       offerdate,
       offeramnt,
       loandate2,
       loansamnt2,
       intnx('day',loandate2,-7)
          as loand2calc format=date9.
   from want1
   where loandate2 ^=. and
         loansamnt2 ^=.;
select * from want3;
quit;
data want2a;
   set want2;
   if offerdate>loand1calc;
run;
proc print data=want2a;run;
data want3a;
   set want3;
   if offerdate>loand2calc;
run;
proc print data=want3a;run;

/*create final report table 
per request using sql subquery and sql union*/

proc sql;
select custid,
       offerdate,
       offeramnt,
       loandate1 as loandate
                format=date9.,
       loansamnt1 as loansamnt
   from want2a
   where offeramnt in(select max(offeramnt) 
                         from want2a
                         group by custid)
union
select custid,
       offerdate,
       offeramnt,
       loandate2 as loandate
               format=date9.,
       loansamnt2 as loansamnt
   from want3a
   where offeramnt in(select max(offeramnt) 
                         from want3a
                         group by custid);
quit;

Re: Max per loan in last 7 days

Kurt_Bremser — Mon, 15 Sep 2025 13:27:29 GMT

@dxiao2017 :

Please run this:

Data have;
Format date ddmmyy10.;
Input custid date :date9. loansAmnt offerAmnt;
Cards;
111 01JUL2025 . 50000
111 03jul2025 . 22000
111 08jul2025 . 19000
111 09jul2025 5000 .
222 01jul2025 . 40000
222 03jul2025 . 28000
222 04jul2025 13000 .
222 08jul2025 . 27000
222 09jul2025 35000 .
;

data want;
set have;
by custid;
array ams [1:30000] _temporary_;
if first.custid
then do _date = 1 to 30000;
  ams[_date] = 0;
end;
if offeramnt ne . then ams[date] = offeramnt;
if loansamnt ne .
then do _date = date - 7 to date;
  put _date ams[_date];
  if ams[_date] > maxamnt
  then do;
    maxamnt = ams[_date];
    offerdate = _date;
  end;
end;
if loansamnt;
drop _date offeramnt;
format offerdate ddmmyy10.;
run;

proc print data=want noobs;
var custid offerdate maxamnt date loansamnt;
run;

Then compare the results with yours, and the respective complexity of the codes.

Re: Max per loan in last 7 days

dxiao2017 — Mon, 15 Sep 2025 16:58:40 GMT

Hi @Kurt_Bremser , thanks a lot for your suggestion, I run your code and it works perfect 😀, I copy and paste the code and result here:

Data have;
Format date ddmmyy10.;
Input custid date :date9. loansAmnt offerAmnt;
Cards;
111 01JUL2025 . 50000
111 03jul2025 . 22000
111 08jul2025 . 19000
111 09jul2025 5000 .
222 01jul2025 . 40000
222 03jul2025 . 28000
222 04jul2025 13000 .
222 08jul2025 . 27000
222 09jul2025 35000 .
;
run;
proc print data=have;run;
data want;
   set have;
   by custid;
   array ams [1:30000] _temporary_;
   if first.custid then do _date = 1 to 30000;
      ams[_date] = 0;
   end;
   if offeramnt ne . then ams[date] = offeramnt;
   if loansamnt ne . then do _date = date - 7 to date;
      put _date ams[_date];
      if ams[_date] > maxamnt then do;
         maxamnt = ams[_date];
         offerdate = _date;
      end;
   end;
   if loansamnt;
   drop _date offeramnt;
   format offerdate ddmmyy10.;
run;
proc print data=want noobs;
var custid offerdate maxamnt date loansamnt;
run;

I wrote my code that way just in case my job decides how much to pay me according to how many rows of code I can write for a project 😀, I am kidding. I do not have a job now and want to find one. I think your code works perfect. But the logic, syntax, and techniques are difficult to understand. A lot of experience and practice is needed before one can write code like this. Could you explain the logic of your code a little bit @Kurt_Bremser , thanks a lot!

Re: Max per loan in last 7 days

Kurt_Bremser — Tue, 16 Sep 2025 11:33:42 GMT

The key element is an array used to store date-related values. Keep in mind that dates are counts of days and can therefore easily be used as indexes into an array. For all practical date values, the size of the array will be rather small (my 30000 element array is just 240K).

/* create want */
data want;
/* read have */
set have;
/* group by custid */
by custid;
/* define the array */
array ams [1:30000] _temporary_;
/* clear the array at the start of a new group */
if first.custid
then do _date = 1 to 30000;
  ams[_date] = 0;
end;
/* store offer amount for a given date in the array */
if offeramnt ne . then ams[date] = offeramnt;
/* if a loan is given, iterate through the array for the preceding 7 days */
if loansamnt ne .
then do _date = date - 7 to date;
  /* determine the maximum offer amount, and keep the date from it */
  /* maxamnt is always missing before the DO loop starts */
  if ams[_date] > maxamnt
  then do;
    maxamnt = ams[_date];
    offerdate = _date;
  end;
end;
/* only proceed further (to the implicit OUTPUT) when a loan was given */
if loansamnt;
drop _date offeramnt;
format offerdate ddmmyy10.;
run;