Customer ownership table

[Ronein]

Hello

The "have" data set includes information about ownership structure of each customer in the bank.

It includes the columns:

Customer_number,ID_number,Wealth

As can see each customer can have multiple rows If there are multiple ID's under customer .

I want to create a new dats set called "want" with the following columns:

Customer_number

Nr_ID

Nr_Related_customers

customer_wealth

Total_Wealth_From_Related_Customers

As can see each customer will have only one row in "want" data set.

Nr_ID  be calculated by :count(*) as Nr_ID

customer_wealth be calculated by :Max(Wealth) as Customer_Wealth

My question:

What is the way to calculate columns:

"Nr_Related_customers"  ,

"Total_Wealth_From_Related_Customers"

For example:

Customer_number 1: 

There are 2 ID's under customer 1:111,222

ID 111 belongs also to another customer :2

ID 222 belong also to another customer:3

So there are total 3 customers whoch are related to customer 111

and the total wealth of these 3 customers is 100+200+300=600

Data have;		
input  Customer_number   ID_number   Wealth;		
cards;		
1 111 100
1 222 100
2 111 200
3 222 300
4 333 400
5 444 500
5 555 500
;		
Run;		

data want;				
input  Customer_number Nr_ID Nr_Related_customers customer_wealth Total_Wealth_From_Related_Customers;				
cards;				
1 2 3 100	600
2 1 2 200	300
3 1 2 300	400
4 1 1 400	400
5 2 1 500	500
;
Run;

[Ksharp · Accepted solution]

Data have;		
input  Customer_number   ID_number   Wealth;		
cards;		
1 111 100
1 222 100
2 111 200
3 222 300
4 333 400
5 444 500
5 555 500
;		
Run;

proc sql;
create table part1 as
select Customer_number,count(distinct ID_number) as Nr_ID 
 from have 
  group by Customer_number;

create table part2 as
select a.Customer_number,count(distinct b.Customer_number) as Nr_Related_customers 
 from have as a,have as b
  where a.ID_number=b.ID_number
   group by a.Customer_number;

create table part3 as
select distinct Customer_number,Wealth as customer_wealth 
 from have;

create table part4 as
select a.Customer_number,sum(b.customer_wealth) as Total_Wealth_From_Related_Custom
from
(
select distinct a.Customer_number, b.Customer_number as  _Customer_number
 from have as a,have as b
  where a.ID_number=b.ID_number
) as a left join part3 as b
on a._Customer_number=b.Customer_number
group by a.Customer_number;
quit;

data want;
 merge part1-part4;
 by Customer_number;
run;

[Ronein]

Thanks,
As I see the key here is to use CARTESIAN JOIN