how I get joint account details

[Trishjais]

Hi,

I have a dataset as following.

customer Id	Account
A	100
B	100
C	100
D	100

I'm expecting to see all joint account holder's name for each individual customer as below.

Customer_ID	Account	Joint Customers1	AccountJoint Customers2	AccountJoint Customers3
A	100	B	C	D
B	100	A	C	D
C	100	A	B	D
D	100	A	B	C

An account can have any number of joint customer ( no fixed column number of Joint Customer1, Joint Customer2 & so on.

here in this case an account is shared by 4 customers, it can have as many as 20+. Please suggest a dynamic way of achieving this.

[ed_sas_member · Accepted solution]

Hi @Trishjais 

Here is an approach to achieve this:

data have;
	input customer_Id $	Account;
	datalines;
A 100
B 100
C 100
D 100
D 200
E 200
F 200
;
run;

proc sql;
	create table have2 as
	select a.*, b.customer_id as Joint_customer
	from have as a, have as b
	where a.customer_Id ne b.customer_Id and a.account=b.account
	order by a.account, a.customer_Id, b.customer_id;
quit;

proc transpose data=have2 out=want (drop=_:) prefix=AccountJoint_Customers;
	var Joint_customer;
	by account customer_Id;
run;

Best,

[Trishjais]

Thanks that worked..