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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 166 views
  • 0 likes
  • 2 in conversation