BookmarkSubscribeRSS Feed
Ronein
Onyx | Level 15

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

Ronein_0-1757053058151.png

 

 

 

 

 

 

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;

 

1 REPLY 1
Ksharp
Super User

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;

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 79 views
  • 0 likes
  • 2 in conversation