BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14

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;

 

Ronein_0-1656687573729.png

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

2 REPLIES 2
Ksharp
Super User
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
Meteorite | Level 14
Thanks,
As I see the key here is to use CARTESIAN JOIN

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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
  • 2 replies
  • 419 views
  • 1 like
  • 2 in conversation