Hello
I have 2 tables- loans tables (loans that customer take) and offer table (offers that customer get on digital).
I want to combine these 2 tables in order to see the offers each customer get previous to each loan.
Offers that were made after loan are not relevant.
My task-
I want to create sequence number in the following rules-
For each customer (CustID)- create seq numbers based on loans date only.
So, when there is a new loan then seq number jump by 1
Note- After last loan seq will be null (see customer 2)
desired table
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
;
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 28JUL2025 50000
;
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;
If you only consider about DATE, that would be very easy.
CODE EDITED.
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 ; 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 28JUL2025 50000 ; Run; data have ; merge loans_tbl(rename=(date_Loan=date)) offers_tbl(rename=(date_offer=date)); by CUSTID date; run; data temp; set have; by CUSTID; if first.CUSTID then seq=0; if first.CUSTID or lag(Loan_Amnt) ne . then seq+1; run; proc sql; create table want as select *,case when sum(Loan_Amnt)=. then . else seq end as new_seq from temp group by CUSTID,seq order by CUSTID,date; quit;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.