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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.