Max per loan in last 7 days

[dxiao2017]

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:

[dxiao2017]

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;

[Kurt_Bremser]

@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.

[dxiao2017]

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!

[Kurt_Bremser]

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;

[dxiao2017]

So the basic ideas are: ( 1) create an array that has columns of offer amount according to offer dates, and set them to 0, (2) loop over the dates (i.e., dates as index of the array) and fill in the offer amount of correspondence dates, 3) calculate the dates interval, which is 7 days, and find out the offer amount occurred in the time interval, and keep comparing all of the offer amount until the max amount is found, and then fill in the correspondence date. These are very advanced logic and technique, thanks a lot for reply, Kurt_Bremser! It's good learning experience for me and helps me understand how to improve my code and thinking!