create seq number for each ownership structure

[Ronein]

Hello

Let's say that Dave(Id number 1) and Suzan(id number 2) are married.

They want to open a few bank accounts.

On 25JAN2025 Dave+Suzan applied to open bank account

On 05FEB2026 Dave+Suzan applied to open bank account

On 01JAN2026 Dave (without Suzan) applied to open bank account

On 17DEC2025 Suzan (without Dave) applied to open bank account

On 05JAN2025 Suzan (without Dave) applied to open bank account

As you can see the ownership of the application can be - Dave+Suzan  or Dave only or Suzan only

My task-

For  each  ownership I want to create a sequence number by date of application.

(ownership structure is defined by set of IDS)

What is the way to do it please?

Here is the data set and want data set

Data have;
format date date9.;
input ID	appidentity	date :date9. ;
cards;
1 123 05FEB2026
2 123 05FEB2026
1 999 25JAN2025
2 999 25JAN2025
1 456 01JAN2026
2 678 05JAN2025
2 777 17DEC2025
;
Run;


/**Concateneate IDS to build ownership key**/
/**Rank the applicatioDates with each ownership key(newest=1 so descending)
/**Attach the rank back to all rows**/

proc sort data=have ; by appidentity ;Run;
data Vector_IDS_t(drop=ID);
set have ;
by appidentity;
length Vector_IDS $ 300;
retain Vector_IDS;
Vector_IDS=catx(',',Vector_IDS,ID);
if last.appidentity then do; 
output;
call missing(Vector_IDS);
end;
run;


proc sql;
create table t1 as
select a.*,b.Vector_IDS
from have as a
left join Vector_IDS_t  as b
on a.appidentity=b.appidentity
;
quit;


proc sort data=t1;
by Vector_IDS descending date;
Run;

Data want;
set t1;
by Vector_IDS;
retain order;
if first.Vector_IDS then order=0;
order+1;
Run;

/*Data want;*/
/*format date date9.;*/
/*input ID	appidentity	date :date9.  Order;*/
/*cards;*/
/*1 999 25JAN2025 2*/
/*2 999 25JAN2025 2*/
/*1 123 05FEB2026 1*/
/*2 123 05FEB2026 1*/
/*1 456 01JAN2026 1*/
/*2 678 05JAN2025 1*/
/*2 777 17DEC2025 2*/
/*;*/
/*Run;*/





















 

[LinusH]

Sorry, I don't understand how you wish calculate "order".
Can elaborate a bit more?

[Ronein]

Hi, I found the solution,

Maybe have other offers for solution?

Data have;
format date date9.;
input ID	appidentity	date :date9. ;
cards;
1 123 05FEB2026
2 123 05FEB2026
1 999 25JAN2025
2 999 25JAN2025
1 456 01JAN2026
2 678 05JAN2025
2 777 17DEC2025
;
Run;

/**Concateneate IDS to build ownership key**/
/**Rank the applicatioDates with each ownership key(newest=1 so descending)
/**Attach the rank back to all rows**/

proc sort data=have ; by appidentity ;Run;
data Vector_IDS_t(drop=ID);
set have ;
by appidentity;
length Vector_IDS $ 300;
retain Vector_IDS;
Vector_IDS=catx(',',Vector_IDS,ID);
if last.appidentity then do; 
output;
call missing(Vector_IDS);
end;
run;


proc sql;
create table t1 as
select distinct a.date,b.Vector_IDS
from have as a
left join Vector_IDS_t  as b
on a.appidentity=b.appidentity
;
quit;

proc sort data=t1;
by Vector_IDS descending date;
Run;

Data t2;
set t1;
by Vector_IDS;
retain order;
if first.Vector_IDS then order=0;
order+1;
Run;


proc sql;
create table t0 as
select a.*,b.Vector_IDS
from have as a
left join Vector_IDS_t  as b
on a.appidentity=b.appidentity
;
quit;


proc sql;
create table want as
select a.*,b.order
from t0 as a
left join t2  as b
on a.Vector_IDS=b.Vector_IDS  and a.date=b.date
;
quit;




/*Data want;*/
/*format date date9.;*/
/*input ID	appidentity	date :date9.  Order;*/
/*cards;*/
/*1 999 25JAN2025 2*/
/*2 999 25JAN2025 2*/
/*1 123 05FEB2026 1*/
/*2 123 05FEB2026 1*/
/*1 456 01JAN2026 1*/
/*2 678 05JAN2025 1*/
/*2 777 17DEC2025 2*/
/*;*/
/*Run;*/


 

[Ksharp]

Why 999's order number is 2 ? Aren't you count it from past to the future?

Data have;
format date date9.;
input ID	appidentity	date :date9. ;
format date date9.;
cards;
1 123 05FEB2026
2 123 05FEB2026
1 999 25JAN2025
2 999 25JAN2025
1 456 01JAN2026
2 678 05JAN2025
2 777 17DEC2025
;
Run;

proc freq data=have noprint;
table appidentity*id/out=id nopercent;
run;
data new_id;
do until(last.appidentity);
 set id(keep=appidentity id);
 by appidentity;
 length new_id $ 100;
 new_id=catx('|',new_id,id);
end;
drop id;
run;
proc sql;
create table have2 as
select * from have  natural left join new_id 
 order by new_id,date,appidentity;
quit;
data want;
 set have2;
 by new_id date;
 if first.new_id then order=0;
 if first.date then order+1;
run;

[Ronein]

The newest (date close to today) get value 1