BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Trishjais
Obsidian | Level 7

Hi,

I have a dataset as following.

customer IdAccount

A

100
B100
C100
D100

 

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

Customer_IDAccountJoint Customers1AccountJoint Customers2AccountJoint Customers3
A100BCD
B100ACD
C100ABD
D100ABC

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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;

Capture d’écran 2020-04-14 à 08.42.25.png

Best,

View solution in original post

2 REPLIES 2
ed_sas_member
Meteorite | Level 14

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;

Capture d’écran 2020-04-14 à 08.42.25.png

Best,

Trishjais
Obsidian | Level 7
Thanks that worked..

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 456 views
  • 0 likes
  • 2 in conversation