create seq number by customer ID+Event date

Ronein — Fri, 05 Sep 2025 06:17:44 GMT

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;

Re: create seq number by customer ID+Event date

Ksharp — Fri, 05 Sep 2025 08:14:29 GMT

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;

topic Re: create seq number by customer ID+Event date in SAS Programming

create seq number by customer ID+Event date

Re: create seq number by customer ID+Event date