Calculate column Relevant_offer_to_loan

[Ronein]

Hello

I have 2 tables- loans tables (loans that customer take) and offer table (offers that customer get on digital).

Note that customer can take loan on digital or in branch (so if take in branch then it might happens that no have digital offer).

I combine these 2 tables in order to see all events of loans and offers together in one table.

My task-

I want to calculate for each loan what was the relevant offer the customer received 7 days before the loan (included day of loan).

In the case that customer has one loan then it is very simple and just check  offers 7 days before loan and take max of them.

In the case that customer has more than one loan then it is more complicated and here are the rules-

Look at offers 7 days prior to loan(included day of loan)

If other loan occurred before then all offers till previous loan are not relevant for the offer for this loan

Here is desired table: Need to create column -Relevant_offer_to_loan

Data loans_tbl;
format date_Loan ddmmyy10.;
Input CUSTID	date_Loan :date9.	Loan_Amnt;
cards;
111 08JUL2025 30000
222 17JUL2025 15000
222 20JUL2025 10000
333 28JUL2025 17000
444 02JUL2025 5000
444 03JUL2025 12000
444 29JUL2025 18000
555 14JUL2025 38000
555 15JUL2025 30000
555 27JUL2025 18000
555 01JUL2025 100000
;
Run;



Data offers_tbl;
format date_offer ddmmyy10.;
Input CUSTID	date_offer :date9.	Offer_Amnt;
cards;
111 02JUL2025 20000
111 05JUL2025 20000
111 06JUL2025 40000
111 09JUL2025 8000
111 28JUL2025 15000
222 15JUL2025 15000
222 18JUL2025 40000
333 22JUL2025 20000
444 02JUL2025 7000
444 28JUL2025 50000
555 05JUL2025 50000
555 09JUL2025 48000
555 10JUL2025 55000
555 14JUL2025 38000
555 22JUL2025 20000
555 27JUL2025 18000
;
Run;


proc sql;
create table want1 as
select COALESCE(a.CUSTID,b.CUSTID)  as CUSTID,
       COALESCE(date_Loan,date_offer) as date format=ddmmyy10.,
	   a.Loan_Amnt,
	   b.Offer_Amnt
from loans_tbl as a
full join offers_tbl as b
on a.date_Loan=b.date_offer and a.CUSTID=b.CUSTID
order by calculated CUSTID,calculated date
;
quit;

[Ksharp]

If your dataset is not big, that would be very easy by using SQL.

Otherwise, you need to resort to use other skills.

Data loans_tbl;
format date_Loan ddmmyy10.;
Input CUSTID	date_Loan :date9.	Loan_Amnt;
cards;
111 08JUL2025 30000
222 17JUL2025 15000
222 20JUL2025 10000
333 28JUL2025 17000
444 02JUL2025 5000
444 03JUL2025 12000
444 29JUL2025 18000
555 14JUL2025 38000
555 15JUL2025 30000
555 27JUL2025 18000
555 01JUL2025 100000
;
Run;



Data offers_tbl;
format date_offer ddmmyy10.;
Input CUSTID	date_offer :date9.	Offer_Amnt;
cards;
111 02JUL2025 20000
111 05JUL2025 20000
111 06JUL2025 40000
111 09JUL2025 8000
111 28JUL2025 15000
222 15JUL2025 15000
222 18JUL2025 40000
333 22JUL2025 20000
444 02JUL2025 7000
444 28JUL2025 50000
555 05JUL2025 50000
555 09JUL2025 48000
555 10JUL2025 55000
555 14JUL2025 38000
555 22JUL2025 20000
555 27JUL2025 18000
;
Run;


proc sort data=loans_tbl;
by CUSTID date_Loan;
run;
proc sort data=offers_tbl;
by CUSTID date_offer;
run;
data have ;
 merge loans_tbl(rename=(date_Loan=date)) offers_tbl(rename=(date_offer=date));
 by CUSTID date;
run;

proc sql;
create table want as
select *,case when not missing(Loan_Amnt) then
(select max(Offer_Amnt) from have where CUSTID=a.CUSTID and date between a.date-7 and a.date)
else . end as Relevant_off_to_loan
 from have as a ;
quit;

[Ronein]

If my data loans have 1000 rows and data offers have 3000 rows. Is it big?